multi threaded database access...

[Guest]

hey, I have 2 threads, th and th2, both of them run a method.

each of these 2 methods requires database access.

sometimes I get an error, that database requires an open connection, and
that the current connection state is "connecting".

is this because both threads are trying to use the same database???

they use the same public static data access class. would this be why? isnt
there some way I can avoid this?

thanks.

 

[Guest]

You cannot do this with a public static database access thread. You need to
have each thread open its own database connection, do the work, then
immediately close the connection and allow it to return to the ADO.NET
connection pool.
Peter

 

[Guest]

so the best solution would be to make 2 static database access classes? then
I can have each thread use their own public static class.

I tried making the methods in the class public instead of public static, and
making 2 instances of the class and I still get the error.

 

[Nicholas Paldino [.NET/C# MVP]]

I get the feeling you are using the same Connection instance between the
two threads. You don't want to do this. Rather, you want to create the
instance of the Connection when you need it, use it, and then close it.

 

[Mr. Arnold]

so the best solution would be to make 2 static database access classes?
then
I can have each thread use their own public static class.

I tried making the methods in the class public instead of public static,
and
making 2 instances of the class and I still get the error.

Making a static method to do what? You simply use the ADO.Net Using
Statement to open the database connection, do the database access, and let
the Using statement close the connection or you close the connection with a
try/catch/finally.

You do all of this with inline code and in your case for each thread that
needs that SQL access.

You don't need some static method to do this nor is it warranted, as being
shown in the examples of how to open a database connection, access the
database, and close the database when finished, each time the code is
executed.

You don't have to use the ADO.Net USING statement but it is nice.

http://www.sql-server-performance.com/jk_ado_transactions.asp

 

[Guest]

I fixed it. I had made a private static SqlConnection at the top of the
class, then in each method open and closing it like this:

class1
{
SqlConnection SqlCon = new SqlConnectio();

public static void DoDBStuff()
{
SqlCon = new SqlConnectio(ConString);

SqlCon.Open()
sql command stuff
SqlCon.Close()
}
}//end class

this was using the same SqlConnection instance every time.

I simply had to change my code in each method, to make a new instance for
each method. like this

class1
{
//no definitions here

public static void DoDBStuff()
{
SqlConnection SqlCon = new SqlConnectio(ConString);
SqlCon.Open();
sql command stuff
SqlCon.Close();
}
}//end class

thanks guys.

 

[Mr. Arnold]

I fixed it. I had made a private static SqlConnection at the top of the
class, then in each method open and closing it like this:

class1
{
SqlConnection SqlCon = new SqlConnectio();

public static void DoDBStuff()
{
SqlCon = new SqlConnectio(ConString);

SqlCon.Open()
sql command stuff
SqlCon.Close()
}
}//end class

this was using the same SqlConnection instance every time.

I simply had to change my code in each method, to make a new instance for
each method. like this

class1
{
//no definitions here

public static void DoDBStuff()
{
SqlConnection SqlCon = new SqlConnectio(ConString);
SqlCon.Open();
sql command stuff
SqlCon.Close();
}
}//end class

thanks guys.

You ever read about a try/catch block?

 

[Guest]

that wasnt my actual source code. it was a representation of my code. of
course I have try catch's. why you so rude about it anyways.