Thread-Safe Data Access Layer
Bryan Dickson
which explains my situation in a bit more detail.
I'm trying to design a thread-safe DataLayer. The reason
it needs to be thread safe is because I keep my
DataAdapter and DataCommand objects as member variables
to each Data Module class ( i.e. EmployeesModule, or
Customers Module ). I only have one DataAdapter per
Module.
So, I want exlusive access to a DataCommand's parameters
collection, and a DataAdapter's SelectCommand every time
an associated method is called. The last comment
suggested that I lock the Connection for each associated
DataCommand.
I was trying to reduce the overhead of creating
DataAdapters and DataCommands every time a ( say,
EmployeesModule.GetEmployeeData(...) ) method is called.
Am I wasting my time? Is there a better design out there?
I just want a simple, thread-safe Data Access Layer in
ADO.NET. Any ideas?
B
no potted meat@hotmail.com David Browne
news:007e01c31320$145daaf0$a301...@phx.gbl...
A thread must have exclusive access to a connection for the duration of the
atomic database operations it needs.
This lead to the following general design to be, I believe, the only
reasonable DAL design in .NET for resusing commands and data adapters.
An instance of the DAL class must wrapper a database connection. Which
leads directly to to conclusion that if you want to reuse commands or data
adapters or anything else, you must pool your DAL connection wrappers,
bypassing the built-in connection pools.
So in short the DAL class is NOT thread-save, instead it is pooled. The
only threading issues are in writing the pooling logic.
Here's a simple example (without any of the transaction handling). (the
pooling logic is untested).
NB: Since C# lacks static local variables in C# the commands, and
dataadapters would need to be scoped on the dbConnection class.
Class dbConnection
Implements IDisposable
Private conn As SqlConnection
Private pool As Stack
Private disposed As Boolean = False
Private Shared connectionPools As New Hashtable()
Private Sub New(ByVal ConnectionString As String, ByVal pool As Stack)
Me.pool = pool
conn = New SqlConnection(ConnectionString)
conn.Open()
End Sub
Public Shared Function GetConnection(ByVal ConnectionString As String) As
dbConnection
Dim pool As Stack
SyncLock connectionPools
pool = connectionPools(ConnectionString)
If pool Is Nothing Then
pool = New Stack()
connectionPools.Add(ConnectionString, pool)
End If
End SyncLock
Dim con As dbConnection
SyncLock pool
If pool.Count > 0 Then
con = DirectCast(pool.Pop, dbConnection)
Else
con = New dbConnection(ConnectionString, pool)
End If
End SyncLock
End Function
Public Sub Dispose() Implements IDisposable.Dispose
If Me.disposed Then Return
SyncLock pool
pool.Push(Me)
End SyncLock
End Sub
Public Sub Close()
Dispose()
End Sub
Public Function DoSomething(ByVal param1 As String, ByVal param2 As
Integer) As DataSet
Static cmd As SqlCommand
Static p1, p2 As SqlParameter
Static adapt As SqlDataAdapter
If cmd Is Nothing Then
cmd = New SqlCommand("sp_DoSomething")
cmd.CommandType = CommandType.StoredProcedure
cmd.connection = conn
p1 = New SqlParameter("@p1", SqlDbType.VarChar, 12)
p2 = New SqlParameter("@p2", SqlDbType.Int)
cmd.Parameters.Add(p1)
cmd.Parameters.Add(p2)
adapt = New SqlDataAdapter(cmd)
End If
Dim ds As New DataSet()
p1.Value = param1
p2.Value = param2
adapt.Fill(ds)
Return ds
End Function
End Class
To finish this out, add a transaction object to the dbConnection, and
BeginTransaction and RollbackTransaction method, along with the appropriate
enlistment code for the commands.
Better yet, implement IDBConnection.
David
Bryan Dickson
their connections and DataAdapters around in member
variables is a very bad idea.
I like your idea, David; however it seems to be the
(necessary) antithesis of what I was trying to do. I
guess the DataAdapters, DbCommands and DbConnections are
intended for dynamic creation during a DataLayer method
invocation, and are not intended to be stored as class
variables. Am I getting this right?
I'm trying to get an idea of what the .NET gurus are
doing with their Data Layer. Are we just creating all
the ADO objects on the fly every time a "DoSomething"
method is called ( and pooling the connections like
David)?
Please help me before I make a huge mistake. Thanks,
B
>.
>
Frans Bouma
news:eJWA9byE...@TK2MSFTNGP12.phx.gbl:
> dataadapters would need to be scoped on the dbConnection class.
private static int foo;
there, local static member in C#. Add a static property to control
that member or a static method et voila.
btw, pooling can be done using the build in connection pooling, you
don't have to write your own pooling. Connections from the process to the
database server are pooled, not connection objects. If thread a creates a
SqlConnection instance and thread b does the same there are 2 connections.
If thread b closes its connection and thread c creates a SqlConnection
instance most likely c will get the physical connection b 'closed'. If you
want to share 1 open connection WITH a transaction (!) among threads,
that's also possible, you just have to schedule the access to the
sqlconnection instance among these threads, thus via lock {} in C# or
other synchronisation method. When sharing an open sqlconnection instance
among threads, synchronized via f.e. lock, be aware of the fact that all
statements executed on the connection, and thus inside the transaction,
are part of 1 transaction and are all rolled back when the transaction is
rolled back.
FB
--
======= You can't sell what's free ====================================
Senior Software Engineer @ Solutions Design : http://www.sd.nl
Get my free, open source .NET software at : http://www.sd.nl/software
=========================================================================
Daniel Jin
>-----Original Message-----
>"David Browne" <davidbaxterbrowne no potted
me...@hotmail.com> wrote in
>news:eJWA9byE...@TK2MSFTNGP12.phx.gbl:
>> NB: Since C# lacks static local variables in C# the
commands, and
>> dataadapters would need to be scoped on the
dbConnection class.
>
> private static int foo;
>
> there, local static member in C#. Add a static
property to control
>that member or a static method et voila.
he's talking about the keyword Static in VB. C# static is
the same as Shared in VB, but C# doesn't have the VB
Static in within the scope of a specific method, which is
not the same as C# static. they are two different things.
>.
>
Frans Bouma
$2f01...@phx.gbl:
>>"David Browne" <davidbaxterbrowne no potted me...@hotmail.com> wrote in
>>news:eJWA9byE...@TK2MSFTNGP12.phx.gbl:
>>> NB: Since C# lacks static local variables in C# the commands, and
>>> dataadapters would need to be scoped on the dbConnection class.
>>
>> private static int foo;
>>
>> there, local static member in C#. Add a static property to
>> control that member or a static method et voila.
>
> he's talking about the keyword Static in VB. C# static is
> the same as Shared in VB, but C# doesn't have the VB
> Static in within the scope of a specific method, which is
> not the same as C# static. they are two different things.
ah, talking about a misunderstanding :D. I thought you were talking
about 'static' as in static in C, C# etc. No problem
Bryan Dickson
in C# did I?
I appreciate everyone's input, but I'm still not getting
an answer to this question: Should I be creating
DataAdapters, DbCommands on the fly ( every time an
associated method is called ), or storing them as member
variables( which are created once - aside from their
DbConnections )?
It sounds like, for thread safety's sake, I'll need to
create everything on the fly.
B
>.
>
no potted meat@hotmail.com David Browne
> So, I'm getting the idea that trying to keep Commands,
> their connections and DataAdapters around in member
> variables is a very bad idea.
>
> I like your idea, David; however it seems to be the
> (necessary) antithesis of what I was trying to do.
possible without serialization. But you can have one command for each
concurrent connection.
If you pool the dbConnections, you would only have a handfull of
dbConnection objects.
Each dbConnection would have a set of SQLCommands, SQLDataAdapters, etc
which are created when the dbConnection is created or (as in my example)
when they are first used.
That way you avoid the dynamic creation of these objects (except perhaps the
first time for each connection), and potentially improve the execution of
the commands by enabling you to use prepared commands instead of dynamic
SQL.
>I
> guess the DataAdapters, DbCommands and DbConnections are
> intended for dynamic creation during a DataLayer method
> invocation, and are not intended to be stored as class
> variables. Am I getting this right?
It's not that SQLCommands are designed for dynamic creation. If they were,
there wouldn't be an option to prepare them.
It's just that SQLCommands are tied to SQLConnections. So for the same
reasons you have to pool connections, you must pool the objects which are
tied to the connection.
The question here is really: "How do I use prepared commands with connection
pooling?"
The answer is: you must save the prepared commands with the connection in
the pool.
You're on the right track, it's just that it's kind of hard going.
> I'm trying to get an idea of what the .NET gurus are
> doing with their Data Layer. Are we just creating all
> the ADO objects on the fly every time a "DoSomething"
> method is called ( and pooling the connections like
> David)?
I get the impression that most people just live with the overhead of
constantly recreating commands and adapters for the sake of simplicity.
David
Bryan Dickson
answers I needed. Your help is very much appreciated.
With all due respect to David, if anyone else has any
ideas or comments related to Data Layer implementations
in ADO.NET, please do contribute! Thanks!
B
>.
>
Shawn B.
in production...
http://www.lookwrite.com/software/DataAccess (VB alert) (ignore the
SqlDataReader class, it's not a part of the project...)
all files (exception sqlDataReader) must be in the same project...
What the class does is reate a connection and command per object instance
but it open and closes the connection only upon demand. If you are
inheriting from the Debug class, it'll also write some things to the
Performance Monitor. The transaction support is imoprtant and it does some
management. The Sql classes are more modern than the OleDb (I used to make
each change to both similtaneously but as of late, I haven't synchronized
the two yet... so Sql is the one you're looking at... there's not need for
threading with these classes (unless you want to thread them).
Thanks,
Shawn
"Bryan Dickson" <nos...@thinairdata.com> wrote in message
news:007e01c31320$145daaf0$a301...@phx.gbl...
Shawn B.
I posted a reply in this thread somewhere else...
http://www.lookwrite.com/software/DataAccess (vb alert) has a DAL (3rd
generation). All files must be in the same project (ignore the
SqlDataReader class, please... it's not complete and not a part of the
project).
It creates a command and a connection per object instance and directly wraps
against ADO.NET. Each time you invoke a Execute* command it'll open the
connection and then close the connection inside the function (IE, on
demand). I have a setting in the constructor where you can make it "always"
connected as per the life of the object but connection pooling, I've tested
for hundreds of hours with millions of records and purposely threw in some
exceptions to see how it would behave, and keeping it in "OnDeman" (the
default) is the best option (if all the objects are "Always" and you have
100 users, you'll have 100 connections concurrent if they concurrently have
100 objects instanced -- I'm doing a web app --). With OnDeman, I've found
that 1,500 concurrent users plugging away at about 10 clicks per minute
(average) consume less than 30 connections in the pool.
Therefore it's harmless just to create a DAL object to access data each time
on demand (all you do is create the object and use it) as it will manage the
transactions, distributed transactions, and the connections for you...
C# alert...
using [Project].SqlClient
...
SqlDbBase data;
data = new SqlDbBase(MyConnectionString);
...
DataSet ds;
ds = data.ExecuteDataSet("SELECT SomeData FROM SuchAndSuchATable");
ds = data.ExecuteDataSet("sp_MyStoredProc", new SqlParam("@Field1",
SqlDbType.Int, Input, 4, MyFieldID))
data.Dispose();
or in a business layer... (assuming a class level data object is set)...
public sqlDataReader GetCustomerOrders(int customerID) {
return data.ExecuteReader("sp_MyStoredProc", CloseConnection, new
SqlParam("@CustID", Int, Input, 4, customerID));
}
Then use it like...
...
CustomerBase cust;
sqlDataReader dr;
try {
dr = cust.GetCustomerOrders(CustID);
while (dr.Read) {
// do something
}
finally {
dr.Close();
}
Hope this gets you started...
Thanks,
Shawn
"Bryan Dickson" <nos...@thinairdata.com> wrote in message
news:02e001c31348$73eef8c0$a301...@phx.gbl...