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)

"Guadala Harry" <gh****@aol.com > wrote in message
news:e6******** ******@TK2MSFTN GP09.phx.gbl...

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.

[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(B yVal commandText As String)
As IDataReader
Return ExecuteReader(m ConnStr, CommandType.Tex t, commandText,
CType(Nothing, IDataParameter( )))
End Function

Public Overloads Shared Function ExecuteReader(B yVal spName As String, ByVal
ParamArray parameterValues () As Object) As IDataReader
Return ExecuteReader(m ConnStr, spName, CommandType.Sto redProcedure,
parameterValues )
End Function

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

Private Overloads Shared Function ExecuteReader(B yVal connection As
IDbConnection, ByVal transaction As IDbTransaction, ByVal commandType As
CommandType, ByVal commandText As String, ByVal commandParamete rs() As
IDataParameter, ByVal connectionOwner ship As ConnectionOwner ship) As
IDataReader
If (connection Is Nothing) Then Throw New
ArgumentNullExc eption("Missing connection")
Dim cmd As IDbCommand = CreateCommand()
Dim dr As IDataReader
Dim mustCloseConnec tion As Boolean = False

Try
PrepareCommand( cmd, connection, transaction, commandType,
commandText, commandParamete rs, mustCloseConnec tion)
If connectionOwner ship = connectionOwner ship.External Then
dr = cmd.ExecuteRead er()
Else
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion)
End If

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

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

Return dr
Catch
If (mustCloseConne ction) Then connection.Clos e()
Throw
End Try
End Function
--
Joe Fallon


"Guadala Harry" <gh****@aol.com > wrote in message
news:e6******** ********@TK2MSF TNGP09.phx.gbl. ..

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.

[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" <gh****@aol.com > wrote in message
news:e6******** ********@TK2MSF TNGP09.phx.gbl. ..

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.

[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

"Joe Fallon" <jf******@nospa mtwcny.rr.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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(B yVal commandText As String) As IDataReader
Return ExecuteReader(m ConnStr, CommandType.Tex t, commandText,
CType(Nothing, IDataParameter( )))
End Function

Public Overloads Shared Function ExecuteReader(B yVal spName As String, ByVal ParamArray parameterValues () As Object) As IDataReader
Return ExecuteReader(m ConnStr, spName, CommandType.Sto redProcedure,
parameterValues )
End Function

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

Private Overloads Shared Function ExecuteReader(B yVal connection As
IDbConnection, ByVal transaction As IDbTransaction, ByVal commandType As
CommandType, ByVal commandText As String, ByVal commandParamete rs() As
IDataParameter, ByVal connectionOwner ship As ConnectionOwner ship) As
IDataReader
If (connection Is Nothing) Then Throw New
ArgumentNullExc eption("Missing connection")
Dim cmd As IDbCommand = CreateCommand()
Dim dr As IDataReader
Dim mustCloseConnec tion As Boolean = False

Try
PrepareCommand( cmd, connection, transaction, commandType,
commandText, commandParamete rs, mustCloseConnec tion)
If connectionOwner ship = connectionOwner ship.External Then
dr = cmd.ExecuteRead er()
Else
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion)
End If

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

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

Return dr
Catch
If (mustCloseConne ction) Then connection.Clos e()
Throw
End Try
End Function
--
Joe Fallon


"Guadala Harry" <gh****@aol.com > wrote in message
news:e6******** ********@TK2MSF TNGP09.phx.gbl. ..

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.

[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/de...tml/BOAGag.asp

..NET Data Access Architecture Guide:
http://msdn.microsoft.com/library/de.../html/daag.asp
"Guadala Harry" <gh****@aol.com > wrote in message
news:e6******** ********@TK2MSF TNGP09.phx.gbl. ..

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.

[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.
--
Joe Fallon

"Guadala Harry" <gh****@aol.com > wrote in message
news:O8******** ********@tk2msf tngp13.phx.gbl. ..

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

"Joe Fallon" <jf******@nospa mtwcny.rr.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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(B yVal commandText As

String)

As IDataReader
Return ExecuteReader(m ConnStr, CommandType.Tex t, commandText,
CType(Nothing, IDataParameter( )))
End Function

Public Overloads Shared Function ExecuteReader(B yVal spName As String,

ByVal

ParamArray parameterValues () As Object) As IDataReader
Return ExecuteReader(m ConnStr, spName, CommandType.Sto redProcedure, parameterValues )
End Function

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

Private Overloads Shared Function ExecuteReader(B yVal connection As
IDbConnection, ByVal transaction As IDbTransaction, ByVal commandType As
CommandType, ByVal commandText As String, ByVal commandParamete rs() As
IDataParameter, ByVal connectionOwner ship As ConnectionOwner ship) As
IDataReader
If (connection Is Nothing) Then Throw New
ArgumentNullExc eption("Missing connection")
Dim cmd As IDbCommand = CreateCommand()
Dim dr As IDataReader
Dim mustCloseConnec tion As Boolean = False

Try
PrepareCommand( cmd, connection, transaction, commandType,
commandText, commandParamete rs, mustCloseConnec tion)
If connectionOwner ship = connectionOwner ship.External Then
dr = cmd.ExecuteRead er()
Else
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion)
End If

'Detach the Parameters from the command object, so they can be

used

again
Dim canClear As Boolean = True
Dim commandParamete r As IDataParameter
For Each commandParamete r In cmd.Parameters
If commandParamete r.Direction <> ParameterDirect ion.Input Then
canClear = False
End If
Next

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

Return dr
Catch
If (mustCloseConne ction) Then connection.Clos e()
Throw
End Try
End Function
--
Joe Fallon


"Guadala Harry" <gh****@aol.com > wrote in message
news:e6******** ********@TK2MSF TNGP09.phx.gbl. ..

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.

[Guadala Harry]

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

G

"Joe Fallon" <jf******@nospa mtwcny.rr.com> wrote in message
news:uH******** *******@TK2MSFT NGP09.phx.gbl.. .

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.
--
Joe Fallon

"Guadala Harry" <gh****@aol.com > wrote in message
news:O8******** ********@tk2msf tngp13.phx.gbl. ..

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

"Joe Fallon" <jf******@nospa mtwcny.rr.com> wrote in message
news:%2******** **********@TK2M SFTNGP12.phx.gb l...

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(B yVal commandText As

String)

As IDataReader
Return ExecuteReader(m ConnStr, CommandType.Tex t, commandText,
CType(Nothing, IDataParameter( )))
End Function

Public Overloads Shared Function ExecuteReader(B yVal spName As String,

ByVal

ParamArray parameterValues () As Object) As IDataReader
Return ExecuteReader(m ConnStr, spName, CommandType.Sto redProcedure, parameterValues )
End Function

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

Private Overloads Shared Function ExecuteReader(B yVal connection As
IDbConnection, ByVal transaction As IDbTransaction, ByVal commandType As CommandType, ByVal commandText As String, ByVal commandParamete rs() As
IDataParameter, ByVal connectionOwner ship As ConnectionOwner ship) As
IDataReader
If (connection Is Nothing) Then Throw New
ArgumentNullExc eption("Missing connection")
Dim cmd As IDbCommand = CreateCommand()
Dim dr As IDataReader
Dim mustCloseConnec tion As Boolean = False

Try
PrepareCommand( cmd, connection, transaction, commandType,
commandText, commandParamete rs, mustCloseConnec tion)
If connectionOwner ship = connectionOwner ship.External Then
dr = cmd.ExecuteRead er()
Else
dr = cmd.ExecuteRead er(CommandBehav ior.CloseConnec tion)
End If

'Detach the Parameters from the command object, so they can be

used

again
Dim canClear As Boolean = True
Dim commandParamete r As IDataParameter
For Each commandParamete r In cmd.Parameters
If commandParamete r.Direction <> ParameterDirect ion.Input Then canClear = False
End If
Next

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

Return dr
Catch
If (mustCloseConne ction) Then connection.Clos e()
Throw
End Try
End Function
--
Joe Fallon


"Guadala Harry" <gh****@aol.com > wrote in message
news:e6******** ********@TK2MSF TNGP09.phx.gbl. ..
> 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.
>
>