The problem is that I have a very complex product with differnt layers of code.
In the DAL layer, I am calling multiple functions several times for which always use FILL method of DATASET.
Following is the helper class which we have in DAL, to simply the function calling, based on MS Data access Block functionality. I know CoreLab also has a similar kind of Class available , but we have modified the functions to our need.
I am using the ExecuteRow function of this class, which is called multiple times, and after 5-6 iteration, this exception is thrown.
Generally I get this error when multiple Read Queiries are made, At this point the Application is single threaded. and as you can see connection is closed within these function calls.
Some times of I dont get this error, The application just simply hangs and nothing happens, no exceptions.
Code: Select all
Option Strict On
Imports System
Imports System.Configuration
Imports System.Collections
Imports CoreLab.MySql
Public Class MYSQLHelper
'Database connection strings
'Public Shared ReadOnly CONN_STRING As String = ConfigurationSettings.AppSettings("MySQLConnString1")
'// Hashtable to store cached parameters
'Private parmCache As Hashtable = Hashtable.Synchronized(New Hashtable)
'///
'/// Execute a SqlCommand (that returns no resultset) against the database specified in the connection string
'/// using the provided parameters.
'///
'///
'/// e.g.:
'/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
'///
'/// a valid connection string for a SqlConnection
'/// the CommandType (stored procedure, text, etc.)
'/// the stored procedure name or T-SQL command
'/// an array of SqlParamters used to execute the command
'/// an int representing the number of rows affected by the command
Public Shared Function ExecuteNonQuery(ByVal connString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As MySqlParameter()) As Integer
Dim cmd As MySqlCommand = New MySqlCommand
Dim conn As MySqlConnection = New MySqlConnection(connString)
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
Dim val As Integer = cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Return val
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw New Exception("ExecuteNonQuery Function", exx)
Finally 'Add this for finally closing the connection and destroying the command
conn.Close()
cmd = Nothing
cmdParms = Nothing
End Try
End Function
'///
'/// Execute a SqlCommand (that returns no resultset) against an existing database connection
'/// using the provided parameters.
'///
'///
'/// e.g.:
'/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
'///
'/// an existing database connection
'/// the CommandType (stored procedure, text, etc.)
'/// the stored procedure name or T-SQL command
'/// an array of SqlParamters used to execute the command
'/// an int representing the number of rows affected by the command
Public Shared Function ExecuteNonQuery(ByRef conn As MySqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As MySqlParameter()) As Integer
Dim cmd As MySqlCommand = New MySqlCommand
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
Dim parm As MySqlParameter
'For Each parm In cmdParms
' cmd.Parameters.Add(parm)
'Next
Dim val As Integer = cmd.ExecuteNonQuery()
cmd.Parameters.Clear()
Return val
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw exx
Finally
cmd = Nothing
End Try
End Function
' ///
'/// Execute a SqlCommand that returns a resultset against the database specified in the connection string
'/// using the provided parameters.
'///
'///
''/// e.g.:
'/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
'///
'/// a valid connection string for a SqlConnection
'/// the CommandType (stored procedure, text, etc.)
'/// the stored procedure name or T-SQL command
'/// an array of SqlParamters used to execute the command
'/// A SqlDataReader containing the results
Public Shared Function ExecuteReader(ByRef conn As CoreLab.MySql.MySqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, Optional ByVal cmdParms As CoreLab.MySql.MySqlParameter() = Nothing) As CoreLab.MySql.MySqlDataReader
Dim cmd As MySqlCommand = New MySqlCommand
'Dim conn As OleDbConnection = New OleDbConnection(connString)
' we use a try/catch here because if the method throws an exception we want to
' close the connection throw ex code, because no datareader will exist, hence the
' commandBehaviour.CloseConnection will not work
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
Dim rdr As MySqlDataReader = cmd.ExecuteReader()
'cmd.Parameters.Clear()
Return rdr
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw exx
Finally
cmd = Nothing
End Try
End Function
Public Shared Function ExecuteTable(ByVal connString As String, ByVal cmdType As CommandType, ByVal cmdText As String, Optional ByVal cmdParms As MySqlParameter() = Nothing) As DataTable
Dim cmd As MySqlCommand = New MySqlCommand
Dim conn As MySqlConnection = New MySqlConnection(connString)
Dim oDataAdapter As New MySqlDataAdapter
Dim oDataTable As New DataTable
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
'cmd.Parameters = cmdParms
oDataAdapter.SelectCommand = cmd
oDataAdapter.Fill(oDataTable)
cmd.Parameters.Clear()
Return oDataTable
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw New Exception("ExecuteTable Exception :", exx)
Finally
conn.Close()
cmd = Nothing
oDataAdapter = Nothing
End Try
End Function
Public Shared Function ExecuteTable(ByRef oConnection As MySqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, Optional ByVal cmdParms As MySqlParameter() = Nothing) As DataTable
Dim cmd As MySqlCommand = New MySqlCommand
Dim oDataAdapter As New MySqlDataAdapter
Dim oDataTable As New DataTable
Try
PrepareCommand(cmd, oConnection, cmdType, cmdText, cmdParms)
oDataAdapter.SelectCommand = cmd
oDataAdapter.Fill(oDataTable)
cmd.Parameters.Clear()
Return oDataTable
Catch ex As Exception
Throw New Exception("ExecuteTable", ex)
Finally
cmd.Dispose()
oDataAdapter.Dispose()
oDataTable.Dispose()
End Try
End Function
Public Shared Function ExecuteDataSet(ByVal connString As String, ByVal cmdType As CommandType, ByVal cmdText As String, Optional ByVal cmdParms As MySqlParameter() = Nothing) As DataSet
Dim cmd As MySqlCommand = New MySqlCommand
Dim conn As MySqlConnection = New MySqlConnection(connString)
Dim oDataAdapter As New MySqlDataAdapter
Dim oDataSet As New DataSet
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
oDataAdapter.SelectCommand = cmd
'cmd.Connection = conn
oDataAdapter.Fill(oDataSet)
cmd.Parameters.Clear()
Return oDataSet
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw New Exception("ExecuteDataSet", exx)
Finally
conn.Close()
cmd = Nothing
oDataAdapter = Nothing
End Try
End Function
Public Shared Function ExecuteRow(ByVal connString As String, ByVal cmdType As CommandType, ByVal cmdText As String, Optional ByVal cmdParms As MySqlParameter() = Nothing) As DataRow
Dim cmd As MySqlCommand = New MySqlCommand
Dim conn As MySqlConnection = New MySqlConnection(connString)
Dim oDataAdapter As New MySqlDataAdapter
Dim oDataRow As DataRow
Dim oDataTable As New DataTable
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
oDataAdapter.SelectCommand = cmd
oDataAdapter.Fill(oDataTable)
cmd.Parameters.Clear()
If oDataTable.Rows.Count = 0 Then
Return Nothing
Else
Dim oRow As DataRow = oDataTable.Rows(0)
Return oRow
End If
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw New Exception("ExecuteRow", exx)
Finally
conn.Close()
oDataTable = Nothing
cmd = Nothing
oDataAdapter = Nothing
End Try
End Function
Public Shared Function ExecuteRow(ByRef oConnection As MySqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, Optional ByVal cmdParms As MySqlParameter() = Nothing) As DataRow
Dim cmd As MySqlCommand = New MySqlCommand
Dim conn As MySqlConnection = oConnection
Dim oDataAdapter As New MySqlDataAdapter
Dim oDataRow As DataRow
Dim oDataTable As New DataTable
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
oDataAdapter.SelectCommand = cmd
oDataAdapter.Fill(oDataTable)
cmd.Parameters.Clear()
If oDataTable.Rows.Count = 0 Then
Return Nothing
Else
Dim oRow As DataRow = oDataTable.Rows(0)
Return oRow
End If
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw New Exception("ExeculateScalar", exx)
Finally
oDataTable = Nothing
cmd = Nothing
oDataAdapter = Nothing
End Try
End Function
'///
'/// Execute a SqlCommand that returns the first column of the first record against the database specified in the connection string
'/// using the provided parameters.
'///
'///
'/// e.g.:
'/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
'///
'/// a valid connection string for a SqlConnection
'/// the CommandType (stored procedure, text, etc.)
'/// the stored procedure name or T-SQL command
'/// an array of SqlParamters used to execute the command
'/// An object that should be converted to the expected type using Convert.To{Type}
Public Shared Function ExecuteScalar(ByVal connString As String, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As MySqlParameter()) As Object
Dim cmd As MySqlCommand = New MySqlCommand
Dim conn As MySqlConnection = New MySqlConnection(connString)
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
Dim val As Object = cmd.ExecuteScalar()
cmd.Parameters.Clear()
Return val
Catch ex As MySqlException
Throw New Exception("Corelab Exception ", ex)
Catch exx As Exception
Throw New Exception("ExeculateScalar", exx)
Finally
conn.Close()
conn = Nothing
cmd = Nothing
End Try
End Function
'///
'/// Execute a SqlCommand that returns the first column of the first record against an existing database connection
'/// using the provided parameters.
'///
'///
'/// e.g.:
'/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
'///
'/// an existing database connection
'/// the CommandType (stored procedure, text, etc.)
'/// the stored procedure name or T-SQL command
'/// an array of SqlParamters used to execute the command
'/// An object that should be converted to the expected type using Convert.To{Type}
Public Shared Function ExecuteScalar(ByRef conn As MySqlConnection, ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As MySqlParameter()) As Object
Dim cmd As MySqlCommand = New MySqlCommand
Try
PrepareCommand(cmd, conn, cmdType, cmdText, cmdParms)
Dim val As Object = cmd.ExecuteScalar()
cmd.Parameters.Clear()
Return val
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw New Exception("ExeculateScalar", exx)
Finally
cmd = Nothing
End Try
End Function
'///
'/// add parameter array to the cache
'///
'/// Key to the parameter cache
'/// an array of SqlParamters to be cached
'Public Function CacheParameters(ByVal cacheKey As String, ByVal cmdParms As SqlParameter())
' parmCache(cacheKey) = cmdParms
'End Function
'///
'/// Retrieve cached parameters
'///
'/// key used to lookup parameters
'/// Cached SqlParamters array
'Public Function GetCachedParameters(ByVal cacheKey As String) As OleDbParameter()
' Dim cachedParms As OleDbParameter() = parmCache(cacheKey)
' If IsNothing(cachedParms) Then
' Return Nothing
' End If
' Dim clonedParms() As OleDbParameter = New OleDbParameter("abc", cachedParms.Length)
' Dim i As Integer
' Dim j As Integer = cachedParms.Length
' For i = 0 To j
'/// Prepare a command for execution
'///
'/// SqlCommand object
'/// SqlConnection object
'/// SqlTransaction object
'/// Cmd type e.g. stored procedure or text
'/// Command text, e.g. Select * from Products
'/// SqlParameters to use in the command
Public Shared Function PrepareCommand(ByRef cmd As MySqlCommand, ByRef conn As MySqlConnection, ByRef cmdType As CommandType, ByRef cmdText As String, ByRef cmdParms As MySqlParameter()) As Boolean
If Not conn.State = ConnectionState.Open Then
conn.Open()
End If
Try
cmd.Connection = conn
cmd.CommandText = cmdText
cmd.Parameters.Clear()
cmd.ParameterCheck = True
cmd.CommandType = cmdType
If Not (IsNothing(cmdParms)) Then
Dim parm As MySqlParameter
For Each parm In cmdParms
cmd.Parameters.Add(parm)
Next
End If
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw New Exception("PrepareCommand : ", exx)
End Try
End Function
Public Shared Function ExcuteAdapter(ByVal connString As String, ByVal oTable As DataTable, ByVal cmdText As String, Optional ByRef lngMaxID As Long = 0) As Boolean
Dim conn As MySqlConnection
Dim oDataAdapter As New MySqlDataAdapter
Dim oSqlCmd As New MySqlCommand
Dim oCmdBuilder As MySqlCommandBuilder
Try
conn = New MySqlConnection(connString)
If Not conn.State = ConnectionState.Open Then
conn.Open()
End If
oSqlCmd.Connection = conn
oSqlCmd.CommandText = cmdText
oSqlCmd.CommandType = CommandType.Text
oDataAdapter.SelectCommand = oSqlCmd
oCmdBuilder = New MySqlCommandBuilder(oDataAdapter)
oCmdBuilder.GetUpdateCommand()
oCmdBuilder.GetInsertCommand()
oCmdBuilder.GetDeleteCommand()
oDataAdapter.Update(oTable)
oDataAdapter.SelectCommand = New MySqlCommand("SELECT @@IDENTITY", conn)
lngMaxID = CType(oDataAdapter.SelectCommand.ExecuteScalar(), Long)
Catch ex As MySqlException
Throw New Exception("CoreLab Exception ", ex)
Catch exx As Exception
Throw New Exception("ExeculateAdapter", exx)
Finally
If conn.State = ConnectionState.Open Then conn.Close()
oSqlCmd = Nothing
oDataAdapter = Nothing
oCmdBuilder = Nothing
End Try
End Function
'''
''' return table Schema
'''
'''
'''
'''
'''
Public Shared Function FillSchema(ByVal connString As String, ByVal cmdText As String, ByVal strTableName As String) As DataTable
Dim conn As MySqlConnection
Dim oDataAdapter As MySqlDataAdapter
Dim oCmdBuilder As MySqlCommandBuilder
Dim oDataTable As New DataTable
Try
conn = New MySqlConnection(connString)
If Not conn.State = ConnectionState.Open Then
conn.Open()
End If
oDataAdapter = New MySqlDataAdapter(cmdText, conn)
oCmdBuilder = New MySqlCommandBuilder(oDataAdapter)
oDataAdapter.FillSchema(oDataTable, SchemaType.Source)
oDataTable.TableName = strTableName
Return oDataTable
Catch ex As Exception
Throw ex
End Try
End Function
End Class