Transactions Not working ??????????

  • Thread starter Thread starter S.Kartikeyan
  • Start date Start date
S

S.Kartikeyan

I have MSSQL SERVER
I have the following C# function
But the database entries are not being created why
Do i need to change any database settings?????

If i remove the transaction it is working


public static bool CreateNewStudent(string userid,string passwd,string
emailid,string roll,string title,string fullname,string batch,string
program,string department)
{
bool success = false;
SqlConnection con = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SQL_CONNECTION_STRING"]);
con.Open();
SqlCommand cmd = con.CreateCommand();
SqlTransaction createStudentTrans =
con.BeginTransaction(IsolationLevel.Serializable);
cmd.Connection = con;
cmd.Transaction = createStudentTrans;

try
{
//order of commands is important due to foreign key contraints
//command 1 of transaction
cmd.CommandText = "INSERT INTO
ulsusers(userid,passwd,role,lastlogin,createdon,emailid)
VALUES(@userid,@passwd,@role,@lastlogin,@createdon,@emailid)";
cmd.Parameters.Add("@userid",userid);
cmd.Parameters.Add("@passwd",FormsAuthentication.HashPasswordForStoringInConfigFile(passwd,"MD5"));
cmd.Parameters.Add("@role","student");
cmd.Parameters.Add("@lastlogin",DateTime.Now);
cmd.Parameters.Add("@createdon",DateTime.Now);
cmd.Parameters.Add("@emailid",emailid);
int rows = cmd.ExecuteNonQuery();
//command 2 of transaction
cmd.CommandText = "INSERT INTO
ulsstudents(userid,roll,title,fullname,batch,program,department) "+
" VALUES(@userid,@roll,@title,@fullname,@batch,@program,@department)";
cmd.Parameters.Add("@userid",userid);
cmd.Parameters.Add("@roll",roll);
cmd.Parameters.Add("@title",title);
cmd.Parameters.Add("@fullname",fullname);
cmd.Parameters.Add("@batch",batch);
cmd.Parameters.Add("@program",program);
cmd.Parameters.Add("@department",department);
rows += cmd.ExecuteNonQuery();

//commit if both successfull
createStudentTrans.Commit();
success = (rows==2);
}
catch(Exception e)
{
try
{
createStudentTrans.Rollback();
}
catch(Exception ex)
{
}
}
con.Close();
return success;

}
 
I wasn't able to test your code in detail, but I did notice 1 item that
might have been your problem....
basicly, the only thing I did was create a new instance of the command
object.
You where appending additional parameters to the command before executing it
the second time. this adds more parameters to the command without removing
the old ones. So even though you had created a new commandtext, the old
parameters where still there.

I am not convinced this will fix your problem, since I would have expected
you to get an exception with the way it was, but since it can't hurt to try
the changes, I offer them up......

Here is the changed code.

-----------------------------
public static bool CreateNewStudent(string userid,string passwd,string
emailid,string roll,string title,string fullname,string batch,string
program,string department)
{
bool success = false;
SqlCommand cmd;

SqlConnection con = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SQL_CO
NNECTION_STRING"]);
con.Open();

SqlTransaction createStudentTrans =
con.BeginTransaction(IsolationLevel.Serializable);

try
{
//order of commands is important due to foreign key contraints
//command 1 of transaction
cmd.Connection = con;
cmd.Transaction = createStudentTrans;
cmd.CommandText = "INSERT INTO
ulsusers(userid,passwd,role,lastlogin,createdon,emailid)"
+ "VALUES(@userid,@passwd,@role,@lastlogin,@createdon,@emailid)";
cmd.Parameters.Add("@userid",userid);

cmd.Parameters.Add("@passwd",FormsAuthentication.HashPasswordForStoringInCon
figFile(passwd,"MD5"));
cmd.Parameters.Add("@role","student");
cmd.Parameters.Add("@lastlogin",DateTime.Now);
cmd.Parameters.Add("@createdon",DateTime.Now);
cmd.Parameters.Add("@emailid",emailid);
int rows = cmd.ExecuteNonQuery();
//command 2 of transaction
cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = createStudentTrans;
cmd.CommandText = "INSERT INTO ulsstudents(userid,roll,title,fullname,batc
h,program,department) " +
" VALUES(@userid,@roll,@title,@fullname,@batch,@program,@department)";
cmd.Parameters.Add("@userid",userid);
cmd.Parameters.Add("@roll",roll);
cmd.Parameters.Add("@title",title);
cmd.Parameters.Add("@fullname",fullname);
cmd.Parameters.Add("@batch",batch);
cmd.Parameters.Add("@program",program);
cmd.Parameters.Add("@department",department);
rows += cmd.ExecuteNonQuery();
//commit if both successfull
createStudentTrans.Commit();
success = (rows==2);
}
catch(Exception e)
{
try
{
createStudentTrans.Rollback();
}
catch(Exception ex)
{
}
}
con.Close();
return success;
}
 
your Suggestion was correct regarding extra parameters.
I tested the thing you showed.It was working fine.
Also the following thing was working.
before start of new sub transaction i am calling
cmd.Parameters.Clear();

Thanks for your suggestion.
Unfortunately i followed one of the example in the msdn help itself which
did not point out this.

S.Kartikeyan

Kirk Graves said:
I wasn't able to test your code in detail, but I did notice 1 item that
might have been your problem....
basicly, the only thing I did was create a new instance of the command
object.
You where appending additional parameters to the command before executing it
the second time. this adds more parameters to the command without removing
the old ones. So even though you had created a new commandtext, the old
parameters where still there.

I am not convinced this will fix your problem, since I would have expected
you to get an exception with the way it was, but since it can't hurt to try
the changes, I offer them up......

Here is the changed code.

-----------------------------
public static bool CreateNewStudent(string userid,string passwd,string
emailid,string roll,string title,string fullname,string batch,string
program,string department)
{
bool success = false;
SqlCommand cmd;

SqlConnection con = new
SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["SQL_CO
NNECTION_STRING"]);
con.Open();

SqlTransaction createStudentTrans =
con.BeginTransaction(IsolationLevel.Serializable);

try
{
//order of commands is important due to foreign key contraints
//command 1 of transaction
cmd.Connection = con;
cmd.Transaction = createStudentTrans;
cmd.CommandText = "INSERT INTO
ulsusers(userid,passwd,role,lastlogin,createdon,emailid)"
+ "VALUES(@userid,@passwd,@role,@lastlogin,@createdon,@emailid)";
cmd.Parameters.Add("@userid",userid);

cmd.Parameters.Add("@passwd",FormsAuthentication.HashPasswordForStoringInCon
figFile(passwd,"MD5"));
cmd.Parameters.Add("@role","student");
cmd.Parameters.Add("@lastlogin",DateTime.Now);
cmd.Parameters.Add("@createdon",DateTime.Now);
cmd.Parameters.Add("@emailid",emailid);
int rows = cmd.ExecuteNonQuery();
//command 2 of transaction
cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = createStudentTrans;
cmd.CommandText = "INSERT INTO ulsstudents(userid,roll,title,fullname,batc
h,program,department) " +
" VALUES(@userid,@roll,@title,@fullname,@batch,@program,@department)";
cmd.Parameters.Add("@userid",userid);
cmd.Parameters.Add("@roll",roll);
cmd.Parameters.Add("@title",title);
cmd.Parameters.Add("@fullname",fullname);
cmd.Parameters.Add("@batch",batch);
cmd.Parameters.Add("@program",program);
cmd.Parameters.Add("@department",department);
rows += cmd.ExecuteNonQuery();
//commit if both successfull
createStudentTrans.Commit();
success = (rows==2);
}
catch(Exception e)
{
try
{
createStudentTrans.Rollback();
}
catch(Exception ex)
{
}
}
con.Close();
return success;
}
------------------------------------

Kirk Graves
KRGIT Software

S.Kartikeyan said:
I have MSSQL SERVER
I have the following C# function
But the database entries are not being created why
Do i need to change any database settings?????

If i remove the transaction it is working

-------------------------------------------------------------------------- ----
ANY HELP
S.Kartikeyan
Click to expand...
Click to expand...
 
Back
Top