public TableModule(string dbname, string tablename)
{
_Selected = false;
try
{
_TableName = tablename;
_Fields = new List <FieldModule>();
//GeneralDao dao = new GeneralDao("139.223.24.227", dbname, "sa", "1qazxcvbnm,./");
GeneralDao dao = new GeneralDao(GlobalParameters.DBConStr_IP, dbname, GlobalParameters.DBConStr_Account, GlobalParameters.DBConStr_PW);
string strSql = "SELECT COLUMN_NAME,* FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tablename + "' AND TABLE_SCHEMA = 'dbo'";
DataTable dtField = dao.QueryForDataTable(strSql);
if (dtField.Rows.Count > 0)
{
foreach (DataRow row in dtField.Rows)
{
FieldModule newItem = new FieldModule(false, row["COLUMN_NAME"].ToString(), row["DATA_TYPE"].ToString(), row["CHARACTER_MAXIMUM_LENGTH"].ToString());
if (!_Fields.Contains(newItem))
{
_Fields.Add(newItem);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataModule()
{
try
{
GeneralDao dao = null;
DataTable dtSource = new DataTable();
DataTable dtDBNameTables = new DataTable();
DataTable dtTablesName = new DataTable();
DataTable dtFieldName = new DataTable();
dtSource.Columns.Add("Selected", typeof(string));
dtSource.Columns.Add("DBName", typeof(string));
dtSource.Columns.Add("SourceTableName", typeof(string));
dtSource.Columns.Add("DestinationTableName", typeof(string));
dao = new GeneralDao("139.223.24.223", "master", "sa", "1qazxcvbnm,./");
string strSql = "SELECT name FROM master.dbo.sysdatabases";
dtDBNameTables = dao.QueryForDataTable(strSql);
if (dtDBNameTables.Rows.Count > 0)
{
foreach (DataRow dbrow in dtDBNameTables.Rows)
{
if (!DBEntity.ContainsKey(dbrow["name"].ToString()))
{
DBEntity.Add(dbrow["name"].ToString(), null);
}
switch (dbrow["name"].ToString())
{
case "jdcs":
case "g0":
case "jdcstest":
case "exdcs":
case "tdcs":
case "jdcsx3":
GeneralDao dbdao = new GeneralDao("139.223.24.223", dbrow["name"].ToString(), "sa", "1qazxcvbnm,./");
strSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '" + dbrow["name"].ToString() + "'";
dtTablesName = dbdao.QueryForDataTable(strSql);
if (dtTablesName.Rows.Count > 0)
{
foreach (DataRow tnrow in dtTablesName.Rows)
{
//if()
strSql = "SELECT COLUMN_NAME,*FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tnrow["TABLE_NAME"].ToString() + "' AND TABLE_SCHEMA = 'dbo'";
dtFieldName = dbdao.QueryForDataTable(strSql);
foreach (DataRow fnrow in dtFieldName.Rows)
{
DBEntity.Add(dbrow["name"].ToString(), new Dictionary <string, List <string> >()
{
{ tnrow["TABLE_NAME"].ToString(), new List <string>()
{
fnrow["COLUMN_NAME"].ToString()
} }
});
}
}
}
break;
default:
break;
}
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataBaseModule(string dbname)
{
try
{
_DBName = dbname;
_Tables = new List <TableModule>();
//GeneralDao dao = new GeneralDao("139.223.24.227", dbname, "sa", "1qazxcvbnm,./");
GeneralDao dao = new GeneralDao(GlobalParameters.DBConStr_IP, dbname, GlobalParameters.DBConStr_Account, GlobalParameters.DBConStr_PW);
string strSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '" + dbname + "'";
DataTable dtTables = dao.QueryForDataTable(strSql);
if (dtTables.Rows.Count > 0)
{
foreach (DataRow row in dtTables.Rows)
{
TableModule newItem = new TableModule(dbname, row["TABLE_NAME"].ToString());
if (!_Tables.Contains(newItem))
{
_Tables.Add(newItem);
}
}
}
//DataTable dtSource = new DataTable();
//DataTable dtDBNameTables = new DataTable();
//DataTable dtTablesName = new DataTable();
//DataTable dtFieldName = new DataTable();
//dtSource.Columns.Add("Selected", typeof(string));
//dtSource.Columns.Add("DBName", typeof(string));
//dtSource.Columns.Add("SourceTableName", typeof(string));
//dtSource.Columns.Add("DestinationTableName", typeof(string));
//dao = new GeneralDao("139.223.24.227", "master", "sa", "1qazxcvbnm,./");
//string strSql = "SELECT name FROM master.dbo.sysdatabases";
//dtDBNameTables = dao.QueryForDataTable(strSql);
//if (dtDBNameTables.Rows.Count > 0)
//{
// foreach (DataRow dbrow in dtDBNameTables.Rows)
// {
// if (!DBEntity.ContainsKey(dbrow["name"].ToString()))
// {
// DBEntity.Add(dbrow["name"].ToString(), null);
// }
// switch (dbrow["name"].ToString())
// {
// case "jdcs":
// case "g0":
// case "jdcstest":
// case "exdcs":
// case "tdcs":
// case "jdcsx3":
// GeneralDao dbdao = new GeneralDao("139.223.24.22", dbrow["name"].ToString(), "sa", "1qazxcvbnm,./");
// strSql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = '" + dbrow["name"].ToString() + "'";
// dtTablesName = dbdao.QueryForDataTable(strSql);
// if (dtTablesName.Rows.Count > 0)
// {
// foreach (DataRow tnrow in dtTablesName.Rows)
// {
// //if()
// strSql = "SELECT COLUMN_NAME,*FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + tnrow["TABLE_NAME"].ToString() + "' AND TABLE_SCHEMA = 'dbo'";
// dtFieldName = dbdao.QueryForDataTable(strSql);
// foreach (DataRow fnrow in dtFieldName.Rows)
// {
// DBEntity.Add(dbrow["name"].ToString(), new Dictionary<string, List<string>>() { { tnrow["TABLE_NAME"].ToString(), new List<string>() { fnrow["COLUMN_NAME"].ToString() } } });
// }
// }
// }
// break;
// default:
// break;
// }
// }
//}
}
catch (Exception ex)
{
throw ex;
}
}
private void StartTransfer(int iActFlag)
{
try
{
GeneralDao dao = null;;
string strSql = string.Empty;
Stopwatch sw = new Stopwatch();
string rowid = string.Empty;
double TotalBackupRows = 0;
double TotalTransRows = 0;
//開始轉碼
//備份各欄位資料
#region 備份資料
if (iActFlag == 0 || iActFlag == 2)//全部||只備份不轉碼
{
foreach (DataBaseModule dbm in ComfirmItem)
{
//dao = new GeneralDao("139.223.24.227", dbm.DBName, "sa", "1qazxcvbnm,./");
dao = new GeneralDao(GlobalParameters.DBConStr_IP, dbm.DBName, GlobalParameters.DBConStr_Account, GlobalParameters.DBConStr_PW);
////重建資料型態TVP_Transfer
//strSql = "IF type_id('[dbo].[TVP_Transfer]') IS NOT NULL DROP TYPE[dbo].[TVP_Transfer]";
//dao.Execute(strSql);
////strSql = "CREATE TYPE [dbo].[TVP_Transfer] AS TABLE([rowid][int] NOT NULL,[tablename] [varchar] (20) NOT NULL,[fieldname] [varchar] (20) NOT NULL,[value] [nvarchar] (512) NOT NULL)";
//strSql = "CREATE TYPE [dbo].[TVP_Transfer] AS TABLE([rowid][int] NOT NULL,[value] [nvarchar] (512) NOT NULL)";
//dao.Execute(strSql);
foreach (TableModule tbm in dbm.Tables)
{
DataTable tvp = new DataTable();
//測試2 使用TVP
using (SqlConnection cn = new SqlConnection(dao.ConnectionString))
{
cn.Open();
sw.Reset();
sw.Start();
int fieldCount = 0;
string UpdateField = string.Empty;
SqlCommand cmd = cn.CreateCommand();
cmd.CommandTimeout = 600;
cmd.Parameters.Clear();
SqlParameter pTVP = cmd.Parameters.Add("@UpdateTable", SqlDbType.Structured);
pTVP.Value = tvp; //SqlParameter選用SqlDbType.Structured並指定TypeName
pTVP.TypeName = "TVP_Transfer";
//cmd.Parameters.AddWithValue("@tablename", tbm.TableName);
tvp.Columns.Add("rowid", typeof(SqlInt32));
//tvp.Columns.Add("tablename", typeof(SqlString));
//tvp.Columns.Add("fieldname", typeof(SqlString));
//tvp.Columns.Add("value", typeof(SqlString));
strSelectedFields = new List <string>();
strSelectedMSFields = new List <string>();
foreach (FieldModule fdm in tbm.Fields)
{
if (fdm.Selected)
{
fieldCount++;
#region 欄位處理
try
{
//查詢備份欄位
strSql = "select Name from sys.columns where Name = '" + fdm.MS950_FieldName + "' and Object_ID = Object_ID('" + tbm.TableName + "')";
DataTable dt = dao.QueryForDataTable(strSql);
if (dt.Rows.Count == 0) //查無備份欄位
{
//新增備位欄位
strSql = "ALTER TABLE " + tbm.TableName + " ADD " + fdm.MS950_FieldName + " [varchar](" + (Convert.ToInt16(Convert.ToInt16(fdm.DataLength) * 1.5)) + ") NOT NULL DEFAULT ''";
dao.Execute(strSql);
}
strSelectedFields.Add(fdm.FieldName);
strSelectedMSFields.Add(fdm.MS950_FieldName);
tvp.Columns.Add(fdm.FieldName, typeof(SqlString));
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
}
if (strSelectedFields.Count > 0)
{
//重建資料型態TVP_Transfer
strSql = "IF type_id('[dbo].[TVP_Transfer]') IS NOT NULL DROP TYPE[dbo].[TVP_Transfer]";
dao.Execute(strSql);
//strSql = "CREATE TYPE [dbo].[TVP_Transfer] AS TABLE([rowid][int] NOT NULL,[tablename] [varchar] (20) NOT NULL,[fieldname] [varchar] (20) NOT NULL,[value] [nvarchar] (512) NOT NULL)";
strSql = "CREATE TYPE [dbo].[TVP_Transfer] AS TABLE([rowid][int]";// NOT NULL,[value] [nvarchar] (512) NOT NULL)";
foreach (string ss in strSelectedFields)
{
strSql += ",[" + ss + "][nvarchar](512) NOT NULL DEFAULT ''";
}
strSql += ")";
dao.Execute(strSql);
//將原有資料匯入ms950欄位
//取出原所有需備份資料//新增欄位值
int fieldcount = 0;
strSql = "SELECT rowid" + (strSelectedFields.Count > 0 ? "," + string.Join(",", strSelectedFields) : "") + " FROM " + tbm.TableName;
DataTable Backdt = dao.QueryForDataTable(strSql);
if (Backdt.Rows.Count > 0) //有資料
{
for (int i = 0; i < Backdt.Rows.Count; i++)
{
TotalBackupRows++;
DataRow tvpNew = tvp.NewRow();
tvpNew["rowid"] = Backdt.Rows[i]["rowid"];
foreach (string field in strSelectedFields)
{
tvpNew[field] = Backdt.Rows[i][field] == DBNull.Value ? "" : Backdt.Rows[i][field];
}
//tvpNew["tablename"] = tbm.TableName;
//tvpNew["fieldname"] = fdm.FieldName;
//tvpNew["aaa"] = Backdt.Rows[i][fdm.FieldName] == DBNull.Value ? "" : Backdt.Rows[i][fdm.FieldName];
tvp.Rows.Add(tvpNew);
//滿10000更新一次資料
if (tvp.Rows.Count > 999)
{
cmd.CommandText = "UPDATE " + tbm.TableName + " SET ";
for (int ii = 0; ii < strSelectedMSFields.Count; ii++)
{
if (ii > 0)
{
cmd.CommandText += ",";
}
cmd.CommandText += (strSelectedMSFields[ii] + "=ut." + strSelectedFields[ii]);
}
//cmd.CommandText += fdm.MS950_FieldName + "=ut.value";// + (((fdm.FieldName == "DJ_WORD") && (dbm.DBName == "jdcs") && (tbm.TableName == "DCS0_DOC_JONL")) ? " ,flag=1" : "");
cmd.CommandText += " FROM " + tbm.TableName + " bt Join @UpdateTable ut On bt.rowid=ut.rowid";
try
{
fieldcount = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + dbm.DBName + "：" + tbm.TableName);
}
tvp.Rows.Clear();//清除已更新資料庫後的資料集
Console.WriteLine("資料表{0}欄位，{2:N0}ms {1}筆紀錄備份", tbm.TableName, fieldcount, sw.ElapsedMilliseconds);
txtMsg.AppendText(Environment.NewLine + string.Format("資料表{0}欄位，{2:N0}ms {1}筆紀錄備份", tbm.TableName, fieldcount, sw.ElapsedMilliseconds));
txtMsg.SelectionStart = txtMsg.TextLength;
txtMsg.ScrollToCaret();
txtMsg.Refresh();
}
}
//已更新批次萬筆資料後，剩餘資料更新
if (tvp.Rows.Count > 0)
{
cmd.CommandText = "UPDATE " + tbm.TableName + " SET ";
for (int ii = 0; ii < strSelectedMSFields.Count; ii++)
{
if (ii > 0)
{
cmd.CommandText += ",";
}
cmd.CommandText += (strSelectedMSFields[ii] + "=ut." + strSelectedFields[ii]);
}
//cmd.CommandText += fdm.MS950_FieldName + "=ut.value";
cmd.CommandText += " FROM " + tbm.TableName + " bt Join @UpdateTable ut On bt.rowid=ut.rowid";
try
{
fieldcount = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + dbm.DBName + "：" + tbm.TableName);
}
tvp.Rows.Clear();// 清除已更新資料庫後的資料集
Console.WriteLine("資料表{0}欄位，{2:N0}ms {1}筆紀錄備份", tbm.TableName, fieldcount, sw.ElapsedMilliseconds);
txtMsg.AppendText(Environment.NewLine + string.Format("資料表{0}欄位，{2:N0}ms {1}筆紀錄備份", tbm.TableName, fieldcount, sw.ElapsedMilliseconds));
txtMsg.SelectionStart = txtMsg.TextLength;
txtMsg.ScrollToCaret();
txtMsg.Refresh();
}
}
}
sw.Stop();
}
}
}
Console.WriteLine(DateTime.Now + "備份結束");
txtMsg.AppendText(Environment.NewLine + string.Format(DateTime.Now + "備份結束"));
txtMsg.SelectionStart = txtMsg.TextLength;
txtMsg.ScrollToCaret();
txtMsg.Refresh();
}
#endregion
#region 資料轉碼
if (iActFlag == 0 || iActFlag == 1)//全部||只轉碼不備份
{
DialogResult result = MessageBox.Show("所有欄位已備份，請問是否繼續？", "請選擇", MessageBoxButtons.YesNoCancel);
if (DialogResult.Yes == result)
{
sw.Reset();
sw.Start();
Console.WriteLine(DateTime.Now + "轉碼開始");
txtMsg.AppendText(Environment.NewLine + string.Format(DateTime.Now + "轉碼開始"));
txtMsg.SelectionStart = txtMsg.TextLength;
txtMsg.ScrollToCaret();
txtMsg.Refresh();
//轉碼並更新各欄位
foreach (DataBaseModule dbm in ComfirmItem)
{
//dao = new GeneralDao("139.223.24.227", dbm.DBName, "sa", "1qazxcvbnm,./");
dao = new GeneralDao(GlobalParameters.DBConStr_IP, dbm.DBName, GlobalParameters.DBConStr_Account, GlobalParameters.DBConStr_PW);
foreach (TableModule tbm in dbm.Tables)
{
DataTable tvp = new DataTable();
//測試2 使用TVP
using (SqlConnection cn = new SqlConnection(dao.ConnectionString))
{
cn.Open();
int fieldCount = 0;
SqlCommand cmd = cn.CreateCommand();
cmd.ResetCommandTimeout();
cmd.CommandTimeout = 120;
cmd.Parameters.Clear();
SqlParameter pTVP = cmd.Parameters.Add("@UpdateTable", SqlDbType.Structured);
pTVP.Value = tvp; //SqlParameter選用SqlDbType.Structured並指定TypeName
pTVP.TypeName = "TVP_Transfer";
tvp.Columns.Add("rowid");
strSelectedFields = new List <string>();
strSelectedMSFields = new List <string>();
foreach (FieldModule fdm in tbm.Fields)
{
if (fdm.Selected)
{
fieldCount++;
#region 欄位處理
try
{
DataTable dt;
strSql = "select Name from sys.columns where Name = '" + fdm.FieldName + "' and Object_ID = Object_ID('" + tbm.TableName + "')";
dt = dao.QueryForDataTable(strSql);
if (dt.Rows.Count == 1)
{
//strSql = "IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'" + tbm.TableName + "') AND name = '" + fdm.MS950_FieldName + "' ) ALTER TABLE " + tbm.TableName + " ADD " + fdm.MS950_FieldName + " [varchar](" + fdm.DataLength + ") NOT NULL DEFAULT ''";
strSql = "ALTER TABLE " + tbm.TableName + " ALTER COLUMN " + fdm.FieldName + " [nvarchar](" + (fdm.DataLength) + ")";
dao.Execute(strSql);
}
strSelectedFields.Add(fdm.FieldName);
strSelectedMSFields.Add(fdm.MS950_FieldName);
tvp.Columns.Add(fdm.FieldName, typeof(SqlString));
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
}
if (strSelectedFields.Count > 0)
{
//重建資料型態TVP_Transfer
strSql = "IF type_id('[dbo].[TVP_Transfer]') IS NOT NULL DROP TYPE[dbo].[TVP_Transfer]";
dao.Execute(strSql);
//strSql = "CREATE TYPE [dbo].[TVP_Transfer] AS TABLE([rowid][int] NOT NULL,[tablename] [varchar] (20) NOT NULL,[fieldname] [varchar] (20) NOT NULL,[value] [nvarchar] (512) NOT NULL)";
strSql = "CREATE TYPE [dbo].[TVP_Transfer] AS TABLE([rowid][int]";// NOT NULL,[value] [nvarchar] (512) NOT NULL)";
foreach (string ss in strSelectedFields)
{
strSql += ",[" + ss + "][nvarchar](512) NOT NULL DEFAULT ''";
}
strSql += ")";
dao.Execute(strSql);
//確認是否資料表存在
strSql = "IF OBJECT_ID('dbo.EncodingTransform', 'U') IS NULL CREATE TABLE [dbo].[EncodingTransform]([rowid][int] IDENTITY(1, 1) NOT NULL,[RefRowid] [int] NOT NULL,[Position] [int] NOT NULL,[Original] [varchar] (1000) NOT NULL,[TableName] [varchar] (200) NOT NULL,[FieldName] [varchar] (200) NOT NULL,[TransWord] [nvarchar] (200) NULL,[ENCType] [INT] NULL,[CreateDate] [datetime] NULL,[ModifyDate] [datetime] NULL)";
dao.Execute(strSql);
strSql = "IF OBJECT_ID('dbo.EncodingTransform', 'U') IS NULL ALTER TABLE [dbo].[EncodingTransform] ADD CONSTRAINT [DF_EncodingTransform_CreateDate] DEFAULT (getdate()) FOR [CreateDate]";
dao.Execute(strSql);
strSql = "SELECT rowid" + (strSelectedMSFields.Count > 0 ? "," + string.Join(",", strSelectedMSFields) : "") + " FROM " + tbm.TableName;// + " WHERE rowid='3153358'";
DataTable dtResult = dao.QueryForDataTable(strSql);
if (dtResult.Rows.Count > 0)
{
for (int i = 0; i < dtResult.Rows.Count; i++)
{
TotalTransRows++;
rowid = dtResult.Rows[i]["rowid"].ToString();
EncodingTransfer ET = new EncodingTransfer();
DataRow tvpNew = tvp.NewRow();
tvpNew["rowid"] = rowid;
for (int iii = 0; iii < strSelectedMSFields.Count; iii++)
{
string strUTF8Result = ET.filterOutUTF8ExtraChar(dtResult.Rows[i][strSelectedMSFields[iii]].ToString());
//寫入已轉好的文字進資料庫
if (ET.UnknowWords.Count > 0)
{
//SendtfResultList.Add(string.Format(@"檔名：{0}，轉碼錯誤：{1}", fn, memo));
//回寫異常轉碼錯誤說明至資料庫
foreach (Abnormal item in ET.UnknowWords)
{
strSql = "INSERT INTO EncodingTransform (RefRowid,Position,Original,TableName,FieldName,TransWord,ENCType) VALUES (";
strSql += "'" + rowid + "'";
strSql += ",'" + item.WordCount + "'";
strSql += ",N'" + item.OriginWord + "'";
strSql += ",'" + tbm.TableName + "'";
strSql += ",'" + strSelectedMSFields[iii] + "'";
strSql += ",'" + item.TransWord + "'";
strSql += ",0";
strSql += ")";
dao.Execute(strSql);
}
ET.UnknowWords.Clear();
}
else if (ET.UnknowWords.Count == 0)
{
}
//回寫已轉碼文字至資料庫
if (ET.TransferedWords.Count > 0)
{
foreach (Abnormal item in ET.TransferedWords)
{
strSql = "INSERT INTO EncodingTransform (RefRowid,Position,Original,TableName,FieldName,TransWord,ENCType) VALUES (";
strSql += "'" + rowid + "'";
strSql += ",'" + item.WordCount + "'";
strSql += ",N'" + item.OriginWord + "'";
strSql += ",'" + tbm.TableName + "'";
strSql += ",'" + strSelectedMSFields[iii] + "'";
strSql += ",N'" + item.TransWord + "'";
strSql += ",1";
strSql += ")";
dao.Execute(strSql);
}
ET.TransferedWords.Clear();
}
tvpNew[strSelectedFields[iii]] = strUTF8Result;//dtResult.Rows[i][field] == DBNull.Value ? "" : dtResult.Rows[i][field];
}
tvp.Rows.Add(tvpNew);
if ((tvp.Rows.Count > 999))
{
cmd.CommandText = "UPDATE " + tbm.TableName + " SET ";
for (int ii = 0; ii < strSelectedFields.Count; ii++)
{
if (ii > 0)
{
cmd.CommandText += ",";
}
cmd.CommandText += (strSelectedFields[ii] + "=ut." + strSelectedFields[ii]);
}
cmd.CommandText += " FROM " + tbm.TableName + " bt Join @UpdateTable ut On bt.rowid=ut.rowid";
int ECount = 0;
try
{
ECount = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + dbm.DBName + "：" + tbm.TableName);
}
Console.WriteLine("TVP匯入資料庫{3}：資料表 {1} {0:N0}ms，共{2}筆資料被更新", sw.ElapsedMilliseconds, tbm.TableName, ECount, dbm.DBName);
txtMsg.AppendText(Environment.NewLine + string.Format("TVP匯入資料庫{3}：資料表 {1} {0:N0}ms，共{2}筆資料被更新", sw.ElapsedMilliseconds, tbm.TableName, ECount, dbm.DBName));
txtMsg.SelectionStart = txtMsg.TextLength;
txtMsg.ScrollToCaret();
txtMsg.Refresh();
tvp.Rows.Clear();
}
}
if (tvp.Rows.Count > 0)
{
cmd.CommandText = "UPDATE " + tbm.TableName + " SET ";
for (int ii = 0; ii < strSelectedFields.Count; ii++)
{
if (ii > 0)
{
cmd.CommandText += ",";
}
cmd.CommandText += (strSelectedFields[ii] + "=ut." + strSelectedFields[ii]);
}
cmd.CommandText += " FROM " + tbm.TableName + " bt Join @UpdateTable ut On bt.rowid=ut.rowid";
int ECount = 0;
try
{
ECount = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + dbm.DBName + "：" + tbm.TableName);
}
Console.WriteLine("TVP匯入資料庫{3}：資料表 {1} {0:N0}ms，共{2}筆資料被更新", sw.ElapsedMilliseconds, tbm.TableName, ECount, dbm.DBName);
txtMsg.AppendText(Environment.NewLine + string.Format("TVP匯入資料庫{3}：資料表 {1} {0:N0}ms，共{2}筆資料被更新", sw.ElapsedMilliseconds, tbm.TableName, ECount, dbm.DBName));
txtMsg.SelectionStart = txtMsg.TextLength;
txtMsg.ScrollToCaret();
txtMsg.Refresh();
tvp.Rows.Clear();
}
}
}
cn.Close();
}
}
}
Console.WriteLine(DateTime.Now + "轉碼結束");
Console.WriteLine("共備份" + TotalBackupRows + "筆資料");
Console.WriteLine("共轉碼" + TotalTransRows + "筆資料");
txtMsg.AppendText(Environment.NewLine + string.Format(DateTime.Now + "轉碼結束"));
txtMsg.AppendText(Environment.NewLine + string.Format("共備份" + TotalBackupRows + "筆資料"));
txtMsg.AppendText(Environment.NewLine + string.Format("共轉碼" + TotalTransRows + "筆資料"));
txtMsg.SelectionStart = txtMsg.TextLength;
txtMsg.ScrollToCaret();
txtMsg.Refresh();
sw.Stop();
}
else if (DialogResult.No == result || DialogResult.Cancel == result)
{
MessageBox.Show("取消轉碼作業");
return;
}
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
}