每一个OPEN后我都Close了呀,下面是我的类的代码:Imports System.Data Imports System.Data.SqlClient Imports System.ConfigurationPublic Class DBServer Dim gConn As New SqlConnection '******************************************************************************* '作用: 连接SQL Server数据库 '******************************************************************************* Public Overloads Function ConnSQLServer() Dim gConnString As String gConnString = ConfigurationSettings.AppSettings("ConnectionString") gConn.ConnectionString = gConnString gConn.Open() End Function '******************************************************************************* '作用: 关闭SQL Server数据库 '******************************************************************************* Public Overloads Function Close() gConn.Close() End Function '******************************************************************************* '作用:连接表 '参数:TableName表名 '返回:查询访问 '******************************************************************************* Public Overloads Function GetDataReader(ByVal TableName As String) As SqlDataReader Dim strSql As String Dim mySqlCommand As New SqlCommand Dim myReader As SqlDataReader strSql = "SELECT * FROM " & TableName mySqlCommand.Connection = gConn mySqlCommand.CommandText = strSql myReader = mySqlCommand.ExecuteReader() Return myReader End Function '******************************************************************************* '作用:连接表 '参数:TableName表名,strWhere条件 '返回:查询访问 '******************************************************************************* Public Overloads Function GetDataReader(ByVal TableName As String, ByVal strWhere As String) As SqlDataReader Dim strSql As String Dim mySqlCommand As New SqlCommand Dim myReader As SqlDataReader strSql = "SELECT * FROM " & TableName & " WHERE " & strWhere mySqlCommand.Connection = gConn mySqlCommand.CommandText = strSql myReader = mySqlCommand.ExecuteReader() Return myReader End Function '******************************************************************************* '作用:连接表 '参数:TableName表名 '返回:数据集 '******************************************************************************* Public Overloads Function GetDataSet(ByVal TableName As String) As DataSet Dim strSql As String Dim myDataSet As New DataSet Dim mySqlDataAdapter As New SqlDataAdapter strSql = "SELECT * FROM " & TableName mySqlDataAdapter.SelectCommand = New SqlCommand(strSql, gConn) mySqlDataAdapter.Fill(myDataSet) Return myDataSet End Function '******************************************************************************* '作用:连接表 '参数:TableName表名,strWhere条件 '返回:数据集 '******************************************************************************* Public Overloads Function GetDataSet(ByVal TableName As String, ByVal strWhere As String) As DataSet Dim strSql As String Dim mySqlCommand As New SqlCommand Dim myDataSet As New DataSet Dim mySqlDataAdapter As New SqlDataAdapter mySqlDataAdapter.TableMappings.Add(TableName, TableName) strSql = "SELECT * FROM " & TableName & " WHERE " & strWhere mySqlCommand.Connection = gConn mySqlCommand.CommandText = strSql mySqlDataAdapter.SelectCommand = mySqlCommand mySqlDataAdapter.Fill(myDataSet) Return myDataSet End Function '******************************************************************************* '作用:连接多表 '参数:strSQL 查询语句 '返回:数据集 '******************************************************************************* Public Overloads Function SQLDataSet(ByVal strSQL As String) As DataSet Dim myDataSet As New DataSet Dim mySqlDataAdapter As New SqlDataAdapter mySqlDataAdapter.SelectCommand = New SqlCommand(strSQL, gConn) mySqlDataAdapter.Fill(myDataSet) Return myDataSet End Function '******************************************************************************* '作用:连接表 '参数:TableName表名 '返回:数据表 '******************************************************************************* Public Overloads Function GetDataTable(ByVal TableName As String) As DataTable Dim myDataSet As New DataSet myDataSet = GetDataSet(TableName) Return myDataSet.Tables(0) End Function '******************************************************************************* '作用:连接表 '参数:TableName表名 '返回:数据表 '******************************************************************************* Public Overloads Function GetDataTable(ByVal TableName As String, ByVal strWhere As String) As DataTable Dim myDataSet As New DataSet myDataSet = GetDataSet(TableName, strWhere) Return myDataSet.Tables(0) End FunctionEnd Class
跟踪一下,看看SQL语句是否可以在查询分析器中运行
有呀,有一个Close成员函数: '作用: 关闭SQL Server数据库 '******************************************************************************* Public Overloads Function Close() gConn.Close() End Function
数据连接语句加: Data Source=****;Pooling=true;Min Pool Size=0;Max Pool Size=300;packet size=4096;Initial Catalog=love;User ID=love;Pwd=**;";"Min Pool Size=0;Max Pool Size=300;"扩大链接池. 很管用!
Imports System.Data.SqlClient
Imports System.ConfigurationPublic Class DBServer Dim gConn As New SqlConnection
'*******************************************************************************
'作用: 连接SQL Server数据库
'*******************************************************************************
Public Overloads Function ConnSQLServer() Dim gConnString As String
gConnString = ConfigurationSettings.AppSettings("ConnectionString")
gConn.ConnectionString = gConnString
gConn.Open() End Function
'*******************************************************************************
'作用: 关闭SQL Server数据库
'*******************************************************************************
Public Overloads Function Close() gConn.Close() End Function
'*******************************************************************************
'作用:连接表
'参数:TableName表名
'返回:查询访问
'*******************************************************************************
Public Overloads Function GetDataReader(ByVal TableName As String) As SqlDataReader Dim strSql As String Dim mySqlCommand As New SqlCommand Dim myReader As SqlDataReader strSql = "SELECT * FROM " & TableName mySqlCommand.Connection = gConn mySqlCommand.CommandText = strSql myReader = mySqlCommand.ExecuteReader() Return myReader End Function
'*******************************************************************************
'作用:连接表
'参数:TableName表名,strWhere条件
'返回:查询访问
'*******************************************************************************
Public Overloads Function GetDataReader(ByVal TableName As String, ByVal strWhere As String) As SqlDataReader Dim strSql As String Dim mySqlCommand As New SqlCommand Dim myReader As SqlDataReader strSql = "SELECT * FROM " & TableName & " WHERE " & strWhere mySqlCommand.Connection = gConn mySqlCommand.CommandText = strSql myReader = mySqlCommand.ExecuteReader() Return myReader End Function '*******************************************************************************
'作用:连接表
'参数:TableName表名
'返回:数据集
'*******************************************************************************
Public Overloads Function GetDataSet(ByVal TableName As String) As DataSet Dim strSql As String Dim myDataSet As New DataSet Dim mySqlDataAdapter As New SqlDataAdapter strSql = "SELECT * FROM " & TableName mySqlDataAdapter.SelectCommand = New SqlCommand(strSql, gConn) mySqlDataAdapter.Fill(myDataSet) Return myDataSet End Function '*******************************************************************************
'作用:连接表
'参数:TableName表名,strWhere条件
'返回:数据集
'*******************************************************************************
Public Overloads Function GetDataSet(ByVal TableName As String, ByVal strWhere As String) As DataSet Dim strSql As String Dim mySqlCommand As New SqlCommand Dim myDataSet As New DataSet Dim mySqlDataAdapter As New SqlDataAdapter mySqlDataAdapter.TableMappings.Add(TableName, TableName) strSql = "SELECT * FROM " & TableName & " WHERE " & strWhere mySqlCommand.Connection = gConn mySqlCommand.CommandText = strSql mySqlDataAdapter.SelectCommand = mySqlCommand mySqlDataAdapter.Fill(myDataSet) Return myDataSet End Function '*******************************************************************************
'作用:连接多表
'参数:strSQL 查询语句
'返回:数据集
'*******************************************************************************
Public Overloads Function SQLDataSet(ByVal strSQL As String) As DataSet Dim myDataSet As New DataSet Dim mySqlDataAdapter As New SqlDataAdapter mySqlDataAdapter.SelectCommand = New SqlCommand(strSQL, gConn) mySqlDataAdapter.Fill(myDataSet) Return myDataSet End Function '*******************************************************************************
'作用:连接表
'参数:TableName表名
'返回:数据表
'*******************************************************************************
Public Overloads Function GetDataTable(ByVal TableName As String) As DataTable Dim myDataSet As New DataSet myDataSet = GetDataSet(TableName) Return myDataSet.Tables(0) End Function '*******************************************************************************
'作用:连接表
'参数:TableName表名
'返回:数据表
'*******************************************************************************
Public Overloads Function GetDataTable(ByVal TableName As String, ByVal strWhere As String) As DataTable Dim myDataSet As New DataSet myDataSet = GetDataSet(TableName, strWhere) Return myDataSet.Tables(0) End FunctionEnd Class
'*******************************************************************************
Public Overloads Function Close() gConn.Close() End Function
try
{
//数据操作部分的代码
}
catch()
{
//处理错误
}
finally
{
conn.Close(); //关闭操作放在这
}
比如第一次你重新NEW一个gconn
连接上了,取值
等你去关闭的时候,你又重新new了一个gconn然后关闭的
在有你一步一步调试一下看看取值的时候怎么处理的
overloads可以不用的
是的,我现在每建立一个DBServer,都会要NEW一次,是不是有什么办法可以获得以前NEW的连接呢?
开发环境:
服务器A 开发机B ,C ,D ,E
在B机上经常出现数据库连接超时,而C,D,E就没有这个问题,后面反复测试,发现和A机的IIS有关系,重新安装IIS,问题解决...不知道这个对你有没有帮助
Data Source=****;Pooling=true;Min Pool Size=0;Max Pool Size=300;packet size=4096;Initial Catalog=love;User ID=love;Pwd=**;";"Min Pool Size=0;Max Pool Size=300;"扩大链接池.
很管用!
怎么看呢?在企业管理器的哪里看呢
第一,使用DataAdapter填充DataSet或DataTable是,不必用conn.Open()这样的方式来打开数据库,因为DataAdapter会在调用它的Fill方法时自动打开数据库,在把数据填充到DataSet或DataTable后立即自动关闭,你调用conn.Open()来打开数据库是多余的。
第二,没有看到你如何调用其中那几个返回DataReader的函数的,所以也无法知道你是如何关闭数据库连接的,一个返回DataReader的函数,应该是这样写:public static SqlDataReader GetDataReader(string parm1,string parm2){
SqlConnection conn=....
SqlCommand cmd=....
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}调用时这样:
SqlDataReader reader=MyClass.GetDataReader("参数1","参数2");
//在这里绑定数据或取值
reader.Close();OK