Example #1
0
        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);
        }
Example #2
0
        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;
        }
Example #3
0
 public DBFactory(string connect)
 {
     FugoRepository  = new FugoRepository(new DbConnectionHelper(connect));
     MSSQLRepository = new MSSQLRepository(new DbConnectionHelper(connect));
     MlecRepository  = new MlecRepository(new DbConnectionHelper(connect));
 }
Example #4
0
        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;
        }