VB.Net and ADO

[Newbie!]

Hi Group,

Hope someone wouldn`t mind advising on a bit of Code i`ve wrote. Could
someone tell me does this look right and advise me of problems or
suggestions - before I start duplicating it across my project?

Private Sub frmContracts_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'AutoLoads and Pupulates the Dataset
If odcContracts.State <> ConnectionState.Open Then odcContracts.Open()
odaContracts.Fill(dsContracts)
End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCancel.Click
'Closes the Database and Exits to frmMain
If odcContracts.State <> ConnectionState.Closed Then
odcContracts.Close()
Me.Close()
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnDelete.Click
'Deletes Selected Colum
If (Me.BindingContext(dsContracts, "ContractProviders").Count > 0)
Then
Me.BindingContext(dsContracts,
"ContractProviders").RemoveAt(Me.BindingContext(dsContracts,
"ContractProviders").Position)
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAdd.Click
'Adds a new Colum
Me.BindingContext(dsContracts, "ContractProviders").EndCurrentEdit()
Me.BindingContext(dsContracts, "ContractProviders").AddNew()
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click
'Updates All Changes to DATAGRID - Also Displays Confirmation
Dim RecordsUpdate As Integer
If dsContracts.HasChanges Then
RecordsUpdate = odaContracts.Update(dsContracts)
MessageBox.Show(RecordsUpdate.ToString & " Contract Record(s)
Updated.")
Else
MessageBox.Show("There Are No Changed Records to Update.")
End If
End Sub

Many Thanks and Best Regards
Si

 

[Norman Yuan]

Without seeing your variable declaration, I assume prefix odc refers to
Connection (Sql or Oledb?), oda - DataAdapter.

One thing in Form_Load() event handler may not be regarded as best practise
is to open a connection to DB and hold it to the end when form is closed,
depending on what the back end database is and how many users access to it.
Good practise is to open connection only necessary and close it ASAP.
Imagine, if the DB is a server (SQL Server, for example), and dozens of
users connect to it through your app. many users may leave the app opened
and never close it. Then much of the resources on the DB server are taken up
unnecessarily, which results in deterioration of server operation. If the db
is Jet (Access) database, it may also increase the possibility of *.mdb file
corruption.

Also, it is not necessary to explicitly open connection before calling
DataAdapter.Fill(), which open and close connection implicitly inside Fill()
method.