每篇随便都得有个背景吧,这次做一个项目时,突然碰到这个样一个问题,需要将本地sqlserver中的数据导出到access后,再传输access数据库,所以就在想怎样实现这样的操作。后面经过在网上查找了一些资料结合以前的知识,搞了这样一个东西出来;
1 /// <summary>
2 /// 从sqlserver中导出数据到access
3 /// state=0 Jzjl 导出菜品信息表
4 /// state=1 lbxf_jz 导出收银信息表
5 /// </summary>
6 /// <param name="host"></param>
7 /// <param name="name"></param>
8 /// <param name="password"></param>
9 /// <param name="root"></param>
10 static void BackupA(string tablename, int state, string connection = "Data Source=.;Initial Catalog=mpcy;Integrated Security=True")
11 {
12 string path = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
13 string file = "mpcy.model";
14 string root = path + file;
15
16 Console.WriteLine("文件路径:" + root);
17 //备份Access数据库
18 if (state == 0) { tablename = "Jzjl"; chkandcrt(root); } else { tablename = "lbxf_jz"; }
19
20 root = BakPath + "\\mpcy.dat";
21 string accesssqlconn = @"Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:DataBase Password=xhjxjf168;Data Source=" + root + ";";
22 //SqlHelper.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", "127.0.0.1", "mpcyTemp", "sa", "12345");
23 //connection = string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True", "mpcyTemp");
24 SqlHelper.ConnectionString = connection;
25 //从sqlserver中读出数据到datatable
26 OleDbConnection conn = new OleDbConnection(accesssqlconn);
27 OleDbCommand cmd = conn.CreateCommand();
28 try
29 {
30 DataTable user = SqlHelper.ExecuteTable(CommandType.Text, string.Format("select * from {0}", tablename), null);
31 int i = 0;
32 if (user.Rows.Count > 0)
33 {
34 using (conn)
35 {
36 using (cmd)
37 {
38 conn.Open();
39
40 //每次导入前,先清空数据库表
41 string sql = string.Format("delete * from {0}", tablename); cmd.CommandText = sql; cmd.ExecuteNonQuery();
42
43 OleDbDataAdapter adp = new OleDbDataAdapter(); adp.SelectCommand = new OleDbCommand(string.Format("select * from {0}", tablename), conn); OleDbCommandBuilder cb = new OleDbCommandBuilder(adp); DataSet data = new DataSet();
44 //加载access中的数据表,并通过追加的方式放入dataset中
45 adp.Fill(data);
46 for (int j = 0; j < user.Rows.Count; j++)
47 {
48 //for (int l = 0; l < user.Columns.Count; l++)
49 //{
50 // if (l > user.Columns.Count - 1)
51 // break;
52 DataRow dr = data.Tables[0].NewRow();
53 for (int k = 0; k < dr.Table.Columns.Count; k++)
54 {
55 Type typ = user.Rows[j][k].GetType();
56 if (typ == typeof(double) || typ == typeof(int) || typ == typeof(decimal) || typ == typeof(float))
57 { if (string.IsNullOrEmpty(user.Rows[j][k].ToString())) { dr[k] = "0"; } else { dr[k] = user.Rows[j][k].ToString(); } }
58 else
59 { if (string.IsNullOrEmpty(user.Rows[j][k].ToString())) { dr[k] = " "; } else { dr[k] = user.Rows[j][k].ToString(); } }
60 }
61 //dr["UserID"] = user.Rows[j][l].ToString();
62 //dr["UserName"] = user.Rows[j][l + 1].ToString();
63 //网dataset中添加数据
64 data.Tables[0].Rows.Add(dr);
65 //}
66 }
67 data.Tables[0].TableName = tablename;
68 i = adp.Update(data.Tables[0]); //通过update方法Insert或update数据
69 Console.WriteLine(i.ToString());
70 }
71 }
72 }
73 }
74 //判断异常类型
75 catch (SqlException er)
76 {
77 ErrorTime++;
78 if (ErrorTime > 3)
79 { FileLog.Logger.Write(er); FileLog.Logger.Write("错误次数太多,退出程序!"); Console.WriteLine("错误次数太多,退出程序!"); }
80 else
81 {
82 if (ErrorTime % 2 == 0)
83 {
84 FileLog.Logger.Write("尝试使用MSSQL身份验证登陆数据库!"); FileLog.Logger.Write(er); Console.WriteLine("SQLSERVER打开失败,尝试使用MSSQL方式登录数据库。");
85 string sqlcon = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", ConfigHelper.DBHost, "mpcy", ConfigHelper.DBName, ConfigHelper.DBPwd);
86 //string sqlcon = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", "127.0.0.1", "mpcyTemp", "sa", "12345");
87 BackupA("", state, sqlcon);
88 }
89 else
90 { FileLog.Logger.Write("尝试使用WINDOWS身份验证登陆数据库!"); FileLog.Logger.Write(er); Console.WriteLine("SQLSERVER打开失败,尝试使用WINDOWS方式登录数据库。"); BackupA("", state); }
91 }
92 }
93 catch (Exception e)
94 {
95 FileLog.Logger.Write("数据导出失败!"); FileLog.Logger.Write(e); Console.WriteLine("数据导出失败!");
96 //throw (new Exception("数据导出失败,请联系IT部!"));
97 }
98 finally
99 {
100 conn.Close();
101 }
102 if (state == 0)
103 {
104 string sqlcon = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", ConfigHelper.DBHost, "mpcyTemp", ConfigHelper.DBName, ConfigHelper.DBPwd);
105 //string sqlcon = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", "127.0.0.1", "mpcyTemp", "sa", "12345");
106 BackupA("", 1, sqlcon);
107 }
108 }
109
110 /// <summary>
111 /// 检查并备份
112 /// </summary>
113 /// <param name="check"></param>
114 /// <param name="root"></param>
115 static void chkandcrt(string root)
116 {
117 try { if (!Directory.Exists(BakPath)) { Directory.CreateDirectory(BakPath); } if (!File.Exists(BakPath + "\\mpcy.dat")) { File.Copy(root, BakPath + "\\mpcy.dat"); } }
118 catch (Exception e)
119 { FileLog.Logger.Write(e); FileLog.Logger.Write("文件检查失败!"); Console.WriteLine("文件信息检查失败!"); }
120 }
代码不多,核心代码就这几句:
OleDbDataAdapter adp = new OleDbDataAdapter(); adp.SelectCommand = new OleDbCommand(string.Format("select * from {0}", tablename), conn); OleDbCommandBuilder cb = new OleDbCommandBuilder(adp); DataSet data = new DataSet();
44 //加载access中的数据表,并通过追加的方式放入dataset中
45 adp.Fill(data);
46 for (int j = 0; j < user.Rows.Count; j++)
47 {
48 //for (int l = 0; l < user.Columns.Count; l++)
49 //{
50 // if (l > user.Columns.Count - 1)
51 // break;
52 DataRow dr = data.Tables[0].NewRow();
53 for (int k = 0; k < dr.Table.Columns.Count; k++)
54 {
55 Type typ = user.Rows[j][k].GetType();
56 if (typ == typeof(double) || typ == typeof(int) || typ == typeof(decimal) || typ == typeof(float))
57 { if (string.IsNullOrEmpty(user.Rows[j][k].ToString())) { dr[k] = "0"; } else { dr[k] = user.Rows[j][k].ToString(); } }
58 else
59 { if (string.IsNullOrEmpty(user.Rows[j][k].ToString())) { dr[k] = " "; } else { dr[k] = user.Rows[j][k].ToString(); } }
60 }
61 //dr["UserID"] = user.Rows[j][l].ToString();
62 //dr["UserName"] = user.Rows[j][l + 1].ToString();
63 //网dataset中添加数据
64 data.Tables[0].Rows.Add(dr);
65 //}
66 }
67 data.Tables[0].TableName = tablename;
68 i = adp.Update(data.Tables[0]); //通过update方法Insert或update数据
69 Console.WriteLine(i.ToString());
这中间是这样的:先通过一个DataAdapter读取了Access数据库的结构,然后再通过从Sqlserver中读取到数据的DataTable将数据导入到这个被DataAdapter填充了结构的DataSet的表中。最后,通过这个DataAdapter来更新整个表,当然,表的结构要一致!!
另外,这里有一个OleDbCommandBuilder,根据网上解释:自动生成用于协调对 DataSet的更改与关联数据库的单表命令。也就是说,在我们这个例子中,我们的表结构是没有改变,但是后面新添加了数据,于是这个OleDbCommandBuilder就会自动将表单命令生成为Insert语句,于是就执行了插入命令,但是如果在我们初始化的DataSet中本来就存在数据,然后我们进行了对原始数据的改变,后面就会将命令变成Update。
这样,就将Sqlserver中的数据填充到了Access数据库中了。