SQL Datareader problem

[Rykie]

I have an application that constantly needs to read and write data to a
SQL box. For most of my read transactions I use a datareader. I
compiled a class that has all my datareader commands in it etc.

This works well, but every now and then a bunch of errors occurs,
relating to either the connection not being initialized, or reader
already associated with existing connection.

Now I have done everything I could think of to get rid of these errors,
but they still happen. Please note that the application is heavily
used, and at any time there can be up to 70 users doing something on
the system that will use the same datareader command in the class.

Here is the code for the property that gets used the most, and also
errors the most in the class:

Public ReadOnly Property SQLSelectCommand(ByVal SQLSelectString
As String, ByVal ReturnTableName As String) As drTypes
Get
Try
dTypes = New drTypes(ReturnTableName)
If sqlCon.State = ConnectionState.Closed Or
sqlCon.State = ConnectionState.Broken Then
sqlCon.Open()
End If
sqlCom = New SqlCommand(SQLSelectString, sqlCon)
If Not sqlDR Is Nothing Then
If sqlDR.IsClosed = False Then
sqlDR.Close()
End If
End If
sqlDR = sqlCom.ExecuteReader()
dt = New DataTable(ReturnTableName)
Dim y As Integer = 0
While sqlDR.Read
Dim i As Integer = 0, z As Integer = 0
i = sqlDR.FieldCount
Do While y < i
If Not
dt.Columns.Contains(sqlDR.GetName(y)).Equals(True) Then
dt.Columns.Add(sqlDR.GetName(y),
sqlDR.GetFieldType(y))
Else
'*column already exits
dt.Columns.Add(sqlDR.GetName(y) & "_" &
y, sqlDR.GetFieldType(y))
End If
y += 1
Loop
dr = dt.NewRow()
Do While z < i
dr(z) = sqlDR(z)
z += 1
Loop
dt.Rows.Add(dr)
End While
sqlDR.Close()
If sqlCon.State = ConnectionState.Open Or
sqlCon.State = ConnectionState.Connecting Or sqlCon.State =
ConnectionState.Executing Or sqlCon.State = ConnectionState.Fetching
Then
sqlCon.Close()
sqlCon.Dispose()
End If
dTypes.dt = dt
Return dTypes
Catch ex As Exception
Throw New Exception("SQLSelectCommand: " &
ex.ToString() & " SQL: " & sqlCom.CommandText.ToString)
Finally
If Not sqlDR Is Nothing Then
If sqlDR.IsClosed = False Then
sqlDR.Close()
End If
End If
If sqlCon.State = ConnectionState.Open Or
sqlCon.State = ConnectionState.Connecting Or sqlCon.State =
ConnectionState.Executing Or sqlCon.State = ConnectionState.Fetching
Then
sqlCon.Close()
End If
sqlCon.Dispose()
End Try
End Get
End Property


PLEASE NOTE: The SQL Connection is initialized in the constructor for
the class, so the connection can never be nothing or according to me
un-initialized.
There are also a bunch of other properties in the same class that will
use the same connection.

Public Sub New()
sqlCon = New
SqlConnection(ConfigurationSettings.AppSettings("dbConnectionString"))
End Sub

Public Sub New(ByVal SqlConnectionString As String)
sqlCon = New SqlConnection(SqlConnectionString)
End Sub

Public Sub New(ByVal SqlConnectionString As String, ByVal
DefaultTableSize As Integer)
sqlCon = New SqlConnection(SqlConnectionString)
Dim ns(DefaultTableSize) As String
defSqlStrings = ns
sTbls = New sTables(DefaultTableSize, Me)
End Sub

 

[Cor Ligthert [MVP]]

Rykie,

Why do you make it yourself so difficult and do not create the object
completly new everytime you need it (you can put the connectionstring
somewhere global). OOP is build on this principle so don't assume that
creating of an object takes much time.

You than need only every time in your methods
sub/function whatever
dim conn as new sqlconnection(connectionstring)
conn.open
'do what you want to do
conn.close
end sub/function

Than you normally cannot have any pooling or whatever problem.

I hope this helps,

Cor

 

[Rykie]

Cor,

Thank you, I have taken that approach now, and I will see how it goes
and post back here my findings.

Rykie

 

[Rykie]

Thank you, that fixed it.