«

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("C onnectionString"))
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

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: om...
> 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("C onnectionString"))
> 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

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

-- bruce (sqlwork.com)


"Victor" <> wrote in message
news: om...
| 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("C onnectionString"))
| 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

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: om...
> 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("C onnectionString"))
> 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

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" <> wrote in message
news: om...
> 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("C onnectionString"))
> 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("C onnectionString"))
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,

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" <> 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,

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" <> 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