|
 |
ado_dotnet thread: Open SQL connections problem
Message #1 by "Bob Herrmann" <bob@m...> on Thu, 27 Feb 2003 13:30:26 -0500
|
|
Hi all,
The SQL database admin at our company says thet I have a bunch of open connections on our SQL server. I run the following code and
then following this code is my class file code (Menudb.vb) that actually returns a dataset. Can anyone see anything that I am doing
wrong? I close my connection at the end of each function.
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
BuildMenuStructure()
End If
End Sub
Private Sub BuildMenuStructure()
Dim newItem As CYBERAKT.WebControls.Navigation.MenuItem
Dim newChildMenu1Group As CYBERAKT.WebControls.Navigation.MenuGroup
Menu1.ImagesBaseURL = "Images/"
Menu1.DefaultGroupCssClass = "MenuGroup"
Menu1.DefaultItemCssClass = "MenuItem"
Menu1.DefaultItemCssClassOver = "MenuItemOver"
Menu1.DefaultItemSpacing = 1
' Create instance of the MenuDB class
Dim myMenu As New MZNetSite.MenuDB()
Dim parents As DataSet = myMenu.GetMainMenuItems()
Dim child1s As DataSet
Dim parent As DataRow
Dim child1 As DataRow
For Each parent In parents.Tables(0).Rows
newItem = Menu1.TopGroup.Items.Add()
newItem.Label = parent("MainMenuDesc").ToString()
If parent("MainMenuURL").ToString() <> "" Then
newItem.URL = parent("MainMenuURL").ToString()
End If
child1s = myMenu.GetChildMenu1Items(parent("MainMenuID").ToString())
newChildMenu1Group = newItem.AddSubGroup()
For Each child1 In child1s.Tables(0).Rows
newItem = newChildMenu1Group.Items.Add()
newItem.Label = child1("ChildMenu1Desc").ToString()
newItem.URL = child1("ChildMenu1URL").ToString()
If child1("ChildMenu1Arrow") = True Then
newItem.RightIcon = "arrow_black.gif"
End If
Next 'Child 1
Next 'Parent
End Sub
End Class
Menudb.vb code:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class MenuDB
' This function will populate the top-level menu from table tabMainMenu
Public Function GetMainMenuItems() As DataSet
Dim oCon As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
Dim oCom As SqlCommand = New SqlCommand("GetMainMenuItems", oCon)
oCom.CommandType = CommandType.StoredProcedure
oCon.Open()
Dim da As New SqlDataAdapter(oCom)
Dim ds As New DataSet()
da.Fill(ds, "tabMainMenu")
Return ds
oCon.Close()
oCom = Nothing
End Function
' This function will populate the first child-level menu from table tabChildMenu1
Public Function GetChildMenu1Items(ByVal MainMenuID As Integer) As DataSet
Dim oCon As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
Dim oCom As SqlCommand = New SqlCommand("GetChildMenu1Items", oCon)
oCom.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterMainMenuID As New SqlParameter("@MainMenuID", SqlDbType.Int, 4)
parameterMainMenuID.Value = MainMenuID
oCom.Parameters.Add(parameterMainMenuID)
oCon.Open()
Dim da As New SqlDataAdapter(oCom)
Dim ds As New DataSet()
da.Fill(ds, "tabChildMenu1")
Return ds
oCon.Close()
oCom = Nothing
End Function
End Class
Message #2 by "Kevin Ayers" <kevin@f...> on Thu, 27 Feb 2003 13:42:54 -0500
|
|
you declare a new connection each time you get data. Try creating a
program-wide connection variable, open it when you first start, and just use
that whenever you use access the data. I did the same thing as you, but
caught it when I was tracing my database activity.
Kevin
----- Original Message -----
From: "Bob Herrmann" <bob@m...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, February 27, 2003 1:30 PM
Subject: [ado_dotnet] Open SQL connections problem
> Hi all,
>
> The SQL database admin at our company says thet I have a bunch of open
connections on our SQL server. I run the following code and then following
this code is my class file code (Menudb.vb) that actually returns a dataset.
Can anyone see anything that I am doing wrong? I close my connection at the
end of each function.
>
> Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
>
> If Not Page.IsPostBack Then
>
> BuildMenuStructure()
>
> End If
>
> End Sub
>
> Private Sub BuildMenuStructure()
>
> Dim newItem As CYBERAKT.WebControls.Navigation.MenuItem
>
> Dim newChildMenu1Group As CYBERAKT.WebControls.Navigation.MenuGroup
>
>
>
> Menu1.ImagesBaseURL = "Images/"
>
> Menu1.DefaultGroupCssClass = "MenuGroup"
>
> Menu1.DefaultItemCssClass = "MenuItem"
>
> Menu1.DefaultItemCssClassOver = "MenuItemOver"
>
> Menu1.DefaultItemSpacing = 1
>
> ' Create instance of the MenuDB class
>
> Dim myMenu As New MZNetSite.MenuDB()
>
> Dim parents As DataSet = myMenu.GetMainMenuItems()
>
> Dim child1s As DataSet
>
> Dim parent As DataRow
>
> Dim child1 As DataRow
>
> For Each parent In parents.Tables(0).Rows
>
> newItem = Menu1.TopGroup.Items.Add()
>
> newItem.Label = parent("MainMenuDesc").ToString()
>
> If parent("MainMenuURL").ToString() <> "" Then
>
> newItem.URL = parent("MainMenuURL").ToString()
>
> End If
>
> child1s = myMenu.GetChildMenu1Items(parent("MainMenuID").ToString())
>
> newChildMenu1Group = newItem.AddSubGroup()
>
> For Each child1 In child1s.Tables(0).Rows
>
> newItem = newChildMenu1Group.Items.Add()
>
> newItem.Label = child1("ChildMenu1Desc").ToString()
>
> newItem.URL = child1("ChildMenu1URL").ToString()
>
> If child1("ChildMenu1Arrow") = True Then
>
> newItem.RightIcon = "arrow_black.gif"
>
> End If
>
> Next 'Child 1
>
> Next 'Parent
>
> End Sub
>
> End Class
>
>
>
> Menudb.vb code:
>
> Imports System
>
> Imports System.Data
>
> Imports System.Data.SqlClient
>
> Public Class MenuDB
>
> ' This function will populate the top-level menu from table tabMainMenu
>
> Public Function GetMainMenuItems() As DataSet
>
> Dim oCon As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
>
> Dim oCom As SqlCommand = New SqlCommand("GetMainMenuItems", oCon)
>
> oCom.CommandType = CommandType.StoredProcedure
>
> oCon.Open()
>
> Dim da As New SqlDataAdapter(oCom)
>
> Dim ds As New DataSet()
>
> da.Fill(ds, "tabMainMenu")
>
> Return ds
>
> oCon.Close()
>
> oCom = Nothing
>
> End Function
>
> ' This function will populate the first child-level menu from table
tabChildMenu1
>
> Public Function GetChildMenu1Items(ByVal MainMenuID As Integer) As DataSet
>
> Dim oCon As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
>
> Dim oCom As SqlCommand = New SqlCommand("GetChildMenu1Items", oCon)
>
> oCom.CommandType = CommandType.StoredProcedure
>
> ' Add Parameters to SPROC
>
> Dim parameterMainMenuID As New SqlParameter("@MainMenuID", SqlDbType.Int,
4)
>
> parameterMainMenuID.Value = MainMenuID
>
> oCom.Parameters.Add(parameterMainMenuID)
>
> oCon.Open()
>
> Dim da As New SqlDataAdapter(oCom)
>
> Dim ds As New DataSet()
>
> da.Fill(ds, "tabChildMenu1")
>
> Return ds
>
> oCon.Close()
>
> oCom = Nothing
>
> End Function
>
> End Class
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
> http://www.wrox.com/books/1861007604.htm
Message #3 by "Bob Herrmann" <bob@m...> on Thu, 27 Feb 2003 13:58:54 -0500
|
|
Kevin,
Yes, you are right I do create my sqlconnection each time. I missed that.
Can you give me a short code example on how best to handle this?
Thanks,
Bob
----- Original Message -----
From: "Kevin Ayers" <kevin@f...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, February 27, 2003 1:42 PM
Subject: [ado_dotnet] Re: Open SQL connections problem
> you declare a new connection each time you get data. Try creating a
> program-wide connection variable, open it when you first start, and just
use
> that whenever you use access the data. I did the same thing as you, but
> caught it when I was tracing my database activity.
>
> Kevin
>
> ----- Original Message -----
> From: "Bob Herrmann" <bob@m...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Thursday, February 27, 2003 1:30 PM
> Subject: [ado_dotnet] Open SQL connections problem
>
>
> > Hi all,
> >
> > The SQL database admin at our company says thet I have a bunch of open
> connections on our SQL server. I run the following code and then
following
> this code is my class file code (Menudb.vb) that actually returns a
dataset.
> Can anyone see anything that I am doing wrong? I close my connection at
the
> end of each function.
> >
> > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> System.EventArgs) Handles MyBase.Load
> >
> > If Not Page.IsPostBack Then
> >
> > BuildMenuStructure()
> >
> > End If
> >
> > End Sub
> >
> > Private Sub BuildMenuStructure()
> >
> > Dim newItem As CYBERAKT.WebControls.Navigation.MenuItem
> >
> > Dim newChildMenu1Group As CYBERAKT.WebControls.Navigation.MenuGroup
> >
> >
> >
> > Menu1.ImagesBaseURL = "Images/"
> >
> > Menu1.DefaultGroupCssClass = "MenuGroup"
> >
> > Menu1.DefaultItemCssClass = "MenuItem"
> >
> > Menu1.DefaultItemCssClassOver = "MenuItemOver"
> >
> > Menu1.DefaultItemSpacing = 1
> >
> > ' Create instance of the MenuDB class
> >
> > Dim myMenu As New MZNetSite.MenuDB()
> >
> > Dim parents As DataSet = myMenu.GetMainMenuItems()
> >
> > Dim child1s As DataSet
> >
> > Dim parent As DataRow
> >
> > Dim child1 As DataRow
> >
> > For Each parent In parents.Tables(0).Rows
> >
> > newItem = Menu1.TopGroup.Items.Add()
> >
> > newItem.Label = parent("MainMenuDesc").ToString()
> >
> > If parent("MainMenuURL").ToString() <> "" Then
> >
> > newItem.URL = parent("MainMenuURL").ToString()
> >
> > End If
> >
> > child1s = myMenu.GetChildMenu1Items(parent("MainMenuID").ToString())
> >
> > newChildMenu1Group = newItem.AddSubGroup()
> >
> > For Each child1 In child1s.Tables(0).Rows
> >
> > newItem = newChildMenu1Group.Items.Add()
> >
> > newItem.Label = child1("ChildMenu1Desc").ToString()
> >
> > newItem.URL = child1("ChildMenu1URL").ToString()
> >
> > If child1("ChildMenu1Arrow") = True Then
> >
> > newItem.RightIcon = "arrow_black.gif"
> >
> > End If
> >
> > Next 'Child 1
> >
> > Next 'Parent
> >
> > End Sub
> >
> > End Class
> >
> >
> >
> > Menudb.vb code:
> >
> > Imports System
> >
> > Imports System.Data
> >
> > Imports System.Data.SqlClient
> >
> > Public Class MenuDB
> >
> > ' This function will populate the top-level menu from table tabMainMenu
> >
> > Public Function GetMainMenuItems() As DataSet
> >
> > Dim oCon As SqlConnection = New
> SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
> >
> > Dim oCom As SqlCommand = New SqlCommand("GetMainMenuItems", oCon)
> >
> > oCom.CommandType = CommandType.StoredProcedure
> >
> > oCon.Open()
> >
> > Dim da As New SqlDataAdapter(oCom)
> >
> > Dim ds As New DataSet()
> >
> > da.Fill(ds, "tabMainMenu")
> >
> > Return ds
> >
> > oCon.Close()
> >
> > oCom = Nothing
> >
> > End Function
> >
> > ' This function will populate the first child-level menu from table
> tabChildMenu1
> >
> > Public Function GetChildMenu1Items(ByVal MainMenuID As Integer) As
DataSet
> >
> > Dim oCon As SqlConnection = New
> SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
> >
> > Dim oCom As SqlCommand = New SqlCommand("GetChildMenu1Items", oCon)
> >
> > oCom.CommandType = CommandType.StoredProcedure
> >
> > ' Add Parameters to SPROC
> >
> > Dim parameterMainMenuID As New SqlParameter("@MainMenuID",
SqlDbType.Int,
> 4)
> >
> > parameterMainMenuID.Value = MainMenuID
> >
> > oCom.Parameters.Add(parameterMainMenuID)
> >
> > oCon.Open()
> >
> > Dim da As New SqlDataAdapter(oCom)
> >
> > Dim ds As New DataSet()
> >
> > da.Fill(ds, "tabChildMenu1")
> >
> > Return ds
> >
> > oCon.Close()
> >
> > oCom = Nothing
> >
> > End Function
> >
> > End Class
> >
> >
> > ===
> > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
ADO.NET
> in your applications. The book covers DataSets and Typed DataSets,
accessing
> data using DataReaders and DataAdaptors, the close relationship between
> ADO.NET and XML, how and where to use ADO.NET in your enterprise
> applications, and how to use Web Services and ADO.NET to easily pass data
> between applications.
> > http://www.wrox.com/books/1861007604.htm
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
> http://www.wrox.com/books/1861007604.htm
>
Message #4 by "Alex Smotritsky" <alex.smotritsky@v...> on Thu, 27 Feb 2003 13:56:46 -0500
|
|
Sometimes you need multiple connections open at the same time. If you
have a datareader on connection1 you can't open another datareader on
that connection until you close the first datareader, this is a problem
if you're nesting. I think the problem is that the dataset is a
disconnected object and manages when it connects and disconnects by
itself, those automatic connections is probably what the admin guy is
talking about. I think the solution may be to use a datareader in all
instances where forward-only access to the db is sufficient.
-----Original Message-----
From: Kevin Ayers [mailto:kevin@f...]
Sent: Thursday, February 27, 2003 1:43 PM
To: ADO.NET
Subject: [ado_dotnet] Re: Open SQL connections problem
you declare a new connection each time you get data. Try creating a
program-wide connection variable, open it when you first start, and just
use that whenever you use access the data. I did the same thing as you,
but caught it when I was tracing my database activity.
Kevin
----- Original Message -----
From: "Bob Herrmann" <bob@m...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, February 27, 2003 1:30 PM
Subject: [ado_dotnet] Open SQL connections problem
> Hi all,
>
> The SQL database admin at our company says thet I have a bunch of open
connections on our SQL server. I run the following code and then
following this code is my class file code (Menudb.vb) that actually
returns a dataset. Can anyone see anything that I am doing wrong? I
close my connection at the end of each function.
>
> Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
>
> If Not Page.IsPostBack Then
>
> BuildMenuStructure()
>
> End If
>
> End Sub
>
> Private Sub BuildMenuStructure()
>
> Dim newItem As CYBERAKT.WebControls.Navigation.MenuItem
>
> Dim newChildMenu1Group As CYBERAKT.WebControls.Navigation.MenuGroup
>
>
>
> Menu1.ImagesBaseURL = "Images/"
>
> Menu1.DefaultGroupCssClass = "MenuGroup"
>
> Menu1.DefaultItemCssClass = "MenuItem"
>
> Menu1.DefaultItemCssClassOver = "MenuItemOver"
>
> Menu1.DefaultItemSpacing = 1
>
> ' Create instance of the MenuDB class
>
> Dim myMenu As New MZNetSite.MenuDB()
>
> Dim parents As DataSet = myMenu.GetMainMenuItems()
>
> Dim child1s As DataSet
>
> Dim parent As DataRow
>
> Dim child1 As DataRow
>
> For Each parent In parents.Tables(0).Rows
>
> newItem = Menu1.TopGroup.Items.Add()
>
> newItem.Label = parent("MainMenuDesc").ToString()
>
> If parent("MainMenuURL").ToString() <> "" Then
>
> newItem.URL = parent("MainMenuURL").ToString()
>
> End If
>
> child1s = myMenu.GetChildMenu1Items(parent("MainMenuID").ToString())
>
> newChildMenu1Group = newItem.AddSubGroup()
>
> For Each child1 In child1s.Tables(0).Rows
>
> newItem = newChildMenu1Group.Items.Add()
>
> newItem.Label = child1("ChildMenu1Desc").ToString()
>
> newItem.URL = child1("ChildMenu1URL").ToString()
>
> If child1("ChildMenu1Arrow") = True Then
>
> newItem.RightIcon = "arrow_black.gif"
>
> End If
>
> Next 'Child 1
>
> Next 'Parent
>
> End Sub
>
> End Class
>
>
>
> Menudb.vb code:
>
> Imports System
>
> Imports System.Data
>
> Imports System.Data.SqlClient
>
> Public Class MenuDB
>
> ' This function will populate the top-level menu from table
> tabMainMenu
>
> Public Function GetMainMenuItems() As DataSet
>
> Dim oCon As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
>
> Dim oCom As SqlCommand = New SqlCommand("GetMainMenuItems", oCon)
>
> oCom.CommandType = CommandType.StoredProcedure
>
> oCon.Open()
>
> Dim da As New SqlDataAdapter(oCom)
>
> Dim ds As New DataSet()
>
> da.Fill(ds, "tabMainMenu")
>
> Return ds
>
> oCon.Close()
>
> oCom = Nothing
>
> End Function
>
> ' This function will populate the first child-level menu from table
tabChildMenu1
>
> Public Function GetChildMenu1Items(ByVal MainMenuID As Integer) As
> DataSet
>
> Dim oCon As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
>
> Dim oCom As SqlCommand = New SqlCommand("GetChildMenu1Items", oCon)
>
> oCom.CommandType = CommandType.StoredProcedure
>
> ' Add Parameters to SPROC
>
> Dim parameterMainMenuID As New SqlParameter("@MainMenuID",
> SqlDbType.Int,
4)
>
> parameterMainMenuID.Value = MainMenuID
>
> oCom.Parameters.Add(parameterMainMenuID)
>
> oCon.Open()
>
> Dim da As New SqlDataAdapter(oCom)
>
> Dim ds As New DataSet()
>
> da.Fill(ds, "tabChildMenu1")
>
> Return ds
>
> oCon.Close()
>
> oCom = Nothing
>
> End Function
>
> End Class
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
> http://www.wrox.com/books/1861007604.htm
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to
> unsubscribe send a blank email to
===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm
Message #5 by "Kevin Ayers" <kevin@f...> on Thu, 27 Feb 2003 14:32:35 -0500
|
|
This is some code I'm using from the Wrox book - VB.Net and SQL Server 2k.
I've modified to use 1 connection because of the multiple connection
problem.
Kevin
Imports System.Data.SqlClient
Public Class DataAccess
Private Const MODULE_NAME As String = "DataAccess"
Private Const MODULE_SOURCE As String = "DataAccess"
Private myConnection As SqlConnection
Private m_sServer As String
Private m_sDatabase As String
Public Property Server() As String
Get
Return m_sServer
End Get
Set(ByVal Value As String)
m_sServer = Value
End Set
End Property
Public Property DataBase() As String
Get
Return m_sDatabase
End Get
Set(ByVal Value As String)
m_sDatabase = Value
End Set
End Property
Public Sub New(ByVal strServer As String, ByVal strDatabase As String)
m_sServer = strServer
m_sDatabase = strDatabase
myConnection = New SqlConnection(GetConnectionString())
myConnection.Open()
End Sub
Private Sub LogError(ByVal e As Exception)
Dim LogMsg As StringWriter = New StringWriter()
LogMsg.WriteLine("Module: " & MODULE_SOURCE)
LogMsg.WriteLine("Source: " & e.Source)
LogMsg.WriteLine("Message: " & e.Message)
LogMsg.WriteLine("Connect: " & GetConnectionString())
MsgBox(e.Message)
System.Diagnostics.EventLog.WriteEntry(MODULE_NAME, _
LogMsg.ToString, Diagnostics.EventLogEntryType.Error)
End Sub
Private Function GetConnectionString() As String
Dim myConnectStr As String = _
"Server = " & m_sServer & ";" & _
"Database = " & m_sDatabase & ";" & _
"User ID = sa;Password=;"
Return myConnectStr
End Function
Private Function CreateCommand(ByVal SQLText As String, _
ByVal Param() As SqlParameter, _
ByVal SQLCmdType As CommandType) _
As SqlCommand
'Dim myConnection As SqlConnection = New
SqlConnection(GetConnectionString())
Dim myCommand As SqlCommand = New SqlCommand(SQLText, myConnection)
myCommand.CommandType = SQLCmdType
Dim ParamTemp As SqlParameter
If Not Param Is Nothing Then
For Each ParamTemp In Param
myCommand.Parameters.Add(ParamTemp)
Next
End If
Try
Return myCommand
Catch e As Exception
LogError(e)
Return Nothing
End Try
End Function
Public Overloads Function ExecuteSP(ByVal SProcName As String, _
ByVal Param() As SqlParameter, _
ByVal MyDataSet As DataSet) _
As Boolean
Dim myCommand As SqlCommand = CreateCommand(SProcName, _
Param, _
CommandType.StoredProcedure)
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(myCommand)
Try
myDataAdapter.Fill(MyDataSet, "Parent")
Return True
Catch e As Exception
LogError(e)
Return False
End Try
End Function
End Class
----- Original Message -----
From: "Bob Herrmann" <bob@m...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, February 27, 2003 1:58 PM
Subject: [ado_dotnet] Re: Open SQL connections problem
> Kevin,
>
> Yes, you are right I do create my sqlconnection each time. I missed that.
> Can you give me a short code example on how best to handle this?
>
> Thanks,
> Bob
> ----- Original Message -----
> From: "Kevin Ayers" <kevin@f...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Thursday, February 27, 2003 1:42 PM
> Subject: [ado_dotnet] Re: Open SQL connections problem
>
>
> > you declare a new connection each time you get data. Try creating a
> > program-wide connection variable, open it when you first start, and just
> use
> > that whenever you use access the data. I did the same thing as you, but
> > caught it when I was tracing my database activity.
> >
> > Kevin
> >
> > ----- Original Message -----
> > From: "Bob Herrmann" <bob@m...>
> > To: "ADO.NET" <ado_dotnet@p...>
> > Sent: Thursday, February 27, 2003 1:30 PM
> > Subject: [ado_dotnet] Open SQL connections problem
> >
> >
> > > Hi all,
> > >
> > > The SQL database admin at our company says thet I have a bunch of open
> > connections on our SQL server. I run the following code and then
> following
> > this code is my class file code (Menudb.vb) that actually returns a
> dataset.
> > Can anyone see anything that I am doing wrong? I close my connection at
> the
> > end of each function.
> > >
> > > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> > System.EventArgs) Handles MyBase.Load
> > >
> > > If Not Page.IsPostBack Then
> > >
> > > BuildMenuStructure()
> > >
> > > End If
> > >
> > > End Sub
> > >
> > > Private Sub BuildMenuStructure()
> > >
> > > Dim newItem As CYBERAKT.WebControls.Navigation.MenuItem
> > >
> > > Dim newChildMenu1Group As CYBERAKT.WebControls.Navigation.MenuGroup
> > >
> > >
> > >
> > > Menu1.ImagesBaseURL = "Images/"
> > >
> > > Menu1.DefaultGroupCssClass = "MenuGroup"
> > >
> > > Menu1.DefaultItemCssClass = "MenuItem"
> > >
> > > Menu1.DefaultItemCssClassOver = "MenuItemOver"
> > >
> > > Menu1.DefaultItemSpacing = 1
> > >
> > > ' Create instance of the MenuDB class
> > >
> > > Dim myMenu As New MZNetSite.MenuDB()
> > >
> > > Dim parents As DataSet = myMenu.GetMainMenuItems()
> > >
> > > Dim child1s As DataSet
> > >
> > > Dim parent As DataRow
> > >
> > > Dim child1 As DataRow
> > >
> > > For Each parent In parents.Tables(0).Rows
> > >
> > > newItem = Menu1.TopGroup.Items.Add()
> > >
> > > newItem.Label = parent("MainMenuDesc").ToString()
> > >
> > > If parent("MainMenuURL").ToString() <> "" Then
> > >
> > > newItem.URL = parent("MainMenuURL").ToString()
> > >
> > > End If
> > >
> > > child1s = myMenu.GetChildMenu1Items(parent("MainMenuID").ToString())
> > >
> > > newChildMenu1Group = newItem.AddSubGroup()
> > >
> > > For Each child1 In child1s.Tables(0).Rows
> > >
> > > newItem = newChildMenu1Group.Items.Add()
> > >
> > > newItem.Label = child1("ChildMenu1Desc").ToString()
> > >
> > > newItem.URL = child1("ChildMenu1URL").ToString()
> > >
> > > If child1("ChildMenu1Arrow") = True Then
> > >
> > > newItem.RightIcon = "arrow_black.gif"
> > >
> > > End If
> > >
> > > Next 'Child 1
> > >
> > > Next 'Parent
> > >
> > > End Sub
> > >
> > > End Class
> > >
> > >
> > >
> > > Menudb.vb code:
> > >
> > > Imports System
> > >
> > > Imports System.Data
> > >
> > > Imports System.Data.SqlClient
> > >
> > > Public Class MenuDB
> > >
> > > ' This function will populate the top-level menu from table
tabMainMenu
> > >
> > > Public Function GetMainMenuItems() As DataSet
> > >
> > > Dim oCon As SqlConnection = New
> > SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
> > >
> > > Dim oCom As SqlCommand = New SqlCommand("GetMainMenuItems", oCon)
> > >
> > > oCom.CommandType = CommandType.StoredProcedure
> > >
> > > oCon.Open()
> > >
> > > Dim da As New SqlDataAdapter(oCom)
> > >
> > > Dim ds As New DataSet()
> > >
> > > da.Fill(ds, "tabMainMenu")
> > >
> > > Return ds
> > >
> > > oCon.Close()
> > >
> > > oCom = Nothing
> > >
> > > End Function
> > >
> > > ' This function will populate the first child-level menu from table
> > tabChildMenu1
> > >
> > > Public Function GetChildMenu1Items(ByVal MainMenuID As Integer) As
> DataSet
> > >
> > > Dim oCon As SqlConnection = New
> > SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
> > >
> > > Dim oCom As SqlCommand = New SqlCommand("GetChildMenu1Items", oCon)
> > >
> > > oCom.CommandType = CommandType.StoredProcedure
> > >
> > > ' Add Parameters to SPROC
> > >
> > > Dim parameterMainMenuID As New SqlParameter("@MainMenuID",
> SqlDbType.Int,
> > 4)
> > >
> > > parameterMainMenuID.Value = MainMenuID
> > >
> > > oCom.Parameters.Add(parameterMainMenuID)
> > >
> > > oCon.Open()
> > >
> > > Dim da As New SqlDataAdapter(oCom)
> > >
> > > Dim ds As New DataSet()
> > >
> > > da.Fill(ds, "tabChildMenu1")
> > >
> > > Return ds
> > >
> > > oCon.Close()
> > >
> > > oCom = Nothing
> > >
> > > End Function
> > >
> > > End Class
> > >
> > >
> > > ===
> > > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> > techniques, and libraries that you will need in order to start using
> ADO.NET
> > in your applications. The book covers DataSets and Typed DataSets,
> accessing
> > data using DataReaders and DataAdaptors, the close relationship between
> > ADO.NET and XML, how and where to use ADO.NET in your enterprise
> > applications, and how to use Web Services and ADO.NET to easily pass
data
> > between applications.
> > > http://www.wrox.com/books/1861007604.htm
> >
> >
> > ===
> > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
ADO.NET
> in your applications. The book covers DataSets and Typed DataSets,
accessing
> data using DataReaders and DataAdaptors, the close relationship between
> ADO.NET and XML, how and where to use ADO.NET in your enterprise
> applications, and how to use Web Services and ADO.NET to easily pass data
> between applications.
> > http://www.wrox.com/books/1861007604.htm
> >
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
> http://www.wrox.com/books/1861007604.htm
>
Message #6 by "Bob Herrmann" <bob@m...> on Thu, 27 Feb 2003 15:05:29 -0500
|
|
Kevin,
Thanks for the code. I did figure it out though. I'm just braindead today.
Thanks again,
Bob
----- Original Message -----
From: "Kevin Ayers" <kevin@f...>
To: "ADO.NET" <ado_dotnet@p...>
Sent: Thursday, February 27, 2003 2:32 PM
Subject: [ado_dotnet] Re: Open SQL connections problem
> This is some code I'm using from the Wrox book - VB.Net and SQL Server 2k.
> I've modified to use 1 connection because of the multiple connection
> problem.
>
> Kevin
>
>
>
> Imports System.Data.SqlClient
>
> Public Class DataAccess
>
> Private Const MODULE_NAME As String = "DataAccess"
>
> Private Const MODULE_SOURCE As String = "DataAccess"
>
> Private myConnection As SqlConnection
>
> Private m_sServer As String
>
> Private m_sDatabase As String
>
> Public Property Server() As String
>
> Get
>
> Return m_sServer
>
> End Get
>
> Set(ByVal Value As String)
>
> m_sServer = Value
>
> End Set
>
> End Property
>
> Public Property DataBase() As String
>
> Get
>
> Return m_sDatabase
>
> End Get
>
> Set(ByVal Value As String)
>
> m_sDatabase = Value
>
> End Set
>
> End Property
>
>
>
> Public Sub New(ByVal strServer As String, ByVal strDatabase As String)
>
>
> m_sServer = strServer
>
> m_sDatabase = strDatabase
>
> myConnection = New SqlConnection(GetConnectionString())
>
> myConnection.Open()
>
> End Sub
>
> Private Sub LogError(ByVal e As Exception)
>
> Dim LogMsg As StringWriter = New StringWriter()
>
> LogMsg.WriteLine("Module: " & MODULE_SOURCE)
>
> LogMsg.WriteLine("Source: " & e.Source)
>
> LogMsg.WriteLine("Message: " & e.Message)
>
> LogMsg.WriteLine("Connect: " & GetConnectionString())
>
> MsgBox(e.Message)
>
> System.Diagnostics.EventLog.WriteEntry(MODULE_NAME, _
>
> LogMsg.ToString, Diagnostics.EventLogEntryType.Error)
>
> End Sub
>
> Private Function GetConnectionString() As String
>
> Dim myConnectStr As String = _
>
> "Server = " & m_sServer & ";" & _
>
> "Database = " & m_sDatabase & ";" & _
>
> "User ID = sa;Password=;"
>
> Return myConnectStr
>
> End Function
>
> Private Function CreateCommand(ByVal SQLText As String, _
>
> ByVal Param() As SqlParameter, _
>
> ByVal SQLCmdType As CommandType) _
>
> As SqlCommand
>
> 'Dim myConnection As SqlConnection = New
> SqlConnection(GetConnectionString())
>
> Dim myCommand As SqlCommand = New SqlCommand(SQLText, myConnection)
>
> myCommand.CommandType = SQLCmdType
>
> Dim ParamTemp As SqlParameter
>
> If Not Param Is Nothing Then
>
> For Each ParamTemp In Param
>
> myCommand.Parameters.Add(ParamTemp)
>
> Next
>
> End If
>
> Try
>
> Return myCommand
>
> Catch e As Exception
>
> LogError(e)
>
> Return Nothing
>
> End Try
>
> End Function
>
> Public Overloads Function ExecuteSP(ByVal SProcName As String, _
>
> ByVal Param() As SqlParameter, _
>
> ByVal MyDataSet As DataSet) _
>
> As Boolean
>
> Dim myCommand As SqlCommand = CreateCommand(SProcName, _
>
> Param, _
>
> CommandType.StoredProcedure)
>
> Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(myCommand)
>
> Try
>
> myDataAdapter.Fill(MyDataSet, "Parent")
>
> Return True
>
> Catch e As Exception
>
> LogError(e)
>
> Return False
>
> End Try
>
> End Function
>
>
>
> End Class
>
> ----- Original Message -----
> From: "Bob Herrmann" <bob@m...>
> To: "ADO.NET" <ado_dotnet@p...>
> Sent: Thursday, February 27, 2003 1:58 PM
> Subject: [ado_dotnet] Re: Open SQL connections problem
>
>
> > Kevin,
> >
> > Yes, you are right I do create my sqlconnection each time. I missed
that.
> > Can you give me a short code example on how best to handle this?
> >
> > Thanks,
> > Bob
> > ----- Original Message -----
> > From: "Kevin Ayers" <kevin@f...>
> > To: "ADO.NET" <ado_dotnet@p...>
> > Sent: Thursday, February 27, 2003 1:42 PM
> > Subject: [ado_dotnet] Re: Open SQL connections problem
> >
> >
> > > you declare a new connection each time you get data. Try creating a
> > > program-wide connection variable, open it when you first start, and
just
> > use
> > > that whenever you use access the data. I did the same thing as you,
but
> > > caught it when I was tracing my database activity.
> > >
> > > Kevin
> > >
> > > ----- Original Message -----
> > > From: "Bob Herrmann" <bob@m...>
> > > To: "ADO.NET" <ado_dotnet@p...>
> > > Sent: Thursday, February 27, 2003 1:30 PM
> > > Subject: [ado_dotnet] Open SQL connections problem
> > >
> > >
> > > > Hi all,
> > > >
> > > > The SQL database admin at our company says thet I have a bunch of
open
> > > connections on our SQL server. I run the following code and then
> > following
> > > this code is my class file code (Menudb.vb) that actually returns a
> > dataset.
> > > Can anyone see anything that I am doing wrong? I close my connection
at
> > the
> > > end of each function.
> > > >
> > > > Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
> > > System.EventArgs) Handles MyBase.Load
> > > >
> > > > If Not Page.IsPostBack Then
> > > >
> > > > BuildMenuStructure()
> > > >
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > > Private Sub BuildMenuStructure()
> > > >
> > > > Dim newItem As CYBERAKT.WebControls.Navigation.MenuItem
> > > >
> > > > Dim newChildMenu1Group As CYBERAKT.WebControls.Navigation.MenuGroup
> > > >
> > > >
> > > >
> > > > Menu1.ImagesBaseURL = "Images/"
> > > >
> > > > Menu1.DefaultGroupCssClass = "MenuGroup"
> > > >
> > > > Menu1.DefaultItemCssClass = "MenuItem"
> > > >
> > > > Menu1.DefaultItemCssClassOver = "MenuItemOver"
> > > >
> > > > Menu1.DefaultItemSpacing = 1
> > > >
> > > > ' Create instance of the MenuDB class
> > > >
> > > > Dim myMenu As New MZNetSite.MenuDB()
> > > >
> > > > Dim parents As DataSet = myMenu.GetMainMenuItems()
> > > >
> > > > Dim child1s As DataSet
> > > >
> > > > Dim parent As DataRow
> > > >
> > > > Dim child1 As DataRow
> > > >
> > > > For Each parent In parents.Tables(0).Rows
> > > >
> > > > newItem = Menu1.TopGroup.Items.Add()
> > > >
> > > > newItem.Label = parent("MainMenuDesc").ToString()
> > > >
> > > > If parent("MainMenuURL").ToString() <> "" Then
> > > >
> > > > newItem.URL = parent("MainMenuURL").ToString()
> > > >
> > > > End If
> > > >
> > > > child1s = myMenu.GetChildMenu1Items(parent("MainMenuID").ToString())
> > > >
> > > > newChildMenu1Group = newItem.AddSubGroup()
> > > >
> > > > For Each child1 In child1s.Tables(0).Rows
> > > >
> > > > newItem = newChildMenu1Group.Items.Add()
> > > >
> > > > newItem.Label = child1("ChildMenu1Desc").ToString()
> > > >
> > > > newItem.URL = child1("ChildMenu1URL").ToString()
> > > >
> > > > If child1("ChildMenu1Arrow") = True Then
> > > >
> > > > newItem.RightIcon = "arrow_black.gif"
> > > >
> > > > End If
> > > >
> > > > Next 'Child 1
> > > >
> > > > Next 'Parent
> > > >
> > > > End Sub
> > > >
> > > > End Class
> > > >
> > > >
> > > >
> > > > Menudb.vb code:
> > > >
> > > > Imports System
> > > >
> > > > Imports System.Data
> > > >
> > > > Imports System.Data.SqlClient
> > > >
> > > > Public Class MenuDB
> > > >
> > > > ' This function will populate the top-level menu from table
> tabMainMenu
> > > >
> > > > Public Function GetMainMenuItems() As DataSet
> > > >
> > > > Dim oCon As SqlConnection = New
> > > SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
> > > >
> > > > Dim oCom As SqlCommand = New SqlCommand("GetMainMenuItems", oCon)
> > > >
> > > > oCom.CommandType = CommandType.StoredProcedure
> > > >
> > > > oCon.Open()
> > > >
> > > > Dim da As New SqlDataAdapter(oCom)
> > > >
> > > > Dim ds As New DataSet()
> > > >
> > > > da.Fill(ds, "tabMainMenu")
> > > >
> > > > Return ds
> > > >
> > > > oCon.Close()
> > > >
> > > > oCom = Nothing
> > > >
> > > > End Function
> > > >
> > > > ' This function will populate the first child-level menu from table
> > > tabChildMenu1
> > > >
> > > > Public Function GetChildMenu1Items(ByVal MainMenuID As Integer) As
> > DataSet
> > > >
> > > > Dim oCon As SqlConnection = New
> > > SqlConnection(ConfigurationSettings.AppSettings("sMZNetSiteSQL"))
> > > >
> > > > Dim oCom As SqlCommand = New SqlCommand("GetChildMenu1Items", oCon)
> > > >
> > > > oCom.CommandType = CommandType.StoredProcedure
> > > >
> > > > ' Add Parameters to SPROC
> > > >
> > > > Dim parameterMainMenuID As New SqlParameter("@MainMenuID",
> > SqlDbType.Int,
> > > 4)
> > > >
> > > > parameterMainMenuID.Value = MainMenuID
> > > >
> > > > oCom.Parameters.Add(parameterMainMenuID)
> > > >
> > > > oCon.Open()
> > > >
> > > > Dim da As New SqlDataAdapter(oCom)
> > > >
> > > > Dim ds As New DataSet()
> > > >
> > > > da.Fill(ds, "tabChildMenu1")
> > > >
> > > > Return ds
> > > >
> > > > oCon.Close()
> > > >
> > > > oCom = Nothing
> > > >
> > > > End Function
> > > >
> > > > End Class
> > > >
> > > >
> > > > ===
> > > > Fast Track ADO.NET with C# is a concise introduction to the
concepts,
> > > techniques, and libraries that you will need in order to start using
> > ADO.NET
> > > in your applications. The book covers DataSets and Typed DataSets,
> > accessing
> > > data using DataReaders and DataAdaptors, the close relationship
between
> > > ADO.NET and XML, how and where to use ADO.NET in your enterprise
> > > applications, and how to use Web Services and ADO.NET to easily pass
> data
> > > between applications.
> > > > http://www.wrox.com/books/1861007604.htm
> > >
> > >
> > > ===
> > > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> > techniques, and libraries that you will need in order to start using
> ADO.NET
> > in your applications. The book covers DataSets and Typed DataSets,
> accessing
> > data using DataReaders and DataAdaptors, the close relationship between
> > ADO.NET and XML, how and where to use ADO.NET in your enterprise
> > applications, and how to use Web Services and ADO.NET to easily pass
data
> > between applications.
> > > http://www.wrox.com/books/1861007604.htm
> > >
> >
> >
> > ===
> > Fast Track ADO.NET with C# is a concise introduction to the concepts,
> techniques, and libraries that you will need in order to start using
ADO.NET
> in your applications. The book covers DataSets and Typed DataSets,
accessing
> data using DataReaders and DataAdaptors, the close relationship between
> ADO.NET and XML, how and where to use ADO.NET in your enterprise
> applications, and how to use Web Services and ADO.NET to easily pass data
> between applications.
> > http://www.wrox.com/books/1861007604.htm
> >
>
>
> ===
> Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using ADO.NET
in your applications. The book covers DataSets and Typed DataSets, accessing
data using DataReaders and DataAdaptors, the close relationship between
ADO.NET and XML, how and where to use ADO.NET in your enterprise
applications, and how to use Web Services and ADO.NET to easily pass data
between applications.
> http://www.wrox.com/books/1861007604.htm
>
Message #7 by "Brian Smith" <bsmith@l...> on Fri, 28 Feb 2003 09:37:24 -0000
|
|
Snippet:
> da.Fill(ds, "tabChildMenu1")
> Return ds
> oCon.Close()
> oCom = Nothing
Your Close() never gets called! If you must Open the connection
explicitly (which you don't need to do if filling a Dataset) then put
your Open etc inside a Try block, and put the Close() in the Finally
section.
But as I said, for DataSets it's better to let the DataAdapter open and
close the connection - just omit the Open and Close from your code..
brian
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
Message #8 by "Alex Smotritsky" <alex.smotritsky@v...> on Fri, 28 Feb 2003 06:10:02 -0500
|
|
In my methods that require db connections, I open the connection outside
of the method call, pass it to the method and then close the connection
after the method returns. I think that's the way to handle this stuff.
-----Original Message-----
From: Brian Smith [mailto:bsmith@l...]
Sent: Friday, February 28, 2003 4:37 AM
To: ADO.NET
Subject: [ado_dotnet] RE: Open SQL connections problem
Snippet:
> da.Fill(ds, "tabChildMenu1")
> Return ds
> oCon.Close()
> oCom = Nothing
Your Close() never gets called! If you must Open the connection
explicitly (which you don't need to do if filling a Dataset) then put
your Open etc inside a Try block, and put the Close() in the Finally
section.
But as I said, for DataSets it's better to let the DataAdapter open and
close the connection - just omit the Open and Close from your code..
brian
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 27/01/2003
===
Fast Track ADO.NET with C# is a concise introduction to the concepts,
techniques, and libraries that you will need in order to start using
ADO.NET in your applications. The book covers DataSets and Typed
DataSets, accessing data using DataReaders and DataAdaptors, the close
relationship between ADO.NET and XML, how and where to use ADO.NET in
your enterprise applications, and how to use Web Services and ADO.NET to
easily pass data between applications.
http://www.wrox.com/books/1861007604.htm
|
|
|
View Cart
