SqlBulkCopy使用

可以处理批量数据的插入，例如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、如果字段是自增的可以不用给值，即使给了，即使给了也是无效的。