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.

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

-- bruce (sqlwork.com)

"Guadala Harry" <(E-Mail Removed)> wrote in message
news:e6Oe0OB#(E-Mail Removed)...
> 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.
>
>

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




"Guadala Harry" <(E-Mail Removed)> wrote in message
news:e6Oe0OB%(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
news:e6Oe0OB%(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
news:%23bcsaRC%(E-Mail Removed)...
> 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
> --
> Joe Fallon
>
>
>
>
> "Guadala Harry" <(E-Mail Removed)> wrote in message
> news:e6Oe0OB%(E-Mail Removed)...
> > 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.
> >
> >
>
>

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" <(E-Mail Removed)> wrote in message
news:e6Oe0OB%(E-Mail Removed)...
> 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.
>
>

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" <(E-Mail Removed)> wrote in message
news:O8$eq1E%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23bcsaRC%(E-Mail Removed)...
> > 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
> > --
> > Joe Fallon
> >
> >
> >
> >
> > "Guadala Harry" <(E-Mail Removed)> wrote in message
> > news:e6Oe0OB%(E-Mail Removed)...
> > > 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.
> > >
> > >
> >
> >
>
>

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

G

"Joe Fallon" <(E-Mail Removed)> wrote in message
news:uHXwLBL%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:O8$eq1E%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:%23bcsaRC%(E-Mail Removed)...
> > > 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
> > > --
> > > Joe Fallon
> > >
> > >
> > >
> > >
> > > "Guadala Harry" <(E-Mail Removed)> wrote in message
> > > news:e6Oe0OB%(E-Mail Removed)...
> > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>