欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

从SQLserver中导出表数据到Access

程序员文章站 2022-03-27 12:33:46
...

每篇随便都得有个背景吧,这次做一个项目时,突然碰到这个样一个问题,需要将本地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 )