private void button1_Click(object sender, EventArgs e)
{
// Create source connection
SqlConnection source = new SqlConnection(connectionString);
// Create destination connection
SqlConnection destination = new SqlConnection(connectionString);
// Clean up destination table. Your destination database must have the
// table with schema which you are copying data to.
// Before executing this code, you must create a table BulkDataTable
// in your database where you are trying to copy data to.
SqlCommand cmd = new SqlCommand("DELETE FROM BulkDataTable", destination);
// Open source and destination connections.
source.Open();
destination.Open();
cmd.ExecuteNonQuery();
// Select data from Products table
cmd = new SqlCommand("SELECT * FROM Products", source);
// Execute reader
SqlDataReader reader = cmd.ExecuteReader();
// Create SqlBulkCopy
SqlBulkCopy bulkData = new SqlBulkCopy(destination);
// Set destination table name
bulkData.DestinationTableName = "BulkDataTable";
// Write data
bulkData.WriteToServer(reader);
// Close objects
bulkData.Close();
destination.Close();
source.Close();
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
if (_sbc != null)
{
_sbc.Close();
}
}
/// <summary>
/// BulkToDB,批量插入数据
/// </summary>
/// <param name="dt"></param>
/// <param name="tableName"></param>
public void BulkToDB(DataTable dt, string tableName)
{
SqlConnection sqlConn = new SqlConnection(db.ConnectionString);
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = dt.Rows.Count;
foreach (DataColumn dc in dt.Columns)
{
bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
}
}
catch (Exception ex)
{
throw new Exception(tableName + ex.ToString());
}
finally
{
sqlConn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
/// <summary>
/// 批量保存数据
/// </summary>
/// <param name="dataTable">dataTable</param>
/// <param name="srcTable">目标表名</param>
public static void BatchSaveData(DataTable dataTable, string srcTable)
{
try
{
using (var bulk = new SqlBulkCopy(strConnect)
{
DestinationTableName = srcTable,
BatchSize = 10000
})
{
//先取出表里面有的字段
DataTable dtSrc = FillDataTable("SELECT * FROM "+srcTable+" WHERE 1=2");
//取出数据库和实体里面都存在的列，为bulk添加映射
foreach (DataColumn item in dtSrc.Columns)
{
if (dataTable.Columns.Contains(item.ColumnName))
{
bulk.ColumnMappings.Add(item.ColumnName, item.ColumnName);
}
}
bulk.WriteToServer(dataTable);
bulk.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 大数据量批量插入BulkToDB
/// </summary>
/// <param name="data">数据表</param>
public static void BulkToDB(DataTable data)
{
SqlConnection conn = new SqlConnection(GetConnSting());
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
bulkCopy.DestinationTableName = data.TableName;
bulkCopy.BatchSize = data.Rows.Count;
try
{
conn.Open();
if (data != null && data.Rows.Count != 0)
{
bulkCopy.WriteToServer(data);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
/// <summary>
///
/// Start consuming rows from the buffer.
///
/// </summary>
public void Consume()
{
SqlBulkCopy bulkCopy;
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
try
{
if (SetTraceFlag610)
{
SqlCommand setTraceFlag = new SqlCommand("DBCC TRACEON (610)", connection);
setTraceFlag.ExecuteNonQuery();
}
else
{
SqlCommand setTraceFlag = new SqlCommand("DBCC TRACEOFF (610)", connection);
setTraceFlag.ExecuteNonQuery();
}
bulkCopy = new SqlBulkCopy(connection, Options, null);
bulkCopy.BatchSize = BatchSize;
bulkCopy.BulkCopyTimeout = BulkCopyTimeout;
bulkCopy.DestinationTableName = TableName;
bulkCopy.WriteToServer(Buffer);
bulkCopy.Close();
}
finally
{
connection.Close();
}
}
public void BulkUserBrowserHistory(DataTable browserHistory)
{
if (browserHistory != null)
{
if (browserHistory.Rows.Count > 0)
{
try
{
using (SqlBulkCopy bcp = new SqlBulkCopy(this.database.ConnectionString))
{
bcp.DestinationTableName = "Ecshop_UserBrowseHistory";
bcp.ColumnMappings.Add("ProductId", "ProductId");
bcp.ColumnMappings.Add("UserId", "UserId");
bcp.ColumnMappings.Add("UserName", "UserName");
bcp.ColumnMappings.Add("BrowseTime", "BrowseTime");
bcp.ColumnMappings.Add("UserIP", "UserIP");
bcp.ColumnMappings.Add("Description", "Description");
bcp.ColumnMappings.Add("Sort", "Sort");
bcp.ColumnMappings.Add("Url", "Url");
bcp.ColumnMappings.Add("BrowerTimes", "BrowerTimes");
bcp.ColumnMappings.Add("PlatType", "PlatType");
bcp.ColumnMappings.Add("IP", "IP");
bcp.ColumnMappings.Add("CategoryId", "CategoryId");
bcp.WriteToServer(browserHistory);
bcp.Close();
}
}
catch
{ }
}
}
}
public static void Insert(String tableName, DataTable dataTable)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DatabaseAccesser.ConnectionString))
{
try
{
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(dataTable);
}
catch (SqlException sqlException)
{
//if (sqlException.Number != NumberOfViolationOfPrimaryKey)
//{
// Console.WriteLine($"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
// LogWritter.WriteLog(LogWritter.LogMode.Error, $"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
//}
Console.WriteLine($"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
}
finally
{
bulkCopy.Close();
}
}
}
/// <summary>
/// 注意：DataTable中的列需要与数据库表中的列完全一致。
/// 已自测可用。
/// </summary>
/// <param name="conStr">数据库连接串</param>
/// <param name="strTableName">数据库中对应的表名</param>
/// <param name="dtData">数据集</param>
public static void SqlBulkCopyInsert(this IDbContext context, string strTableName, DataTable dtData)
{
try
{
Log.Debug($"批量插入1{context.CurrentContext}");
using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(context.CurrentContext))
{
Log.Debug($"批量插入2{context.CurrentContext}");
sqlRevdBulkCopy.DestinationTableName = strTableName;
sqlRevdBulkCopy.NotifyAfter = dtData.Rows.Count;
sqlRevdBulkCopy.ColumnMappings.Add("TEST_ITEM_ID", "TEST_ITEM_ID");
sqlRevdBulkCopy.ColumnMappings.Add("ENGLISH_NAME", "ENGLISH_NAME");
sqlRevdBulkCopy.ColumnMappings.Add("CHINESE_NAME", "CHINESE_NAME");
sqlRevdBulkCopy.ColumnMappings.Add("QUANTITATIVE_RESULT", "QUANTITATIVE_RESULT");
sqlRevdBulkCopy.ColumnMappings.Add("INPATIENT_ID", "INPATIENT_ID");
sqlRevdBulkCopy.ColumnMappings.Add("INSERT_TIME", "INSERT_TIME");
sqlRevdBulkCopy.WriteToServer(dtData);
sqlRevdBulkCopy.Close();
}
}
catch (Exception ex)
{
Log.Debug(ex, "批量插入错误");
}
}
/// <summary>
/// 往数据库中批量插入数据
/// </summary>
/// <param name="sourceDt">数据源表</param>
/// <param name="targetTable">服务器上目标表</param>
public static void BulkToDB(DataTable sourceDt, string targetTable)
{
SqlConnection conn = new SqlConnection(strConn);
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的数据有效批量加载sql server表中
bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
bulkCopy.BatchSize = sourceDt.Rows.Count; //每一批次中的行数
try
{
conn.Open();
if (sourceDt != null && sourceDt.Rows.Count != 0)
{
bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有行复制到目标表中
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
public override void ActualizarTabla(string TablaDestino, DataTable dt, string sSQLUpdate, string sSQLCreate)
{
Mensaje = string.Empty;
using (SqlCommand comando = new SqlCommand("", conexion))
{
try
{
AbrirConexion();
comando.CommandText = sSQLCreate;
comando.ExecuteNonQuery();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conexion))
{
bulkcopy.BulkCopyTimeout = 660;
bulkcopy.DestinationTableName = "#temporal";
bulkcopy.WriteToServer(dt);
bulkcopy.Close();
}
comando.CommandTimeout = 300;
comando.CommandText = sSQLUpdate;
comando.ExecuteNonQuery();
}
catch (Exception ex)
{
Mensaje = ex.Message;
}
finally
{
Desconectar();
}
}
}
public void UploadDatabaseRecords(DataTable convertedTable)
{
//Delete all old records for that supplier/admin
SchedulingImportsTableAdapter adapter = new SchedulingImportsTableAdapter();
adapter.Connection = new SqlConnection(ExecutionHelper.GetConnectionString());
//adapter.DeleteAll(_SchedulingId, _AdminClientId, _CustClientId);
//Bulk insert the new records
using (SqlBulkCopy s = new SqlBulkCopy(ExecutionHelper.GetConnectionString()))
{
s.DestinationTableName = "SchedulingImports";
s.BatchSize = 5000;
s.ColumnMappings.Add("SchedulingPlatform", "SchedulingPlatform");
s.ColumnMappings.Add("AdminClientID", "AdminClientID");
s.ColumnMappings.Add("CustClientID", "CustClientID");
s.ColumnMappings.Add("TripNumber", "TripNumber");
s.ColumnMappings.Add("TailNumber", "TailNumber");
s.ColumnMappings.Add("ICAO", "ICAO");
s.ColumnMappings.Add("FBO", "FBO");
s.ColumnMappings.Add("Arrival", "Arrival");
s.ColumnMappings.Add("ImportDate", "ImportDate");
s.WriteToServer(convertedTable);
s.Close();
}
}
void BulkInsertAll(DataTable table)
{
string cs = _db.Database.Connection.ConnectionString;
Type t = typeof(T);
try
{
using (SqlBulkCopy sqlRevdBulkCopy = new SqlBulkCopy(cs)) //引用SqlBulkCopy
{
sqlRevdBulkCopy.DestinationTableName = string.Format("[{0}]", t.Name); //数据库中对应的表名
sqlRevdBulkCopy.NotifyAfter = table.Rows.Count; //有几行数据
for (int i = 0, j = table.Columns.Count; i < j; i++)
{
string name = table.Columns[i].ToString();
sqlRevdBulkCopy.ColumnMappings.Add(name, name);
}
sqlRevdBulkCopy.WriteToServer(table); //数据导入数据库
sqlRevdBulkCopy.Close(); //关闭连接
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
}
}
public static void BulkToDB(DataTable dt)
{
SqlConnection sqlConn = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
bulkCopy.DestinationTableName = "Table_1"; //指定的表的
bulkCopy.BatchSize = dt.Rows.Count; //要插入的数量
bulkCopy.BulkCopyTimeout = 60000; //允许的秒数
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt); //将所有行复制到指定的表中
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
public void InsertItems <T>(IEnumerable <T> items, string schema, string tableName, IList <ColumnMapping> properties, DbConnection storeConnection, int?batchSize)
{
using (var reader = new EFDataReader <T>(items, properties))
{
var con = storeConnection as SqlConnection;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (SqlBulkCopy copy = new SqlBulkCopy(con))
{
copy.BatchSize = Math.Min(reader.RecordsAffected, batchSize ?? 15000); //default batch size
if (!string.IsNullOrWhiteSpace(schema))
{
copy.DestinationTableName = string.Format("[{0}].[{1}]", schema, tableName);
}
else
{
copy.DestinationTableName = "[" + tableName + "]";
}
copy.NotifyAfter = 0;
foreach (var i in Enumerable.Range(0, reader.FieldCount))
{
copy.ColumnMappings.Add(i, properties[i].NameInDatabase);
}
copy.WriteToServer(reader);
copy.Close();
}
}
}
/// <summary>
/// 利用Net SqlBulkCopy 批量导入数据库,速度超快
/// </summary>
/// <param name="dataTable">源内存数据表</param>
public bool MsSqlBulkCopyData(DataTable dt, string connectionString)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans);
// 设置源表名称
sqlbulkCopy.DestinationTableName = dt.TableName;
//分几次拷贝
//sqlbulkCopy.BatchSize = 10;
// 设置超时限制
sqlbulkCopy.BulkCopyTimeout = 1000;
foreach (DataColumn dtColumn in dt.Columns)
{
sqlbulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName);
}
try
{
// 写入
sqlbulkCopy.WriteToServer(dt);
// 提交事务
trans.Commit();
return(true);
}
catch
{
trans.Rollback();
sqlbulkCopy.Close();
return(false);
}
finally
{
sqlbulkCopy.Close();
conn.Close();
}
}
}
catch (Exception e)
{
Logger.Error("-----------利用Net SqlBulkCopyData 批量导入数据库,速度超快-----------\r\n" + e.Message + "\r\n");
return(false);
}
}
//private
//**************************************
//* Purpose: Accessing SQL database
//*Methods:
//*GetDataSet
//*RunProc
//*GetDataReader
//*GetDataView
//* *************************************
public bool BulkCopyData(string strConnect, string bulkCopyTable, SqlDataReader sourcedata)
{
//********************************
//* Purpose: Performs bulk copy from one data source to another
//* Input parameters:
//* bulkCopytable ---the target table to bulk data into
//* sourcedata ---the SqlDataReader holding the data to bulk insert
//* Returns :
//* nothing
//**************************************************
bool err = false;
//create source connection
var sourceConnection = new SqlConnection(strConnect);
sourceConnection.Open();
// Create SqlBulkCopy
var bulkData = new SqlBulkCopy(strConnect, SqlBulkCopyOptions.TableLock)
{
BatchSize = 1000,
BulkCopyTimeout = 360,
DestinationTableName = bulkCopyTable
};
//set number of records to process in one batch
//set timeout for a single bulk process
// Set destination table name
try
{
bulkData.WriteToServer(sourcedata);
}
catch (Exception e)
{
err = true;
PLOG.Write(e.Message, 1);
bulkData.Close();
sourceConnection.Close();
sourceConnection.Dispose();
}
finally
{
bulkData.Close();
sourceConnection.Close();
sourceConnection.Dispose();
}
return(err);
}
/// <summary>
/// write all configs
/// </summary>
/// <param name="configs">configs</param>
/// <returns></returns>
public bool WriteConfigs(Dictionary <string, dynamic> configs)
{
using (var con = new SqlConnection(_connectionString))
{
con.Open();
var tran = con.BeginTransaction();
try
{
var cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = tran;
//创建Configs表
cmd.CommandText = @"IF object_id('Configs') is null
BEGIN
CREATE TABLE [dbo].[Configs](
[Key] [varchar](50) NOT NULL,
[Value] [text] NULL,
CONSTRAINT [PK_Configs] PRIMARY KEY CLUSTERED
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
";
cmd.ExecuteNonQuery();
//delete all config data
cmd.CommandText = "DELETE FROM Configs";
cmd.ExecuteNonQuery();
//get datatable organization
cmd.CommandText = "SELECT [Key],[Value] FROM Configs";
var reader = cmd.ExecuteReader();
var table = new DataTable();
table.Load(reader);
foreach (var item in configs)
{
var row = table.NewRow();
row["key"] = item.Key;
row["value"] = Newtonsoft.Json.JsonConvert.SerializeObject(item.Value as object);
table.Rows.Add(row);
}
//add all config data
var sqlbulk = new SqlBulkCopy(con, SqlBulkCopyOptions.UseInternalTransaction, tran);
sqlbulk.NotifyAfter = table.Rows.Count;
sqlbulk.DestinationTableName = "Configs";
sqlbulk.ColumnMappings.Add(0, "Key");
sqlbulk.ColumnMappings.Add(1, "Value");
sqlbulk.WriteToServer(table);
sqlbulk.Close();
tran.Commit();
return(true);
}
catch (Exception exc)
{
tran.Rollback();
throw exc;
}
}
}
private static async Task TestAsync(string srcConstr, string dstConstr, string dstTable, SemaphoreSlim outputSemaphore)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10), col4 datetime)");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select * from employees", srcConn))
{
srcConn.Open();
dataset = new DataSet("MyDataSet");
adapter = new SqlDataAdapter(srcCmd);
adapter.Fill(dataset);
datatable = dataset.Tables[0];
datatable.Rows[0].BeginEdit();
datatable.Rows[0][0] = 333;
datatable.Rows[0].EndEdit();
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
bulkcopy.BatchSize = 7;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "col1");
ColumnMappings.Add(1, "col2");
ColumnMappings.Add(2, "col3");
bulkcopy.WriteToServer(datatable, DataRowState.Unchanged);
datatable.Rows.GetEnumerator().Reset();
await bulkcopy.WriteToServerAsync(datatable, DataRowState.Modified);
datatable.Rows.GetEnumerator().Reset();
await bulkcopy.WriteToServerAsync(datatable, DataRowState.Deleted);
bulkcopy.Close();
}
await outputSemaphore.WaitAsync();
Helpers.VerifyResults(dstConn, dstTable, 4, 18);
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
private bool StringToIntTest(SqlConnection cnn, string targetTable, SourceType sourceType)
{
var value = "abcde";
int rowNo = -1;
DataTable table = PrepareDataTable(targetTable, ColumnsEnum._varChar3, value);
bool hitException = false;
try
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(cnn))
{
bulkcopy.DestinationTableName = targetTable;
bulkcopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping((int)ColumnsEnum._varChar3, (int)ColumnsEnum._int));
switch (sourceType)
{
case SourceType.DataTable:
rowNo = table.Rows.Count;
bulkcopy.WriteToServer(table);
break;
case SourceType.DataRows:
rowNo = table.Rows.Count;
bulkcopy.WriteToServer(table.Select());
break;
case SourceType.DataReader:
bulkcopy.WriteToServer(table.CreateDataReader());
break;
default:
break;
}
bulkcopy.Close();
}
}
catch (Exception ex)
{
string pattern;
object[] args = new object[] { string.Format(" '{0}'", value), value.GetType().Name, "int", (int)ColumnsEnum._int, Enum.GetName(typeof(ColumnsEnum), ColumnsEnum._int), rowNo };
if (rowNo == -1)
{
Array.Resize(ref args, args.Length - 1);
pattern = SystemDataResourceManager.Instance.SQL_BulkLoadCannotConvertValueWithoutRowNo;
}
else
{
pattern = SystemDataResourceManager.Instance.SQL_BulkLoadCannotConvertValue;
}
string expectedErrorMsg = string.Format(pattern, args);
Assert.True(ex.Message.Contains(expectedErrorMsg), "Unexpected error message: " + ex.Message);
hitException = true;
}
return(hitException);
}
public BulkResult BulkInsert(DataTable DT, string DestinationTable, List <SqlBulkCopyColumnMapping> ColumnMappings = null, int Timeout = 1000, int BatchSize = 0, int NotifyAfter = 0, Action <long> NotifyAfterFunction = null, bool IdentityInsert = false)
{
//If there's nothing to insert, return now
if (DT.Rows.Count == 0)
{
return(new BulkResult()
{
RowsAffected = 0, RowsInserted = 0, RowsUpdated = 0
});
}
BulkResult result = new BulkResult();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(this.ConnectionString, (IdentityInsert ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default)))
{
bulkCopy.BulkCopyTimeout = Timeout;
bulkCopy.DestinationTableName = DestinationTable;
bulkCopy.BatchSize = BatchSize;
bulkCopy.NotifyAfter = NotifyAfter;
if (NotifyAfter > 0 && NotifyAfterFunction != null)
{
bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler((o, e) => { NotifyAfterFunction(e.RowsCopied); });
}
if (ColumnMappings == null)
{
foreach (DataColumn c in DT.Columns)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName));
}
}
else
{
foreach (SqlBulkCopyColumnMapping c in ColumnMappings)
{
bulkCopy.ColumnMappings.Add(c);
}
}
try
{
bulkCopy.WriteToServer(DT);
}
catch (Exception ex)
{
result.Exception = ex;
System.Diagnostics.Debug.Print("Bulk Insert Error: " + ex.Message);
}
bulkCopy.Close();
}
result.RowsInserted = DT.Rows.Count;
result.RowsAffected = DT.Rows.Count;
return(result);
}
// CongNC
public string ThucHienDongBo(string sqlConnection)
{
try
{
// Lấy bảng thay đổi
DataTable dtThayDoi = GetThayDoi();
string SQL = "", TenBang = "", IDThayDoi = "";
DataTable dtbThayDoi;
if (dtThayDoi != null && dtThayDoi.Rows.Count > 0)
{
cDBase oDBase = new cDBase();
// Đẩy bảng HT_DongBo lên hosting để thực hiện xóa dữ liệu cũ
DataTable dtDongBo = Get(new HT_DongBoInfo());
using (SqlBulkCopy s = new SqlBulkCopy(sqlConnection))
{
s.DestinationTableName = "HT_DongBo";
s.NotifyAfter = 10000;
s.WriteToServer(dtDongBo);
s.Close();
}
// Xóa hết những dữ liệu đã có theo thay đổi trên DB Web
for (int i = 0; i < dtThayDoi.Rows.Count; i++)
{
TenBang = dtThayDoi.Rows[i]["TenBang"] + "";
SQL = "DELETE A FROM " + TenBang +
" A INNER JOIN HT_DongBo B ON B.TenBang = '" + TenBang +
"' AND DaDongBo = 0 AND A." + TenBang + "ID = B.IDThayDoi";
oDBase.RunQuery(sqlConnection, SQL);
// Lấy các dữ liệu thay đổi của bảng dtThayDoi.Rows[i]["TenBang"] có ID là IDThayDoi và ThaoTac là Update hoặc Insert
// Insert dữ liệu thay đổi mới lên DB Web
dtbThayDoi = GetDanhSachThayDoi(TenBang, IDThayDoi);
if (dtbThayDoi != null && dtbThayDoi.Rows.Count > 0)
{
using (SqlBulkCopy s = new SqlBulkCopy(sqlConnection))
{
s.DestinationTableName = TenBang;
s.NotifyAfter = 10000;
s.WriteToServer(dtbThayDoi);
s.Close();
}
}
}
// Xóa dữ liệu bảng HT_DongBo trên hosting
SQL = "DELETE HT_DongBo";
oDBase.RunQuery(sqlConnection, SQL);
// Xong duoi thi quay lại cập nhật lại trạng thái cho bảng đồng bộ DB ở trường
UpdateDanhSachThayDoi();
}
return("TRUE");
}
catch (Exception es)
{
return(es.Message);
}
}
protected void InsertDataToDbBulkMethod(DataTable table)
{
SqlBulkCopy bcopy = new SqlBulkCopy(_conn as SqlConnection);
bcopy.DestinationTableName = table.TableName;
bcopy.WriteToServer(table);
bcopy.Close();
}
public static void SqlBCP(DataTable dt, string destablename)
{
SqlBulkCopy sqlBulkCopyMobile = new SqlBulkCopy(connectionString);
sqlBulkCopyMobile.DestinationTableName = destablename;
sqlBulkCopyMobile.WriteToServer(dt);
sqlBulkCopyMobile.Close();
}
protected virtual void Dispose(bool disposing)
{
if (disposed)
{
return;
}
if (disposing)
{
if (bulkCopy != null)
{
bulkCopy.Close();
bulkCopy = null;
}
}
disposed = true;
}
protected void disconnect()
{
if (sqlBulkCopy != null)
{
sqlBulkCopy.Close();
sqlBulkCopy = null;
}
}
private void bulkWriteToPacking(System.Data.DataTable dataTable)
{
SqlConnection bulkConn = new SqlConnection(dbConnStr);
SqlBulkCopy SBC = new SqlBulkCopy(bulkConn);
SBC.DestinationTableName = "dbo.PACKING";
try
{
//對應資料行
SBC.ColumnMappings.Add("客戶物料", "CUS_ITEM");
SBC.ColumnMappings.Add("訂單號碼", "NBR");
SBC.ColumnMappings.Add("出貨人代號", "CUS_NBR");
SBC.ColumnMappings.Add("出貨人名稱", "CUS_ALIAS");
SBC.ColumnMappings.Add("預計出貨日", "DATE");
SBC.ColumnMappings.Add("結帳月份", "ACR_MON");
SBC.ColumnMappings.Add("客戶訂單", "DESC_NO");
SBC.ColumnMappings.Add("品號", "ITEM_NBR");
SBC.ColumnMappings.Add("品名", "ITEM_DESC");
SBC.ColumnMappings.Add("單位", "UN_DESC");
SBC.ColumnMappings.Add("總數量", "QTY");
SBC.ColumnMappings.Add("內盒", "IN_NBR");
SBC.ColumnMappings.Add("滿箱數", "IN_BOX");
SBC.ColumnMappings.Add("外箱", "PBOX_NBR");
SBC.ColumnMappings.Add("單箱數量", "QTY_PBOX");
SBC.ColumnMappings.Add("淨重", "N_WIGHT");
SBC.ColumnMappings.Add("總淨重", "TOTN_WIGHT");
SBC.ColumnMappings.Add("毛重", "G_WIGHT");
SBC.ColumnMappings.Add("總毛重", "TOTG_WIGHT");
SBC.ColumnMappings.Add("才數", "CUFT");
SBC.ColumnMappings.Add("總才數", "TOTCUFT");
SBC.ColumnMappings.Add("箱數", "PACK_QTY");
SBC.ColumnMappings.Add("起始箱號", "NO1");
SBC.ColumnMappings.Add("結束箱號", "NO2");
SBC.ColumnMappings.Add("箱號", "_NullFlags");
SBC.ColumnMappings.Add("項次", "POSNR");
SBC.ColumnMappings.Add("舊料號", "IHREZ_E");
SBC.ColumnMappings.Add("內盒舊品號", "BOX_O");
SBC.ColumnMappings.Add("外箱舊品號", "CTN_O");
SBC.ColumnMappings.Add("包裝指示碼", "POBJID");
SBC.ColumnMappings.Add("買方代號", "KUNNR_S");
SBC.ColumnMappings.Add("買方名稱", "NAME1_S");
SBC.ColumnMappings.Add("KEY", "KEY");
SBC.ColumnMappings.Add("USERID", "USERID");
bulkConn.Open();
SBC.WriteToServer(dataTable);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
SBC.Close();
bulkConn.Close();
}
}
public static void SavePrograms(List <Program> programs)
{
#if DEBUG
Console.WriteLine(string.Format("{0} Loading programs data into DataTable...", DateTime.Now));
DateTime debugStartTime = DateTime.Now;
#endif
string[] columns = new string [] { "ID", "DisplayName", "Type", "IsActive" };
DataTable dt = new DataTable();
DataRow row;
foreach (string dc in columns)
{
dt.Columns.Add(new DataColumn(dc));
}
foreach (Program p in programs)
{
row = dt.NewRow();
row ["ID"] = p.ID;
row ["DisplayName"] = p.DisplayName;
row ["Type"] = p.Type;
row ["IsActive"] = p.IsActive;
dt.Rows.Add(row);
}
#if DEBUG
TimeSpan debugEndTime = DateTime.Now.Subtract(debugStartTime);
Console.WriteLine(string.Format("{0} Loading programs DataTable took {1} second(s) to execute. It has {2} rows. Verifying if Fenero_Programs table exists in SQL...", DateTime.Now, debugEndTime.TotalSeconds, dt.Rows.Count));
debugStartTime = DateTime.Now;
#endif
string conn = ConfigurationManager.ConnectionStrings["FeneroStaging"].ToString();
SqlConnection con = new SqlConnection(conn);
con.Open();
CheckIfTableExists("Fenero_Programs", dt, con);
#if DEBUG
debugEndTime = DateTime.Now.Subtract(debugStartTime);
Console.WriteLine(string.Format("{0} Done checking if programs SQL table exists. Preparing to WriteToServer...", DateTime.Now));
debugStartTime = DateTime.Now;
#endif
// move data from data table into DB
SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = "Fenero_Programs";
bc.BatchSize = dt.Rows.Count;
bc.WriteToServer(dt);
bc.Close();
con.Close();
#if DEBUG
debugEndTime = DateTime.Now.Subtract(debugStartTime);
Console.WriteLine(string.Format("{0} WriteToServer for Fenero_Programs completed.", DateTime.Now));
debugStartTime = DateTime.Now;
#endif
}
private static async Task TestAsync(string srcConstr, string dstConstr, string dstTable, SemaphoreSlim outputSemaphore)
{
DataSet dataset;
SqlDataAdapter adapter;
DataTable datatable;
DataRow[] rows;
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10), col4 datetime)");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select * from employees", srcConn))
{
srcConn.Open();
dataset = new DataSet("MyDataSet");
adapter = new SqlDataAdapter(srcCmd);
adapter.Fill(dataset);
datatable = dataset.Tables[0];
rows = new DataRow[datatable.Rows.Count];
for (int i = 0; i < rows.Length; i++)
{
rows[i] = datatable.Rows[i];
}
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
bulkcopy.BatchSize = 4;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add(0, "col1");
ColumnMappings.Add(2, "col3");
await bulkcopy.WriteToServerAsync(rows);
bulkcopy.Close();
}
await outputSemaphore.WaitAsync();
Helpers.VerifyResults(dstConn, dstTable, 4, 9);
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public DataTable RetornaDados(string schema, string key, string tabela, DataTable colunas, List <string> pks)
{
var nomeTempTable = $"##{(Guid.NewGuid().ToString().Replace("-", ""))}";
var listTypeString = new List <String>()
{
"nvarchar", "varchar", "varbinary"
};
var typePkIsString = colunas.AsEnumerable().Where(x => x["COLUMN_NAME"].ToString() == key).Any(s => listTypeString.Any(x => x == s["DATA_TYPE"].ToString()));
if (pks != null && pks.Count > 0)
{
var table = new DataTable();
table.Columns.Add("col1", typeof(string));
foreach (var row in pks)
{
table.Rows.Add(row);
}
var queryTempTable = $@"
CREATE TABLE {nomeTempTable}(
col1 varchar(max)
)
";
_connection = validaConexao(_connection);
var command = _connection.CreateCommand();
command.CommandText = queryTempTable;
command.CommandTimeout = 0;
command.ExecuteNonQuery();
Program.AtualizarStatus(server, $"Inserindo as pks na tabela temporaria para recuperar informacoes {nomeTempTable}", null);
using (var bulkcopy = new SqlBulkCopy(_connectionString))
{
bulkcopy.BulkCopyTimeout = 0;
bulkcopy.DestinationTableName = nomeTempTable;
bulkcopy.BatchSize = 1000;
bulkcopy.NotifyAfter = 1000;
bulkcopy.SqlRowsCopied += (objc, EventArgs) =>
{
Program.AtualizarStatus(server, $"(temp) Qtd de linhas já inserida para recuperar dados {EventArgs.RowsCopied} de {table.Rows.Count}", null);
};
bulkcopy.WriteToServer(table);
bulkcopy.Close();
Program.AtualizarStatus(server, $"Inserção na tabela temporaria finalizada", null);
}
}
Program.AtualizarStatus(server, $"Recuperando dados para ser inserido no destino", null);
var querySelect = $@"
select {string.Join(", ", colunas.AsEnumerable().Select(x => x["COLUMN_NAME"]))} from {schema}.{tabela}
{(pks.Count > 0 ? $@"
where {key} {(typePkIsString ? "COLLATE DATABASE_DEFAULT" : "")} in (SELECT col1 FROM {nomeTempTable} )
" : "")}
order by {key}
";
return(returnDataTable(querySelect));
}
