Using Transactions

Hi,

I am using C#.NET 2005, SQL Server 2000.

I have a few questions to ask. Firstly, can anybody help me by telling the difference between the code below and the same code with comments removed:

string localquery = "select bla bla"; SqlDataAdapter _sda; DataTable _dt; SqlCommand _cmd; bool isErrCatched = false; Exception ExcToThrow = new Exception(); using (SqlConnection _cn = NewConnection) // NewConnection returns a static SQLConnection object { _cn.Open(); _cmd = new SqlCommand(localquery, _cn); //SqlTransaction myTrans; //myTrans = _cn.BeginTransaction(); //_cmd.Transaction = myTrans; _dt = new DataTable(); _sda = new SqlDataAdapter(_cmd); try { _sda.Fill(_dt); _sda.Dispose(); //myTrans.Commit(); } catch (Exception exc) { //try { myTrans.Rollback(); } catch {} isErrCatched = true; ExcToThrow = exc; } finally { _cn.Close(); } _cmd.Dispose(); } _cmd = null; if (isErrCatched) throw ExcToThrow; return _dt;


My first question: Can there be a performance loss if I uncomment the lines about transaction usage? I mean, when I do this I start to get more timeouts.

My problem goes on. When I comment those lines and run a stress tool, I am getting "column X does not belong to table Y" errors. If those lines are not commented i am not getting this error, but I get timeout errors frequently. So, my second question: is there something wrong in my query or is there a bad coding practice I am following? Could someone offer a better and more robust sample for this code block?

By the way, connection pooling is on. And these errors are observed under high loads.

Thanks everybody.