使用SqlBulkCopy批量插入数据

static void Main(string[] args)

        {

            //定义与目标表结构相同的DataTable

            DataTable dataTable = new DataTable();

            dataTable.Columns.Add(new DataColumn() { ColumnName = "EmployeeId", DataType = System.Type.GetType("System.String") });

            dataTable.Columns.Add(new DataColumn() { ColumnName = "LastName", DataType = System.Type.GetType("System.String") });

            dataTable.Columns.Add(new DataColumn() { ColumnName = "FirstName", DataType = System.Type.GetType("System.String") });

            //...此处省略很多行

            //插入数据行, 请注意不同类型的赋值, 这里应该根据实际情况添加很多行

            DataRow dataRow = dataTable.NewRow();

            dataRow["EmployeeId"] = "";

            dataRow["LastName"] = "Nick";

            dataRow["FirstName"] = "Yang";

            //此处省略插入很多行

            dataTable.Rows.Add(dataRow);

            string connStr = "Data Source=.;Database=ABC;user id=sa;password=***;Persist Security Info=True";

            SqlConnection conn = new SqlConnection(connStr);

            conn.Open();

            SqlTransaction sqlbulkTransaction = conn.BeginTransaction();

            SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);

            copy.DestinationTableName = "dbo.目标表表名";

            //这里假设上面定义的DataTable与目标表结构相同

            foreach (DataColumn dc in dataTable.Columns)

            {

                copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);

            }

            try

            {

                copy.WriteToServer(dataTable);

                sqlbulkTransaction.Commit();

            }

            catch (Exception ex)

            {

                sqlbulkTransaction.Rollback();

                Console.WriteLine(ex.ToString());

            }

            finally

            {

                copy.Close();

                conn.Close();

            }

        }