C#中高效批量插入的方法

方式一：使用数据库中的Table类型变量实现数据插入

这种方法的前提是数据库中必须支持Table类型的变量。

--创建自定义表变量
Create Type PassportTableType as Table(columns.... nvarchar(50))

--创建存储过程

Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END

--在程序中调用

SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);

方式二：使用SqlBulkCopy

使用SqlBulkCopy类进行数据插入其原理是采用了SQL Server的BCP协议进行数据的批量复制，使用这个有点要注意的地方，就是数据源表中的列和目标表中的列一一对应

/// <summary>
        /// 批量插入数据
        /// </summary>
        /// <param name="DestinationTableName">目标表</param>
        /// <param name="dt">数据源表</param>
        /// <param name="connectionString">链接字符串</param>
        /// <returns></returns>
        public static bool SqlBulkCopyInsert(string DestinationTableName, DataTable dt,string connectionString)
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString))
            {
                try
                {
                    sqlBulkCopy.DestinationTableName = DestinationTableName;
                    sqlBulkCopy.BatchSize = dt.Rows.Count;
                    SqlConnection sqlConnection = new SqlConnection(connectionString);
                    sqlConnection.Open();

                    if (dt != null && dt.Rows.Count != 0)
                    {
                        sqlBulkCopy.WriteToServer(dt);
                    }
                    sqlBulkCopy.Close();
                    sqlConnection.Close();
                }
                catch
                {
                    return false;
                }
            }
            return true;
        }

下面是想写成泛型的，这是个半成品还没有想好怎样实现通用性会更好

/// <summary>
        ///
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="P"></typeparam>
        /// <param name="DestinationTableName"></param>
        /// <param name="DataColumnName"></param>
        /// <param name="dictionary"></param>
        /// <returns></returns>
        public static bool SqlBulkCopyInsert<T, P>(string DestinationTableName, string[] DataColumnName, Dictionary<T, P> dictionary)
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(WebConfig.AutoSiteV2String))
            {
                try
                {
                    DataTable dataTable = BaseDalc.GetTableSchema(DataColumnName);

                    foreach (KeyValuePair<T, P> keyValuePair in dictionary)
                    {
                        DataRow dataRow = dataTable.NewRow();
                        dataRow.ItemArray = new object[] { keyValuePair.Key, keyValuePair.Value };
                        dataTable.Rows.Add(dataRow);
                    }

                    sqlBulkCopy.DestinationTableName = DestinationTableName;
                    sqlBulkCopy.BatchSize = dataTable.Rows.Count;
                    SqlConnection sqlConnection = new SqlConnection(WebConfig.AutoSiteV2String);
                    sqlConnection.Open();

                    if (dataTable != null && dataTable.Rows.Count != 0)
                    {
                        sqlBulkCopy.WriteToServer(dataTable, DataRowState.Detached);
                    }
                    sqlBulkCopy.Close();
                    sqlConnection.Close();
                }
                catch
                {
                    return false;
                }
            }
            return true;
        }