public void AddTest_Pattern4()
{
Products products = null;
var p = new SqlParamCreator();
Assert.Throws<ArgumentNullException>(()=> p.Add<Products>(products));
}
public void AddTest_Pattern2()
{
var p = new SqlParamCreator();
p.Add("@param1", SqlDbType.Char, "");
p.Add("@param2", SqlDbType.DateTime, null);
Assert.AreEqual(DBNull.Value, p.SqlParameters[0].Value);
Assert.AreEqual(DBNull.Value, p.SqlParameters[1].Value);
}
public void AddTest_Pattern1()
{
var p = new SqlParamCreator();
p.Add("@param1", SqlDbType.Char, "test");
p.Add("@param2", SqlDbType.Int, 100);
p.Add("@param3", SqlDbType.DateTime, DateTime.Now);
Assert.AreEqual(3, p.SqlParameters.Length);
}
public void AddTest_Pattern3()
{
var table = new DataTable();
table.Columns.Add("param1");
table.Columns.Add("param2");
table.Columns.Add("param3");
table.Rows.Add(1, "test", "row");
table.Rows.Add(2, "test", "row2");
var p = new SqlParamCreator();
p.Add("@table", table);
Assert.AreEqual(1, p.SqlParameters.Length);
table = p.SqlParameters[0].Value as DataTable;
Assert.AreEqual(typeof(DataTable), table.GetType());
}
public void ToDBNullTest_Pattern2(object arg)
{
var p = new SqlParamCreator();
var result = p.ToDBNull(arg);
Assert.AreEqual(DBNull.Value, result);
}
public void ToDBNullTest_Pattern1(object arg)
{
var p = new SqlParamCreator();
var result = p.ToDBNull(arg);
Assert.IsNotNull(result);
}
public void ClearTest_Pattern3()
{
var p = new SqlParamCreator();
Assert.AreEqual(0, p.SqlParameters.Length);
p.Clear();
Assert.AreEqual(0, p.SqlParameters.Length);
}
public void SaveDataTest_Pattern3()
{
string sql = "insert into emp values(@empno, @empname, @sal, @hiredate, @deptno)";
var p = new SqlParamCreator();
p.Add("@empno", SqlDbType.Int, 11);
p.Add("@empname", SqlDbType.Char, "Test");
p.Add("@sal", SqlDbType.Int, 100000);
p.Add("@hiredate", SqlDbType.DateTime, DateTime.Now);
p.Add("@deptno", SqlDbType.Int, 20);
bool result = true;
using (var tran = new TransactionScope())
{
try
{
SqlCommander.ExecuteNonQuery(sql, p);
tran.Complete();
}
catch
{
result = false;
}
}
Assert.AreEqual(true, result);
}
public void AddTest_Pattern6()
{
var products = new Products();
products.ProductID = "100";
products.ProductName = "";
products.Price = 10000;
products.UpdateDate = null;
var p = new SqlParamCreator();
p.Add<Products>(products);
var target = p.SqlParameters.OfType<SqlParameter>().Where(x => x.ParameterName == "@UpdateDate").First();
Assert.AreEqual(target.Value, DBNull.Value);
}
public void SelectToDataSetTest_Pattern2()
{
string sql = "select * from emp where empno between @empNoStart and @empnoEnd";
var p = new SqlParamCreator();
p.Add("@empNoStart", SqlDbType.Int, 1);
p.Add("@empnoEnd", SqlDbType.Int, 7);
var result = SqlCommander.SelectToDataSet(sql, p);
Assert.AreEqual(7, result.Tables[0].Rows.Count);
}
public void ExecTSqlTest_Pattern1()
{
var e = new Employee();
e.empno = 13;
e.empname = "T-SqlTest";
e.sal = 100000;
e.hiredate = null;
e.deptno = 10;
var p = new SqlParamCreator();
p.Add<Employee>(e);
var result = SqlCommander.ExecuteStoredProcedure("SaveEmployee", p);
Assert.AreEqual(1, result);
}
public void SaveTest_Pattern7()
{
string delete = "delete from emp where empno = 30";
string upd = "update emp set empname = @empname, sal = @sal, hiredate = @hiredate, deptno = @depno where empno = @empno";
var e = new Employee();
var updParam = new SqlParamCreator();
e.empno = 11;
e.empname = "update";
e.sal = 100000;
e.hiredate = null;
e.deptno = 10;
updParam.Add<Employee>(e);
var result = true;
using (var tran = new TransactionScope())
{
try
{
//Success
SqlCommander.ExecuteNonQuery(delete, null);
//Success
SqlCommander.ExecuteNonQuery(upd, updParam);
tran.Complete();
}
catch
{
result = false;
}
}
//Commit
Assert.AreNotEqual(false, result);
}
public void SaveTest_Pattern6()
{
var upd = "update emp set empname = @empame,sal = @sal,hiredate = @hiredate,deptno = @depno where empno = @empno";
var e = new Employee();
var updParam = new SqlParamCreator();
e.empno = 11;
e.empname = "update rollback";
e.sal = 100000;
e.hiredate = null;
e.deptno = 10;
updParam.Add<Employee>(e);
//Error query
var ins = "insert into emp values (@empno ,@empame, @sal, @hiredate, @depno)";
var e2 = new Employee();
var insParam = new SqlParamCreator();
e.empno = 13;
e.empname = "update rollback";
e.sal = 100000;
e.hiredate = null;
e.deptno = 10;
updParam.Add<Employee>(e);
var result = true;
using (var tran = new TransactionScope())
{
try
{
//Success
SqlCommander.ExecuteNonQuery(upd, updParam);
//Fault
SqlCommander.ExecuteNonQuery(ins, insParam);
tran.Complete();
}
catch
{
result = false;
}
}
//Rollback check
Assert.AreEqual(false, result);
}
public void SaveDataTest_Pattern5()
{
var sql = new StringBuilder();
sql.AppendLine("begin try");
sql.AppendLine(" begin tran");
sql.AppendLine(" insert into emp values (@empno, @empname, @sal, @hiredate, @deptno)");
sql.AppendLine(" commit tran");
sql.AppendLine("end try");
sql.AppendLine("begin catch");
sql.AppendLine(" rollback tran");
sql.AppendLine("end catch");
var e = new Employee();
e.empno = 13;
e.empname = "Test";
e.sal = 100000;
e.hiredate = null;
e.deptno = 10;
var p = new SqlParamCreator();
p.Add<Employee>(e);
var result = SqlCommander.ExecuteNonQuery(sql.ToString(), p);
Assert.AreEqual(1, result);
}
public void SaveDataTest_Pattern4()
{
string sql = "insert into emp values(@empno, @empname, @sal, @hiredate, @deptno)";
var e = new Employee();
e.empno = 12;
e.empname = "Test";
e.sal = 100000;
e.hiredate = null;
e.deptno = 10;
var p = new SqlParamCreator();
p.Add<Employee>(e);
using (var tran = new TransactionScope())
{
bool result = true;
try
{
SqlCommander.ExecuteNonQuery(sql, p);
tran.Complete();
}
catch
{
result = false;
}
Assert.AreEqual(true, result);
}
}
public void ClearTest_Pattern2()
{
var table = new DataTable();
table.Columns.Add("param1");
table.Columns.Add("param2");
table.Columns.Add("param3");
table.Rows.Add(1, "test", "row");
table.Rows.Add(2, "test", "row2");
var p = new SqlParamCreator();
p.Add("@table", table);
Assert.AreEqual(1, p.SqlParameters.Length);
p.Clear();
Assert.AreEqual(0, p.SqlParameters.Length);
}
public void AddTest_Pattern5()
{
var products = new Products();
products.ProductID = "100";
products.ProductName = "test";
products.Price = 10000;
products.UpdateDate = null;
var p = new SqlParamCreator();
p.Add<Products>(products);
Assert.AreEqual(4, p.SqlParameters.Length);
var name = p.SqlParameters[0].ParameterName;
Assert.AreEqual(true, name.Contains('@'));
}
public void SelectToDataSetTest_Pattern3()
{
string sql = "select * from emp where empno between @empNoStart and @empnoEnd";
var p = new SqlParamCreator();
p.Add("@empNoStart", SqlDbType.Int, 1);
Assert.Throws<SqlException>(() => SqlCommander.SelectToDataSet(sql, p));
}
