static async void Main(string[] args)
{
// Instantiate a new SQL Server connection
var conn = "Data Source=SQL01;Initial Catalog=db;Persist Security Info=True;User ID=user;Password=pass";
var db = new MSSQLRepository(conn);
// Create a new 'Employee' with auto generated id
var newEmp = new Employee()
{
Name = "Jeremy Johnson", Email = "*****@*****.**"
};
var newTask = db.NewAsync("INSERT INTO [Employees] ([Name], [Email]) VALUES (@Name, @Email)", record: newEmp);
newEmp.ID = newTask.Result;
// Get all 'Employees'
var getTask = db.GetAsync <Employee>("SELECT [ID],[Name],[Email] FROM [Employees]");
var emps = getTask.Result;
// Bulk insert all 'Employees' into the 'PR_Employees' table
await db.NewAsync(emps, "PR_Employees");
// Get 'Employee' with id 123
(string, object)empID = (nameof(Employee.ID), 123);
getTask = db.GetAsync <Employee>("SELECT [ID],[Name],[Email] FROM [Employees] WHERE [ID] = @ID", @params: new[] { empID });
var emp123 = getTask.Result.First();
// Update email address for 'Employee' 123
emp123.Email = "*****@*****.**";
await db.EditAsync("UPDATE [Employees] SET [Email] = '@Email' WHERE [ID] = @ID", record : emp123);
// Delete 'Employee' 123
await db.RemoveAsync("DELETE FROM [Employees] WHERE [ID] = @ID", record : emp123);
}
private void OracleTOMSSQLDATA(List <TableToDB> Data, bool big5 = false)
{
Dictionary <string, Exception> Error1 = new Dictionary <string, Exception>();
// 批次取值數量
int Catch_Max = 5;
int T_NUM = 0;
foreach (var item in Data)
{
try
{
var sqlcode = "";
string cloumnname = "";
T_NUM += 1;
List <int> Big5_ColNum = new List <int>();
richTextBox1.Text += string.Concat(T_NUM.ToString(), " - ", item.Table_Name + "\n");
MSSQLRepository <UpDefualtModelDTO> db = new MSSQLRepository <UpDefualtModelDTO>();
Oracle9iRepository <ACC_TITLE> services = new Oracle9iRepository <ACC_TITLE>();
var num_sql = (int)services.GetAlls <dynamic>("SELECT count(*) COUNT FROM " + item.Table_Name).FirstOrDefault().COUNT;
var list = services.GetAlls <dynamic>(
" SELECT " +
" B.TABLE_NAME AS TABLE_NAME , B.COLUMN_NAME AS COLUMN_NAME , B.COMMENTS AS COMMENTS , A.DATA_TYPE AS DATA_TYPE ,A.DATA_LENGTH AS DATA_LENGTH , A.DATA_PRECISION AS DATA_PRECISION,A.DATA_SCALE AS DATA_SCALE , A.NULLABLE AS NULLABLE " +
" FROM USER_TAB_COLUMNS A ,USER_COL_COMMENTS B WHERE A.TABLE_NAME = '" + item.Table_Name + "' AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME ORDER BY COLUMN_ID ").ToList();
if (big5)
{
var TmpD = services.GetAlls <dynamic>($" SELECT * FROM {item.Table_Name} WHERE ROWNUM < 6").ToList();
foreach (var t in TmpD)
{
int Q = 0;
foreach (var tt in t)
{
if (CheckEncode(tt.Value == null ?"":tt.Value.ToString()) == true)
{
Big5_ColNum.Add(Q);
}
Q += 1;
}
}
Big5_ColNum = Big5_ColNum.Distinct().ToList();
// RAWTOHEX
for (int ii = 0; ii < list.Count(); ii++)
{
cloumnname += (Big5_ColNum.Any(x => x == ii) == true ? string.Concat("RAWTOHEX(", list[ii].COLUMN_NAME, ") ", list[ii].COLUMN_NAME) : list[ii].COLUMN_NAME) + ",";
}
cloumnname = cloumnname.Substring(0, cloumnname.Length - 1);
}
for (int i = 0; i < (int)((num_sql / Catch_Max) + 1); i++)
{
sqlcode = String.Format("SELECT {0} FROM (SELECT ROWNUM NO, A.* FROM {1} A WHERE ROWNUM <= {2} {4}) WHERE NO > {3} {4}", cloumnname, item.Table_Name, Catch_Max * (i + 1), Catch_Max * i, item.Where.Replace("WHERE", "AND"));
var AE_list1 = services.GetAlls <dynamic>(sqlcode).ToList();
List <dynamic> AE_list = new List <dynamic>();
// Decode BIG5
foreach (var items in AE_list1)
{
int index = 0;
String EnHex = "";
List <dynamic> tmp = new List <dynamic>();
foreach (var ind in items)
{
if (Big5_ColNum.Any(x => x == index) == true)
{
string hex = ind.Value;
byte[] buff = new byte[hex.Length / 2];
for (int ie = 0; ie < hex.Length / 2; ie++)
{
buff[ie] = byte.Parse(hex.Substring(ie * 2, 2),
System.Globalization.NumberStyles.AllowHexSpecifier);
}
// 處理KeyValuePair唯獨
KeyValuePair <string, object> lKVP = new KeyValuePair <string, object>(ind.Key, Encoding.GetEncoding("big5").GetString(buff));
tmp.Add(lKVP);
//EnHex = Encoding.GetEncoding("big5").GetString(buff);
}
else
{
tmp.Add(ind);
}
index += 1;
}
AE_list.Add(tmp);
}
// Decode BIG5
//foreach (var items in AE_list)
//{
// string hex = items.PT_NAME;
// byte[] buff = new byte[hex.Length / 2];
// for (int index0 = 0; index0 < hex.Length / 2; index0++)
// {
// buff[index0] = byte.Parse(hex.Substring(index0 * 2, 2),
// System.Globalization.NumberStyles.AllowHexSpecifier);
// }
// items.PT_NAME = Encoding.GetEncoding("big5").GetString(buff);
//}
var frlp = ToDataTable(AE_list1);
//using (SqlConnection conn = new SqlConnection("Data Source=60...;I
////using (SqlConnection conn = new SqlConnection("Data Source=192..
//{
// conn.Open();
// SqlTransaction trans = conn.BeginTransaction();
// using (SqlBulkCopy bulkCopy =
// new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, trans))
// {
// bulkCopy.DestinationTableName = "dbo." + item.Table_Name;
// bulkCopy.BulkCopyTimeout = 3600;
// foreach (var itemss in list)
// {
// bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(itemss.COLUMN_NAME, itemss.COLUMN_NAME));
// }
// bulkCopy.WriteToServer(ToDataTable(AE_list));
// }
// trans.Commit();
// richTextBox1.Text += string.Concat("目前進度到" + (Catch_Max * (i + 1) >= num_sql ? num_sql : Catch_Max * (i + 1)).ToString() + "筆\n");
//}
}
//richTextBox1.Text += "--- success";
}
catch (Exception e)
{
//Error1.Add(item.Table_Name, e);
richTextBox1.Text += e;
}
}
richTextBox1.Text += "----------------------";
//return Error1;
}
public DBFactory(string connect)
{
FugoRepository = new FugoRepository(new DbConnectionHelper(connect));
MSSQLRepository = new MSSQLRepository(new DbConnectionHelper(connect));
MlecRepository = new MlecRepository(new DbConnectionHelper(connect));
}
private void OracleTOMSSQLDATA(List <TableToDB> Data)
{
Dictionary <string, Exception> Error1 = new Dictionary <string, Exception>();
// 批次取值數量
int Catch_Max = 10000;
int T_NUM = 0;
foreach (var item in Data)
{
try
{
var sqlcode = "";
string cloumnname = "";
T_NUM += 1;
richTextBox1.Text += string.Concat(T_NUM.ToString(), " - ", item.Table_Name + "\n");
MSSQLRepository <UpDefualtModelDTO> db = new MSSQLRepository <UpDefualtModelDTO>();
Oracle9iRepository <ACC_TITLE> services = new Oracle9iRepository <ACC_TITLE>();
var num_sql = (int)services.GetAlls <dynamic>("SELECT count(*) COUNT FROM " + item.Table_Name).FirstOrDefault().COUNT;
var list = services.GetAlls <dynamic>(
" SELECT " +
" B.TABLE_NAME AS TABLE_NAME , B.COLUMN_NAME AS COLUMN_NAME , B.COMMENTS AS COMMENTS , A.DATA_TYPE AS DATA_TYPE ,A.DATA_LENGTH AS DATA_LENGTH , A.DATA_PRECISION AS DATA_PRECISION,A.DATA_SCALE AS DATA_SCALE , A.NULLABLE AS NULLABLE " +
" FROM USER_TAB_COLUMNS A ,USER_COL_COMMENTS B WHERE A.TABLE_NAME = '" + item.Table_Name + "' AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME ORDER BY COLUMN_ID ").ToList();
foreach (var items in list)
{
cloumnname = cloumnname + items.COLUMN_NAME + ",";
}
cloumnname = cloumnname.Substring(0, cloumnname.Length - 1);
for (int i = 0; i < (int)((num_sql / Catch_Max) + 1); i++)
{
sqlcode = String.Format("SELECT {0} FROM (SELECT ROWNUM NO, A.* FROM {1} A WHERE ROWNUM <= {2} {4}) WHERE NO > {3} {4}", cloumnname, item.Table_Name, Catch_Max * (i + 1), Catch_Max * i, item.Where.Replace("WHERE", "AND"));
//sqlcode += " and " + where;
using (OracleConnection cn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST=192...)(PORT=123)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=abc)));User ID=abc;Password=abc;Enlist=false;Unicode=True"))
{
cn.Open();
using (OracleCommand command = new OracleCommand(sqlcode, cn))
{
//4.搭配SqlCommand物件使用SqlDataReader
using (OracleDataReader dr = command.ExecuteReader())
{
using (SqlConnection conn = new SqlConnection($"Data Source={Globals.ID_HOST};Initial Catalog=kmuhmis;Persist Security Info=True;User ID=sqlmis;Password=!qaz2wsx;Connection Timeout=0"))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, trans))
{
bulkCopy.DestinationTableName = "dbo." + item.Table_Name;
bulkCopy.BulkCopyTimeout = 36000;
foreach (var itemss in list)
{
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(itemss.COLUMN_NAME, itemss.COLUMN_NAME));
}
bulkCopy.WriteToServer(dr);
}
trans.Commit();
richTextBox1.Text += string.Concat("目前進度到" + (Catch_Max * (i + 1) >= num_sql ? num_sql : Catch_Max * (i + 1)).ToString() + "筆\n");
}
}
}
}
}
//richTextBox1.Text += "--- success";
}
catch (Exception e)
{
//Error1.Add(item.Table_Name, e);
richTextBox1.Text += e;
}
}
richTextBox1.Text += "----------------------";
//return Error1;
}
