C# 使用 SqlBulkCopy 类批量复制数据到数据库

最近公司需要优化导入的问题，由于之前使用的方式是生成 Insert 语句插入数据库，数据量小的时候还行，但是随着发展数据量渐渐大了，之前的方法性能就跟不上了，于是发现了 SqlBulkCopy 这个类。

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。

public class Conn

    {

        private static string StrConn

        {

            get

            {

                return ConfigurationManager.ConnectionStrings["StrConn"].ToString();

                //return ConfigurationManager.AppSettings["StrConn"].ToString();

            }

        }

        public static SqlConnection SqlConn

        {

            get

            {

                return new SqlConnection(StrConn);

            }

        }

    }

      public class SqlHelper

    {

        public DataTable GetDataTable(string sql)

        {

            DataTable dt = new DataTable();

            SqlConnection conn = null;

            SqlDataAdapter sda = null;

            try

            {

                conn = Conn.SqlConn;

                sda = new SqlDataAdapter(sql, conn);

                conn.Open();

                sda.Fill(dt);

            }

            catch (Exception ex)

            {

            }

            finally

            {

                if (conn != null)

                {

                    conn.Close();

                    conn.Dispose();

                }

                if (sda != null)

                {

                    sda.Dispose();

                }

            }

            return dt;

        }

        public DataSet GetDataSet(string sql)

        {

            DataSet ds = new DataSet();

            SqlConnection conn = null;

            SqlDataAdapter sda = null;

            try

            {

                conn = Conn.SqlConn;

                sda = new SqlDataAdapter(sql, conn);

                conn.Open();

                sda.Fill(ds);

            }

            catch (Exception ex)

            {

            }

            finally

            {

                if (conn != null)

                {

                    conn.Close();

                    conn.Dispose();

                }

                if (sda != null)

                {

                    sda.Dispose();

                }

            }

            return ds;

        }

        /// <summary>

        /// 使用事务插入方法

        /// </summary>

        /// <param name="dt">源数据</param>

        /// <param name="tableName">目标表名</param>

        public void InsertO(DataTable dt, string tableName)

        {

            using (SqlConnection conn = Conn.SqlConn)

            {

                using (SqlBulkCopy sqlBuleCopy = new SqlBulkCopy(conn.ConnectionString, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.Default | SqlBulkCopyOptions.UseInternalTransaction))

                {

                    try

                    {

                        //设置目标表名，即数据库表名

                        sqlBuleCopy.DestinationTableName = tableName;

                        //设置每一批次的行数，即达到指定的行数就插入一次数据库

                        sqlBuleCopy.BatchSize = ;

                        //设置超时之前完成的时间（秒）

                        sqlBuleCopy.BulkCopyTimeout = ;

                        for (int i = ; i < dt.Columns.Count; i++)

                        {

                            //设置源数据列与目标表的列的映射关系，第一个参数为源数据列，第二个参数为目标表列

                            sqlBuleCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);

                        }

                        sqlBuleCopy.WriteToServer(dt);

                    }

                    catch (Exception)

                    {

                    }

                }

            }

        }

        /// <summary>

        /// 未使用事务插入方法

        /// </summary>

        /// <param name="dt">源数据</param>

        /// <param name="tableName">目标表名</param>

        public void InsertT(DataTable dt, string tableName)

        {

            using (SqlConnection conn = Conn.SqlConn)

            {

                using (SqlBulkCopy sqlBuleCopy = new SqlBulkCopy(conn))

                {

                    try

                    {

                        conn.Open();

                        //设置目标表名，即数据库表名

                        sqlBuleCopy.DestinationTableName = tableName;

                        //设置每一批次的行数，即达到指定的行数就插入一次数据库

                        sqlBuleCopy.BatchSize = ;

                        //设置超时之前完成的时间（秒）

                        sqlBuleCopy.BulkCopyTimeout = ;

                        for (int i = ; i < dt.Columns.Count; i++)

                        {

                            //设置源数据列与目标表的列的映射关系，第一个参数为源数据列，第二个参数为目标表列

                            sqlBuleCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);

                        }

                        sqlBuleCopy.WriteToServer(dt);

                    }

                    catch (Exception)

                    {

                        conn.Close();

                        conn.Dispose();

                    }

                    finally

                    {

                        conn.Close();

                        conn.Dispose();

                    }

                }

            }

        }

    }

我的源数据是使用 Excel 导入的数据，导入的方法就不说了，不是这里的重点，之后我会专门总结一下 Excel 导入的方法。然后查询目标表需要插入数据的字段，修改源数据表的字段名和类型，然后调用批量插入的方法。

protected void btnImport_Click(object sender, EventArgs e)

        {

            try

            {

                //获取导入的数据

                DataSet ds = BI.ExecleDs(savePath, "");

                if (ds != null && ds.Tables.Count > )

                {

                    DataTable dt = ds.Tables[];

                    //查询目标表需要插入的字段

                    string sql = " select U_No,U_Name,U_Pwd,P_Id from UserInfo ";

                    DataTable dt1 = sqlhelper.GetDataTable(sql);

                    if (dt1 != null)

                    {

                        for (int i = ; i < dt1.Columns.Count; i++)

                        {

                            //修改源数据表的字段类型和字段名称

                            dt.Columns[i].DataType = dt1.Columns[i].DataType;

                            dt.Columns[i].ColumnMapping = dt1.Columns[i].ColumnMapping;

                            dt.Columns[i].ColumnName = dt1.Columns[i].ColumnName;

                        }

                        sqlhelper.InsertO(dt, "UserInfo");

                    }

                }

            }

            catch (Exception ex)

            {

                throw;

            }

        }

以上这种修改数据类型的方法，如果碰到数据类型不一致并且 DataTable 有数据的时候，会报错，不能修改有数据的列的数据类型。（好像是废话，嘿嘿，没有数据和有数据时不能修改数据类型，这完全是没用的。）

所以就有了下面的方法，先实例化一个新的 DataTable，然后复制目标表的架构，然后再把数据保存到新的 DataTable 中。

protected void btnImport_Click(object sender, EventArgs e)

    {

        try

        {

            //获取导入的数据

            DataSet ds = BI.ExecleDs(savePath, "");

            if (ds != null && ds.Tables.Count > )

            {

                DataTable dt = ds.Tables[];

                //查询目标表需要插入的字段

                string sql = " select U_No,U_Name,U_Pwd,P_Id from UserInfo ";

                DataTable dt1 = sqlhelper.GetDataTable(sql);

                DataTable dt2 = new DataTable();

                if (dt1 != null)

                {

                    //复制目标表的架构

                    dt2 = dt1.Clone();

                    for (int i = ; i < dt1.Rows.Count; i++)

                    {

                        DataRow dr = dt2.NewRow();

                        dr = dt1.Rows[i];

                        dt2.Rows.Add(dr.ItemArray);

                    }

                    sqlhelper.InsertO(dt2, "UserInfo");

                }

            }

        }

        catch (Exception ex)

        {

            throw;

        }

    }

如果源数据表的列和目标表的列的顺序或列名不相同，那就必须使用 ColumnMappings.Add() 方法设置映射关系。

参考：

http://www.cnblogs.com/zfanlong1314/archive/2013/02/05/2892998.html

巴特西

C# 使用 SqlBulkCopy 类批量复制数据到数据库

最新文章

热门文章