可以处理批量数据的插入,例如excel导入到sql,比一条一条的insert插入在性能上有不小的提升。要插入的数据库表结结构
- 要插入的数据库表结结构
CREATE TABLE [dbo].[Student]( [Id] [INT] IDENTITY(1,1) NOT NULL, [Number] [NCHAR](10) NULL, [Name] [NVARCHAR](50) NULL, [Address] [NVARCHAR](50) NULL, [Phone] [NVARCHAR](50) NULL, [Sex] [INT] NULL, [IsChinese] [BIT] NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
- C#代码
public static void SqlBulkCopyDemo() { var conn = new SqlConnection { ConnectionString = ConfigurationSettings.AppSettings["connectionStrings"] }; conn.Open(); //事务 SqlTransaction transaction = conn.BeginTransaction(); var sqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, transaction) { DestinationTableName = "Student"//数据库表名称 }; #region 创建Datatble var dataTable = new DataTable(); var column = new DataColumn { DataType = typeof(int), ColumnName = "Id" }; dataTable.Columns.Add(column); column = new DataColumn { DataType = typeof(string), ColumnName = "Number" }; dataTable.Columns.Add(column); column = new DataColumn { DataType = typeof(string), ColumnName = "Name" }; dataTable.Columns.Add(column); column = new DataColumn { DataType = typeof(string), ColumnName = "Address" }; dataTable.Columns.Add(column); column = new DataColumn { DataType = typeof(string), ColumnName = "Phone" }; dataTable.Columns.Add(column); column = new DataColumn { DataType = typeof(int), ColumnName = "Sex" }; dataTable.Columns.Add(column); column = new DataColumn { DataType = typeof(bool), ColumnName = "IsChinese" }; dataTable.Columns.Add(column); for (int i = 0; i < 10; i++) { DataRow dr = dataTable.NewRow(); dr["Number"] = i; dr["Name"] = i; dr["Address"] = i; dr["Phone"] = i; dr["Sex"] = i; dr["IsChinese"] = i % 2 == 0; dataTable.Rows.Add(dr); } #endregion sqlBulkCopy.WriteToServer(dataTable); transaction.Commit(); }
- 注:1、datatable对象的每一列都要和数据库对应,名称可以不一样,但顺序不能变化,也就是说datatable的第一列对应表的第一列。
2、如果字段是自增的可以不用给值,即使给了,即使给了也是无效的。