使用事务和SqlBulkCopy批量导入数据.doc

/使用事务和SqlBulkCopy批量导入数据示例/ByConan3042009年8月29日21:51:02/privatestaticvoidSqlBulkCopyData(stringphone,stringsmsInfo)if(phone=null)return;/生成DataTableDataTabledataTable=newDataTable();DataColumncolumn=newDataColumn();/qy_idcolumn.DataType=System.Type.GetType(System.Int32);column.ColumnName=QY_ID;dataTable.Columns.Add(column);/Info_IDcolumn=newDataColumn();column.DataType=System.Type.GetType(System.String);column.MaxLength=32;column.ColumnName=Info_ID;dataTable.Columns.Add(column);/SP_Portcolumn=newDataColumn();column.DataType=System.Type.GetType(System.String);column.MaxLength=24;column.ColumnName=SP_Port;dataTable.Columns.Add(column);/Phonecolumn=newDataColumn();column.DataType=System.Type.GetType(System.String);column.MaxLength=11;column.ColumnName=Phone;dataTable.Columns.Add(column);/Contentcolumn=newDataColumn();column.DataType=System.Type.GetType(System.String);column.MaxLength=300;column.ColumnName=Content;dataTable.Columns.Add(column);/SendTimecolumn=newDataColumn();column.DataType=System.Type.GetType(System.DateTime);column.ColumnName=SendTime;column.DefaultValue=DateTime.Now;dataTable.Columns.Add(column);/SendLevelcolumn=newDataColumn();column.DataType=System.Type.GetType(System.Int32);column.ColumnName=SendLevel;column.DefaultValue=4;dataTable.Columns.Add(column);/IsLongcolumn=newDataColumn();column.DataType=System.Type.GetType(System.Int32);column.ColumnName=IsLong;dataTable.Columns.Add(column);/AdminIDcolumn=newDataColumn();column.DataType=System.Type.GetType(System.Int32);column.ColumnName=AdminID;column.DefaultValue=0;dataTable.Columns.Add(column);stringresult=newstringphone.Length;for(inti=0;iphone.Length;i+)/生成GUIDstringGuid=System.Guid.NewGuid().ToString(N).ToUpper();stringphoneNew=string.Empty;intphoneType=GetMobileType(phonei,outphoneNew);if(phoneType=0)resulti=phoneNew+-0-0;elseDataRowdataRow=dataTable.NewRow();dataRowqy_id=16;dataRowInfo_ID=Guid;dataRowSP_Port=10657027014211;dataRowPhone=phoneNew;dataRowContent=smsInfo;dataRowIsLong=0;dataTable.Rows.Add(dataRow);resulti=phoneNew+-1-+Guid;/BCPcopySqlConnectionconn=newSqlConnection();conn.ConnectionString=server=.;uid=dmkj_hpc;pwd=#$wlh*&1110h%c;database=DMKJ_SMS;conn.Open();SqlTransactionsqlbulkTransaction=conn.BeginTransaction();/请在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务SqlBulkCopycopy=newSqlBulkCopy(conn,SqlBulkCopyOptions.CheckConstraints,sqlbulkTransaction);copy.DestinationTableName=T_SMS_SendInfo;foreach(DataColumndcindataTable.Columns)copy.ColumnMappings.Add(dc.ColumnName,dc.ColumnName);trycopy.WriteToServer(dataTable);sqlbulkTransaction.Commit();catch(Exceptionex)sqlbulkTransaction.Rollback();Console.WriteLine(ex.ToString();finallycopy.Close();conn.Close();/for(intx=0;xdataTable.Rows.Count;x+)/for(inti=0;idataTable.Columns.Count;i+)/Console.WriteLine(ColumnName:0,andvalueis:1,dataT