Static DAL and BL

[Sjaakie]

Hi,

I was wondering...
If I'd create a (web)application using static methods in both DataLayer
and BusinessLayer, could it happen that, in case of multiple concurrent
visitors/users, the applications throws a sqlexception, because multiple
DataReaders (from different methods) try to access the same (static)
SqlConnection?

TIA

 

[Guest]

If you declare the SqlConnection inside a method then this wont happen as it
will be a stack variable (the address will be on the stack while it is on the
heap) and therefore there will be one per thread.

Ciaran O'Donnell

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

It depends of how you code them.
Personally I always use a static DAL , of course ALL the objects ( command,
readers, connections) are created inside the method and nothing is shared
(Except the connection string)

from you message it seems that you are creating one connection , this is not
correct and will give you the error you are seeing.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

If I'd create a (web)application using static methods in both DataLayer
and BusinessLayer, could it happen that, in case of multiple concurrent
visitors/users, the applications throws a sqlexception, because multiple
DataReaders (from different methods) try to access the same (static)
SqlConnection?

Static methods can be OK, but you should use only local
variables no static class variables.

If you do then you should synchronize to avoid multiple
access. But that would kill performance.

The best solution is probably to rewrite to not be static.

To summarize:

best = rewrite to not be static
usable = rewrite to only use local variables
not good = use synchronized to prevent problems

Arne

 

[Sjaakie]

Ignacio Machin ( .NET/ C# MVP ) schreef:

Hi,

It depends of how you code them.
Personally I always use a static DAL , of course ALL the objects ( command,
readers, connections) are created inside the method and nothing is shared
(Except the connection string)

from you message it seems that you are creating one connection , this is not
correct and will give you the error you are seeing.

Thanks, you just prevented me from making a big mistake.
I was planning to write the class like this:

public static SqlConnection Connection { ... }
public static SqlDataReader ExecuteReader(..) { }
....

Now I understand I should abandon the static Connection and declare it
in the method itself.

Cheers

 

[Sjaakie]

Ciaran O''Donnell schreef:

If you declare the SqlConnection inside a method then this wont happen as it
will be a stack variable (the address will be on the stack while it is on the
heap) and therefore there will be one per thread.

Ciaran O'Donnell

Thanks!!

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

well, your code kind of is correct

you can have:
public static SqlConnection Connection() { return new SqlConnection(
.....); }

public static SqlDataReader ExecuteReader(..)
{
using ( SqlConnection con= Connection() ) {}
}

 

[Sjaakie]

Hi,

well, your code kind of is correct

you can have:
public static SqlConnection Connection() { return new SqlConnection(
....); }

public static SqlDataReader ExecuteReader(..)
{
using ( SqlConnection con= Connection() ) {}
}

Lol, well I intended to write something like:

private static SqlConnection _conn;
public static SqlConnection Connection() {
if (_conn == null) {
_conn = new SqlConnection(...);
}

... check for connectionstate, open it if closed
... etc etc

return _conn;
}

....so you most definitely kept me from making a mistake.
Cheers