Transactions a little bit faster

Transactions a little bit faster I´ve created a class to make some standard transaction development a little bit faster. The destructor seem to run, but something makes this object slow down the database, if SqlTransaction and/or SqlConnection isnt manualy handled with the method Commit(). Any ideas on how to handle the SqlTransaction and SqlConnection better? public class DataTransaction { private bool blnError = false; private ArrayList arrErrorList = new ArrayList(); private SqlConnection objConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString()); private SqlTransaction objTransaction; public DataTransaction() { objConnection.Open(); objTransaction = objConnection.BeginTransaction(); } ~DataTransaction() { if(objTransaction.Connection != null) { objTransaction = null; objConnection.Close(); } } public int ExecuteNonQuery(string Query) { int intRowsAffected = -1; SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction); try { intRowsAffected = objCommand.ExecuteNonQuery(); } catch(Exception e) { blnError = true; arrErrorList.Add(e.ToString()); } finally { objCommand.Dispose(); } return intRowsAffected; } public System.Object ExecuteScalar(string Query) { System.Object objToReturn = null; SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction); try { objToReturn = objCommand.ExecuteScalar(); } catch(Exception e) { blnError = true; arrErrorList.Add(e.ToString()); } finally { objCommand.Dispose(); } return objToReturn; } public bool Commit() { if(!blnError) { objTransaction.Commit(); return true; } else { objTransaction.Rollback(); return false; } objConnection.Close(); } }

Hi If you like it fast you should: 1.) Make your transactions on the database server and not in your appliacation whenever possible. (BEGIN TRAN, COMMIT TRAN, ROLLBACK on sql server) 2.) NOT generate a command string in your application and send id to the server(you should use a parametrized Command object, because in this case, the server can optimize your command on the server and does not have to compile the query on each call) (There are articles about this on MSDN) 3.) NOT call Dispose() if you do not absolutely have to. You have to call dispose if you use unmanaged resorces. But in this case there is no need for it. (The Garbage Collector will do it just fine for you) There is tool available on msdn to see, what the gc does (if daddy is not at home) try it, if your problems wont go away. Sorry, this shows like your transaction class seems to be a bad idea, bad truly i think it is.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

haerry22y ago
Hi If you like it fast you should: 1.) Make your transactions on the database server and not in your appliacation whenever possible. (BEGIN TRAN, COMMIT TRAN, ROLLBACK on sql server) 2.) NOT generate a command string in your application and send id to the server(you should use a parametrized Command object, because in this case, the server can optimize your command on the server and does not have to compile the query on each call) (There are articles about this on MSDN) 3.) NOT call Dispose() if you do not absolutely have to. You have to call dispose if you use unmanaged resorces. But in this case there is no need for it. (The Garbage Collector will do it just fine for you) There is tool available on msdn to see, what the gc does (if daddy is not at home) try it, if your problems wont go away. Sorry, this shows like your transaction class seems to be a bad idea, bad truly i think it is.
+1