mysql sqlbuckcopy_批量新增数据（BuckCopy）

批量新增数据(BuckCopy)

使用webService传输数据时要注意，Datatable中的数据类型，以及科学计数

/// /// 批量新增数据 /// /// 源数据 /// 目标Table的名称 /// 列的集合，null /// 每次新增的数据量 /// 数据库连接 /// 成功与否 public Boolean BuckCopy(DataTable SourceData, string targetName,Dictionary 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();

}

}

}