ADO.NET DataReaders and the Middle Tier

Discussion in 'ASP .Net' started by Guadala Harry, Feb 21, 2004.

  1. 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, Feb 21, 2004
    #1
    1. Advertising

  2. Guadala Harry

    bruce barker Guest

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

    -- bruce (sqlwork.com)

    "Guadala Harry" <> wrote in message
    news:e6Oe0OB#...
    > 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, Feb 21, 2004
    #2
    1. Advertising

  3. Guadala Harry

    Joe Fallon Guest

    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" <> wrote in message
    news:e6Oe0OB%...
    > 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, Feb 21, 2004
    #3
  4. 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" <> wrote in message
    news:e6Oe0OB%...
    > 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], Feb 21, 2004
    #4
  5. 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" <> wrote in message
    news:%23bcsaRC%...
    > 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" <> wrote in message
    > news:e6Oe0OB%...
    > > 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, Feb 21, 2004
    #5
  6. Guadala Harry

    richlm Guest

    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


    "Guadala Harry" <> wrote in message
    news:e6Oe0OB%...
    > 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, Feb 21, 2004
    #6
  7. Guadala Harry

    Joe Fallon Guest

    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" <> wrote in message
    news:O8$eq1E%...
    > 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" <> wrote in message
    > news:%23bcsaRC%...
    > > 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" <> wrote in message
    > > news:e6Oe0OB%...
    > > > 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, Feb 21, 2004
    #7
  8. Yes - that helps.
    You've saved me a bunch of time.
    Thanks.

    G

    "Joe Fallon" <> wrote in message
    news:uHXwLBL%...
    > 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" <> wrote in message
    > news:O8$eq1E%...
    > > 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" <> wrote in message
    > > news:%23bcsaRC%...
    > > > 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" <> wrote in message
    > > > news:e6Oe0OB%...
    > > > > 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, Feb 21, 2004
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Craig Buchanan

    Middle-tier equivalent to server.mappath?

    Craig Buchanan, Jan 26, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    1,614
    martin
    Jan 27, 2004
  2. Ryan Ternier
    Replies:
    5
    Views:
    1,277
    Kevin Spencer
    Nov 10, 2004
  3. =?Utf-8?B?SWFuIFdpbGxpYW1zb24=?=

    Retrieving state information from a middle tier

    =?Utf-8?B?SWFuIFdpbGxpYW1zb24=?=, Feb 25, 2005, in forum: ASP .Net
    Replies:
    6
    Views:
    1,281
    =?Utf-8?B?aW5ldG11Zw==?=
    Feb 25, 2005
  4. Joe
    Replies:
    71
    Views:
    1,467
    JEDIDIAH
    Jun 24, 2004
  5. John
    Replies:
    7
    Views:
    1,050
    Alex Hunsley
    Apr 7, 2006
Loading...

Share This Page