[求助]以下VB代码是否会引起不断打开数据连接
同事写的代码,在类里打开连接后不关闭,在后台代码的其它位置关闭。我对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 多用户同时操作,最好用完即关 真TNND佩服,“封装”了一个类仅仅为了声明变量时少写几句话?
天才!大大的天才!
整个 Page_Load 的代码,除了循环,其他部分(包括类代码),完全可以做成一个函数,传入 id,返回一个 DataTable,函数内部自动打开/关闭连接。
这样连接的保持时间只有单纯查询的花费,后面填充页面内容时不管多慢,都对数据库没影响,应该有很大的改善。 楼主学会用语法着色。
Public Class Class190个连接就挂了啊?服务器不咋的,用户多的话峰值可能比这个数值大的多了。
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
如果考虑稳定的话,可以限定最大同时连接数,高级点的应该将查询都交给后台单独的一个程序去完成,而且在这个程序中完全控制。某一时刻超过最大允许的连接数(例如60个),那么就队列形式,一个个挨着来,不要一下子全部去访问数据库服务器了。
服务器一般都支持过千过万的连接的。就怕这个程序有上千个用户在用,那就悲剧鸟~~
补充:VB , 数据库(包含打印,安装,报表)