使用 SqlBulkCopy 向 destinationTableName 表插入数据

public static class SqlConnectionExtension
    {
        /// <summary>
        /// 使用 SqlBulkCopy 向 destinationTableName 表插入数据
        /// </summary>
        /// <typeparam name="TModel">必须拥有与目标表所有字段对应属性</typeparam>
        /// <param name="conn">连接字符串</param>
        /// <param name="modelList">要插入的数据</param>
        /// <param name="batchSize">SqlBulkCopy.BatchSize</param>
        /// <param name="destinationTableName">表名</param>        
        public static int BulkCopy<TModel>(string connstr, List<TModel> list, int batchSize, string destinationTableName)
        {
            var table = new DataTable();

            var type = typeof(TModel);
            int rValue = 0;
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                SqlCommand cmd = new SqlCommand("select * from [" + destinationTableName + "] where 1=2", conn);

                // 执行
                conn.Open();
                var reader = cmd.ExecuteReader();
                table.Load(reader);

                foreach (var item in list)
                {
                    var row = table.NewRow();
                    foreach (DataColumn column in table.Columns)
                    {
                        row[column] = type.GetProperty(column.ColumnName).GetValue(item, null) ?? DBNull.Value;
                    }
                    table.Rows.Add(row);
                }
                reader.Close();

                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn as SqlConnection);
                bulkCopy.DestinationTableName = destinationTableName;
                bulkCopy.BatchSize = batchSize;

                try
                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }

                    var dataReader = table.CreateDataReader();

                    if (table != null && table.Rows.Count != 0)
                        bulkCopy.WriteToServer(dataReader);
                    rValue = table.Rows.Count;
                }
                catch (Exception ex)
                {
                    rValue = 0;
                    conn.Close();
                    throw ex;
                }
                finally
                {
                    if (bulkCopy != null)
                        bulkCopy.Close();
                }                
            }

            return rValue;
        }
    }