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);
}
}
