Transaction.RollBack() not working when executing multiple stored procedures

[Mohan kishore Rayapureddy]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Configuration;

namespace task3at3
{
public partial class CourseDetails : System.Web.UI.Page
{
clsInsertion ins = new clsInsertion();
clsSelect sels = new clsSelect();
clsGetDetails2 gtd = new clsGetDetails2();
DataTable dt = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
try
{
if (!IsPostBack)
{
int id = 0;
id = Convert.ToInt32(Request.QueryString["cid"]);
if (id > 0)
{
Bind_Data_cdl(id);
}
Bind_ddlStaff();
Bind_Grid();
}
}
catch (Exception ex)
{
throw ex;
}

}
public void Bind_Grid()
{
try
{
if (ViewState["dt1"] != null)
{
DataTable dt = new DataTable();
dt = (DataTable)ViewState["dt1"];
}
else
{
dt.Columns.Add("CourseType");
dt.Columns.Add("Days");
dt.Columns.Add("CourseName");
dt.Columns.Add("StaffId");
dt.Columns.Add("StaffName");
dt.Columns.Add("Fee");
ViewState["dt1"] = dt;
}
}

catch (Exception ex)
{
throw ex;
}
}

public void Bind_ddlStaff()
{
try
{
ddlStaffName.DataSource = sels.Bind_ddlStaff1();
ddlStaffName.DataTextField = "StaffName";
ddlStaffName.DataValueField = "StaffId";
ddlStaffName.DataBind();
ddlStaffName.Items.Insert(0, "Please Select Staff");
}
catch (Exception ex)
{
throw ex;
}

}
public void Validategrp()
{
try
{
revFee.Validate();
rfvCourseType.Validate();
rfvCourseName.Validate();
rfvStaffName.Validate();
rfvFee.Validate();
if (ddlCourseType.SelectedValue == "Crash Course")
{
csvDays.Validate();
}
else { }
}
catch (Exception ex)
{
throw ex;
}

}
protected void btnAdd_Click(object sender, EventArgs e)
{
try
{
// Checkfilter();
lblSaveMsg.Visible = false;
//if (lblErrorMsg.Text == "")
//{
Validategrp();
if (rfvCourseType.IsValid && rfvCourseName.IsValid && rfvFee.IsValid && rfvStaffName.IsValid && revFee.IsValid)
{
readdetails();
DataSet dsd = new DataSet();
dsd = sels.CheckCourseEntryDuplicates(Convert.ToInt32(Stid), Cname);
if (dsd.Tables[0].Rows.Count > 0)
{
lblErrorMsg.Visible = true;
lblErrorMsg.Text = " Details Already Existed";
btnEdit.Visible = false;
}
else
{
if (ddlCourseType.SelectedValue == "Crash Course")
{
if (csvDays.IsValid)
{
AddDetailsGrid();
Clear();
btnEdit.Visible = true;
btnSave.Visible = true;
}
else { }

}
else
{
AddDetailsGrid();
Clear();
btnEdit.Visible = true;
btnSave.Visible = true;
}
}


//}
//else
//{
// lblErrorMsg.Visible = true;
// lblErrorMsg.Text = "Entered Wrong Credentials";
//}
}
else
{

}
}

catch (Exception ex)
{
throw ex;
}
}
string Cname = "";
string Ctype = "";
int Stid = 0 ;
string days1 = "";
string fee1 = "";
string stname = "";
public void readdetails()
{
try
{
string k = "";

if (ddlCourseType.SelectedItem.Text == "Crash Course")
{

for (int i = 0; i < chbklDays.Items.Count; i++)
{
if (chbklDays.Items.Selected)
{
k = k + "" + chbklDays.Items.Text + ",";
}
}
k = k.Trim(',');
}
else
{ }

Ctype = ddlCourseType.SelectedValue;
Cname = txtCourseName.Text;
Stid = ddlStaffName.SelectedIndex;
stname= ddlStaffName.SelectedItem.Text;
days1 = k;
fee1 = txtFee.Text;
}
catch (Exception ex)
{
throw ex;
}
}

public void AddDetailsGrid()
{
try
{
readdetails();
dt = (DataTable)ViewState["dt1"];
DataRow dr = dt.NewRow();
dr["CourseType"] = Ctype;
dr["Days"] = days1;
dr["CourseName"] = Cname; ;
dr["StaffId"] = Stid;
dr["StaffName"] = stname;
dr["Fee"] = fee1;
dt.Rows.Add(dr);
GridView1.DataSource = dt;
GridView1.DataBind();
ViewState["dt1"] = dt;
ddlCourseType.Focus();
ddlStaffName.Focus();
}

catch (Exception ex)
{
throw ex;
}
}

protected void ddlCourseType_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
//if (ddlCourseType.SelectedItem.Text == "Regular")
//{
// chbklDays.Visible = false;
//}
//else
if (ddlCourseType.SelectedItem.Text == "Crash Course")
{
chbklDays.Visible = true;
}
else
{
chbklDays.Visible = false;
}
}

catch (Exception ex)
{
throw ex;
}

}
public void Clear()
{
try
{
ddlCourseType.Focus();
ddlStaffName.Focus();
ddlCourseType.SelectedValue = null;
ddlStaffName.SelectedValue = null;
txtCourseName.Text = "";
txtFee.Text = "";
chbklDays.SelectedValue = null;
chbklDays.Visible = false;
lblErrorMsg.Visible = false;
lblSaveMsg.Visible = false;
btnUpdate.Visible = false;
}

catch (Exception ex)
{
throw ex;
}
}

protected void btnClear_Click(object sender, EventArgs e)
{
try
{
Clear();
btnAdd.Visible = true;

lblSaveMsg.Visible = false;
lblErrorMsg.Visible = false;
chbklDays.Visible = false;
btnEdit.Visible = false;

btnDelete.Visible = false;
btnSave.Visible = false;
ViewState["dt1"] = null;
GridView1.DataSource = null;
GridView1.DataBind();
Bind_Grid();
}

catch (Exception ex)
{
throw ex;
}

}
int currentcid = 0;
int count = 0;
int cnt = 0;
protected void btnSave_Click(object sender, EventArgs e)
{
try
{
lblErrorMsg.Visible = false;
lblSaveMsg.Visible = false;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
Label lblCourseTypect = (Label)GridView1.Rows.FindControl("lblCourseTypect");
Label lbldaysd = (Label)GridView1.Rows.FindControl("lbldaysd");
Label lblStaffIdt = (Label)GridView1.Rows.FindControl("lblStaffIdt");
Label lblStaffName1 = (Label)GridView1.Rows.FindControl("lblStaffName1");
Label lblCourseN = (Label)GridView1.Rows.FindControl("lblCourseN");
Label lblFeeAmount = (Label)GridView1.Rows.FindControl("lblFeeAmount");
string stid = lblStaffIdt.Text;
string ct = lblCourseTypect.Text;
string days = lbldaysd.Text;
string csname = lblCourseN.Text;
string feeamount = lblFeeAmount.Text;
DataSet dsd = new DataSet();
dsd = sels.CheckCourseEntryDuplicates(Convert.ToInt32(stid), csname);
if (dsd.Tables[0].Rows.Count > 0)
{
cnt++;
lblErrorMsg.Visible = true;
lblErrorMsg.Text = " Details Already Existed";
btnEdit.Visible = false;
//Response.Write("<script>alert('Student Details Already Existed,Cannot Saved');</script>");
//Response.Redirect("~/frmStudentDetails.aspx");
}
else
{
ins.starttrans();
gtd.CourseName = csname;
gtd.CourseType = ct;
gtd.StaffId = Convert.ToInt32(stid);
if (days == "&nbsp;")
{
gtd.Days = "";
}

else
{
gtd.Days = days;
}
gtd.fee = Convert.ToDecimal(feeamount);
currentcid = gtd.InsertDetails();
gtd.InsertDetails_Child(currentcid);
ins.committrans();
}
if (currentcid > 0)
{
Clear();
count++;
chbklDays.Visible = false;
lblSaveMsg.Visible = true;
lblErrorMsg.Visible = false;
lblErrorMsg.Text = "";
// Response.Write("<script>alert('Course Details Saved Successfully');</script>");
lblSaveMsg.Text = count + " Details Saved Succesfully";
}
else
{
btnEdit.Visible = true;
lblErrorMsg.Visible = true;
lblErrorMsg.Text = cnt + " Details Not Saved";
}

}
}

catch (Exception)
{
ins.rollbacktrans();
throw;
}

}

protected void btnEdit_Click(object sender, EventArgs e)
{
try
{
lblErrorMsg.Visible = false;
lblSaveMsg.Visible = false;
int cnc = 0;
for (int j = 0; j < GridView1.Rows.Count; j++)
{
CheckBox chk1 = (CheckBox)GridView1.Rows[j].FindControl("chkEdit");
if (chk1.Checked == true)
{
int cnt = 0;
for (int k = 0; k < GridView1.Rows.Count; k++)
{
CheckBox chkcurrent11 = (CheckBox)GridView1.Rows[k].FindControl("chkEdit");
if (chkcurrent11.Checked == true)
{
cnt++;
cnc++;
}

if (cnt > 0)
{
DataTable dt = new DataTable();
if (ViewState["dt1"] != null)
{
dt = (DataTable)ViewState["dt1"];
int m = GridView1.PageIndex;
int gg = GridView1.PageSize;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
CheckBox chkcurrent = (CheckBox)GridView1.Rows.FindControl("chkEdit");
if (chkcurrent.Checked == true)
{
int currentid = i;
if (m > 0)
{
currentid = (m * gg) + i;
}
Label lblCourseTypect = (Label)GridView1.Rows.FindControl("lblCourseTypect");
string ct = lblCourseTypect.Text;
ddlCourseType.SelectedValue = ct;
Label lbldaysd = (Label)GridView1.Rows.FindControl("lbldaysd");
string days = lbldaysd.Text;
if (ct == "Regular")
{
chbklDays.Visible = false;
}
else
{
chbklDays.Visible = true;
if (days == "&nbsp;")
{
chbklDays.SelectedValue = null;
}
else
{
chbklDays.SelectedValue = null;
string[] ss = days.Split(',');

for (int n = 0; n < ss.Length; n++)
{
for (int c = 0; c < chbklDays.Items.Count; c++)
{
if (chbklDays.Items[c].Value == ss[n].Trim())
{
chbklDays.Items[c].Selected = true;
}
}
}
}
}
Label lblStaffIdt = (Label)GridView1.Rows.FindControl("lblStaffIdt");
Label lblCourseN = (Label)GridView1.Rows.FindControl("lblCourseN");
Label lblFeeAmount = (Label)GridView1.Rows.FindControl("lblFeeAmount");
txtCourseName.Text = lblCourseN.Text;
ddlStaffName.SelectedValue = lblStaffIdt.Text;
txtFee.Text = lblFeeAmount.Text;
dt.Rows.RemoveAt(currentid);
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
ViewState["dt1"] = dt;
}
}
}
}
else
{
//lblErrorMsg.Visible = true;
//lblErrorMsg.Text = "Select A Record To Edit";
}
}
if (cnc > 0)
{

}
else
{
lblErrorMsg.Visible = true;
lblErrorMsg.Text = "Select a record to edit";
}
}
catch (Exception ex)
{
throw ex;
}

}

protected void btnDelete_Click(object sender, EventArgs e)
{
try
{
lblSaveMsg.Visible = false;
for (int j = 0; j < GridView1.Rows.Count; j++)
{
CheckBox chk1 = (CheckBox)GridView1.Rows[j].FindControl("chkEdit");
if (chk1.Checked == true)
{
int cnt = 0;
for (int k = 0; k < GridView1.Rows.Count; k++)
{
CheckBox chkcurrent11 = (CheckBox)GridView1.Rows[k].FindControl("chkEdit");
if (chkcurrent11.Checked == true)
{
cnt++;
}

if (cnt > 0)
{
DataTable dt = new DataTable();
if (ViewState["dt1"] != null)
{
dt = (DataTable)ViewState["dt1"];
int m = GridView1.PageIndex;
int gg = GridView1.PageSize;
for (int i = 0; i < GridView1.Rows.Count; i++)
{
CheckBox chkcurrent = (CheckBox)GridView1.Rows.FindControl("chkEdit");
if (chkcurrent.Checked == true)
{
int currentid = i;
if (m > 0)
{
currentid = (m * gg) + i;
}
Label lblCourseTypect = (Label)GridView1.Rows.FindControl("lblCourseTypect");
Label lbldaysd = (Label)GridView1.Rows.FindControl("lbldaysd");
Label lblStaffIdt = (Label)GridView1.Rows.FindControl("lblStaffIdt");
Label lblCourseN = (Label)GridView1.Rows.FindControl("lblCourseN");
Label lblFeeAmount = (Label)GridView1.Rows.FindControl("lblFeeAmount");
dt.Rows.RemoveAt(currentid);
}
}
GridView1.DataSource = dt;
GridView1.DataBind();
ViewState["dt1"] = dt;
}
}
}
lblErrorMsg.Visible = true;
lblErrorMsg.Text = "Deleted Successfully";
}
else
{
lblErrorMsg.Visible = true;
lblErrorMsg.Text = "Select a record to delete";
}
}
}
catch (Exception ex)
{
throw ex;
}

}

protected void csvDays_ServerValidate(object source, ServerValidateEventArgs args)
{
try
{
args.IsValid = chbklDays.SelectedItem != null;
}
catch (Exception ex)
{
throw ex;
}
}
//public string Checkfilter()
//{
// try
// {
// string rfilter = string.Empty;
// string rfltr = string.Empty;
// dt = (DataTable)ViewState["dt1"];
// if (GridView1.Rows.Count > 0)
// {
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// rfilter = dt.Rows["StaffId"].ToString();
// rfltr = dt.Rows["CourseName"].ToString();
// if (ddlStaffName.SelectedValue == rfilter || txtCourseName.Text==rfltr)
// {
// lblErrorMsg.Visible = true;
// lblErrorMsg.Text = "Selected Detals Already Existed";
// break;
// }
// else
// {
// lblErrorMsg.Visible = false;
// lblErrorMsg.Text = "";
// }
// }
// }
// else
// {
// lblErrorMsg.Visible = false;
// lblErrorMsg.Text = "";
// }
// return rfilter;
// }

// catch (Exception ex)
// {
// throw ex;
// }
//}
public void Bind_Data_cdl(int eid)
{
try
{
//for Binding data to page from edit click
DataSet ds = new DataSet();
ds =sels.Bind_Cdl_Edit(eid);
btnUpdate.Visible = true;
btnSave.Visible = false;
btnAdd.Visible = false;
btnDelete.Visible = false;
btnEdit.Visible = false;

txtCourseID.Text = ds.Tables[0].Rows[0]["CourseId"].ToString();
txtCourseName.Text = ds.Tables[0].Rows[0]["CourseName"].ToString();
txtFee.Text = ds.Tables[0].Rows[0]["Fee"].ToString();
ddlCourseType.SelectedValue = ds.Tables[0].Rows[0]["CourseType"].ToString();
ddlStaffName.SelectedValue = ds.Tables[0].Rows[0]["StaffId"].ToString();
if(ddlCourseType.SelectedValue == "Crash Course")
{
chbklDays.Visible = true;
string strchbklDays = string.Empty;
strchbklDays = ds.Tables[0].Rows[0]["Days"].ToString();
string[] ss = strchbklDays.Split(',');

for (int n = 0; n < ss.Length; n++)
{
for (int k = 0; k < chbklDays.Items.Count; k++)
{
if (chbklDays.Items[k].Value == ss[n].Trim())
{
chbklDays.Items[k].Selected = true;
}
}
}
}
else
{
chbklDays.Visible = false;
}
}
catch (Exception ex)
{
throw ex;
}
}

public void updatedetails()
{
string k = "";

if (ddlCourseType.SelectedItem.Text == "Crash Course")
{

for (int i = 0; i < chbklDays.Items.Count; i++)
{
if (chbklDays.Items.Selected)
{
k = k + "" + chbklDays.Items.Text + ",";
}
}
k = k.Trim(',');
}
else
{ }
int cid = Convert.ToInt32(txtCourseID.Text);
gtd.Days = k;
gtd.CourseName = txtCourseName.Text;
gtd.CourseType = ddlCourseType.SelectedValue;
gtd.StaffId = Convert.ToInt32(ddlStaffName.SelectedValue);
gtd.fee = Convert.ToDecimal(txtFee.Text);
gtd.UpdateDetails(cid);
gtd.UpdateDetails_Child(cid);

}
protected void btnUpdate_Click(object sender, EventArgs e)
{
try
{
Validategrp();
if (rfvCourseType.IsValid && rfvCourseName.IsValid && rfvFee.IsValid && rfvStaffName.IsValid && revFee.IsValid)
{
if(ddlCourseType.SelectedValue=="CrashCourse")
{
if(csvDays.IsValid)
{
updatedetails();
Clear();
btnUpdate.Visible = false;
btnAdd.Visible = true;
btnClear.Visible = true;
lblSaveMsg.Visible = true;
lblSaveMsg.Text = "Details Updated Successfully";
}
else { }
}
else
{

updatedetails();
Clear();
btnUpdate.Visible = false;
btnAdd.Visible = true;
btnClear.Visible = true;
lblSaveMsg.Visible = true;
lblSaveMsg.Text = "Details Updated Successfully";
}

}

}
catch (Exception ex)
{
throw ex;
}
}
}
}




using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;
using System.Data.SqlTypes;
using System.IO;


namespace task3at3
{
public class clsInsertion
{
public SqlConnection con;
public SqlCommand cmd;
public SqlTransaction transaction;
public CultureInfo objDate = new CultureInfo("en-CA");

//public string SqlConnectionstring = System.Configuration.ConfigurationManager.AppSettings["MKR"].ToString();

string connStr = ConfigurationManager.ConnectionStrings["mkrConnectionString"].ConnectionString;


public void insWebform1(int RegId, string StudentName,string InstitutionName, int CourseId, decimal Fee,string Gender,string Timings,DateTime ExpectedDate, string Mobile, string Email, int Age,string TYPE)
{
con = new SqlConnection(connStr);
cmd=new SqlCommand("StudentDetailsTable_PROCEDURE",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

cmd.Parameters.Add("@RegId", SqlDbType.Int);
cmd.Parameters["@RegID"].Value = RegId;

cmd.Parameters.Add("@StudentName", SqlDbType.VarChar );
cmd.Parameters["@StudentName"].Value = StudentName;

cmd.Parameters.Add("@InstitutionName", SqlDbType.VarChar);
cmd.Parameters["@InstitutionName"].Value = InstitutionName;

cmd.Parameters.Add("@CourseId", SqlDbType.Int);
cmd.Parameters["@CourseId"].Value = CourseId;

cmd.Parameters.Add("@Fee", SqlDbType.Decimal);
cmd.Parameters["@Fee"].Value = Fee;

cmd.Parameters.Add("@Gender", SqlDbType.VarChar);
cmd.Parameters["@Gender"].Value = Gender;

cmd.Parameters.Add("@Timings", SqlDbType.NVarChar);
cmd.Parameters["@Timings"].Value = Timings;

cmd.Parameters.Add("@ExpectedDate", SqlDbType.DateTime);
cmd.Parameters["@ExpectedDate"].Value = ExpectedDate;

cmd.Parameters.Add("@Mobile", SqlDbType.NVarChar);
cmd.Parameters["@Mobile"].Value = Mobile;

cmd.Parameters.Add("@Email", SqlDbType.NVarChar);
cmd.Parameters["@Email"].Value = Email;

cmd.Parameters.Add("@age", SqlDbType.Int);
cmd.Parameters["@age"].Value = Age;

cmd.Parameters.Add("@TYPE", SqlDbType.NChar);
cmd.Parameters["@TYPE"].Value = TYPE;

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

public int insCoursetable(int CourseId, string CourseName, string CourseType,string Days, string TYPE)
{
con = new SqlConnection(connStr);
cmd = new SqlCommand("CourseDetails_Procedure",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

cmd.Parameters.Add("@CourseId", SqlDbType.Int);
cmd.Parameters["@CourseId"].Value = CourseId;

cmd.Parameters.Add("@CourseName", SqlDbType.NVarChar);
cmd.Parameters["@CourseName"].Value = CourseName;

cmd.Parameters.Add("@CourseType", SqlDbType.NVarChar);
cmd.Parameters["@CourseType"].Value = CourseType;

cmd.Parameters.Add("@Days", SqlDbType.NVarChar);
cmd.Parameters["@Days"].Value = Days;

cmd.Parameters.Add("@TYPE", SqlDbType.NChar);
cmd.Parameters["@TYPE"].Value = TYPE;

cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters["@id"].Direction = ParameterDirection.Output;

con.Open();
cmd.ExecuteNonQuery();
int intReturnValue = Convert.ToInt32(cmd.Parameters["@id"].Value);
return intReturnValue;

}
public void insCoursetable_child(int Sno,int CourseId,int StaffId,decimal Fee, string TYPE)
{
con = new SqlConnection(connStr);
cmd =new SqlCommand("CourseDetails_Child_Procedure",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

cmd.Parameters.Add("@sno", SqlDbType.Int);
cmd.Parameters["@sno"].Value = Sno;

cmd.Parameters.Add("@CourseId", SqlDbType.Int);
cmd.Parameters["@CourseId"].Value = CourseId;

cmd.Parameters.Add("@StaffId", SqlDbType.Int);
cmd.Parameters["@StaffId"].Value = StaffId;

cmd.Parameters.Add("@Fee", SqlDbType.Decimal);
cmd.Parameters["@Fee"].Value = Fee;

cmd.Parameters.Add("@TYPE", SqlDbType.NChar);
cmd.Parameters["@TYPE"].Value = TYPE;

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public void UpdateCoursetable(int CourseId, string CourseName, string CourseType, string Days, string TYPE)
{
con = new SqlConnection(connStr);
cmd= new SqlCommand("CourseDetails_Procedure",con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;

cmd.Parameters.Add("@CourseId", SqlDbType.Int);
cmd.Parameters["@CourseId"].Value = CourseId;

cmd.Parameters.Add("@CourseName", SqlDbType.NVarChar);
cmd.Parameters["@CourseName"].Value = CourseName;

cmd.Parameters.Add("@CourseType", SqlDbType.NVarChar);
cmd.Parameters["@CourseType"].Value = CourseType;

cmd.Parameters.Add("@Days", SqlDbType.NVarChar);
cmd.Parameters["@Days"].Value = Days;

cmd.Parameters.Add("@TYPE", SqlDbType.NChar);
cmd.Parameters["@TYPE"].Value = TYPE;

cmd.Parameters.Add("@id", SqlDbType.Int);
cmd.Parameters["@id"].Direction = ParameterDirection.Output;

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
public void starttrans()
{
con = new SqlConnection(connStr);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
transaction = con.BeginTransaction();
cmd = new SqlCommand();
cmd.Transaction = transaction;
}
public void committrans()
{
con = new SqlConnection(connStr);
transaction.Commit();
if (con.State == ConnectionState.Open || con.State == ConnectionState.Broken)
{
con.Close(); con.Dispose();
}
}
public void rollbacktrans()
{
con = new SqlConnection(connStr);
transaction.Rollback();
if (con.State == ConnectionState.Open || con.State == ConnectionState.Broken)
{
con.Close(); con.Dispose();
}
}
}
}

Continue reading...