public void ExecuteTableWithAnonymousConverter_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME, SAL, HIREDATE, COMM, MGR FROM EMP"; var employees = cmd.ExecuteTable((row) => { return(new { Id = row.Field <int>("EMPNO"), Name = row.Field <string>("ENAME"), Salary = row.Field <Decimal>("SAL"), HireDate = row.Field <DateTime>("HIREDATE"), Comm = row.Field <int?>("COMM"), Manager = row.Field <int?>("MGR"), }); }); var smith = employees.First(); Assert.AreEqual(14, employees.Count()); Assert.AreEqual(EMP.Smith.EmpNo, smith.Id); Assert.AreEqual(EMP.Smith.Salary, smith.Salary); Assert.AreEqual(EMP.Smith.HireDate, smith.HireDate); Assert.AreEqual(EMP.Smith.Comm, smith.Comm); Assert.AreEqual(EMP.Smith.Manager, smith.Manager); } }
public void Mock_ExecuteTable_Test() { var conn = new MockDbConnection(); conn.Mocks .When(null) .ReturnsTable(new MockTable() { Columns = Columns.WithNames("Col1", "Col2", "Col3"), Rows = new object[, ] { { 0, 1, 2 }, { 9, 8, 7 }, { 4, 5, 6 }, } }); using (var cmd = new DatabaseCommand(conn)) { cmd.CommandText.AppendLine("SELECT ..."); var result = cmd.ExecuteTable(new { Col1 = 0, Col2 = 0, Col3 = 0 }); Assert.AreEqual(3, result.Count()); // 3 rows Assert.AreEqual(1, result.First().Col2); // First row / Col2 } }
public void Mock_ExecuteTable_WithNullInFirstRow_Test() { var conn = new MockDbConnection(); conn.Mocks .When(null) .ReturnsTable(new MockTable() { Columns = Columns.WithNames("Col1", "Col2", "Col3"), Rows = new object[, ] { { null, 1, 2 }, { null, 8, 7 }, { 4, 5, 6 }, } }); using (var cmd = new DatabaseCommand(conn)) { cmd.CommandText.AppendLine("SELECT ..."); var result = cmd.ExecuteTable(new { Col1 = (int?)0, Col2 = 0, Col3 = 0 }); Assert.AreEqual(null, result.ElementAt(0).Col1); Assert.AreEqual(null, result.ElementAt(1).Col1); Assert.AreEqual(4, result.ElementAt(2).Col1); } }
public void DbCmd_ExecuteTable_5Cols_14Rows() { using (var cmd = new DatabaseCommand(_connection)) { cmd.CommandText = "SELECT EMPNO, ENAME, HIREDATE, COMM, MGR FROM EMP"; var data = cmd.ExecuteTable <EMP>().ToArray(); } }
public void ExecuteTable_OneRow_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = " + EMP.Smith.EmpNo; var data = cmd.ExecuteTable <EMP>(); Assert.AreEqual(1, data.Count()); } }
public void ExecuteTable_NoData_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = 99999 "; var data = cmd.ExecuteTable <EMP>().ToArray(); Assert.AreEqual(0, data.Count()); } }
public void ExecuteTablePrimitiveAnonymous_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT ENAME FROM EMP "; string[] data = cmd.ExecuteTable(string.Empty).ToArray(); string smith = data.FirstOrDefault(); Assert.AreEqual(EMP.Smith.EName, smith); } }
public void DbCmd_Samples() { // Functions using (var cmd = new DatabaseCommand(_connection)) { cmd.CommandText = "SELECT EMPNO, ENAME, HIREDATE, COMM, MGR FROM EMP"; var data = cmd.ExecuteTable(row => { return(new { Id = row[0], Name = row["ENAME"], HireDate = row.Field <DateTime>("HireDate") }); }).ToArray(); } // Anonymous using (var cmd = new DatabaseCommand(_connection)) { cmd.CommandText = "SELECT EMPNO, ENAME FROM EMP"; var data = cmd.ExecuteTable(new { EMPNO = 0, ENAME = "" }).ToArray(); } // Row Typed using (var cmd = new DatabaseCommand(_connection)) { cmd.CommandText = "SELECT EMPNO, ENAME, HIREDATE, COMM, MGR FROM EMP"; var data = cmd.ExecuteRow <EMP>(); } // Row Anonymous using (var cmd = new DatabaseCommand(_connection)) { cmd.CommandText = "SELECT EMPNO, ENAME MGR FROM EMP"; var data = cmd.ExecuteRow(new { EMPNO = 0, ENAME = "" }); } // Row Function using (var cmd = new DatabaseCommand(_connection)) { cmd.CommandText = "SELECT EMPNO, ENAME, HIREDATE, COMM, MGR FROM EMP"; var data = cmd.ExecuteRow(row => { return(new { Id = row[0], Name = row["ENAME"], HireDate = row.Field <DateTime>("HireDate") }); }); } }
public void ExecuteTablePrimitiveWithAnonymousConverter_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT ENAME FROM EMP"; var employees = cmd.ExecuteTable((row) => row.Field <string>("ENAME")); var smith = employees.First(); Assert.AreEqual(14, employees.Count()); Assert.AreEqual(EMP.Smith.EName, smith); } }
public void ExecuteTableDynamic_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME, HIREDATE, COMM FROM EMP ORDER BY EMPNO"; var emp = cmd.ExecuteTable <dynamic>(); Assert.AreEqual(14, emp.Count()); Assert.AreEqual("SMITH", emp.First().ENAME); Assert.AreEqual(new DateTime(1980, 12, 17), emp.First().HIREDATE); Assert.AreEqual(null, emp.First().COMM); } }
public void ExecuteTableTyped_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME, HIREDATE, COMM, MGR FROM EMP "; EMP[] data = cmd.ExecuteTable <EMP>().ToArray(); EMP smith = data.FirstOrDefault(i => i.EmpNo == 7369); Assert.AreEqual(EMP.Smith.EmpNo, smith.EmpNo); Assert.AreEqual(EMP.Smith.EName, smith.EName); Assert.AreEqual(EMP.Smith.HireDate, smith.HireDate); Assert.AreEqual(EMP.Smith.Comm, smith.Comm); } }
public void ExecuteTableTypedWithColumnAttribute_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME, SAL, HIREDATE, COMM, MGR FROM EMP "; var data = cmd.ExecuteTable <EMP>(); EMP smith = data.FirstOrDefault(i => i.EmpNo == 7369); Assert.AreEqual(smith.EmpNo, EMP.Smith.EmpNo); Assert.AreEqual(smith.EName, EMP.Smith.EName); Assert.AreEqual(smith.HireDate, EMP.Smith.HireDate); Assert.AreEqual(smith.Comm, EMP.Smith.Comm); Assert.AreEqual(smith.Salary, EMP.Smith.Salary); } }
public void ExecuteTableWithAnonymousConverter_NoData_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = 99999 "; var employees = cmd.ExecuteTable((row) => { return(new { Id = row.Field <int>("EMPNO"), Name = row.Field <string>("ENAME") }); }); Assert.AreEqual(0, employees.Count()); } }
public void ExecuteTableCustomedAnonymousTyped_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME, HIREDATE FROM EMP "; var data = cmd.ExecuteTable(new { EmpNo = 0, EName = String.Empty, HireDate = DateTime.Today, MyVar = "" }); var smith = data.FirstOrDefault(i => i.EmpNo == 7369); Assert.AreEqual(EMP.Smith.EmpNo, smith.EmpNo); Assert.AreEqual(EMP.Smith.EName, smith.EName); Assert.AreEqual(EMP.Smith.HireDate, smith.HireDate); } }
public void ExecuteTable_ActionAfter_Test() { bool isPassed = false; using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText.AppendLine(" SELECT * FROM EMP "); cmd.ActionAfterExecution = (command, tables) => { isPassed = true; }; var data = cmd.ExecuteTable <EMP>(); Assert.IsTrue(isPassed); Assert.AreEqual(14, data.Count()); } }
public void ExecuteTable_ActionBefore_Test() { bool isPassed = false; using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText.AppendLine(" SELECT * FROM EMP "); cmd.ActionBeforeExecution = (command) => { cmd.CommandText.AppendLine(" WHERE EMPNO > 7369 "); isPassed = true; }; var data = cmd.ExecuteTable <EMP>(); Assert.IsTrue(isPassed); Assert.AreEqual(13, data.Count()); Assert.AreEqual("ALLEN", data.First().EName); } }
public void ExecuteTableNullableProperties_Test() { using (var cmd = new DatabaseCommand(_connection)) { cmd.Log = Console.WriteLine; cmd.CommandText = " SELECT EMPNO, ENAME, HIREDATE, COMM, MGR FROM EMP "; var data = cmd.ExecuteTable(new { EmpNo = default(int), EName = default(string), HireDate = default(DateTime?), Comm = (int?)null, Mgr = (int?)4 }); var smith = data.FirstOrDefault(i => i.EmpNo == 7369); Assert.AreEqual(EMP.Smith.EmpNo, smith.EmpNo); Assert.AreEqual(EMP.Smith.EName, smith.EName); Assert.AreEqual(EMP.Smith.HireDate, smith.HireDate); Assert.AreEqual(EMP.Smith.Comm, smith.Comm); } }