................
Private objconn As SqlConnection
Private objcmd As SqlCommand
Private strconn As String = System.Configuration.ConfigurationSettings.AppSettings("sqlconn")
..............
'Select users by department id
Public Function spUserbydep(ByVal id As Integer) As SqlDataReader
Try
objconn = New SqlConnection(strconn)
objcmd = New SqlCommand("spUserbydep", objconn)
objcmd.CommandType = CommandType.StoredProcedure
objcmd.Parameters.Add(New SqlParameter("@depid", SqlDbType.Int))
objcmd.Parameters("@depid").Value = id
objconn.Open()
Return objcmd.ExecuteReader()
objconn.Close()
objconn.Dispose()
Catch ex As SqlException
Return Nothing
End Try
End Function
......................
所有的过程我都是如上释放了连接,为什么还是会连接池满呢。解决就结分。谢谢
下面是我的WEBCONFIG,设置的
<appSetting>
<add key="sqlconn" value="server=(local);Pooling=true;Max Pool Size=300;Min Pool Size=0;uid=sa;pwd=;database=hr"></add>
</appSettings>........................
Private objconn As SqlConnection
Private objcmd As SqlCommand
Private strconn As String = System.Configuration.ConfigurationSettings.AppSettings("sqlconn")
..............
'Select users by department id
Public Function spUserbydep(ByVal id As Integer) As SqlDataReader
Try
objconn = New SqlConnection(strconn)
objcmd = New SqlCommand("spUserbydep", objconn)
objcmd.CommandType = CommandType.StoredProcedure
objcmd.Parameters.Add(New SqlParameter("@depid", SqlDbType.Int))
objcmd.Parameters("@depid").Value = id
objconn.Open()
Return objcmd.ExecuteReader()
objconn.Close()
objconn.Dispose()
Catch ex As SqlException
Return Nothing
End Try
End Function
......................
所有的过程我都是如上释放了连接,为什么还是会连接池满呢。解决就结分。谢谢
下面是我的WEBCONFIG,设置的
<appSetting>
<add key="sqlconn" value="server=(local);Pooling=true;Max Pool Size=300;Min Pool Size=0;uid=sa;pwd=;database=hr"></add>
</appSettings>........................
Return objcmd.ExecuteReader()
objconn.Close()
objconn.Dispose()
你已经调用 Return了,函数就已经返回了,所以后面关闭链接的语句得不到执行啊个人不推荐返回 DataReader,因为你返回DataReader的话,在这个函数里就不能关闭链接,否则你返回了也用不了。 最好返回 DataTable 或 其它集合对象 ,DataReader必须关联一个打开的链接。
objconn.open()
dim r as sqldatareader
r=objcmd.executereader()
objconn.close()
return r
这样就OK了,我本也怀疑这个问题,怪不得说sqldataAdapter是不是可以自动管理连接数!
谢谢,OK后就结
Try
objcmd = New SqlCommand("spUserbydep", objconn)
objcmd.CommandType = CommandType.StoredProcedure
objcmd.Parameters.Add(New SqlParameter("@depid", SqlDbType.Int))
objcmd.Parameters("@depid").Value = id
objconn.Open()
Return objcmd.ExecuteReader()
Catch ex As SqlException
Return Nothing
Finally
objconn.Close()
objconn.Dispose()
End Try
善用Fianlly
objconn.Dispose() 根本就没有执行。
用dataadapter
objconn.open()
dim r as sqldatareader
r=objcmd.executereader()
objconn.close()
return r
这样结果好像可以了,但实际上如果r=objcmd.executereader()发生错误,你的objconn.close()还是得不到执行!所以你连接池的问题同样会存在。所以还是要在错误捕捉里面去关闭数据库连接。
用DATAREADER只是看说明说性能会高一点。所以....
没办法我只好在每个页面中调用 **.closeConn
在.vb中写了个函数
public function closeconn()
objconn.dispose()
objconn.close()
end function
再次感谢。结贴