Place of BeginTransactio n in code

[Mukesh]

Hi all,
Consider this code

SqlConnection oConn = new
SqlConnection(A ccountsConnecti onString);
SqlCommand cmdInsert = new SqlCommand("Upd ateQuery");
//Location 1:
//SqlTransaction sqlTran = oConn.BeginTran saction();
try
{
oConn.Open();
//Location 2:
//SqlTransaction sqlTran = oConn.BeginTran saction();
cmdInsert.Execu teNonQuery();
sqlTran.Commit( );
}
catch (Exception ex)
{
//Location 3:
sqlTran.Rollbac k();
}
finally
{
oConn.Close();
}

I have to start a transaction and commit or rollback depending upon the
condition of success of command execution. The problem is if I write
code to Begin Transaction at location 1 it won't work as connection is
still closed. If I write the same at location 2, I won't be able to
rollback transaction at location 3 as sqlTran object is out of
referance. What should I do? I am using framework 2.0.

Thanks and Regards
Chakravarti Mukesh

[Chris Dunaway]

On Oct 10, 8:09 am, "Mukesh" <cmukes...@gmai l.comwrote:

SqlConnection oConn = new
SqlConnection(A ccountsConnecti onString);
SqlCommand cmdInsert = new SqlCommand("Upd ateQuery");
//Location 1:
//SqlTransaction sqlTran = oConn.BeginTran saction();
try
{
oConn.Open();
//Location 2:
//SqlTransaction sqlTran = oConn.BeginTran saction();
cmdInsert.Execu teNonQuery();
sqlTran.Commit( );
}
catch (Exception ex)
{
//Location 3:
sqlTran.Rollbac k();
}
finally
{
oConn.Close();
}

Declare the transaction variable in Location 1, but Begin it in
location 2:

SqlConnection oConn = new
SqlConnection(A ccountsConnecti onString);
SqlCommand cmdInsert = new SqlCommand("Upd ateQuery");
//Location 1:
SqlTransaction sqlTran = null;
try
{
oConn.Open();
//Location 2:
sqlTran = oConn.BeginTran saction();
cmdInsert.Execu teNonQuery();
sqlTran.Commit( );
}
catch (Exception ex)
{
//Location 3:
sqlTran.Rollbac k();
}
finally
{
oConn.Close();
}

[Marina Levit [MVP]]

Declare the transaction object outside the try/catch. Just don't set it to
anything. Then, after you open the connection, inside the try block, set
the variable to the new transaction.

There is no law that says you have to declare the variable and set it to
something in the same line. You can declare everything at the top of your
method, and then use it later. In fact, that is my personal preference,
just to see all the variables involved in my method, so I don't have to
track them down throughout the method.

"Mukesh" <cm*******@gmai l.comwrote in message
news:11******** *************@k 70g2000cwa.goog legroups.com...

Hi all,
Consider this code

SqlConnection oConn = new
SqlConnection(A ccountsConnecti onString);
SqlCommand cmdInsert = new SqlCommand("Upd ateQuery");
//Location 1:
//SqlTransaction sqlTran = oConn.BeginTran saction();
try
{
oConn.Open();
//Location 2:
//SqlTransaction sqlTran = oConn.BeginTran saction();
cmdInsert.Execu teNonQuery();
sqlTran.Commit( );
}
catch (Exception ex)
{
//Location 3:
sqlTran.Rollbac k();
}
finally
{
oConn.Close();
}

I have to start a transaction and commit or rollback depending upon the
condition of success of command execution. The problem is if I write
code to Begin Transaction at location 1 it won't work as connection is
still closed. If I write the same at location 2, I won't be able to
rollback transaction at location 3 as sqlTran object is out of
referance. What should I do? I am using framework 2.0.

Thanks and Regards
Chakravarti Mukesh

[Mukesh]

Hi,
In fact I tried declaring it on location 1 before posting. But as you
wrote, I was not setting it to anything. Thus I am getting the error
"Object reference not found...". But when I set it to null as Chris
suggested, it is working fine.

Thanks and Regards
Chakravarti Mukesh

Marina Levit [MVP] wrote:

Declare the transaction object outside the try/catch. Just don't set it to
anything. Then, after you open the connection, inside the try block, set
the variable to the new transaction.

There is no law that says you have to declare the variable and set it to
something in the same line. You can declare everything at the top of your
method, and then use it later. In fact, that is my personal preference,
just to see all the variables involved in my method, so I don't have to
track them down throughout the method.

"Mukesh" <cm*******@gmai l.comwrote in message
news:11******** *************@k 70g2000cwa.goog legroups.com...

Hi all,
Consider this code

SqlConnection oConn = new
SqlConnection(A ccountsConnecti onString);
SqlCommand cmdInsert = new SqlCommand("Upd ateQuery");
//Location 1:
//SqlTransaction sqlTran = oConn.BeginTran saction();
try
{
oConn.Open();
//Location 2:
//SqlTransaction sqlTran = oConn.BeginTran saction();
cmdInsert.Execu teNonQuery();
sqlTran.Commit( );
}
catch (Exception ex)
{
//Location 3:
sqlTran.Rollbac k();
}
finally
{
oConn.Close();
}

I have to start a transaction and commit or rollback depending upon the
condition of success of command execution. The problem is if I write
code to Begin Transaction at location 1 it won't work as connection is
still closed. If I write the same at location 2, I won't be able to
rollback transaction at location 3 as sqlTran object is out of
referance. What should I do? I am using framework 2.0.

Thanks and Regards
Chakravarti Mukesh

[Marina Levit [MVP]]

Whether or not you set it to null is irrelevant. When you declare a
variable, it is already null. Setting it to null explitily doesn't change
the value of the variable.

In your exception, you are assuming you have a transaction. If the
connection could not be opened, no transaction would ever have been started.

"Mukesh" <cm*******@gmai l.comwrote in message
news:11******** **************@ b28g2000cwb.goo glegroups.com.. .

Hi,
In fact I tried declaring it on location 1 before posting. But as you
wrote, I was not setting it to anything. Thus I am getting the error
"Object reference not found...". But when I set it to null as Chris
suggested, it is working fine.

Thanks and Regards
Chakravarti Mukesh

Marina Levit [MVP] wrote:

>Declare the transaction object outside the try/catch. Just don't set it
to
anything. Then, after you open the connection, inside the try block, set
the variable to the new transaction.

There is no law that says you have to declare the variable and set it to
something in the same line. You can declare everything at the top of your
method, and then use it later. In fact, that is my personal preference,
just to see all the variables involved in my method, so I don't have to
track them down throughout the method.

"Mukesh" <cm*******@gmai l.comwrote in message
news:11******* **************@ k70g2000cwa.goo glegroups.com.. .

Hi all,
Consider this code

SqlConnection oConn = new
SqlConnection(A ccountsConnecti onString);
SqlCommand cmdInsert = new SqlCommand("Upd ateQuery");
//Location 1:
//SqlTransaction sqlTran = oConn.BeginTran saction();
try
{
oConn.Open();
//Location 2:
//SqlTransaction sqlTran = oConn.BeginTran saction();
cmdInsert.Execu teNonQuery();
sqlTran.Commit( );
}
catch (Exception ex)
{
//Location 3:
sqlTran.Rollbac k();
}
finally
{
oConn.Close();
}

I have to start a transaction and commit or rollback depending upon the
condition of success of command execution. The problem is if I write
code to Begin Transaction at location 1 it won't work as connection is
still closed. If I write the same at location 2, I won't be able to
rollback transaction at location 3 as sqlTran object is out of
referance. What should I do? I am using framework 2.0.

Thanks and Regards
Chakravarti Mukesh

[Mark Wilden]

"Marina Levit [MVP]" <so*****@nospam .comwrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..

Whether or not you set it to null is irrelevant. When you declare a
variable, it is already null.

Actually, I don't believe that's true, is it?

///ark

[Marina Levit [MVP]]

Yes, it is. A variable that is an object, is initially not pointing to
anything- a.k.a null.

Declare a variable and try to use it without instantiating it - you will
get a NullReferenceEx ception.
Declare the same variable and point it to null and try to use it - you will
get a NullReferenceEx ception.

This applies to objects, not structures.

"Mark Wilden" <mw*****@commun itymtm.comwrote in message
news:eE******** ******@TK2MSFTN GP02.phx.gbl...

"Marina Levit [MVP]" <so*****@nospam .comwrote in message
news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..

>Whether or not you set it to null is irrelevant. When you declare a
variable, it is already null.

Actually, I don't believe that's true, is it?

///ark

[Willy Denoyette [MVP]]

This is only valid for member variables, local need to be explicitely
assigned/initialized, though failing to do so results in a compiler error .

Willy.

"Marina Levit [MVP]" <so*****@nospam .comwrote in message
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..
| Yes, it is. A variable that is an object, is initially not pointing to
| anything- a.k.a null.
|
| Declare a variable and try to use it without instantiating it - you will
| get a NullReferenceEx ception.
| Declare the same variable and point it to null and try to use it - you
will
| get a NullReferenceEx ception.
|
| This applies to objects, not structures.
|
| "Mark Wilden" <mw*****@commun itymtm.comwrote in message
| news:eE******** ******@TK2MSFTN GP02.phx.gbl...
| "Marina Levit [MVP]" <so*****@nospam .comwrote in message
| news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
| >
| >Whether or not you set it to null is irrelevant. When you declare a
| >variable, it is already null.
| >
| Actually, I don't believe that's true, is it?
| >
| ///ark
| >
| >
|
|

[Marina Levit [MVP]]

Error or warning? I know in VB, it is a warning. And in 2003 I don't
remember C# requiring you to assign values to variables.

Either way, it is a compiler issue. That is a compiler validation - but
effectively assigning a variable to null is the same as not assigning it a
value to begin with.

"Willy Denoyette [MVP]" <wi************ *@telenet.bewro te in message
news:uz******** ******@TK2MSFTN GP05.phx.gbl...

This is only valid for member variables, local need to be explicitely
assigned/initialized, though failing to do so results in a compiler error
.

Willy.

"Marina Levit [MVP]" <so*****@nospam .comwrote in message
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..
| Yes, it is. A variable that is an object, is initially not pointing to
| anything- a.k.a null.
|
| Declare a variable and try to use it without instantiating it - you
will
| get a NullReferenceEx ception.
| Declare the same variable and point it to null and try to use it - you
will
| get a NullReferenceEx ception.
|
| This applies to objects, not structures.
|
| "Mark Wilden" <mw*****@commun itymtm.comwrote in message
| news:eE******** ******@TK2MSFTN GP02.phx.gbl...
| "Marina Levit [MVP]" <so*****@nospam .comwrote in message
| news:%2******** ********@TK2MSF TNGP02.phx.gbl. ..
| >
| >Whether or not you set it to null is irrelevant. When you declare a
| >variable, it is already null.
| >
| Actually, I don't believe that's true, is it?
| >
| ///ark
| >
| >
|
|

[Mark Wilden]

"Marina Levit [MVP]" <so*****@nospam .comwrote in message
news:Oz******** ******@TK2MSFTN GP03.phx.gbl...

Error or warning? I know in VB, it is a warning. And in 2003 I don't
remember C# requiring you to assign values to variables.

C# require variables to be "definitely assigned" before use. So an
uninitialized variable could be null or it could be anything else - there's
no way to tell. :)

///ark