Help with ADO.Net Interfaces

[Matthew Holton]

Hello everyone,

I posted this question in the ADONet NG and havent gotten a response. I am
hoping that one of you have a solution.

Thanks in advance

I was working on creating a data library to use throughout my applications,
when I came across an odd behavior. I encounter “Cannot Open Any More
Tables.� After using the OleDBCommand.ExecuteNonQuery method. I have
included code that recreates the exception. I cannot recreate this error
with the SQLCommand.ExecuteNonQuery method.

The problem only occurs when I first declare my connection object as the
base object IDBConnection. If I use OLEDBConnection from the start, then I
am fine.
Any Ideas?
Thanks
Private Structure Record
Dim Rank As Long
Dim BoyName As String
Dim BoyChosen As Long
Dim GirlName As String
Dim GirlChosen As Long
End Structure

Private aNames As ArrayList

Private Sub LoadData()

'Dim pobjTest As ATG.DataLayer.BaseConnection
Dim pobjConnection As System.Data.IDbConnection
pobjConnection = New System.Data.OleDb.OleDbConnection

ReadNames()

'pobjTest = New
ATG.DataLayer.OLEData("Provider=Microsoft.Jet.OLED B.4.0;User ID=Admin;Data
Source=C:\Documents and Settings\matt\My Documents\TimeKeeper.mdb;Mode=Share
Deny None;")
'pobjTest.Connect()

pobjConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User
ID=Admin;Data Source=C:\Documents and Settings\matt\My
Documents\TimeKeeper.mdb;Mode=Share Deny None;"
pobjConnection.Open()

'pobjTest.BeginTrans()

For Each oRec As Record In aNames
ExecuteSQL("INSERT INTO [NAMES] (Rank, BoyName, BoyChosen, GirlName,
GirlChosen) VALUES('" & oRec.Rank.ToString.Replace("'", "''") & "','" &
oRec.BoyName.Replace("'", "''") & "'," & oRec.BoyChosen.ToString.Replace("'",
"''") & ",'" & oRec.GirlName.Replace("'", "''") & "'," &
oRec.GirlChosen.ToString.Replace("'", "''") & ")", pobjConnection)
Next

'pobjTest.CommitTrans()

pobjConnection.Close()
pobjConnection = Nothing

End Sub
Private Function ExecuteSQL(ByVal SQL As String, ByVal pobjconnection As
System.Data.IDbConnection) As Long
' Executes a SQL Statement
' Checks for an active
' transaction and uses
' it.
' Returns the number of
' rows affected
'

Const Routine As String = "ExecuteSQL()"
Const pstrExceptionDescription As String = "There was an error executing
a SQL statement."
Dim cmd As OleDb.OleDbCommand
Dim iRet As Integer = -1

Try

'If Me.State = ConnectionState.dbOpenConnection Or Me.State =
ConnectionState.dbInTransaction Then

'we have an active connection
'Check to see if we are in a transaction
'If Me.plngState = ConnectionState.dbInTransaction Then
'cmd = New OleDb.OleDbCommand(SQL, pobjConnection, pobjTransaction)
'Else
cmd = New OleDb.OleDbCommand(SQL, pobjconnection)
'End If

'execute the statement
cmd.Prepare()
iRet = cmd.ExecuteNonQuery()

cmd = Nothing

'Else

'ThrowCloseStateError(Routine)

'End If

Catch ex As OleDbException

'There was a problem with the sql connection
'Set our state
'Me.plngState = ConnectionState.dbError
Debug.WriteLine(SQL)
MsgBox(ex.ToString)
'Throw New DataLayer.DataException(pstrExceptionDescription, ex, SQL)

Catch ex As Exception

'Some other error occured
Debug.WriteLine(SQL)
'Throw New DataLayer.DataException(pstrExceptionDescription, ex, SQL)

Finally

cmd = Nothing

End Try

Return iRet
End Function

Private Sub ReadNames()

Dim oRec As Record, Line As String
Dim aRec() As String
aNames = New ArrayList

Dim oFile As System.IO.StreamReader = New
System.IO.StreamReader("c:\2003 popular names.txt.csv")

Do

Line = oFile.ReadLine()

If Line Is Nothing Then Exit Do

aRec = Line.Split(",")
'Debug.WriteLine("Read:" & Line)

With oRec
.Rank = aRec(0)
.BoyName = aRec(1)
.BoyChosen = aRec(2)
.GirlName = aRec(3)
.GirlChosen = aRec(4)
End With

aNames.Add(oRec)

Loop Until Line Is Nothing

End Sub

[KraGiE]

Matthew,

I see the problem almost immediately. It's because you're not disposing the
Command object as well as the Connection object. Oledb usually connects to
Access databases, and Access has a very small scalability capability. Your
best bet is to actually wrap the ExecuteNonQuery() in the try/catch and
handle that portion. Furthermore, in the Finally, you're best off calling
the Dispose rather than Close. Dispose will close the connection, but reduce
the memory footprint.

Thing I noticed is a lot of the old vb6/asp style, and the biggest
difference that you have to become aware of is the memory management. Larger
objects in .Net won't be collected right away, and may keep hold of the
thread for hours at times. I believe if you dispose those command objects
that are spawned in your ExecuteSql function, this problem should be taken
care of.

Kay Lee
"Matthew Holton" wrote:

[Cor Ligthert]

KraGie,

Larger objects in .Net won't be collected right away, and may keep hold of
the
thread for hours at times.

You have probably a 4Gb computer and use only 10Mb in an hour.

I could not resist.

Cor

[W.G. Ryan eMVP]

Kragie - how would that account for the fact that it doesn't happen if he
uses the exact same code but declares the object as an OleDbConnection from
the onset instead of an IDbConnection? I agree with most of what you wrote,
but I don't see where the distinction is drawn betweeen

OleDbConnection Connection = new OleDbConnection()
vs IDbConnection Connection = new OleDbConnection -

Moroever why would not disposing of the Command object make any difference?
I could declare a trillion command objects, as long as my connection to the
db was closed - then that isn't going to have a bearing is it? Am I missing
something?

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"KraGiE" <Kr****@discussions.microsoft.com> wrote in message
news:15**********************************@microsof t.com...

Matthew,

I see the problem almost immediately. It's because you're not disposing the Command object as well as the Connection object. Oledb usually connects to Access databases, and Access has a very small scalability capability. Your best bet is to actually wrap the ExecuteNonQuery() in the try/catch and
handle that portion. Furthermore, in the Finally, you're best off calling
the Dispose rather than Close. Dispose will close the connection, but reduce the memory footprint.

Thing I noticed is a lot of the old vb6/asp style, and the biggest
difference that you have to become aware of is the memory management. Larger objects in .Net won't be collected right away, and may keep hold of the
thread for hours at times. I believe if you dispose those command objects
that are spawned in your ExecuteSql function, this problem should be taken
care of.

Kay Lee
"Matthew Holton" wrote:

[Matthew Holton]

Thanks guys, using KraGie's suggestions worked like a charm. My questions
now are: when is it appropriate to use Dispose()? Why doesn't setting the
object to nothing do the same thing when used as local scope? Doesn't Dispose
happen implicitly when using local scope and exiting a routine?

Thanks.

"W.G. Ryan eMVP" wrote:

Kragie - how would that account for the fact that it doesn't happen if he
uses the exact same code but declares the object as an OleDbConnection from
the onset instead of an IDbConnection? I agree with most of what you wrote,
but I don't see where the distinction is drawn betweeen

OleDbConnection Connection = new OleDbConnection()
vs IDbConnection Connection = new OleDbConnection -

Moroever why would not disposing of the Command object make any difference?
I could declare a trillion command objects, as long as my connection to the
db was closed - then that isn't going to have a bearing is it? Am I missing
something?

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"KraGiE" <Kr****@discussions.microsoft.com> wrote in message
news:15**********************************@microsof t.com...

Matthew,

I see the problem almost immediately. It's because you're not disposing

the

Command object as well as the Connection object. Oledb usually connects

to

Access databases, and Access has a very small scalability capability.

Your

best bet is to actually wrap the ExecuteNonQuery() in the try/catch and
handle that portion. Furthermore, in the Finally, you're best off calling
the Dispose rather than Close. Dispose will close the connection, but

reduce

the memory footprint.

Thing I noticed is a lot of the old vb6/asp style, and the biggest
difference that you have to become aware of is the memory management.

Larger

objects in .Net won't be collected right away, and may keep hold of the
thread for hours at times. I believe if you dispose those command objects
that are spawned in your ExecuteSql function, this problem should be taken
care of.

Kay Lee
"Matthew Holton" wrote:

[W.G. Ryan eMVP]

I don't know - but I'm darned curious. The thing that's so elusive is why
would the same code change just b/c of the declaration type? I guess I got
have some homework to do.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Matthew Holton" <Ma***********@discussions.microsoft.com> wrote in message
news:F6**********************************@microsof t.com...

Thanks guys, using KraGie's suggestions worked like a charm. My questions
now are: when is it appropriate to use Dispose()? Why doesn't setting the
object to nothing do the same thing when used as local scope? Doesn't Dispose happen implicitly when using local scope and exiting a routine?

Thanks.

"W.G. Ryan eMVP" wrote:

Kragie - how would that account for the fact that it doesn't happen if he uses the exact same code but declares the object as an OleDbConnection from the onset instead of an IDbConnection? I agree with most of what you wrote, but I don't see where the distinction is drawn betweeen

OleDbConnection Connection = new OleDbConnection()
vs IDbConnection Connection = new OleDbConnection -

Moroever why would not disposing of the Command object make any difference? I could declare a trillion command objects, as long as my connection to the db was closed - then that isn't going to have a bearing is it? Am I missing something?

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"KraGiE" <Kr****@discussions.microsoft.com> wrote in message
news:15**********************************@microsof t.com...

Matthew,

I see the problem almost immediately. It's because you're not disposing

the

Command object as well as the Connection object. Oledb usually

connects to

Access databases, and Access has a very small scalability capability.

Your

best bet is to actually wrap the ExecuteNonQuery() in the try/catch

and handle that portion. Furthermore, in the Finally, you're best off calling the Dispose rather than Close. Dispose will close the connection, but

reduce

the memory footprint.

Thing I noticed is a lot of the old vb6/asp style, and the biggest
difference that you have to become aware of is the memory management.

Larger

objects in .Net won't be collected right away, and may keep hold of the thread for hours at times. I believe if you dispose those command objects that are spawned in your ExecuteSql function, this problem should be taken care of.

Kay Lee
"Matthew Holton" wrote: