从SQLserver中导出表数据到Access

每篇随便都得有个背景吧，这次做一个项目时，突然碰到这个样一个问题，需要将本地sqlserver中的 数据 导出 到access后，再传输access 数据 库，所以就在想怎样实现这样的操作。后面经过在网上查找了一些资料结合以前的知识，搞了这样一个东西出来； 1 /// su

每篇随便都得有个背景吧，这次做一个项目时，突然碰到这个样一个问题，需要将本地sqlserver中的数据导出到access后，再传输access数据库，所以就在想怎样实现这样的操作。后面经过在网上查找了一些资料结合以前的知识，搞了这样一个东西出来；

  1         /// 

  2         /// 从sqlserver中导出数据到access
  3         /// state=0 Jzjl 导出菜品信息表
  4         /// state=1 lbxf_jz 导出收银信息表
  5         /// 
  6         /// 
  7         /// 
  8         /// 
  9         /// 
 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 )