同事写的代码,在类里打开连接后不关闭,在后台代码的其它位置关闭。我对VB不熟悉,不知道是否导致连接过多。请高手指点。
问题现象:目前单位的ORACLE数据库经常ORA-00020:maximum number of process(150) exceeded;
查了一下,有一个查询的连接数是最大的,高达90以上;此语句在一个主页面frame中的Page_Load()中存在(此页面很多人打开),用了一个CLASS1的类,类的代码中打了连接,类定义如下:
Public Class Class1
Public Oraqd As OleDb.OleDbConnection, adaqd As OleDb.OleDbDataAdapter, dataqd As Data.DataSet
Public OraCrm As OleDb.OleDbConnection, adaCrm As OleDb.OleDbDataAdapter
Public Ora10000 As OleDb.OleDbConnection, ada10000 As OleDb.OleDbDataAdapter
Public OraYw As OleDb.OleDbConnection, adaYw As OleDb.OleDbDataAdapter
Public SqlStr As String Public Sub data_conn()
Oraqd = New OleDb.OleDbConnection
SqlStr = System.Configuration.ConfigurationManager.AppSettings("conn_qd")
Oraqd.ConnectionString = SqlStr
Oraqd.Open() adaqd = New OleDb.OleDbDataAdapter
adaqd.SelectCommand = New OleDb.OleDbCommand
adaqd.SelectCommand.Connection = Oraqd
adaqd.InsertCommand = New OleDb.OleDbCommand
adaqd.InsertCommand.Connection = Oraqd
adaqd.UpdateCommand = New OleDb.OleDbCommand
adaqd.UpdateCommand.Connection = Oraqd
adaqd.DeleteCommand = New OleDb.OleDbCommand
adaqd.DeleteCommand.Connection = Oraqd dataqd = New Data.DataSet
End Sub Public Sub qd_close()
Oraqd.Close()
End Sub
End Class页面后台代码如下:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim i, j As Integer If (Not Page.IsPostBack) Then
Dim class1 As New Class1
class1.data_conn()
class1.adaqd.SelectCommand.CommandText = "select * from ywzc_menu where menu_level_1='" + Request.QueryString("id").ToString + "' and " _
+ " menu_status='0' order by menu_level_2,menu_level_3"
class1.adaqd.SelectCommand.ExecuteNonQuery()
class1.adaqd.Fill(class1.dataqd, "menu") TreeView1.Nodes.Clear() j = -1
For i = 0 To class1.dataqd.Tables("menu").Rows.Count - 1
Dim cnode As New TreeNode
cnode.Text = class1.dataqd.Tables("menu").Rows(i).Item("menu_name").ToString
cnode.ToolTip = class1.dataqd.Tables("menu").Rows(i).Item("menu_name").ToString
cnode.NavigateUrl = class1.dataqd.Tables("menu").Rows(i).Item("menu_url").ToString
cnode.SelectAction = TreeNodeSelectAction.Expand
If i = 0 Then
cnode.Expanded = True
Else
cnode.Expanded = False
End If
If class1.dataqd.Tables("menu").Rows(i).Item("menu_level_3").ToString = "00" Then
cnode.ImageUrl = "image/tele.ico"
TreeView1.Nodes.Add(cnode)
j = j + 1
Else
TreeView1.Nodes.Item(j).ChildNodes.Add(cnode)
End If
Next class1.qd_close()
End If
End Sub
问题现象:目前单位的ORACLE数据库经常ORA-00020:maximum number of process(150) exceeded;
查了一下,有一个查询的连接数是最大的,高达90以上;此语句在一个主页面frame中的Page_Load()中存在(此页面很多人打开),用了一个CLASS1的类,类的代码中打了连接,类定义如下:
Public Class Class1
Public Oraqd As OleDb.OleDbConnection, adaqd As OleDb.OleDbDataAdapter, dataqd As Data.DataSet
Public OraCrm As OleDb.OleDbConnection, adaCrm As OleDb.OleDbDataAdapter
Public Ora10000 As OleDb.OleDbConnection, ada10000 As OleDb.OleDbDataAdapter
Public OraYw As OleDb.OleDbConnection, adaYw As OleDb.OleDbDataAdapter
Public SqlStr As String Public Sub data_conn()
Oraqd = New OleDb.OleDbConnection
SqlStr = System.Configuration.ConfigurationManager.AppSettings("conn_qd")
Oraqd.ConnectionString = SqlStr
Oraqd.Open() adaqd = New OleDb.OleDbDataAdapter
adaqd.SelectCommand = New OleDb.OleDbCommand
adaqd.SelectCommand.Connection = Oraqd
adaqd.InsertCommand = New OleDb.OleDbCommand
adaqd.InsertCommand.Connection = Oraqd
adaqd.UpdateCommand = New OleDb.OleDbCommand
adaqd.UpdateCommand.Connection = Oraqd
adaqd.DeleteCommand = New OleDb.OleDbCommand
adaqd.DeleteCommand.Connection = Oraqd dataqd = New Data.DataSet
End Sub Public Sub qd_close()
Oraqd.Close()
End Sub
End Class页面后台代码如下:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim i, j As Integer If (Not Page.IsPostBack) Then
Dim class1 As New Class1
class1.data_conn()
class1.adaqd.SelectCommand.CommandText = "select * from ywzc_menu where menu_level_1='" + Request.QueryString("id").ToString + "' and " _
+ " menu_status='0' order by menu_level_2,menu_level_3"
class1.adaqd.SelectCommand.ExecuteNonQuery()
class1.adaqd.Fill(class1.dataqd, "menu") TreeView1.Nodes.Clear() j = -1
For i = 0 To class1.dataqd.Tables("menu").Rows.Count - 1
Dim cnode As New TreeNode
cnode.Text = class1.dataqd.Tables("menu").Rows(i).Item("menu_name").ToString
cnode.ToolTip = class1.dataqd.Tables("menu").Rows(i).Item("menu_name").ToString
cnode.NavigateUrl = class1.dataqd.Tables("menu").Rows(i).Item("menu_url").ToString
cnode.SelectAction = TreeNodeSelectAction.Expand
If i = 0 Then
cnode.Expanded = True
Else
cnode.Expanded = False
End If
If class1.dataqd.Tables("menu").Rows(i).Item("menu_level_3").ToString = "00" Then
cnode.ImageUrl = "image/tele.ico"
TreeView1.Nodes.Add(cnode)
j = j + 1
Else
TreeView1.Nodes.Item(j).ChildNodes.Add(cnode)
End If
Next class1.qd_close()
End If
End Sub
天才!大大的天才!整个 Page_Load 的代码,除了循环,其他部分(包括类代码),完全可以做成一个函数,传入 id,返回一个 DataTable,函数内部自动打开/关闭连接。
这样连接的保持时间只有单纯查询的花费,后面填充页面内容时不管多慢,都对数据库没影响,应该有很大的改善。
Public Class Class1
Public Oraqd As OleDb.OleDbConnection, adaqd As OleDb.OleDbDataAdapter, dataqd As Data.DataSet
Public OraCrm As OleDb.OleDbConnection, adaCrm As OleDb.OleDbDataAdapter
Public Ora10000 As OleDb.OleDbConnection, ada10000 As OleDb.OleDbDataAdapter
Public OraYw As OleDb.OleDbConnection, adaYw As OleDb.OleDbDataAdapter
Public SqlStr As String Public Sub data_conn()
Oraqd = New OleDb.OleDbConnection
SqlStr = System.Configuration.ConfigurationManager.AppSettings("conn_qd")
Oraqd.ConnectionString = SqlStr
Oraqd.Open() adaqd = New OleDb.OleDbDataAdapter
adaqd.SelectCommand = New OleDb.OleDbCommand
adaqd.SelectCommand.Connection = Oraqd
adaqd.InsertCommand = New OleDb.OleDbCommand
adaqd.InsertCommand.Connection = Oraqd
adaqd.UpdateCommand = New OleDb.OleDbCommand
adaqd.UpdateCommand.Connection = Oraqd
adaqd.DeleteCommand = New OleDb.OleDbCommand
adaqd.DeleteCommand.Connection = Oraqd dataqd = New Data.DataSet
End Sub Public Sub qd_close()
Oraqd.Close()
End Sub
End Class页面后台代码如下:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim i, j As Integer If (Not Page.IsPostBack) Then
Dim class1 As New Class1
class1.data_conn()
class1.adaqd.SelectCommand.CommandText = "select * from ywzc_menu where menu_level_1='" + Request.QueryString("id").ToString + "' and " _
+ " menu_status='0' order by menu_level_2,menu_level_3"
class1.adaqd.SelectCommand.ExecuteNonQuery()
class1.adaqd.Fill(class1.dataqd, "menu") TreeView1.Nodes.Clear() j = -1
For i = 0 To class1.dataqd.Tables("menu").Rows.Count - 1
Dim cnode As New TreeNode
cnode.Text = class1.dataqd.Tables("menu").Rows(i).Item("menu_name").ToString
cnode.ToolTip = class1.dataqd.Tables("menu").Rows(i).Item("menu_name").ToString
cnode.NavigateUrl = class1.dataqd.Tables("menu").Rows(i).Item("menu_url").ToString
cnode.SelectAction = TreeNodeSelectAction.Expand
If i = 0 Then
cnode.Expanded = True
Else
cnode.Expanded = False
End If
If class1.dataqd.Tables("menu").Rows(i).Item("menu_level_3").ToString = "00" Then
cnode.ImageUrl = "image/tele.ico"
TreeView1.Nodes.Add(cnode)
j = j + 1
Else
TreeView1.Nodes.Item(j).ChildNodes.Add(cnode)
End If
Next class1.qd_close()
End If
End Sub