Prev: using insert with output
Next: Help with Query
From: pers on 12 Jul 2010 05:25
Hi all
In my web application I have following code:
SqlConnection con = new SqlConnection("Data Source=.;Connect Timeout=15;
Initial Catalog=SaamicAcc1;Integrated Security=True");

SqlCommand com = new SqlCommand("",con);


com.Parameters.AddWithValue("@A", "somevalue");

com.CommandText = "Insert into Test (Name) values (@A)";

con.Open();


com.Transaction= con.BeginTransaction();

// con.EnlistTransaction(com.Transaction);

com.ExecuteNonQuery();

Session["con"] = com.Transaction;

Response.Redirect("default5.aspx");

and in default5.aspx I commit or rollback this transaction.

The problem is If Session expired or the page unexpectedly closed the
SqlTransaction stays in pending state.

How can I set for example a timeout for transaction or transaction
automatic closed??

thanks in advance


From: Erland Sommarskog on 12 Jul 2010 16:46
pers (rezarms(a)hotmail.com) writes:
> Hi all
> In my web application I have following code:
> SqlConnection con = new SqlConnection("Data Source=.;Connect Timeout=15;
> Initial Catalog=SaamicAcc1;Integrated Security=True");
>
> SqlCommand com = new SqlCommand("",con);
>
>
> com.Parameters.AddWithValue("@A", "somevalue");
>
> com.CommandText = "Insert into Test (Name) values (@A)";
>
> con.Open();
>
>
> com.Transaction= con.BeginTransaction();
>
> // con.EnlistTransaction(com.Transaction);
> com.ExecuteNonQuery();
>
> Session["con"] = com.Transaction;
> Response.Redirect("default5.aspx");
>
> and in default5.aspx I commit or rollback this transaction.
>
> The problem is If Session expired or the page unexpectedly closed the
> SqlTransaction stays in pending state.
>
> How can I set for example a timeout for transaction or transaction
> automatic closed??

You may get better response in a newsgroup devoted to ASP .Net, but
my gut feeling is that you need to have code that handles this
situation. Or you should not nest transactions this way at all...


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: using insert with output
Next: Help with Query