In this article we shall learn how to connect to several databases using Microsoft visual studio or VB .NET. We shall practice the simplest code to connect to each database one by one. 

1. MS Access Database:

Dim con As New OleDb.OleDbConnection

'Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\SHANKAR\Documents\Employee.accdb;User Id=admin;Password=shanky;"

Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\SHANKAR\Documents\Employee.accdb;User Id=admin;Password=;"

            Try

                        con.ConnectionString = conString
                        con.Open()    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

Here now we shall understand the code. First of all we are declaring a connection con which represents the connection to a data source. 

Secondly we have declared a connection string which will be used to connect to the database. Here connection string is the most important property of a connection.

Connection string consists of following attributes:

• Provider: This attribute is different for each database. Example Microsoft.ACE.OLEDB.12.0 for MS access, MSDAORA.1 for oracle 10g and SQLOLEDB for MS Sql Server.
• Data source: This represents the path or name of the database. Example:                          Data Source=C:\Users\SHANKAR\Documents\Employee.accdb  to connect to MS access database Employee.accdb.
• User Id : User id of the database
• Password: password to connect to the dabase.
• Server: It is the name of the SQL server. Usually it is formed by the system name/ the Instance name. Eg. SHANKAR\SQLEXPRESS
• Integrated Security:  It can be yes or no, if we specify it to be 'Yes' then it means the connection will be established using windows authentication.
• Initial Catalog: It is the name of the Sql database to connect to.
• AttachDbFilename: This attribute repesents the mdf database file using using we can connect to sql server database.

You can choose or create any connection string from here. 

Next is we have set the connection string property of the connection. We put them in try-catch block to handle any exception.

con.ConnectionString = constr

In the above code we have commented out one connection string. That is valid when we are connecting to an encrypted MS access database. In that case we need to give password. The database can be encrypted using a password when you open it in exclusive mode in MS access.

And then we open the connection using con.open().

If the connection string is not correct, the connection to the database will fail. So please make sure that all the attributes of the connection string is correct.

2. Oracle 10g

   Dim con As New OleDb.OleDbConnection     

Dim constr As String = "Provider =MSDAORA.1;User ID =system; Password =shanky;database=orcl"

            Try
                        con.ConnectionString = constr
                        con.Open()
                Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

Here again the only difference is in connection string. Provider is "MSDAORA.1" and the userid and password are the credential to connect to the database.

3.  MS Sql Server

  Dim con As New SqlClient.SqlConnection     

con.ConnectionString = "Data Source=SHANKAR\SQLEXPRESS;Initial Catalog=Teachers;Integrated Security=True"

           

' connew.ConnectionString = "Data source=SHANKAR\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\StudentDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"   'working
           

'connew.ConnectionString = "Server=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\StudentDB.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"   'working
            'above connection string is using database file

            Try
                ' connew.Open()
                con.Open()
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

While connecting to MS Sql server we have used three different connection strings for 3 different cases.

Case 1:  Connecting to Sql server  SHANKAR and the database Teachers. Here please note that we are using sqlClient.SqlConnection. So here, Provider attribute is not required.

Case 2: Connecting to Sql server using database file name StudentDB.mdf

Now as we have connected to database, lets fetch some data and put it into our Form controls

Below is the sample code that can fetch data and assign them to the textboxes or other controls on the GUI. We shall explain the code step by step.

Dim rdr As OleDbDataReader

Dim cmd As New OleDbCommand("select * from EMPLOYEE where empId=" & txtEmpId.Text & "", con)
        rdr = Nothing
        Dim lvm As New ListViewItem
        str1 = "No data found!"  ' undeclared variable str1
        Try
            rdr = cmd.ExecuteReader()  ' executereader is for Select statement
            If (rdr.HasRows = False) Then
                MessageBox.Show(str1)
                resetTexts()
            Else
                While (rdr.Read())
                    txtName.Text = rdr(1) ' rdr(1) refers to second column and so on
                    txtRoll.Text = rdr(2)
                    txtmarks.Text = rdr(3)

                    lvm.SubItems.Add(txtName.Text)
                    lvm.SubItems.Add(txtRoll.Text)
                    lvm.SubItems.Add(txtmarks.Text)

                    ListView1.Items.Add(lvm)
                End While

            End If
            rdr.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Now lets try to understand the above code.

1. We have declared an OleDB Command cmd which represents an sql statement or stored procedure that will be executed again the database. It takes two arguments: the command text as string and the open connection to the database.

Here we have taken the empId from the input textbox txtEmpId.Text. 

2. Then we have declared reader rdr as OleDBdatareader to read data from database (forward-only).

3. Next is we have executed the command again the database to fill the oledbdatareader rdr.

   rdr = cmd.ExecuteReader()

4. Here rdr.HasRows checks if the rdr has retrieved some data or not. so when it is non-empty we have assigned the data from reader rdr to our GUI textboxes.

 While (rdr.Read())           
                    txtName.Text = rdr(1) ' rdr(1) refers to second column and so on
                    txtRoll.Text = rdr(2)
                    txtmarks.Text = rdr(3)

End While

Following is a RAR file containing code for all types of connections. The Project uses Microsot visual studio 2008 as front end and all 3 database as back-end. This project consists of very simple implementation to connect to all these databases.

Download the sampleProject here.