mysql sqlbuckcopy_批量新增数据（BuckCopy）

落日映苍穹つ 2023-01-12 04:10 158阅读 0赞

批量新增数据(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