Transaction Scope Problem
Hi
I am working on Transactionscope class.
when error occur the data don't get rollback.
PC Detail : Window XP Professional.
using Sql Server 2000 with service pack 2.0.
Below i m sending the code which i have written.
Pls tell me if any setting is required related to Sql / Operating system.
--
[Transaction(TransactionOption.Supported, Timeout = 1200)]
public class MMCalender_Save : ServicedComponent
{
[Microsoft.SqlServer.Server.SqlMethod]
public Boolean Save(DataSet dSet, ref EMSPLUSDBConnection EMSPLUSDataConnection, String sMode,Single UserCode)
{
SqlCommand sCom;
SqlConnection sCon;
MMCommonFunc CommFunc = new MMCommonFunc();
//try
//{
using (TransactionScope sTrans = new TransactionScope())
{
try
{
if (EMSPLUSDataConnection == null)
EMSPLUSDataConnection = new EMSPLUSDBConnection();
DataTable dHeader, dDetail, dHoliday;
DataRow dHeadRow;
sCom = new SqlCommand();
sCom.Connection = EMSPLUSDataConnection.EMSPLUSConnection;
dHeader = new DataTable();
dDetail = new DataTable();
dHoliday = new DataTable();
dHeader = dSet.Tables["GL_CALDEF_C"];
dDetail = dSet.Tables["GL_WORKCAL_D"];
dHoliday = dSet.Tables["GL_HOLCAL_D"];
dHeadRow = dHeader.Rows[0];
if (sMode.Trim() == "A")
{
sCom.CommandText = "INSERT INTO GL_CALDEF_C (CALENDAR_ID,FROM_YEAR,CALENDAR_DESC,CREATEDBY,CREATEDON) Values('" + dHeadRow["CALENDAR_ID"].ToString() + "'," + dHeadRow["FROM_YEAR"].ToString() + ",'" + dHeadRow["CALENDAR_DESC"].ToString() + "'," + UserCode + "," + CommFunc.GetDate() + ")";
sCom.ExecuteNonQuery();
}
else if (sMode.Trim() == "E")
{
sCom.CommandText = " UPDATE GL_CALDEF_C SET MODIFIEDBY=" + UserCode + ",MODIFIEDON=" + CommFunc.GetDate() + ",FROM_YEAR = " + dHeadRow["FROM_YEAR"].ToString() + ",CALENDAR_DESC='" + dHeadRow["CALENDAR_DESC"].ToString() + "' WHERE CALENDAR_ID = '" + dHeadRow["CALENDAR_ID"].ToString().Trim() + "'";
sCom.ExecuteNonQuery();
}
foreach (DataRow dRow in dHoliday.Rows)
{
if (sMode.Trim() == "A")
{
sCom.CommandText = "Insert Into GL_HOLCAL_D(CALENDAR_ID, WORKCAL_FROM_YEAR, HOLIDAY_DATE, HOLIDAY_DESC,CREATEDBY,CREATEDON) "
+ " Values('" + dHeadRow["CALENDAR_ID"].ToString() + "'," + dRow["WORKCAL_FROM_YEAR"].ToString() + " "
+ " ,'" + System.DateTime.Parse(dRow["HOLIDAY_DATE"].ToString()).ToString("dd-MMM-yyyy") + "','" + dRow["HOLIDAY_DESC"].ToString() + "' "
+" ," + UserCode + "," + CommFunc.GetDate() + ")";
sCom.ExecuteNonQuery();
}
else if (sMode.Trim() == "E")
{
sCom.CommandText = "Update GL_HOLCAL_D Set HOLIDAY_DESC='" + dRow["HOLIDAY_DESC"].ToString() + "',MODIFIEDBY=" + UserCode + ",MODIFIEDON=" + CommFunc.GetDate() + " "
+ " Where CALENDAR_ID='" + dHeadRow["CALENDAR_ID"].ToString() + "' And WORKCAL_FROM_YEAR=" + dRow["WORKCAL_FROM_YEAR"].ToString() + " And HOLIDAY_DATE='" + System.DateTime.Parse(dRow["HOLIDAY_DATE"].ToString()).ToString("dd-MMM-yyyy") + "'";
sCom.ExecuteNonQuery();
}
}
foreach (DataRow dRow in dDetail.Rows)
{
if (sMode.Trim() == "A")
{
sCom.CommandText = "Insert Into GL_WORKCAL_D(CALENDAR_ID, WORKCAL_FROM_YEAR, WORKCAL_WEEK_DAY, WORKCAL_DAY_OFF, WORKCAL_ALL_WEEK, WORKCAL_FIRST, WORKCAL_SECOND, WORKCAL_THIRD, WORKCAL_FOURTH,WORKCAL_FIFTH,CREATEDBY,CREATEDON) "
+ " Values('" + dHeadRow["CALENDAR_ID"].ToString() + "'," + dRow["WORKCAL_FROM_YEAR"].ToString() + " "
+ " ," + dRow["WORKCAL_WEEK_DAY"].ToString() + "," + dRow["WORKCAL_DAY_OFF"].ToString() + " "
+ " ," + dRow["WORKCAL_ALL_WEEK"].ToString() + "," + dRow["WORKCAL_FIRST"].ToString() + " "
+ " ," + dRow["WORKCAL_SECOND"].ToString() + "," + dRow["WORKCAL_THIRD"].ToString() + " "
+ " ," + dRow["WORKCAL_FOURTH"].ToString() + "," + dRow["WORKCAL_FIFTH"].ToString() + " "
+" ," + UserCode + "," + CommFunc.GetDate() + ")";
sCom.ExecuteNonQuery();
}
else if (sMode.Trim() == "E")
{
sCom.CommandText = "Update GL_WORKCAL_D Set WORKCAL_DAY_OFF='" + dRow["WORKCAL_DAY_OFF"].ToString() + "',WORKCAL_ALL_WEEK='" + dRow["WORKCAL_ALL_WEEK"].ToString() + "',WORKCAL_FIRST='" + dRow["WORKCAL_FIRST"].ToString() + "',WORKCAL_SECOND='" + dRow["WORKCAL_SECOND"].ToString() + "',WORKCAL_THIRD='" + dRow["WORKCAL_THIRD"].ToString() + "',WORKCAL_FOURTH='" + dRow["WORKCAL_FOURTH"].ToString() + "',WORKCAL_FIFTH='" + dRow["WORKCAL_FIFTH"].ToString() + "',MODIFIEDBY=" + UserCode + ",MODIFIEDON=" + CommFunc.GetDate() + " "
+ " Where CALENDAR_ID='" + dHeadRow["CALENDAR_ID"].ToString() + "' And WORKCAL_FROM_YEAR=" + dRow["WORKCAL_FROM_YEAR"].ToString() + " And WORKCAL_WEEK_DAY=" + dRow["WORKCAL_WEEK_DAY"].ToString() + " ";
}
}
sTrans.Complete();
} ////End Try
catch (SqlException SqlEx)
{
ContextUtil.SetAbort();
throw (new Exception("SqlEx error:" + SqlEx));
}
catch (Exception Ex)
{
ContextUtil.SetAbort();
throw (new Exception("SqlEx error:" + Ex));
}
finally
{
sTrans.Dispose();
}
} //end Using
return true;
}
}
