> Can only help me please - this might not be easy to explain but I
> will try.
>
> I have a class that updates the database for the program.
>
> I want to use a transaction but I am having issues.
>
> I have a function called TableExists (Below) that you will see checks
> to see if the table already exists before you try and add it again.
>
>
> public static bool TableExists(string TableName, SqlConnection conns)
> {
> string sql = "SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME =
> @TABLE_NAME AND TYPE = 'U' "; int result;
> SqlCommand command = new SqlCommand(sql, conns);
> command.Parameters.Add("@TABLE_NAME", SqlDbType.VarChar).Value =
> TableName; conns.Open();
> result = int.Parse(command.ExecuteScalar().ToString());
> conns.Close();
> return result != 0;
> }
>
> I have 2 connection Objects
>
> Conn and Conn2 - Conn is where the transaction has been created.
>
> Conn2 is used for checking for Tables etc as above.
>
> Command (SqlCommand) is connected to Conn;
>
> Example
>
> if (!DBLibrary.TableExists("BRANDS", Conn2))
> {
> Sql.Length = 0;
> Sql.Append("CREATE TABLE BRANDS (");
> Sql.Append("BRANDID CHAR(1) NOT NULL PRIMARY KEY ,");
> Sql.Append("BRANDNAME VARCHAR(50) ");
> Sql.Append(")");
> RunNonQuery(Sql.ToString());
> }
>
> public int RunNonQuery(string sql)
> {
> int Result = 0;
> try
> {
> Command.CommandType = CommandType.Text;
> Command.Transaction = MainTrans;
> Command.CommandText = sql;
> Command.Parameters.Clear();
> Result = Command.ExecuteNonQuery();
> }
> catch (Exception E)
> {
> ErrMsg = E.Message;
> throw E;

> }
> return Result;
> }
>
> My issue is after the first table has been added, any subsequent
> calls to TableExists hangs and times out due to transaction lock,
> however if I try and use Conn (which has the transaction) as the
> connection it fails as well but for other reasons.
>
> I am pulling my hair out here.
>
>
> Command is setup here
>
> Command = new SqlCommand();
> Command.Connection = Conn;
> Command.Connection.Open();
> MainTrans =
> Command.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
>
> I have tried other Iso levels with no success.
>
> Any help would be greatly appreciated.
>
> Daniel

"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message
news:xn0fbf6b54f5nm000@news.microsoft.com...
> Daniel Jeffrey wrote:
>
>> Can only help me please - this might not be easy to explain but I
>> will try.
>>
>> I have a class that updates the database for the program.
>>
>> I want to use a transaction but I am having issues.
>>
>> I have a function called TableExists (Below) that you will see checks
>> to see if the table already exists before you try and add it again.
>>
>>
>> public static bool TableExists(string TableName, SqlConnection conns)
>> {
>> string sql = "SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME =
>> @TABLE_NAME AND TYPE = 'U' "; int result;
>> SqlCommand command = new SqlCommand(sql, conns);
>> command.Parameters.Add("@TABLE_NAME", SqlDbType.VarChar).Value =
>> TableName; conns.Open();
>> result = int.Parse(command.ExecuteScalar().ToString());
>> conns.Close();
>> return result != 0;
>> }
>>
>> I have 2 connection Objects
>>
>> Conn and Conn2 - Conn is where the transaction has been created.
>>
>> Conn2 is used for checking for Tables etc as above.
>>
>> Command (SqlCommand) is connected to Conn;
>>
>> Example
>>
>> if (!DBLibrary.TableExists("BRANDS", Conn2))
>> {
>> Sql.Length = 0;
>> Sql.Append("CREATE TABLE BRANDS (");
>> Sql.Append("BRANDID CHAR(1) NOT NULL PRIMARY KEY ,");
>> Sql.Append("BRANDNAME VARCHAR(50) ");
>> Sql.Append(")");
>> RunNonQuery(Sql.ToString());
>> }
>>
>> public int RunNonQuery(string sql)
>> {
>> int Result = 0;
>> try
>> {
>> Command.CommandType = CommandType.Text;
>> Command.Transaction = MainTrans;
>> Command.CommandText = sql;
>> Command.Parameters.Clear();
>> Result = Command.ExecuteNonQuery();
>> }
>> catch (Exception E)
>> {
>> ErrMsg = E.Message;
>> throw E;
>
> don't do this, do:
> throw;
>
> because otherwise you're destroying the stacktrace.
>
>> }
>> return Result;
>> }
>>
>> My issue is after the first table has been added, any subsequent
>> calls to TableExists hangs and times out due to transaction lock,
>> however if I try and use Conn (which has the transaction) as the
>> connection it fails as well but for other reasons.
>>
>> I am pulling my hair out here.
>>
>>
>> Command is setup here
>>
>> Command = new SqlCommand();
>> Command.Connection = Conn;
>> Command.Connection.Open();
>> MainTrans =
>> Command.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
>>
>> I have tried other Iso levels with no success.
>>
>> Any help would be greatly appreciated.
>>
>> Daniel
>
> Welcome to the wonderful world of SqlServer! :). In SqlServer, readers
> block writers and vice versa. So if you write data to a table in a
> transaction, the rows affected are locked. If you then in another
> connection also want to write to these rows, the writer waits till the
> locks are lifted by the previous transaction.
>
> So that's the core reason for this deadlock: you have locks set by the
> transaction on conn1, which block conn2's actions.
>
> However you forgot to post the sequence of the code: where is conn1
> and conn2 used? It's also a bit messy: your TableExists method opens
> and closes the passed in connection. But what if there's a connection
> on that connection?
>
> FB
>
> --
> ------------------------------------------------------------------------
> Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
> LLBLGen Pro website: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------