C#把Acess数据库中的表全部导入到Sql库中

最新推荐文章于 2022-05-17 18:35:45 发布

陈滚滚

最新推荐文章于 2022-05-17 18:35:45 发布

阅读量528

点赞数

CC 4.0 BY-SA版权

文章标签： Acess C# Sql

本文链接：https://blog.youkuaiyun.com/mukoo/article/details/8647438

本文介绍了一个C#方法，用于将Access mdb文件中的数据表批量导入到SQL Server数据库中。通过建立SqlConnection，配置连接字符串，并执行SQL命令来实现数据迁移。首先开启SQL Server的高级选项和Ad Hoc Distributed Queries，然后遍历Access数据库中的表，逐个执行`DROP TABLE`和`INSERT INTO`操作，最后关闭高级选项。整个过程实现了从Access到SQL Server的数据平滑迁移。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//sql服务信息

public struct StructServerConfig
    {
        public string serverName;
        public string userName;
        public string password;
        public string dbType;
        public string dbFolderPath;
        public string serverType;
    }

        /// <summary>
        /// 根据mdb路径及数据库名，把Access数据导入到对应的Sql库中
        /// </summary>
        /// <param name="serverConfig"></param>
        /// <param name="filePath">Acess数据库mdb文件路径</param>
        /// <param name="dbName">sql库名</param>
        public bool ConvertAccess2Sql(StructServerConfig serverConfig, string filePath, string dbName)
        {

            private SqlConnection con = null;
            bool flag = true;
            try
            {

                string connString = "Data Source=" + serverConfig.serverName + ";Initial Catalog=master;User ID=" +
                                    serverConfig.userName + ";Password=" + serverConfig.password + ";DataBase =" + dbName;
                con = new SqlConnection(connString);

                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
                List<String> tableNameList = new List<String>();
                tableNameList = GetShemaTableName(filePath);
                string startAdvancedOptions = "exec sp_configure 'show advanced options',1 \r\n reconfigure ";
                string startAd = "exec sp_configure 'Ad Hoc Distributed Queries',1 \r\n reconfigure";

ExecuteSql(startAdvancedOptions, con);
ExecuteSql(startAd, con);

                for (int i = 0; i < tableNameList.Count; i++)
                {
                    try
                    {
                        //SELECT id,name INTO table(tid,tname)FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
                        //'Data Source="c:\Shop.mdb";User ID=Admin;Password=' )...ShopGoods
                        //其中，table是SQL Server中用来存储Access导入数据的数据表名，ShopGoods是指要导入到SQL Server中的Access数据库中的数据表名。
                        //前面的三个点不能省略
                        string command1 = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[" + tableNameList[i] + "]')"
                        + "and OBJECTPROPERTY(id, N'IsUserTable') = 1)"
                        + "drop table [dbo].[" + tableNameList[i] + "]";

                        string command2 = "select * into dbo." + tableNameList[i] + " From OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source= "
                                          + filePath + " ')..." + tableNameList[i];
                        ExecuteSql(command1, con);
                        ExecuteSql(command2, con);
                    }
                    catch
                    {

                    }
                }
                string endAdvancedOptions = "exec sp_configure 'Ad Hoc Distributed Queries',0 \r\n reconfigure";
                string endAd = "exec sp_configure 'show advanced options',0 \r\n reconfigure ";
                ExecuteSql(endAd, con);
                ExecuteSql(endAdvancedOptions, con);
                flag = true;
            }
            catch
            {

            }
            return flag;
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="con"></param>
        private void ExecuteSql(string sql, SqlConnection con)
        {
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.ExecuteNonQuery();
        }

       //获取Acess库中所有表名
        public List<string> GetShemaTableName(string filePath)
       {
           OleDbConnection conn = new OleDbConnection();
           List<string> tableNameLst = new List<string>();
            try
            {
                //获取数据表
                conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + "";
               // conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:DataBase Password='" + database_password + "Data Source=" + database_path;
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                DataTable shemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                int n = shemaTable.Rows.Count;
                int m = shemaTable.Columns.IndexOf("TABLE_NAME");
                for (int i = 0; i < n; i++)
                {
                    DataRow m_DataRow = shemaTable.Rows[i];
                    tableNameLst.Add(m_DataRow.ItemArray.GetValue(m).ToString());
                }
                return tableNameLst;
            }
            catch (OleDbException ex)
            {
                MessageBox.Show("指定的限制集无效:\n" + ex.Message);
                return null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }