sqlbulkcopy问题

lsjlove 2011-12-19 11:16:31

今天早上做了实验用了用 SQLBulkCopy，代码如下：



using (SqlConnection conn = new SqlConnection(DbHelperSQL.GetConnectionString(ConnectionString.Scm)))

            {

                conn.Open();

                using(SqlTransaction transaction=conn.BeginTransaction())

                {

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn,SqlBulkCopyOptions.Default, transaction))

                    {

                        try

                        {

                        bulkCopy.BatchSize = batchsize;

                        bulkCopy.BulkCopyTimeout = 600;

                        bulkCopy.ColumnMappings.Add("CardID", "CardID");

                        bulkCopy.ColumnMappings.Add("UserID", "UserID");

                        bulkCopy.ColumnMappings.Add("CardPassWord", "CardPassWord");

                        bulkCopy.ColumnMappings.Add("Note", "Note");

                        bulkCopy.ColumnMappings.Add("IsUse", "IsUse");

                        bulkCopy.ColumnMappings.Add("ActivitiesID", "ActivitiesID");

                        //bulkCopy.NotifyAfter = 2000;

                        //bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);

                        bulkCopy.DestinationTableName = "dbo.card";

                        bulkCopy.WriteToServer(data);

                        transaction.Commit();



                        }

                        catch (Exception ex)

                        {

                            transaction.Rollback();

                            throw new Exception("导入数据错误");



                        }

                    } 

                }

                

            }

           

        }

数据库中表结构如下示：



        [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

	[UserID] [int] NOT NULL,

	[FormCode] [bigint] NULL,

	[CardID] [nvarchar](50) NOT NULL,

	[CardPassWord] [nvarchar](50) NOT NULL,

	[Note] [nvarchar](500) NULL,

	[PostDate] [datetime] NULL,

	[IsUse] [smallint] NULL,

	[MobilePhone] [nvarchar](20) NULL,

	[ActivitiesID] [int] NULL,

	[ElesID] [int] NULL,

	[SendTime] [datetime] NULL,

代码中我是从excel中读入的数据，所以临时添加了一个表格，代码如下：



 DataTable data = new DataTable();

            data.Columns.Add(new DataColumn("ID", typeof(int)));

            data.Columns.Add(new DataColumn("UserID", typeof(int)));

            data.Columns.Add(new DataColumn("CardID", typeof(string)));

            data.Columns.Add(new DataColumn("CardPassWord", typeof(string)));

            data.Columns.Add(new DataColumn("Note", typeof(string)));

            data.Columns.Add(new DataColumn("IsUse", typeof(int)));

            data.Columns.Add(new DataColumn("ActivitiesID", typeof(int)));





            int nCardIndex = 0;

            int nRowCounts = dt.Rows.Count;

            for (var i = 0; i < nRowCounts; i++)

            {

                DataRow row = data.NewRow();

                row["CardID"] = dt.Rows[i].Field<string>("CardID");

                row["CardPassWord"] = dt.Rows[i].Field<string>("CardPassWord");

                row["Note"] = this.Note;

                row["IsUse"] = 0;

                row["ActivitiesID"] = this.ActivityId;

                row["UserID"] = 0;

                data.Rows.Add(row);

            }