コード例 #1
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static SalesOrderHeader Insert(DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            SalesOrderHeader order = new SalesOrderHeader
            {
                RevisionNumber  = 8,
                OrderDate       = DateTime.Today,
                DueDate         = DateTime.Today.AddDays(7),
                Status          = 1,
                OnlineOrderFlag = true,
                CustomerID      = 25252,
                BillToAddressID = 20850,
                ShipToAddressID = 20850,
                ShipMethodID    = 1,
                SubTotal        = 100m,
                TaxAmt          = 0m,
                Freight         = 0m,
                Comment         = "TEST",
                Rowguid         = Guid.NewGuid(),
                ModifiedDate    = DateTime.Now
            };
            int nor = conn.Insert(ref order, trans);
            var id  = conn.GetSingleValue <int>("SELECT @@IDENTITY", trans);

            order.SalesOrderId = id;
            Console.WriteLine(nor + " record inserted.");
            return(order);
        }
コード例 #2
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static List <string> GetFirstColumn(DBConnectionWrapper conn, DBTransactionWrapper trans)
        {
            string sql = "SELECT first_name FROM employees";
            var    ret = conn.GetFirstColumn <string>(sql, trans);

            Console.WriteLine("Queried " + ret.Count + " lines of first column.");
            return(ret);
        }
コード例 #3
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static int GetSingleValue(DBConnectionWrapper conn, DBTransactionWrapper trans)
        {
            string sql = "select count(*) from employees";
            var    ret = conn.GetSingleValue <int>(sql, trans);

            Console.WriteLine(ret + " rows in employees table.");
            return(ret);
        }
コード例 #4
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static List <Employee> QueryOracle(DBConnectionWrapper conn, DBTransactionWrapper trans)
        {
            string sql = "select * from employees";
            var    ret = conn.Query <Employee>(sql, trans);

            Console.WriteLine(ret.Count + " rows queried.");
            return(ret);
        }
コード例 #5
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static int GetSingleValue(DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            string sql = "SELECT count(*) FROM [Sales].[SalesOrderHeader]";
            var    ret = conn.GetSingleValue <int>(sql, trans);

            Console.WriteLine("Total " + ret + " records in table.");
            return(ret);
        }
コード例 #6
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static List <int> GetFirstColumn(DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            string sql = "SELECT SalesOrderID FROM [Sales].[SalesOrderHeader]";
            var    ret = conn.GetFirstColumn <int>(sql, trans);

            Console.WriteLine("Queried " + ret.Count + " lines of first column.");
            return(ret);
        }
コード例 #7
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static List <SalesOrderHeader> Query(DBConnectionWrapper conn, DBTransactionWrapper trans)
        {
            string sql = "SELECT [SalesOrderID],[RevisionNumber],[OrderDate],[DueDate],[ShipDate],[Status],[OnlineOrderFlag],[SalesOrderNumber],[PurchaseOrderNumber],[AccountNumber],[CustomerID],[SalesPersonID],[TerritoryID],[BillToAddressID],[ShipToAddressID],[ShipMethodID],[CreditCardID],[CreditCardApprovalCode],[CurrencyRateID],[SubTotal],[TaxAmt],[Freight],[TotalDue],[Comment],[rowguid],[ModifiedDate] FROM [Sales].[SalesOrderHeader]";
            var    ret = conn.Query <SalesOrderHeader>(sql, trans);

            Console.WriteLine("Queried " + ret.Count + " records.");
            return(ret);
        }
コード例 #8
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static SalesOrderHeader Load(DBConnectionWrapper conn, int orderid, DBTransactionWrapper trans = null)
        {
            var ret = conn.Load(new SalesOrderHeader {
                SalesOrderId = orderid
            }, trans);

            Console.WriteLine("Sales Order Number of loaded record is " + ret.SalesOrderNumber);
            return(ret);
        }
コード例 #9
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static int Delete(DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            int ret = conn.Delete(new Employee {
                EMPLOYEE_ID = 207
            }, trans);

            Console.WriteLine(ret + " rows deleted");
            return(ret);
        }
コード例 #10
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static Employee Load(DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            var ret = conn.Load(new Employee {
                EMPLOYEE_ID = 102
            }, trans);

            Console.WriteLine("First name of loaded employee is " + ret.FIRST_NAME);
            return(ret);
        }
コード例 #11
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static void Update(DBConnectionWrapper conn, SalesOrderHeader obj, DBTransactionWrapper trans = null)
        {
            int nor = conn.Update(ref obj, trans);

            Console.WriteLine(nor + " line updated.");
            var obj2 = new SalesOrderHeader();

            obj2.SalesOrderId = obj.SalesOrderId;
            obj2.ModifiedDate = DateTime.Now;
            obj2.Status       = 2;
            nor = conn.Update(ref obj2, trans, i => i.ModifiedDate, i => i.Status);
            Console.WriteLine(nor + " line updated (only update ModifiedDate and Status).");
        }
コード例 #12
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static Employee Update(DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            Employee obj = new Employee
            {
                EMPLOYEE_ID = 207,
                HIRE_DATE   = DateTime.Now.AddMonths(-1)
            };
            int ret = conn.Update(ref obj, trans, i => i.HIRE_DATE);

            Console.WriteLine(ret + " rows updated");
            if (ret > 0)
            {
                Console.WriteLine("The first name of the updated record is " + obj.FIRST_NAME);
            }
            return(obj);
        }
コード例 #13
0
        public IDbCommand CreateCommand(Sql sql, DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            SqlCommand command = new SqlCommand(sql.Text, (SqlConnection)conn.Connection);

            if (trans != null)
            {
                command.Transaction = (SqlTransaction)trans.Transaction;
            }
            if (sql.Parameters.Count > 0)
            {
                command.Parameters.AddRange(sql.Parameters.Select(i => ToSqlParameter(i)).ToArray());
            }
            if (sql.CommandTimeout >= 0)
            {
                command.CommandTimeout = sql.CommandTimeout;
            }
            return(command);
        }
コード例 #14
0
        public override int Execute(ref T obj, DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            var sql = CreateSql(obj);

            sql.Parameters.Add(new Parameter(RowCountParaName, -1, System.Data.ParameterDirection.Output));
            conn.Execute(sql, false, trans);
            int nor = (int)sql.Parameters.Single(i => i.Name == RowCountParaName).Output;

            if (nor > 0)
            {
                var paras = Parameters.ToList();
                for (int i = 0; i < paras.Count; i++)
                {
                    var col = paras[i].Column;
                    col.PropertySetter(obj, sql.Parameters[i].Output);
                }
            }
            return(nor);
        }
コード例 #15
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
        static Employee Insert(DBConnectionWrapper conn, DBTransactionWrapper trans = null)
        {
            Employee obj = new Employee
            {
                EMPLOYEE_ID    = 207,
                FIRST_NAME     = "Kevin",
                LAST_NAME      = "Jin",
                EMAIL          = "kevin.jin",
                PHONE_NUMBER   = "123.456.7890",
                HIRE_DATE      = DateTime.Now,
                JOB_ID         = "AC_ACCOUNT",
                SALARY         = 8300m,
                COMMISSION_PCT = null,
                MANAGER_ID     = 205,
                DEPARTMENT_ID  = 110
            };
            var ret = conn.Insert(ref obj, trans);

            Console.WriteLine(ret + " rows inserted");
            return(obj);
        }
 public ConsultationRepositoryMySQL(DBConnectionWrapper connectionWrapper)
 {
     this.connectionWrapper = connectionWrapper;
 }
 public EventRepositoryMySQL(DBConnectionWrapper connectionWrapper)
 {
     this.connectionWrapper = connectionWrapper;
 }
コード例 #18
0
ファイル: Program.cs プロジェクト: jxdking/ADOExt
 static int Delete(DBConnectionWrapper conn, SalesOrderHeader obj, DBTransactionWrapper trans = null)
 {
     return(conn.Delete(obj, trans));
 }
コード例 #19
0
        public void TestSelect()
        {
            DBConnectionWrapper wrapper = null;

            using (wrapper = new SqliteConnectWrapper("PomodoroTasks.db"))
            {
                // One to One
                {
                    var query = "SELECT * FROM Tasks";

                    var result = wrapper.Query <Tasks>(query);

                    foreach (var p in result)
                    {
                        Debug.WriteLine($"ID: {p.TaskId}  Name: {p.TaskName}");
                    }
                }

                // On to Many
                {
                    string query     = "SELECT * FROM Pomodoros AS P INNER JOIN Tasks AS T ON P.TaskId = T.TaskId;";
                    var    pomodoros = wrapper.Query <Pomodoros, Tasks, Pomodoros>(
                        query,
                        (pomodoro, task) =>
                    {
                        pomodoro.Task = task;
                        return(pomodoro);
                    },
                        splitOn: "TaskId");
                    foreach (var pomodoro in pomodoros)
                    {
                        Debug.WriteLine($"TaskID: {pomodoro.PomodoroId} TaskName:{pomodoro.Task.TaskName}");
                    }
                }

                // 中間テーブル
                {
                    string query = "";
                    query += "SELECT * FROM (Tasks AS T INNER JOIN TaskTagMap AS TTM ON T.TaskId = TTM.TaskId) ";
                    query += "INNER JOIN Categories AS C ON TTM.CategoryId = C.CategoryId;";

                    var taskDictionary = new Dictionary <int, Tasks>();
                    var list           = wrapper.Query <Tasks, Categories, Tasks>(
                        query,
                        (task, category) =>
                    {
                        Tasks taskEntry;
                        if (!taskDictionary.TryGetValue(task.TaskId, out taskEntry))
                        {
                            taskEntry            = task;
                            taskEntry.Categories = new List <Categories>();
                            taskDictionary.Add(taskEntry.TaskId, taskEntry);
                        }
                        taskEntry.Categories.Add(category);
                        return(taskEntry);
                    },
                        splitOn: "TaskId,CategoryId").Distinct();

                    foreach (var task in list)
                    {
                        Debug.WriteLine($"TaskId:{task.TaskId} TaskName:{task.TaskName}:");
                        foreach (var cat in task.Categories)
                        {
                            Debug.WriteLine($"    CategoryId:{cat.CategoryId} CategoryName:{cat.CategoryName}");
                        }
                    }
                }

                wrapper.Close();
            }
        }