public void ExecuteRow_DataRowMapTo_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @"SELECT EMP.EMPNO,
EMP.ENAME,
DEPT.DNAME
FROM EMP
INNER JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO
WHERE EMPNO = 7369";
var smith = cmd.ExecuteRow(row =>
{
MyEmployee emp = row.MapTo <MyEmployee>();
emp.Department = row.MapTo <MyDepartment>();
return(emp);
});
Assert.AreEqual(7369, smith.EmpNo);
Assert.AreEqual("SMITH", smith.EName);
Assert.AreEqual(null, smith.Salary);
Assert.AreEqual(null, smith.SAL);
Assert.AreEqual(0, smith.Department.DeptNo);
Assert.AreEqual("RESEARCH", smith.Department.DName);
}
}
public void ExecuteRowPrimitiveNullable_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT COMM FROM EMP WHERE EMPNO = 7369";
int?comm = cmd.ExecuteRow <int?>();
Assert.AreEqual(null, comm);
}
}
public void ExecuteRowPrimitiveWithFunction_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT EMPNO FROM EMP WHERE EMPNO = 7369";
int empno = cmd.ExecuteRow <int>((row) => Convert.ToInt32(row[0]));
Assert.AreEqual(7369, empno);
}
}
public void ExecuteRowPrimitiveAnonymousTyped_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT ENAME FROM EMP ";
var smith = cmd.ExecuteRow(string.Empty);
Assert.AreEqual("SMITH", smith);
}
}
public void ExecuteRowPrimitiveTyped_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT ENAME FROM EMP ";
var emp = cmd.ExecuteRow <string>();
Assert.AreEqual("SMITH", emp);
}
}
public void ExecuteRowTyped_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = "SELECT * FROM EMP WHERE EMPNO = 7369";
EMP emp = cmd.ExecuteRow <EMP>();
Assert.AreEqual(7369, emp.EmpNo);
}
}
public void ExecuteRow_NoData_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = "SELECT * FROM EMP WHERE EMPNO = 99999";
EMP emp = cmd.ExecuteRow <EMP>();
Assert.AreEqual(null, emp);
}
}
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 ExecuteStarRowDynamic_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT * FROM EMP WHERE EMPNO = 7369";
var emp = cmd.ExecuteRow <dynamic>();
Assert.AreEqual(7369, emp.EMPNO);
Assert.AreEqual("SMITH", emp.ENAME);
Assert.AreEqual(new DateTime(1980, 12, 17), emp.HIREDATE);
Assert.AreEqual(null, emp.COMM);
}
}
public void ExecuteTwoRowsDynamic_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT EMPNO, ENAME, HIREDATE, COMM FROM EMP WHERE EMPNO = 7369";
var emp1 = cmd.ExecuteRow <dynamic>();
cmd.Clear();
cmd.CommandText = " SELECT EMPNO, ENAME, HIREDATE, COMM FROM EMP WHERE EMPNO = 7499";
var emp2 = cmd.ExecuteRow <dynamic>();
Assert.AreEqual(7369, emp1.EMPNO);
Assert.AreEqual("SMITH", emp1.ENAME);
Assert.AreEqual(new DateTime(1980, 12, 17), emp1.HIREDATE);
Assert.AreEqual(null, emp1.COMM);
Assert.AreEqual(7499, emp2.EMPNO);
Assert.AreEqual("ALLEN", emp2.ENAME);
Assert.AreEqual(new DateTime(1981, 02, 20), emp2.HIREDATE);
Assert.AreEqual(300, emp2.COMM);
}
}
public void ExecuteRowAnonymousTyped_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE EMPNO = 7369";
var emp = cmd.ExecuteRow(new
{
EmpNo = 0,
Ename = string.Empty,
HireDate = (DateTime?)null
});
Assert.AreEqual(7369, emp.EmpNo);
}
}
public void ExecuteRowWithAnonymousConverter_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = 7369";
var emp = cmd.ExecuteRow((row) =>
{
return(new
{
Id = Convert.ToInt32(row["EMPNO"]),
Name = Convert.ToString(row["ENAME"])
});
});
Assert.AreEqual(7369, emp.Id);
}
}
public void ExecuteRowWithConverter_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = " SELECT * FROM EMP WHERE EMPNO = 7369";
EMP emp = cmd.ExecuteRow <EMP>((row) =>
{
return(new EMP()
{
EmpNo = Convert.ToInt32(row["EMPNO"]),
EName = Convert.ToString(row["ENAME"])
});
});
Assert.AreEqual(7369, emp.EmpNo);
}
}
public void Mock_ExecuteScalar_WithTable_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.WhenAny()
.ReturnsTable(
new MockTable().AddColumns("Col1", "Col2")
.AddRow(10, 11)
.AddRow(12, 13));
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("SELECT ...");
var result = cmd.ExecuteRow <int>();
Assert.AreEqual(10, result);
}
}
public void ExecuteRow_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 row = cmd.ExecuteRow <EMP>();
Assert.IsTrue(isPassed);
}
}
public void ExecuteRow_CastPrivateProperties_Test()
{
using (var cmd = new DatabaseCommand(_connection))
{
cmd.Log = Console.WriteLine;
cmd.CommandText = @"SELECT EMPNO,
ENAME,
'BlaBla' AS ColumnPrivate,
'BlaBla' AS ColumnGetOnly
FROM EMP
WHERE EMPNO = 7369";
var smith = cmd.ExecuteRow <EMP>();
Assert.AreEqual(7369, smith.EmpNo);
Assert.AreEqual("SMITH", smith.EName);
Assert.AreEqual("7369 SMITH", smith.ColumnGetOnly);
Assert.AreEqual(null, smith.ColumnNotUse);
}
}
public void ExecuteRow_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 row = cmd.ExecuteRow <EMP>();
Assert.IsTrue(isPassed);
Assert.AreEqual("SMITH", row.EName);
}
}
public void Mock_ExecuteRow_WithTable_Test()
{
var conn = new MockDbConnection();
conn.Mocks
.WhenAny()
.ReturnsTable(
MockTable.WithColumns("Col1", "Col2")
.AddRow(10, 11)
.AddRow(12, 13));
using (var cmd = new DatabaseCommand(conn))
{
cmd.CommandText.AppendLine("SELECT ...");
var result = cmd.ExecuteRow(new
{
Col1 = default(int?),
Col2 = default(int),
});
Assert.AreEqual(10, result.Col1);
Assert.AreEqual(11, result.Col2);
}
}