连接池的问题 DR 用READ的时候记得写CLOSE 初始化DR的时候记得用自动句柄来关闭连接 datareader = command.executeReader(CommandBehavior.CloseConnection)
Public Shared Function myReader(ByVal strSql As String) As Data.SqlClient.SqlDataReader Dim db As Database = DatabaseFactory.CreateDatabase() Dim sqlCommand As String = strSql Dim dbCommand As Data.Common.DbCommand = db.GetSqlStringCommand(sqlCommand) Dim dataReader As Data.SqlClient.SqlDataReader = db.ExecuteReader(dbCommand) GetSql = dataReader End Function程序里每次用myReader1=myReader("") myReader1.close()
最起码应该这样: Try objConn.Open() For i As Integer = 0 To 10 Dim strSql As String = "..." objComm.CommandText = strSql Dim dr As SqlDataReader = objComm.ExecuteReader() While dr.Read() '... End While dr.Close() '这个是关键! Next Catch ex As Exception '... Finally If Not objConn Is Nothing Then objConn.Close() End If End Try
Public Shared Function myReader(ByVal strSql As String) As Data.SqlClient.SqlDataReader Dim db As Database = DatabaseFactory.CreateDatabase() Dim sqlCommand As String = strSql Dim dbCommand As Data.Common.DbCommand = db.GetSqlStringCommand(sqlCommand) Dim dataReader As Data.SqlClient.SqlDataReader = db.ExecuteReader(dbCommand) GetSql = dataReader End Function.这里的GetSql没声明??改为 myReader = dataReader
不好意思,还是有一些不明白。现在我把代码改成。 Dim db As Database = DatabaseFactory.CreateDatabase() Dim sqlCommand As String Dim dbCommand As Data.Common.DbCommand Dim dataReader As Data.SqlClient.SqlDataReader For i = 0 To mm - 1 sqlCommand = "SELECT ..." dbCommand = db.GetSqlStringCommand(sqlCommand) dataReader = db.ExecuteReader(dbCommand) While dataReader.Read ... End While dataReader.Close() Next还是不可以。我也觉得是连接太多的问题。报措是: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
While dataReader.Read ... dataReader.Close() End While
Dim db As Database = DatabaseFactory.CreateDatabase() Dim sqlCommand As String Dim dbCommand As Data.Common.DbCommand Dim dataReader As Data.SqlClient.SqlDataReader For i = 0 To mm - 1 try sqlCommand = "SELECT ..." dbCommand = db.GetSqlStringCommand(sqlCommand) dataReader = db.ExecuteReader(dbCommand) While dataReader.Read ... End While dataReader.Close() catch ex as SqlException finally if not dataRreader is nothing then dr.close(); end ifend try Next
beiouwolf(beiouwolf)大哥说的不可能吧。 按dbx()大哥说的,我已经用了dataReader.Close(), finally if not dataRreader is nothing then dr.close(); end if 有必要吗?dr.close(),是不是我的dataReader.Close()?
现在我怀疑是不是我用企业库的问题。 Dim db As Database = DatabaseFactory.CreateDatabase()
我又试了一下改成: Dim myConnection As New SqlConnection(myConnectStr) myConnection.Open() Dim myCommand As New SqlCommand myCommand.Connection = myConnection For i = 0 To mm - 1 myCommand.CommandText = "SELECT ..." Dim dataReader As SqlDataReader = myCommand.ExecuteReader() While dataReader.Read ... End While dataReader.Close() Next 问题依旧。把Dim dataReader As SqlDataReader = myCommand.ExecuteReader()改成 Dim dataReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)。在循环到第二次时报错,已经关闭。所以我觉得用dataReader.Close()并没有真正关闭连接。用句柄到是关上了,可是不会再次打开。
按dbx()大哥说的,我已经用了dataReader.Close(), finally if not dataRreader is nothing then dr.close(); end if 有必要吗?dr.close(),是不是我的dataReader.Close()?是你的dataReader.Close(),我写成dr了。不好意思。
finally if not dataRreader is nothing then dataRreader.close(); end if 当你出现异常的时候。。finally里面的操作就很有用了
瀑布汗啊.... 没看到我写的吗 你吧CLOSE写在循环外面有什么用啊..... While dataReader.Read ... dataReader.Close() End While
不回吧。写在循环里面是什么意思?dataReader还没读完,每读一行关闭一次?
Dim myConnection As New SqlConnection(myConnectStr) myConnection.Open() Dim myCommand As New SqlCommand myCommand.Connection = myConnection For i = 0 To mm - 1 myCommand.CommandText = "SELECT ..." Dim dataReader As SqlDataReader = myCommand.ExecuteReader() While dataReader.Read ... End While dataReader.Close() Next 我觉得这样写没有问题了。最多再优化一下,在循环里只使用一个DataReader对象: Dim myConnection As New SqlConnection(myConnectStr) myConnection.Open() Dim myCommand As New SqlCommand myCommand.Connection = myConnection Dim dataReader As SqlDataReader = Nothing ' 在循环外声明 For i = 0 To mm - 1 myCommand.CommandText = "SELECT ..." dataReader = myCommand.ExecuteReader() While dataReader.Read() ... End While dataReader.Close() Next最后一定要关闭 Connection 对象,如果这样还不好使,那么关掉数据库连接池试试。
Public Shared Function myReader(ByVal strSql As String) As Data.SqlClient.SqlDataReader Dim db As Database = DatabaseFactory.CreateDatabase() Dim sqlCommand As String = strSql Dim dbCommand As Data.Common.DbCommand = db.GetSqlStringCommand(sqlCommand) Dim dataReader As Data.SqlClient.SqlDataReader = db.ExecuteReader(CommandBehavior.CloseConnection) myReader = dataReader End Function程序里每次用myReader1=myReader("") myReader1.close()
The following example creates a SqlConnection, a SqlCommand, and a SqlDataReader. The example reads through the data, writing it out to the console window. The code then closes the SqlDataReader. The SqlConnection is closed automatically at the end of the using code block.Visual Basic Copy Code Private Sub ReadOrderData(ByVal connectionString As String) Dim queryString As String = _ "SELECT OrderID, CustomerID FROM dbo.Orders;" Using connection As New SqlConnection(connectionString) Dim command As New SqlCommand(queryString, connection) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() ' Call Read before accessing data. While reader.Read() Console.WriteLine(String.Format("{0}, {1}", _ reader(0), reader(1))) End While ' Call Close when done reading. reader.Close() End Using End Sub这是msdn中的例子代码,照这么看,reader.Close()是不一定会关闭连接的。(我印象中是保持read打开前的状态) “The SqlConnection is closed automatically at the end of the using code block.”
You must explicitly call the Close method when you are through using the SqlDataReader to use the associated SqlConnection for any other purpose.The Close method fills in the values for output parameters, return values and RecordsAffected, increasing the time that it takes to close a SqlDataReader that was used to process a large or complex query. When the return values and the number of records affected by a query are not significant, the time that it takes to close the SqlDataReader can be reduced by calling the Cancel method of the associated SqlCommand object before calling the Close method.照这个描述,Close并不保证一定会关闭连接。
你检查下代码,调用一个datareader就要在其后datareader.close()!
DR 用READ的时候记得写CLOSE
初始化DR的时候记得用自动句柄来关闭连接
datareader = command.executeReader(CommandBehavior.CloseConnection)
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim sqlCommand As String = strSql
Dim dbCommand As Data.Common.DbCommand = db.GetSqlStringCommand(sqlCommand)
Dim dataReader As Data.SqlClient.SqlDataReader = db.ExecuteReader(dbCommand)
GetSql = dataReader
End Function程序里每次用myReader1=myReader("")
myReader1.close()
datareader = command.executeReader(CommandBehavior.CloseConnection)
应该写在那里哪?
Try
objConn.Open()
For i As Integer = 0 To 10
Dim strSql As String = "..."
objComm.CommandText = strSql
Dim dr As SqlDataReader = objComm.ExecuteReader()
While dr.Read()
'...
End While
dr.Close() '这个是关键!
Next
Catch ex As Exception
'...
Finally
If Not objConn Is Nothing Then
objConn.Close()
End If
End Try
应该写在那里哪?应该写在db.ExecuteReader(dbCommand)里
{
代码写在这里,所有的循环用这一个连接
}
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim sqlCommand As String = strSql
Dim dbCommand As Data.Common.DbCommand = db.GetSqlStringCommand(sqlCommand)
Dim dataReader As Data.SqlClient.SqlDataReader = db.ExecuteReader(dbCommand)
GetSql = dataReader
End Function.这里的GetSql没声明??改为
myReader = dataReader
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim sqlCommand As String
Dim dbCommand As Data.Common.DbCommand
Dim dataReader As Data.SqlClient.SqlDataReader For i = 0 To mm - 1
sqlCommand = "SELECT ..."
dbCommand = db.GetSqlStringCommand(sqlCommand)
dataReader = db.ExecuteReader(dbCommand)
While dataReader.Read
...
End While
dataReader.Close()
Next还是不可以。我也觉得是连接太多的问题。报措是:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
...
dataReader.Close()
End While
Dim sqlCommand As String
Dim dbCommand As Data.Common.DbCommand
Dim dataReader As Data.SqlClient.SqlDataReader For i = 0 To mm - 1
try
sqlCommand = "SELECT ..."
dbCommand = db.GetSqlStringCommand(sqlCommand)
dataReader = db.ExecuteReader(dbCommand)
While dataReader.Read
...
End While
dataReader.Close()
catch ex as SqlException finally
if not dataRreader is nothing then
dr.close();
end ifend try
Next
按dbx()大哥说的,我已经用了dataReader.Close(),
finally
if not dataRreader is nothing then
dr.close();
end if
有必要吗?dr.close(),是不是我的dataReader.Close()?
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim myConnection As New SqlConnection(myConnectStr)
myConnection.Open()
Dim myCommand As New SqlCommand
myCommand.Connection = myConnection
For i = 0 To mm - 1
myCommand.CommandText = "SELECT ..."
Dim dataReader As SqlDataReader = myCommand.ExecuteReader()
While dataReader.Read
...
End While
dataReader.Close()
Next
问题依旧。把Dim dataReader As SqlDataReader = myCommand.ExecuteReader()改成
Dim dataReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)。在循环到第二次时报错,已经关闭。所以我觉得用dataReader.Close()并没有真正关闭连接。用句柄到是关上了,可是不会再次打开。
finally
if not dataRreader is nothing then
dr.close();
end if
有必要吗?dr.close(),是不是我的dataReader.Close()?是你的dataReader.Close(),我写成dr了。不好意思。
if not dataRreader is nothing then
dataRreader.close();
end if
当你出现异常的时候。。finally里面的操作就很有用了
没看到我写的吗
你吧CLOSE写在循环外面有什么用啊.....
While dataReader.Read
...
dataReader.Close()
End While
myConnection.Open()
Dim myCommand As New SqlCommand
myCommand.Connection = myConnection
For i = 0 To mm - 1
myCommand.CommandText = "SELECT ..."
Dim dataReader As SqlDataReader = myCommand.ExecuteReader()
While dataReader.Read
...
End While
dataReader.Close()
Next
我觉得这样写没有问题了。最多再优化一下,在循环里只使用一个DataReader对象: Dim myConnection As New SqlConnection(myConnectStr)
myConnection.Open()
Dim myCommand As New SqlCommand
myCommand.Connection = myConnection
Dim dataReader As SqlDataReader = Nothing ' 在循环外声明
For i = 0 To mm - 1
myCommand.CommandText = "SELECT ..."
dataReader = myCommand.ExecuteReader()
While dataReader.Read()
...
End While
dataReader.Close()
Next最后一定要关闭 Connection 对象,如果这样还不好使,那么关掉数据库连接池试试。
Dim db As Database = DatabaseFactory.CreateDatabase()
Dim sqlCommand As String = strSql
Dim dbCommand As Data.Common.DbCommand = db.GetSqlStringCommand(sqlCommand)
Dim dataReader As Data.SqlClient.SqlDataReader = db.ExecuteReader(CommandBehavior.CloseConnection)
myReader = dataReader
End Function程序里每次用myReader1=myReader("")
myReader1.close()
Private Sub ReadOrderData(ByVal connectionString As String)
Dim queryString As String = _
"SELECT OrderID, CustomerID FROM dbo.Orders;" Using connection As New SqlConnection(connectionString)
Dim command As New SqlCommand(queryString, connection)
connection.Open() Dim reader As SqlDataReader = command.ExecuteReader() ' Call Read before accessing data.
While reader.Read()
Console.WriteLine(String.Format("{0}, {1}", _
reader(0), reader(1)))
End While ' Call Close when done reading.
reader.Close()
End Using
End Sub这是msdn中的例子代码,照这么看,reader.Close()是不一定会关闭连接的。(我印象中是保持read打开前的状态) “The SqlConnection is closed automatically at the end of the using code block.”
DR 用READ的时候记得写CLOSE
初始化DR的时候记得用自动句柄来关闭连接
datareader = command.executeReader(CommandBehavior.CloseConnection)
對頭