sql server 2005大数据量的插入操作
第一,写个存储过程,传入参数,存储过程里面是insert操作,
第二,用System.Data.SqlClient.SqlBulkCopy实例方法,
数据库表Passport
CREATE TABLE [dbo].[Passport](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PassportKey] [nchar](50) NOT NULL,
[addtime] [datetime] NOT NULL,
CONSTRAINT [PK_Passport] PRIMARY KEY CLUSTERED
( [Id] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
存储过程 CreatePassport
Create proc [dbo].[CreatePassport]
@passport varchar(50),
@addtime datetime
as
insert into Passport (PassportKey,addtime) values (@passport,@addtime)
对比测试,方法一调用存储过程,循环插入数据,方法二,构造表的对象,然后调用SqlBulkCopy
少量数据
大量数据
代码
DataTable dataTable = GetTableSchema(); string passportKey; for (int i = 0; i < count; i++) { passportKey = Guid.NewGuid().ToString(); DataRow dataRow = dataTable.NewRow(); dataRow[1] = passportKey; dataRow[2] = DateTime.Now; dataTable.Rows.Add(dataRow); } SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString); sqlBulkCopy.DestinationTableName = "Passport"; sqlBulkCopy.BatchSize = dataTable.Rows.Count; SqlConnection sqlConnection = new SqlConnection(connectionString); sqlConnection.Open(); if (dataTable!=null && dataTable.Rows.Count!=0) { sqlBulkCopy.WriteToServer(dataTable); } sqlBulkCopy.Close(); sqlConnection.Close();