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 = ; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[] = passportKey;
dataRow[] = 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!=)
{
sqlBulkCopy.WriteToServer(dataTable);
}
sqlBulkCopy.Close();
sqlConnection.Close();