Reuse the SqlDataAdapter can it couse troubles?

Discussion in 'ASP .Net' started by Victor, Nov 17, 2004.

  1. Victor

    Victor Guest

    Hello there,

    I have a module in VB.NET (ASP.NET) and on top I declare the
    SqlConnection and the SqlDataAdapter.

    Like:

    Module AccessDB
    Dim sqlCmd As New SqlCommand
    Dim conAanvraag As New
    SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Dim daAanvraag As New SqlDataAdapter

    In my Module there are some functions and all those functions use the
    sqlDataAdapter. Can i use it in this way OR do i have to include a new
    sqldataadapter in every function? The method works but i dont know if
    it can cause problems some how.

    Thnx in advance,
    Victor
     
    Victor, Nov 17, 2004
    #1
    1. Advertising

  2. Victor

    Marina Guest

    That shouldn't be a problem as long as multiple threads aren't making calls
    to this module at the same time.

    "Victor" <> wrote in message
    news:...
    > Hello there,
    >
    > I have a module in VB.NET (ASP.NET) and on top I declare the
    > SqlConnection and the SqlDataAdapter.
    >
    > Like:
    >
    > Module AccessDB
    > Dim sqlCmd As New SqlCommand
    > Dim conAanvraag As New
    > SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    > Dim daAanvraag As New SqlDataAdapter
    >
    > In my Module there are some functions and all those functions use the
    > sqlDataAdapter. Can i use it in this way OR do i have to include a new
    > sqldataadapter in every function? The method works but i dont know if
    > it can cause problems some how.
    >
    > Thnx in advance,
    > Victor
     
    Marina, Nov 17, 2004
    #2
    1. Advertising

  3. Victor

    bruce barker Guest

    this will not support two active web users at once. every function should
    have its own.

    -- bruce (sqlwork.com)


    "Victor" <> wrote in message
    news:...
    | Hello there,
    |
    | I have a module in VB.NET (ASP.NET) and on top I declare the
    | SqlConnection and the SqlDataAdapter.
    |
    | Like:
    |
    | Module AccessDB
    | Dim sqlCmd As New SqlCommand
    | Dim conAanvraag As New
    | SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    | Dim daAanvraag As New SqlDataAdapter
    |
    | In my Module there are some functions and all those functions use the
    | sqlDataAdapter. Can i use it in this way OR do i have to include a new
    | sqldataadapter in every function? The method works but i dont know if
    | it can cause problems some how.
    |
    | Thnx in advance,
    | Victor
     
    bruce barker, Nov 17, 2004
    #3
  4. Hi Victor,

    I do this sort of thing in my business objects and it works well. But as
    Marina points out you don't want to do this globally but tied to an
    operation that's scoped to the current thread. For example, in my case of
    the business object reusing the DA I know that a single instance is not
    accessed from more than a single thread and so this is safe, but doing the
    same thing by tying the data adapter to an Application object or a static
    would not be (in the case of ASP.NET anything that spans more than a single
    request basically since every request can potentially run on a different
    thread).

    +++ Rick ---

    --

    Rick Strahl
    West Wind Technologies
    http://www.west-wind.com/
    http://www.west-wind.com/weblog/
    http://www.west-wind.com/wwThreads/
    ----------------------------------
    Making waves on the Web


    "Victor" <> wrote in message
    news:...
    > Hello there,
    >
    > I have a module in VB.NET (ASP.NET) and on top I declare the
    > SqlConnection and the SqlDataAdapter.
    >
    > Like:
    >
    > Module AccessDB
    > Dim sqlCmd As New SqlCommand
    > Dim conAanvraag As New
    > SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    > Dim daAanvraag As New SqlDataAdapter
    >
    > In my Module there are some functions and all those functions use the
    > sqlDataAdapter. Can i use it in this way OR do i have to include a new
    > sqldataadapter in every function? The method works but i dont know if
    > it can cause problems some how.
    >
    > Thnx in advance,
    > Victor
     
    Rick Strahl [MVP], Nov 17, 2004
    #4
  5. So if i understand correctly it is safe if only one thread at i time is
    using it?

    I have 50 users on my web application and they all do calls to the
    function in the module. Every functions calls the function ExcuteQuery
    (here the data-apater is used) that fills the a dataset. The dataset is
    returned to the requested function and passed to the Form.

    Example:

    Public Function GetBedrijf(ByVal bedrijfrelid As Int32) As DataSet

    sqlCmd.CommandText = "SELECT DISTINCT TRelatie.RelatieId,
    TRelatie.Naam" & _
    " FROM TRelatie INNER JOIN" & _
    " TRelatieRelatieType ON TRelatie.RelatieId =
    TRelatieRelatieType.RelatieId INNER JOIN " & _
    " TRelatieType ON
    TRelatieRelatieType.RelatieTypeId = TRelatieType.RelatieTypeId " & _
    " WHERE TRelatieType.RelatieTypeId = 4 and
    bedrijfrelatieid = " & bedrijfrelid & " AND (TRelatieRelatieType.DtVan <
    GETDATE()) AND (TRelatieRelatieType.DtTot > GETDATE() OR
    TRelatieRelatieType.DtTot IS NULL)" & _
    " Order by TRelatie.Naam"
    ExecuteSelect("Bedrijf")
    Return dsTemp
    End Function

    Private Sub ExecuteSelect(ByVal tbValue As String)

    daAanvraag.SelectCommand = sqlCmd
    daAanvraag.SelectCommand.Connection = conAanvraag
    If Not daAanvraag.SelectCommand.Connection.State =
    ConnectionState.Open Then
    daAanvraag.SelectCommand.Connection.Open()
    End If

    daAanvraag.Fill(dsTemp, tbValue)
    daAanvraag.SelectCommand.Connection.Close()
    End Sub

    Do i have to change the code totally? I did not have complains that it
    didnt work correct but i want to make sure to programm it in the correct
    manner. So can you please give some examples or a link to examples?

    Thnx in advance,
    Victor



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Victor van Hillo, Nov 17, 2004
    #5
  6. "Victor" <> wrote in message
    news:...
    > Hello there,
    >
    > I have a module in VB.NET (ASP.NET) and on top I declare the
    > SqlConnection and the SqlDataAdapter.
    >
    > Like:
    >
    > Module AccessDB
    > Dim sqlCmd As New SqlCommand
    > Dim conAanvraag As New
    > SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    > Dim daAanvraag As New SqlDataAdapter
    >
    > In my Module there are some functions and all those functions use the
    > sqlDataAdapter. Can i use it in this way OR do i have to include a new
    > sqldataadapter in every function? The method works but i dont know if
    > it can cause problems some how.


    No. Don't use modules. Ever.

    All of the variables you declared in the module are global to every request
    that touches the module. If there are two requests happening at a time, they
    will both be accessing the exact same "sqlCmd" instance, not one per
    request.

    My advice to VB.NET developers is to forget that Modules ever existed. To do
    the above without modules, you'd do something like this:

    Public Class AccessDB
    Private sqlCmd As SqlCommand
    Private conAanvraag As SqlConnection
    Private daAanvraag As SqlDataAdapter
    ...
    Public Sub New()
    sqlCmd = New SqlCommand()
    conAanvraag = New
    SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    daAanvraag = New SqlDataAdapter()
    End Sub

    Public Function Fill(query As String) As DataSet
    sqlCmd.CommandText = query
    sqlCmd.Connection = conAanvraag
    conAanvraag.Open()

    Dim ds As New DataSet()
    daAanvraag.Fill(ds)

    Return ds
    End Sub
    End Class


    Your code would have to use this like this:

    Private Sub Page_Load(sender As Object, e As EventArgs)
    Dim adb As New AccessDB
    Dim myDS As DataSet = adb.Fill(query)
    End Sub

    You would clearly be getting one set of "Private" data per instance of
    AccessDB. Since AccessDB is instantiated inside of Page_Load, you would
    clearly be getting one per request.

    John Saunders

    P.S. The one time I feel justified in using Modules is to create a
    collection of utility methods which share no data in common, but only a
    functional area. For instance, a MathFunctions module makes sense as a place
    to gather a number of mathematical functions which may be used by a
    particular application. But such a module would contain only Sub and
    Function members, and no data.
     
    John Saunders, Nov 18, 2004
    #6
  7. John,

    Thanks for your clear example! In the future I will only use classes
    instead of Modules. But for the current application i will stick with
    the module else i have to rewrite the hole module. I know greated in
    every function in the class a new: Dataset, DataAdapter and
    SqlConnection.

    Victor


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Victor van Hillo, Nov 18, 2004
    #7
  8. "Victor van Hillo" <> wrote in message
    news:...
    > So if i understand correctly it is safe if only one thread at i time is
    > using it?
    >
    > I have 50 users on my web application and they all do calls to the
    > function in the module. Every functions calls the function ExcuteQuery
    > (here the data-apater is used) that fills the a dataset. The dataset is
    > returned to the requested function and passed to the Form.
    >
    > Example:
    >
    > Public Function GetBedrijf(ByVal bedrijfrelid As Int32) As DataSet
    >
    > sqlCmd.CommandText = "SELECT DISTINCT TRelatie.RelatieId,
    > TRelatie.Naam" & _
    > " FROM TRelatie INNER JOIN" & _
    > " TRelatieRelatieType ON TRelatie.RelatieId =
    > TRelatieRelatieType.RelatieId INNER JOIN " & _
    > " TRelatieType ON
    > TRelatieRelatieType.RelatieTypeId = TRelatieType.RelatieTypeId " & _
    > " WHERE TRelatieType.RelatieTypeId = 4 and
    > bedrijfrelatieid = " & bedrijfrelid & " AND (TRelatieRelatieType.DtVan <
    > GETDATE()) AND (TRelatieRelatieType.DtTot > GETDATE() OR
    > TRelatieRelatieType.DtTot IS NULL)" & _
    > " Order by TRelatie.Naam"
    > ExecuteSelect("Bedrijf")
    > Return dsTemp
    > End Function
    >
    > Private Sub ExecuteSelect(ByVal tbValue As String)
    >
    > daAanvraag.SelectCommand = sqlCmd
    > daAanvraag.SelectCommand.Connection = conAanvraag
    > If Not daAanvraag.SelectCommand.Connection.State =
    > ConnectionState.Open Then
    > daAanvraag.SelectCommand.Connection.Open()
    > End If
    >
    > daAanvraag.Fill(dsTemp, tbValue)
    > daAanvraag.SelectCommand.Connection.Close()
    > End Sub
    >
    > Do i have to change the code totally? I did not have complains that it
    > didnt work correct but i want to make sure to programm it in the correct
    > manner. So can you please give some examples or a link to examples?


    Your code is wrong. You have multiple threads accessing global data with no
    synchronization.

    One does not receive user complaints about problems like this. Instead, the
    program fails in the absolutely worst way at the worst time (Murphy's Law).

    You do not need to rewrite the entire application. Instead, you need to
    rewrite your module as a class. Each page which currently uses the module
    should create an instance of the class and then call the ExecuteSelect
    method.

    Needless to say, the class should not use any global data.

    My main reason for recommending against modules is that most people using
    modules learned them in VB6 or before. In using modules, they are likely to
    continue practices which made sense in VB6 but not in VB.NET. This is the
    same reason that I replaced:

    Dim x as New Something

    with:

    Private x As Something
    ....
    Public Sub New()
    x = New Something()
    End Sub

    If I recall correctly, "New" in a declaration in VB6 isn't exactly the same
    as "New" in VB.NET. By not using it, the habit is broken.

    John Saunders
     
    John Saunders, Nov 19, 2004
    #8
  9. John,

    Thanks again for your anwser you are a great help! I will for sure will
    forget my VB6 years and move on to VB.NET.

    Victor


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Victor van Hillo, Nov 19, 2004
    #9
  10. "Victor van Hillo" <> wrote in message
    news:...
    > John,
    >
    > Thanks again for your anwser you are a great help! I will for sure will
    > forget my VB6 years and move on to VB.NET.


    Sorry, Victor,

    I was being harsh because I was being brief.

    The longer story is - don't forget your VB6 years. Just learn to understand
    the differences between VB6 and VB.NET.

    I'm afraid that Microsoft hasn't done the worlds best job of helping VB6
    developers (nor ASP 3.0 developers) into the VB.NET world. For instance,
    you didn't express any reservations about using Modules. This suggests that
    nobody from Microsoft communicated to you that there are some aspects of
    Modules which are not appropriate to the "modern" world. Similar problems
    exists with respect to global variables.

    Since I'm not in a position to write "VB.NET for VB6 Experts" or "ASP.NET
    for ASP 3.0 Experts", I'm left with making harsh, abrupt statements in the
    hopes of scaring people like yourself away from the evil Module. ;-)

    John Saunders
     
    John Saunders, Nov 19, 2004
    #10
  11. Hi there John,

    I dont think your're being harsh if find you're comments usefull! I will
    no think twise before using Modules... I think i will NEVER use them
    again (in VB.NET :))

    There are some book about VB6 to VB.NET maybe isshould buy such a book
    and read into it ;-)

    Thanks again,

    Victor



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
     
    Victor van Hillo, Nov 21, 2004
    #11
    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. tshad
    Replies:
    5
    Views:
    553
    Steve C. Orr [MVP, MCSD]
    May 17, 2005
  2. Matt Jensen
    Replies:
    7
    Views:
    2,521
    Matt Jensen
    Jul 1, 2005
  3. Hylander

    To reuse or not to reuse....

    Hylander, Feb 26, 2004, in forum: Java
    Replies:
    0
    Views:
    434
    Hylander
    Feb 26, 2004
  4. code reuse and design reuse

    , Feb 7, 2006, in forum: C Programming
    Replies:
    16
    Views:
    1,051
    Malcolm
    Feb 12, 2006
  5. jacob navia

    To reuse or not to reuse

    jacob navia, Nov 5, 2006, in forum: C Programming
    Replies:
    19
    Views:
    556
    Dave Thompson
    Dec 18, 2006
Loading...

Share This Page