Memory use and retention

[Staticbob]

Hi,

I have an app that is interfacing with a large SQL dbase. I have generated a
database class to house all of the Data adapter and SQL commands, I have
posted example code below.

I can now call the required table by using . . .

db.CVI.fill(dt, criteria)

Or use the Table function like . .

db.CVI.table(CurrentContract.id)

Although this works very well, I am now seeing issues with memory use and
retention. I have tested my app for 15mins visiting most screens and have
seen memory use (VM) of 65 - 75 MB.

I understood that variables and objects are disposed of when they go out of
scope, and are then picked up by the garbage collector, but will this also
apply for any objects I create from my db class ? I seem to be keeping hold
of these datatables for the remainder or the session, until the user closes
the app.

Any pointers on this much appreciated !

Cheers
Bob

Example db class . . .

[VBCODE]
Class db
Class CVI
Private Shared mda As SqlDataAdapter

Friend Shared Function table(ByVal Contract As String) As DataTable
Dim dt As New DataTable("CVI")
Fill(dt, CurrentContract.ID) '***
Return dt '***
End Function

Friend Shared Function NextCVI(ByVal Contract As String) As Integer
Dim cmd As New SqlCommand("SELECT Max(Number) FROM CVI WHERE
Contract='" & Contract & "'", cnn)
Dim bOpen As Boolean = cnn.State = ConnectionState.Open
Try
If Not bOpen Then cnn.Open()
Dim nRtn As Object = cmd.ExecuteScalar
If IsNumeric(nRtn) Then
Return CInt(nRtn) + 1
Else
Return 1
End If
Finally
If Not bOpen Then cnn.Close()
End Try
End Function

Friend Shared Function Fill(ByVal dt As DataTable, ByVal Contract As
String) As Integer
DataAdapter.SelectCommand.Parameters(0).Value = Contract
Return DataAdapter.Fill(dt)
End Function

Friend Shared Function Update(ByVal dt As DataTable) As Integer
Return DataAdapter.Update(dt)
End Function

Private Shared Function DataAdapter() As SqlDataAdapter
If Not mda Is Nothing Then Return mda
mda = New SqlDataAdapter
With mda
.DeleteCommand = New SqlCommand
.InsertCommand = New SqlCommand
.UpdateCommand = New SqlCommand
.SelectCommand = New SqlCommand
With .SelectCommand
.Connection = cnn
.CommandText = "SELECT * FROM vw_CVI where Contract =
@Contract"
.Parameters.Add("@contract", SqlDbType.VarChar, 8)

End With
With .DeleteCommand
.Connection = cnn
.CommandText = "DELETE FROM CVI WHERE =@"
.Parameters.Add("@", SqlDbType.Int, 0, "")
End With
With .InsertCommand
.Connection = cnn
.CommandText = "INSERT INTO CVI" & _
" ( Contract, Number, IssuedToContact, Subject,
DateIssued, Detail, Staff, InstructionIssuedBy, InstructionIssuedTo, & _
InstructionIssuedDate ,CrossReference )" & _
"VALUES (@Contract, @Number, @IssuedToContact, @Subject,
@DateIssued, @Detail, @Staff, @InstructionIssuedBy, & _
@InstructionIssuedTo, @InstructionIssuedDate,@CrossReference )"
.Parameters.Add("@Contract", SqlDbType.VarChar, 8,
"Contract")
.Parameters.Add("@Number", SqlDbType.Int, 4, "Number")
.Parameters.Add("@IssuedToContact", SqlDbType.VarChar,
30, "IssuedToContact")
.Parameters.Add("@Subject", SqlDbType.VarChar, 30,
"Subject")
.Parameters.Add("@DateIssued", SqlDbType.DateTime, 8,
"DateIssued")
.Parameters.Add("@Detail", SqlDbType.VarChar, 0, "Detail")
.Parameters.Add("@Staff", SqlDbType.Int, 4, "Staff")
.Parameters.Add("@InstructionIssuedBy",
SqlDbType.VarChar, 30, "InstructionIssuedBy")
.Parameters.Add("@InstructionIssuedTo", SqlDbType.Int,
4, "InstructionIssuedTo")
.Parameters.Add("@InstructionIssuedDate",
SqlDbType.DateTime, 8, "InstructionIssuedDate")
.Parameters.Add("@CrossReference", SqlDbType.VarChar, 0,
"CrossReference")
End With
With .UpdateCommand
.Connection = cnn
.CommandText = "UPDATE CVI " & _
"SET Contract=@Contract, Number=@Number,
IssuedToContact=@IssuedToContact, Subject=@Subject, DateIssued=@DateIssued, &
_
Detail=@Detail, Staff=@Staff, InstructionIssuedBy=@InstructionIssuedBy,
InstructionIssuedTo=@InstructionIssuedTo, & _
InstructionIssuedDate=@InstructionIssuedDate, CrossReference=@CrossReference
" & _
"WHERE id=@id"
.Parameters.Add("@Contract", SqlDbType.VarChar, 8,
"Contract")
.Parameters.Add("@Number", SqlDbType.Int, 4, "Number")
.Parameters.Add("@IssuedToContact", SqlDbType.VarChar,
30, "IssuedToContact")
.Parameters.Add("@Subject", SqlDbType.VarChar, 30,
"Subject")
.Parameters.Add("@DateIssued", SqlDbType.DateTime, 8,
"DateIssued")
.Parameters.Add("@Detail", SqlDbType.VarChar, 0, "Detail")
.Parameters.Add("@Staff", SqlDbType.Int, 4, "Staff")
.Parameters.Add("@InstructionIssuedBy",
SqlDbType.VarChar, 30, "InstructionIssuedBy")
.Parameters.Add("@InstructionIssuedTo", SqlDbType.Int,
4, "InstructionIssuedTo")
.Parameters.Add("@InstructionIssuedDate",
SqlDbType.DateTime, 8, "InstructionIssuedDate")
.Parameters.Add("@CrossReference", SqlDbType.VarChar, 0,
"CrossReference")
.Parameters.Add("@", SqlDbType.Int, 0, "")
End With
AddHandler .FillError, AddressOf Fill_Error
AddHandler .RowUpdated, New
SqlRowUpdatedEventHandler(AddressOf DataAdapter_OnRowUpdated)
End With
Return mda
End Function
Private Shared Sub Fill_Error(ByVal sender As Object, ByVal e As
FillErrorEventArgs)
If TypeOf e.Errors Is Data.ConstraintException Then e.Continue =
True
End Sub
Private Shared Sub DataAdapter_OnRowUpdated(ByVal sender As Object,
ByVal args As SqlRowUpdatedEventArgs)
Dim newID As Integer = 0
Dim idCMD As SqlCommand = New SqlCommand("SELECT @@IDENTITY",
DataAdapter.UpdateCommand.Connection)
If args.StatementType = StatementType.Insert Then
Try
newID = CInt(idCMD.ExecuteScalar())
Catch ex As Exception
EH.Log(ex, EH.eErrorLogging.DisplayAndFile)
End Try

args.Row("ID") = newID
End If
End Sub
End Class

'repeats for 43 other tables !

end class
[/VBCODE]