public void DataEdit()
{
var dataList = CommonHelper.GetTestListDto(5000);
var dataListTwo = CommonHelper.GetTestListDtoTwo(5000);
var table = SqlBulkCopyHelper.ListToTable(dataList, "BulkTest");
var tableTwo = SqlBulkCopyHelper.ListToTable(dataListTwo, "BulkTestTwo");
var tableUpdate = SqlCoreHelper.ExecuteDataSetText("select top 5000 Id,FieldD from BulkTest order by FieldU asc", null).Tables[0];
var tableTwoUpdate = SqlCoreHelper.ExecuteDataSetText("select top 5000 Id,FieldD from BulkTestTwo order by FieldU asc", null).Tables[0];
foreach (DataRow item in tableUpdate.Rows)
{
item["FieldD"] = "555";
}
foreach (DataRow item in tableTwoUpdate.Rows)
{
item["FieldD"] = "666";
}
tableUpdate.TableName = "BulkTest";
tableTwoUpdate.TableName = "BulkTestTwo";
Stopwatch sw = new Stopwatch();
sw.Start();
SqlBulkCopyHelper.BulkEditTables(new List <DataTable>()
{
table, tableTwo
}, new List <DataTable>()
{
tableUpdate, tableTwoUpdate
});
sw.Stop();
string bb = sw.Elapsed.TotalSeconds.ToString();
Console.WriteLine(bb);
}
public void DataUpdate()
{
var table = SqlCoreHelper.ExecuteDataSetText("select Id,FieldD from BulkTest", null).Tables[0];
var tableTwo = SqlCoreHelper.ExecuteDataSetText("select Id,FieldD from BulkTestTwo", null).Tables[0];
foreach (DataRow item in table.Rows)
{
item["FieldD"] = "111";
}
foreach (DataRow item in tableTwo.Rows)
{
item["FieldD"] = "222";
}
table.TableName = "BulkTest";
tableTwo.TableName = "BulkTestTwo";
Stopwatch sw = new Stopwatch();
sw.Start();
SqlBulkCopyHelper.BulkUpdateTables(new List <DataTable>()
{
table, tableTwo
});
sw.Stop();
string bb = sw.Elapsed.TotalSeconds.ToString();
Console.WriteLine(bb);
}
/// <summary>
/// 获取数据库表的所有列
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <SysColumn> GetTableColumns(string tableName)
{
string sql = string.Format(@"select a.name,a.colorder,c.DATA_TYPE,a.isnullable,SUBSTRING(d.text,3,1) defaultValue from
syscolumns a inner join sysobjects b on a.id=b.id
LEFT JOIN dbo.syscomments d ON a.cdefault = d.id
inner join information_schema.columns c on b.name=c.TABLE_NAME and c.COLUMN_NAME=a.name
where b.xtype='U' and b.name='{0}' order by a.colid asc", tableName);
List <SysColumn> columns = new List <SysColumn>();
DataTable dt = SqlCoreHelper.ExecuteDataSetText(sql, null).Tables[0];
foreach (DataRow reader in dt.Rows)
{
SysColumn column = new SysColumn();
column.Name = reader[0].ToString();
column.ColOrder = Convert.ToInt16(reader[1]);
column.Type = reader[2].ToString();
column.IsNull = Convert.ToInt32(reader[3]);
column.Default = reader[4].ToString();
columns.Add(column);
}
return(columns);
}
/// <summary>
/// 执行SqlBulkCopy批量更新，执行事务。
/// </summary>
/// <param name="bulkTables"></param>
/// <returns></returns>
public static string BulkUpdateTables(List <DataTable> updateTables, Dictionary <string, List <string> > primaryFieldsDict = default)
{
Dictionary <string, List <string> > insertFieldsDict = new Dictionary <string, List <string> >(),
updateFieldsDict = new Dictionary <string, List <string> >();
if (primaryFieldsDict == default)
{
primaryFieldsDict = new Dictionary <string, List <string> >();
}
foreach (var item in updateTables)
{
List <string> insertFields = new List <string>(),
primaryFields = new List <string>(),
updateFields = new List <string>();
if (!primaryFieldsDict.Keys.Contains(item.TableName))
{
var dt = SqlCoreHelper.ExecuteDataSetText(string.Format(@"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}'", item.TableName), null).Tables[0];
foreach (DataRow dr in dt.Rows)
{
primaryFields.Add(dr[0].ToString());
}
primaryFieldsDict.Add(item.TableName, primaryFields);
}
foreach (DataColumn column in item.Columns)
{
insertFields.Add(column.ColumnName);
if (!primaryFields.Contains(column.ColumnName))
{
updateFields.Add(column.ColumnName);
}
}
insertFieldsDict.Add(item.TableName, insertFields);
updateFieldsDict.Add(item.TableName, updateFields);
}
var tempTableSuf = DateTime.Now.ToString("yyyyMMddHHmmss");
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
var tran = conn.BeginTransaction();//开启事务
var sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran)
{
BulkCopyTimeout = 600
};
try
{
using (SqlCommand cmd = new SqlCommand(string.Empty, conn, tran)
{
CommandTimeout = 600
})
{
updateTables.ForEach(o =>
{
var insertFields = insertFieldsDict[o.TableName];
cmd.CommandText += string.Format(@"SELECT {0} into {1} from {2} A WHERE 1=2;", string.Join(',', insertFields.Select(p => "A." + p)), tempTablePre + o.TableName + tempTableSuf, o.TableName);
});
cmd.ExecuteNonQuery();
updateTables.ForEach(o =>
{
var insertFields = insertFieldsDict[o.TableName];
var primarFields = primaryFieldsDict[o.TableName];
var updateFields = updateFieldsDict[o.TableName];
sqlbulkcopy.ColumnMappings.Clear();
sqlbulkcopy.DestinationTableName = tempTablePre + o.TableName + tempTableSuf;
foreach (var item in insertFields)
{
sqlbulkcopy.ColumnMappings.Add(item, item);
}
sqlbulkcopy.WriteToServer(o);
StringBuilder updateSql = new StringBuilder(), onSql = new StringBuilder();
foreach (var column in updateFields)
{
updateSql.Append(string.Format(@"A.{0} = B.{0},", column));
}
foreach (var column in primarFields)
{
if (primarFields.IndexOf(column) == primarFields.Count - 1)
{
onSql.Append(string.Format(@"A.{0} = B.{0}", column));
}
else
{
onSql.Append(string.Format(@"A.{0} = B.{0} And,", column));
}
}
cmd.CommandText = string.Format(@"UPDATE A SET {0} FROM {1} A INNER JOIN {2} B ON {3};drop table {2};",
updateSql.ToString().Trim(','), o.TableName, tempTablePre + o.TableName + tempTableSuf, onSql.ToString());
cmd.ExecuteNonQuery();
});
tran.Commit();
}
}
catch (Exception ex)
{
return(ex.Message);
}
finally
{
sqlbulkcopy.Close();
tran.Dispose();
}
}
return(string.Empty);
}
