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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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);
}
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).");
}
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);
}
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);
}
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);
}
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;
}
static int Delete(DBConnectionWrapper conn, SalesOrderHeader obj, DBTransactionWrapper trans = null)
{
return(conn.Delete(obj, trans));
}
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();
}
}
