ADO.NET DataReaders and the Middle Tier

[Guadala Harry]

I'm trying to design all of my data access logic into one centralized
assembly. I'm wondering how to implement DataReaders.
There's plenty of documentation on passing DataSets to the client from the
middle tier... but what about DataReaders? Do I have to bypass the
centralized data access assembly when I want to use DataReaders?

Thanks.

 

[bruce barker]

you should not return datareaders, as this opens the dal to connection
leaks.

-- bruce (sqlwork.com)

 

[Joe Fallon]

My DAL returns a generic datareader. This way I can use Oracle or SQL Server
and still have just one DAL.

There are many overloaded methods (3 of 13 are shown below) that "forward"
the call to a method that has more paramters and fills them in with default
values (or config file values). Eventually you get to a method that actually
executes the command. (There are also many "helper" methods that are not
shown. Like PrepareCommand.)

Public Overloads Shared Function ExecuteReader(ByVal commandText As String)
As IDataReader
Return ExecuteReader(mConnStr, CommandType.Text, commandText,
CType(Nothing, IDataParameter()))
End Function

Public Overloads Shared Function ExecuteReader(ByVal spName As String, ByVal
ParamArray parameterValues() As Object) As IDataReader
Return ExecuteReader(mConnStr, spName, CommandType.StoredProcedure,
parameterValues)
End Function

Public Overloads Shared Function ExecuteReader(ByVal commandType As
CommandType, ByVal commandText As String) As IDataReader
Return ExecuteReader(mConnStr, commandType, commandText,
CType(Nothing, IDataParameter()))
End Function
===========================
All calls eventually end up here:

Private Overloads Shared Function ExecuteReader(ByVal connection As
IDbConnection, ByVal transaction As IDbTransaction, ByVal commandType As
CommandType, ByVal commandText As String, ByVal commandParameters() As
IDataParameter, ByVal connectionOwnership As ConnectionOwnership) As
IDataReader
If (connection Is Nothing) Then Throw New
ArgumentNullException("Missing connection")
Dim cmd As IDbCommand = CreateCommand()
Dim dr As IDataReader
Dim mustCloseConnection As Boolean = False

Try
PrepareCommand(cmd, connection, transaction, commandType,
commandText, commandParameters, mustCloseConnection)
If connectionOwnership = connectionOwnership.External Then
dr = cmd.ExecuteReader()
Else
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
End If

'Detach the Parameters from the command object, so they can be used
again
Dim canClear As Boolean = True
Dim commandParameter As IDataParameter
For Each commandParameter In cmd.Parameters
If commandParameter.Direction <> ParameterDirection.Input Then
canClear = False
End If
Next

If (canClear) Then cmd.Parameters.Clear()

Return dr
Catch
If (mustCloseConnection) Then connection.Close()
Throw
End Try
End Function

 

[Peter Bromberg [C# MVP]]

Suggest you look at the latest version of Microsoft data access Application
Block (aka "SqlHelper")
Even if you choose not to use this free code from MS,
it should provide numerous insights into how to structure your own
customized DAL
-Peter

 

[Guadala Harry]

Thanks for the great explanation and sample code.
What is the physical implementation of your DAL - is it on a separate
machine, or is it in a class in the same assembly as other application code?
Sorry if it should be obvious to me - but I've never done anything with
Remoting. If your DAL is not on a separate machine, how would the calling
code change? I plan to read the fine manual on Remoting next week - just
hoping for now to get the short version if you can provide that.

G

 

[richlm]

From an architectural perspective, I'm with Bruce on this one.
You may end up killing scaleabilty of your solution if you pass DataReaders
to your client.
General advice is to avoid 1 database connection per client if at all
possible.

Below are a couple of good MSDN articles covering best practices.

Designing Data Tier Components and Passing Data Through Tiers:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp

..NET Data Access Architecture Guide:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp

 

[Joe Fallon]

The DAL is a separate class library: DAL.dll
The application makes reference to it.

The DAL.dll *always* resides on the machine that has access to the database.
So *it* is never involved in remoting.

Business objects can be remoted to the machine where the DAL lives.
(Or if they are the same machine then no remoting is required.)
The BOs use calls to the DAL rather than writing blocks of ADO.Net code
inside of them.

Hope that helps.

 

[Guadala Harry]

Yes - that helps.
You've saved me a bunch of time.
Thanks.

G