Open and Close a Data Connection

[dday9]

Opening: In this article you will learn how to open and close a data connection. With the connection being open, you can execute commands such as: Fill, Insert, Update, and Delete. Please mind you that I do infact use oledb(microsoft access), however to convert to SQL is very simple.

The Beginning
Begin with creating a windows form application named “Opening Connection.” Add two buttons and a datagridview. Below are the properties of the controls

Button1

Name - btnOpen

Size - 75, 23

Location - 13, 13

Anchor - Top, Left

Button2

Name - btnClose

Size - 75, 23

Location - 205, 13

Anchor - Top, Right

Datagridview

Name - Datagridview1

Size - 267, 202

Location - 13, 52

Anchor - Top, Right, Left, Bottom

You can set all the properties of the controls very easily by using a with/end with statement in the form_load like such:

With btnOpen
            .Size = New Size(75, 23)
            .Location = New Point(13, 13)
            .Anchor = AnchorStyles.Left Or AnchorStyles.Top
        End With
        With btnClose
            .Size = btnOpen.Size
            .Location = New Point(205, 13)
            .Anchor = AnchorStyles.Right Or AnchorStyles.Top
        End With
        With DataGridView1
            .Size = New Size()
            .Anchor = AnchorStyles.Bottom Or AnchorStyles.Top _
                Or AnchorStyles.Left Or AnchorStyles.Right
        End With

or you can set the properties in the designer view.


Press F7 to open the code for your form. You should see:

Public Class Form1
    
End Class

The first thing that we will do is turn option strict and explicit on as well as import the oledb or sqlclient namespace. After that you will see:

Option Strict On
Option Explicit On

Imports System.Data.OleDb

Public Class Form1
    
End Class

So far we haven’t done much. All we’ve done is added the controls that we will use later, turn option strict/explicit on, and import the oledb namespace.

Global Variables
We will now declare our global variables. We will use these variables so that we don’t have to build them time and time again. We will add 2 variables, a string and an oledbconnection; your code should look like this now:

 Option Strict On
Option Explicit On

Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDbConnection
    Dim sql As String = "Select * From Customers"

    
End Class

The oledbconnection will be used to actually open and close the connection where the string is a sql command. The sql command is read as such: Select all columns from my data table named Customers. The * is not always the fastest way get records, especially if you’re needing only 1 column then this would not be the way to go. But since this is to demonstrate how to open an oledb connection, we will use the sql string above.

Opening the connection
Double click btnOpen so that the button’s click event is generated for you. Type in try and press enter. You will see that a try/catch is generated for you. This is error handling, and it’s essential to use so that if an error is generated at runtime then the whole program won’t crash! In your catch part of the try/catch, type in:

 MsgBox(ex.ToString)

what this will do is give you a message box with the exception that was thrown if any. Now to actually open the connection! In your try part of the Try/Catch type in

 con.Open()
            MsgBox("Success!")

this opens up our oledbconnection we declared earlier and opens a msgbox telling us that our connection is opened. Now let us debug what we’ve done by pressing F5. Click btnOpen. You will see that an exception gets thrown:

The Connection String Property Has Not Been Initialized.

That’s because in our oledbconnection we haven’t declared a connection string. I did that on purpose so that an error will be thrown and you can experience first hand on how to error handle. The connection string, depending on your version of Microsoft Access, will be different. You can find your connection string by visiting this site. Mine is simple it’s “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb” I add that after my oledbconnection in parenthesis(). Let’s take a look at what the code should look like now:

 Option Strict On
Option Explicit On

Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb")
    Dim sql As String = "Select * From Customers"

    
    Private Sub btnOpen_Click(sender As System.Object, e As System.EventArgs) Handles btnOpen.Click
        Try
            con.Open()
            MsgBox("Success!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

End Class

Now debug the program and click the open button. You get the success msgbox! Congratulations, you’ve successfully opened an oledb connection.

Closing the connection
Now that we’ve successfully opened the oledb connection, let’s close the connection. Double click on your btnClose to generate the btnClose_click event. In that type in con.Close(). This is very simple, yet vital. Think of the oledbconnection as your front door of your house. If you leave your front door open then crud is going to come in, and perhaps a thief will pass by and steal(aka delete) everything that you have!


Final Look at your code:
Your code should look like this in the end.

Option Strict On
Option Explicit On

Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb")
    Dim sql As String = "Select * From Customers"


    Private Sub btnOpen_Click(sender As System.Object, e As System.EventArgs) Handles btnOpen.Click
        Try
            con.Open()
            MsgBox("Success!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        con.Close()
    End Sub
End Class

Conclusion
In conclusion, I walked you through on how to open and close a data connection as well as explain the importance of closing the connection.

[dday9]

To display the data from the connection that you've just opened would be rather simple. Declare yourself 2 new variables, a oledbdataadpter and a dataset. Then after you open the connection, fill the dataadapter with the dataset and set the datagridview's datasource to the datatable you wish from the dataset. An example of how to do such following the "How to open and close a data connection" would be:

Option Strict On
Option Explicit On

Imports System.Data.OleDb
Public Class Form1
    Dim con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb")
    Dim sql As String = "Select * From Customers"
    Dim da As New OleDbDataAdapter(sql, con)
    Dim ds As New DataSet

    Private Sub btnOpen_Click(sender As System.Object, e As System.EventArgs) Handles btnOpen.Click
        Try
            con.Open()
            da.Fill(ds)
            DataGridView1.DataSource = ds.Tables(0)
            MsgBox("Success!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        con.Close()
    End Sub
End Class