private void AddProduct(LocalDbTransaction tranObj, string key, int UnitPrice)
{
string _sql = @"INSERT INTO [dbo].[Products]([ProductName],[CategoryID],[UnitPrice],[LastUpdate])
VALUES
(@ProductName
,@CategoryID
,@UnitPrice
,@LastUpdate)" ;
DbParameter[] _paramterList = new SqlParameter[4];
_paramterList[0] = new SqlParameter("@ProductName", key);
_paramterList[1] = new SqlParameter("@CategoryID", 7);
_paramterList[2] = new SqlParameter("@UnitPrice", UnitPrice);
_paramterList[3] = new SqlParameter("@LastUpdate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqlHelper.ExecuteNonQuery(tranObj, _sql, _paramterList);
}
public void ExecuteNonQueryTest()
{
#region ExecuteNonQuery 普通
string _sql = @"INSERT INTO [dbo].[Products]
([ProductName]
,[CategoryID]
,[UnitPrice]
,[LastUpdate])
VALUES
(@ProductName
,@CategoryID
,@UnitPrice
,@LastUpdate)" ;
DbParameter[] _paramterList = new SqlParameter[4];
_paramterList[0] = new SqlParameter("@ProductName", DateTime.Now.FormatDate(12));
_paramterList[1] = new SqlParameter("@CategoryID", 7);
_paramterList[2] = new SqlParameter("@UnitPrice", 10);
_paramterList[3] = new SqlParameter("@LastUpdate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
sqlHelper.ExecuteNonQuery(_sql, _paramterList);
#endregion ExecuteNonQuery 普通
#region ExecuteNonQuery 事物 成功
ClearProductTestDb();
using (LocalDbTransaction tranObj = sqlHelper.BeginTranscation())
{
try
{
AddProduct(tranObj, string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)), 22);
AddProduct(tranObj, string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)), 33);
tranObj.Commit();
}
catch (Exception)
{
tranObj.Rollback();
}
}
_sql = "select UnitPrice from Products where ProductName=@ProductName";
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)));
Assert.AreEqual(22m, sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)));
Assert.AreEqual(33m, sqlHelper.ExecuteScalar(_sql, _paramterList));
ClearProductTestDb();
#endregion ExecuteNonQuery 事物 成功
#region ExecuteNonQuery 事物 失败
ClearProductTestDb();
using (LocalDbTransaction tranObj = sqlHelper.BeginTranscation())
{
try
{
AddProduct(tranObj, string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)), 22);
throw new Exception("test");
AddProduct(tranObj, string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)), 33);
tranObj.Commit();
}
catch (Exception)
{
tranObj.Rollback();
}
}
_sql = "select UnitPrice from Products where ProductName=@ProductName";
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)));
Assert.IsNull(sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)));
Assert.IsNull(sqlHelper.ExecuteScalar(_sql, _paramterList));
#endregion ExecuteNonQuery 事物 失败
#region ExecuteNonQuery 事物嵌套 成功
ClearProductTestDb();
using (LocalDbTransaction tranObj = sqlHelper.BeginTranscation())
{
try
{
AddProduct(tranObj, string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)), 22);
using (LocalDbTransaction tranObjChild = sqlHelper.BeginTranscation())
{
try
{
AddProduct(tranObjChild, string.Format("{0}_UnitTest_3", DateTime.Now.FormatDate(13)), 44);
AddProduct(tranObjChild, string.Format("{0}_UnitTest_4", DateTime.Now.FormatDate(13)), 55);
tranObjChild.Commit();
}
catch (Exception)
{
tranObjChild.Rollback();
}
}
AddProduct(tranObj, string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)), 33);
tranObj.Commit();
}
catch (Exception)
{
tranObj.Rollback();
}
}
_sql = "select UnitPrice from Products where ProductName=@ProductName";
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)));
Assert.AreEqual(22m, sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)));
Assert.AreEqual(33m, sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_3", DateTime.Now.FormatDate(13)));
Assert.AreEqual(44m, sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_4", DateTime.Now.FormatDate(13)));
Assert.AreEqual(55m, sqlHelper.ExecuteScalar(_sql, _paramterList));
#endregion ExecuteNonQuery 事物嵌套 成功
#region ExecuteNonQuery 事物嵌套 子事物失败
ClearProductTestDb();
using (LocalDbTransaction tranObj = sqlHelper.BeginTranscation())
{
try
{
AddProduct(tranObj, string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)), 22);
using (LocalDbTransaction tranObjChild = sqlHelper.BeginTranscation())
{
try
{
AddProduct(tranObjChild, string.Format("{0}_UnitTest_3", DateTime.Now.FormatDate(13)), 44);
throw new Exception("test");
AddProduct(tranObjChild, string.Format("{0}_UnitTest_4", DateTime.Now.FormatDate(13)), 55);
tranObjChild.Commit();
}
catch (Exception)
{
tranObjChild.Rollback();
}
}
AddProduct(tranObj, string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)), 33);
tranObj.Commit();
}
catch (Exception)
{
tranObj.Rollback();
}
}
_sql = "select UnitPrice from Products where ProductName=@ProductName";
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)));
Assert.AreEqual(22m, sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)));
Assert.AreEqual(33m, sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_3", DateTime.Now.FormatDate(13)));
Assert.IsNull(sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_4", DateTime.Now.FormatDate(13)));
Assert.IsNull(sqlHelper.ExecuteScalar(_sql, _paramterList));
#endregion ExecuteNonQuery 事物嵌套 子事物失败
#region ExecuteNonQuery 事物嵌套 主事物失败
ClearProductTestDb();
using (LocalDbTransaction tranObj = sqlHelper.BeginTranscation())
{
try
{
AddProduct(tranObj, string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)), 22);
using (LocalDbTransaction tranObjChild = sqlHelper.BeginTranscation())
{
try
{
AddProduct(tranObjChild, string.Format("{0}_UnitTest_3", DateTime.Now.FormatDate(13)), 44);
AddProduct(tranObjChild, string.Format("{0}_UnitTest_4", DateTime.Now.FormatDate(13)), 55);
tranObjChild.Commit();
}
catch (Exception)
{
tranObjChild.Rollback();
}
}
throw new Exception("test");
AddProduct(tranObj, string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)), 33);
tranObj.Commit();
}
catch (Exception)
{
tranObj.Rollback();
}
}
_sql = "select UnitPrice from Products where ProductName=@ProductName";
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_1", DateTime.Now.FormatDate(13)));
Assert.IsNull(sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_2", DateTime.Now.FormatDate(13)));
Assert.IsNull(sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_3", DateTime.Now.FormatDate(13)));
Assert.AreEqual(44m, sqlHelper.ExecuteScalar(_sql, _paramterList));
_paramterList = new SqlParameter[1];
_paramterList[0] = new SqlParameter("@ProductName", string.Format("{0}_UnitTest_4", DateTime.Now.FormatDate(13)));
Assert.AreEqual(55m, sqlHelper.ExecuteScalar(_sql, _paramterList));
#endregion ExecuteNonQuery 事物嵌套 主事物失败
}