/// <summary>
/// 批量插入数据
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public string InsertPaperCertInfoListData(List<PaperCertInfo> list)
{
string returnStr = string.Empty;
DataTable dt = new DataTable();
var con = dataProvider.GetSqlConnection();
try
{
dt = GetPaperCertInfolDataTable(list);
}
catch (Exception ex)
{
returnStr = "Error"; ;
}
if (dt != null && dt.Rows.Count > 0)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (con.State == ConnectionState.Open)
{
using (SqlTransaction ts = con.BeginTransaction())
{
try
{
using (SqlBulkCopy bulk = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, ts))
{
bulk.BatchSize = 1000;
bulk.DestinationTableName = "dbo.TPaperCertInfo";
bulk.ColumnMappings.Clear();
bulk.ColumnMappings.Add("fPaperCertCode", "fPaperCertCode");
bulk.ColumnMappings.Add("fCertTypeID", "fCertTypeID");
bulk.ColumnMappings.Add("fCertTypeName", "fCertTypeName");
bulk.ColumnMappings.Add("fPosition", "fPosition");
bulk.ColumnMappings.Add("fCertBatchNo", "fCertBatchNo");
bulk.ColumnMappings.Add("fCertBatchName", "fCertBatchName");
bulk.ColumnMappings.Add("fStatus", "fStatus");
bulk.ColumnMappings.Add("fPrintStatus", "fPrintStatus");
bulk.ColumnMappings.Add("fMailStatus", "fMailStatus");
bulk.ColumnMappings.Add("fCreateTime", "fCreateTime");
bulk.ColumnMappings.Add("fCreateUserID", "fCreateUserID");
bulk.ColumnMappings.Add("fNotes", "fNotes");
bulk.NotifyAfter = 100;
//bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(SqlRowsCopied);
bulk.WriteToServer(dt);
ts.Commit();
//returnStr = "OK";
}
}
catch (Exception ex)
{
ts.Rollback();
returnStr = "Error";
}
}
dt.Dispose();
}
}
catch (Exception ex)
{
returnStr = "Error";
}
finally
{
con.Close();
con.Dispose();
}
}
return returnStr;
}
/// <summary>
/// 建立datatable映射到数据库中的表
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
private DataTable GetPaperCertInfolDataTable(List<PaperCertInfo> list)
{
DataTable dt = new DataTable();
//dt.Columns.Add("fPaperCertID");
dt.Columns.Add("fPaperCertCode");
dt.Columns.Add("fCertTypeID");
dt.Columns.Add("fCertTypeName");
dt.Columns.Add("fPosition");
dt.Columns.Add("fCertBatchNo");
dt.Columns.Add("fCertBatchName");
dt.Columns.Add("fStatus");
dt.Columns.Add("fPrintStatus");
dt.Columns.Add("fMailStatus");
dt.Columns.Add("fCreateTime");
dt.Columns.Add("fCreateUserID");
dt.Columns["fCreateUserID"].DataType = typeof(Guid);
dt.Columns.Add("fNotes");
for (int i = 0; i < list.Count; i++)
{
DataRow dr = dt.NewRow();
//dr["fPaperCertID"] = Convert.ToInt32(list[i].PaperCertID);
dr["fPaperCertCode"] = list[i].PaperCertCode;
dr["fCertTypeID"] = Convert.ToInt32(list[i].CertTypeID);
dr["fCertTypeName"] = list[i].CertTypeName;
dr["fPosition"] = list[i].Position;
dr["fCertBatchNo"] = list[i].CertBatchNo;
dr["fCertBatchName"] = list[i].CertBatchName;
dr["fStatus"] = Convert.ToInt32(list[i].Status);
dr["fPrintStatus"] = Convert.ToInt32(list[i].PrintStatus);
dr["fMailStatus"] = Convert.ToInt32(list[i].MailStatus);
dr["fCreateTime"] = Convert.ToDateTime(list[i].CreateTime);
dr["fCreateUserID"] = list[i].CreateUserID;
//dr["fCreateUserID"] = Guid.NewGuid();
dr["fNotes"] = list[i].Notes;
dt.Rows.Add(dr);
}
return dt;
}
c#大批量数据插入
最新推荐文章于 2022-09-05 19:07:26 发布