winform 批量导入本地sql文件，批量导入mdb（access）文件到sqlserver

0.数据库连接

 1  private void button1_Click(object sender, EventArgs e)
 2         {
 3             this.btnUpdate.Enabled = false;
 4             #region 数据库连接
 5             connStr.Append("Data Source=");
 6             connStr.Append(txtServer.Text);
 7             connStr.Append(";");
 8             connStr.Append("Initial Catalog=");
 9             connStr.Append(txtDataBase.Text);
10             connStr.Append(";");
11             connStr.Append("User ID=");
12             connStr.Append(txtUser.Text);
13             connStr.Append(";");
14             connStr.Append("Password=");
15             connStr.Append(txtPass.Text);
16             this.labShow.Text = "正在查找文件......";
17             using (SqlConnection conn = new SqlConnection(connStr.ToString()))
18             {
19                 //MessageBox.Show(connStr.ToString());
20                 ListSql = new List<string>();
21                 ListMdb = new List<string>();
22                 GetDirectories(Application.StartupPath);
23                 //ListSql.Sort(new MySort());
24 
25                 foreach (var item in ListSql)
26                 {
27                     GetSqlFile(item);
28                 }
29                 foreach (var item in ListMdb)
30                 {
31                     ReadDataToSQL(item);
32                 }
33                 MessageBox.Show("升级成功......");
34                 this.btnUpdate.Enabled = true;
35                 Application.Exit();
36             }
37             #endregion
38 
39         }

View Code

1.查找文件与目录

 1  #region 查找目录与文件
 2         public void GetDirectories(string path)
 3         {
 4             
 5             string[] fileNames = Directory.GetFiles(path);
 6             string[] directories = Directory.GetDirectories(path);
 7             foreach (string file in fileNames)
 8             {
 9                 string str = System.IO.Path.GetFullPath(path);
10                 if (System.IO.Path.GetExtension(file).ToLower() == ".sql")
11                 {
12                     ListSql.Add(file);
13                     //GetSqlFile(file);
14                 }
15                 if (System.IO.Path.GetExtension(file).ToLower() == ".mdb")
16                 {
17                     //ReadDataToSQL(file);
18                     ListMdb.Add(file);
19                 }
20             }
21             
22             foreach (string dir in directories)
23             {
24                 GetDirectories(dir);
25             }
26         }
27         #endregion

View Code

2.获取本地脚本文件、执行sql文件

1  foreach (var item in ListSql)
2                 {
3                     GetSqlFile(item);
4                 }

View Code

 #region 批量执行本地的sql文件到sqlServer数据库
        /// <summary>
        /// 获取本地的脚步文件
        /// </summary>
        private void GetSqlFile(string path)
        {
            try
            {
                string obj_file = this.GetFileReader(path);
                obj_file = obj_file.Replace("GO", " | ").Replace("go", " | ");//替换符号
                string[] obj_filelist = obj_file.Split('|');
                foreach (string str in obj_filelist)
                {
                    this.labShow.Text ="正在执行脚本"+ Path.GetFileName(path)+"文件......";
                    this.ExecSQL(str);
                }
               // MessageBox.Show("导入成功!", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
            }
            catch
            {

            }
        }
        /// <summary>
        /// 获取文件内容
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <returns>返回sql文件的执行语句</returns>
        public string GetFileReader(string path)
        {
            try
            {
                using (StreamReader sr = new StreamReader(path, Encoding.Default))
                {
                    String line = sr.ReadToEnd();

                    return line;
                }
            }
            catch (Exception ex)
            {
                return "";
            }
        }
        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="SQL"></param>
        public void ExecSQL(string SQL)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(@"Data Source=(local);Initial Catalog=数据库名称;User ID=用户名;Password=密码"))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(SQL, conn))
                    {
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "|" + ex.Source);

            }
        }
        #endregion

3.获取mdb本地文件，执行读取与插入操作

 1 #region 批量执行本地的mdb文件到sqlServer数据库
 2         /// <summary>
 3         ///读取access数据库中的数据并插入到sqlServer数据库表中
 4         /// </summary>
 5         /// <param name="file">文件路径</param>
 6         private void ReadDataToSQL(string file)
 7         {
 8             try
 9             {
10                 this.labShow.Text = "正在执行脚本" + Path.GetFileName(file) + "文件......";
11                 string StrConn = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data source=" + file + "";
12                 OleDbConnection adoConn = new OleDbConnection(StrConn);
13                 //adoConn.ConnectionString = StrConn;
14                 adoConn.Open();
15                 DataTable dt = adoConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
16                 for (int i = 0; i < dt.Rows.Count; i++)
17                 {
18                     String tableName = dt.Rows[i]["TABLE_NAME"].ToString();
19 
20                     //从ACCESS数据库查询数据
21                     string sql1 = "select * from " + tableName;
22                     DataSet Myds = new DataSet();
23                     OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql1, adoConn);
24                     //填充数据
25                     dataAdapter.Fill(Myds, tableName);
26                     //插入数据
27                     BulkData(Myds, tableName);
28                 }
29             }
30             catch (Exception ex)
31             {
32                 MessageBox.Show(ex.ToString());
33             }
34         }
35         /// <summary>
36         /// 执行Sql语句
37         /// </summary>
38         /// <param name="_Ds">DataSet数据集</param>
39         /// <param name="_TableName">表名称</param>
40         /// <returns>是否成功</returns>
41         public bool BulkData(DataSet _Ds, string _TableName)
42         {
43             SqlConnection SqlCon = new SqlConnection();
44             SqlCon.ConnectionString = connStr.ToString();
45             SqlCon.Open();
46             SqlBulkCopy SqlBulk = new SqlBulkCopy(SqlCon);
47             SqlBulk.DestinationTableName = _TableName;
48             try
49             {
50                 SqlBulk.WriteToServer(_Ds.Tables[0], DataRowState.Unchanged);
51                 return true;
52             }
53             catch(Exception ex)
54             {
55                 MessageBox.Show(ex.ToString());
56                 return false;
57             }
58             finally
59             {
60                 SqlCon.Close();
61                 SqlCon.Dispose();
62                 SqlBulk.Close();
63             }
64         }
65 #endregion

View Code