批量新增数据（BuckCopy）
使用webService传输数据时要注意，Datatable中的数据类型，以及科学计数
/// <summary> /// 批量新增数据 /// </summary> /// <param name="SourceData">源数据</param> /// <param name="targetName">目标Table的名称</param> /// <param name="columnCollection">列的集合，null</param> /// <param name="batchSize">每次新增的数据量</param> /// <param name="conn">数据库连接</param> /// <returns>成功与否</returns> public Boolean BuckCopy(DataTable SourceData, string targetName,Dictionary<string, Type> columnCollection, int batchSize, SqlConnection conn = null) { //**********************************************************
SourceData.Columns.Remove("updateVersion");//如果有timestamp类型的字段要移除
columnCollection.Remove("updateVersion");
DataTable data = new DataTable();
data = SourceData.Clone();
DataTable dt = SourceData; foreach (DataColumn column in data.Columns)//处理数据库中decamal类型的数据
{ if (column.DataType == typeof(double) || column.DataType == typeof(float))
{
column.DataType = typeof(decimal);
}
}
DataRow[] dataRows = new DataRow[dt.Rows.Count];
dt.Rows.CopyTo(dataRows, 0); foreach (DataRow row in dataRows)
{
data.ImportRow(row);
}
data.TableName = targetName; //************************************************************** if (conn == null)
conn = GetLocalConnection();
conn.Open(); using (SqlTransaction sqlBuckTranscation = conn.BeginTransaction(IsolationLevel.ReadCommitted))
{ using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn,SqlBulkCopyOptions.Default,sqlBuckTranscation))
{
bulkCopy.DestinationTableName = targetName;
bulkCopy.BatchSize = batchSize; foreach (DataColumn col in SourceData.Columns)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(col.ColumnName, col.ColumnName));
} try
{
bulkCopy.WriteToServer(data);
sqlBuckTranscation.Commit(); return true;
} catch
{
sqlBuckTranscation.Rollback(); return false;
} finally
{
conn.Close();
bulkCopy.Close();
}
}
}