Connecting Databases Using ADO.NET In VB.NET

Connecting and communication applications with a database is a necessary part of any type of application. We may even say an application that does not communicate witha database is useless. Applications communicate with a database to retrieve data to the application or to insert, update, or delete data.

ADO.NET (ActiveX Database Objects.Net) is a model provided by .NET framework that allows us to retrieve, insert, update or delete data from a database. ADO.NET contains the following important parts:

Part	Description
Connection	It is used for establishing connection between database and application. OleDBConnectionclass is used for database like oracle and MS-Access. SqlConnection class is used for MS-SQL database.
Command	It is used to execute a command (Query) like (Select * from Student). OleDBCommand class is used for database like oracle and MS-Access. SqlCommand class is used for MS-SQL database.
DataSet	It contains the copy of original database tables.
DataAdapter	It is used to retrieve data from database and update DataSet. In case of inserting, updating or deleting data, it automatically updates database while DataSet is updated. OleDBDataAdapter class is used for database like oracle and MS-Access. SqlDataAdapter class is used for MS-SQL database.
DataReader	It is used to read or retrieve data from database to application. OleDBDataReaderclass is used for database like oracle and MS-Access. SqlDataReader class is used for MS-SQL database.

Ways of Communication using ADO.NET Parts:

Now there are the following two ways of communication between the application and the database using parts of ADO.NET:

1. By using DataSet and DataAdapter
2. By using Command and DataReader

By Using DataSet and DataAdapter:

In this way we have to followthe below steps:

Steps:

1. Establish connection to database
2. Create a DataSet
3. Retrieve, insert, update or delete data by updating DataSet using DataAdapter

Establish Connection to Database

Connect to a database.

Select tab from top menu-bar TOOLS, then Connect to Database…

Browse your database file and click OK button.

After connecting to the new database file create an object of OleDBConnection class in case of database like Oracle or MS-Access and create an object of SqlConnection class in case of MS-SQL database.

Provide connection string (find it by opening properties of connected database) to created object by ConnectionString property.

Code

Dim cn As SqlConnection = New SqlConnection
cn.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\grthamad\Documents\student.mdf;Integrated Security=True;Connect Timeout=30"

Create a DataSet 

Select tab from top menu-bar PROJECT, then click Add New Data Source…

Select Database and click Next button.

Select Dataset and click Next button.

Choose data connection and click Next button.

Save the connection and click Next button.

Select data tables from connected database and click Finish button.

Retrieve, Insert, Update, Delete Data

Create an object of OleDBDataAdapter class in case ofa database like Oracle or MS-Access and create an object of SqlDataAdapter class in case of an MS-SQL database. Pass command and connection object via parameters.

Create object of created DataSet.

Invoke SqlDataAdapter.Fill() method. Pass DataSet object via parameter.

Code:

Dim cn As SqlConnection = New SqlConnection
cn.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\grthamad\Documents\student.mdf;Integrated Security=True;Connect Timeout=30"
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from studentRecord", cn)
Dim ds As studentDataSet = New studentDataSet
da.Fill(ds.studentRecord)

Example: 

Code:

Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim cn As SqlConnection = New SqlConnection
cn.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\grthamad\Desktop\myFirstWindowsApplication\myFirstWindowsApplication\student.mdf;Integrated Security=True;Connect Timeout=30"
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from studentRecord", cn)
Dim ds As studentDataSet = New studentDataSet
da.Fill(ds.studentRecord)
DataGridView1.DataSource = ds.Tables(0)
End Sub
End Class

By Using Command and DataReader

In this way we have to follow below steps: 

Steps:

1. Establish database connection
2. Execute Command
3. Read Data

Establish Database Connection

Establish a database connection same as we have done it in above way of communication.

Execute Command

After establishing database connection create an object of OleDBCommand in case of database like Oracle or MS-Access and SqlCommand in case of MS-SQL database. Pass command and connection object via parameters.

Invoke ExecuteNonQuery in case while you don’t need to get values or invoke ExecuteScalar in case while you need to get only single value or invoke ExecuteReader in case while you need to get all values.

Note: You have to open connection before execution of command and close it after execution.

Code:

Dim cn As SqlConnection = New SqlClient.SqlConnection
cn.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\grthamad\Desktop\myFirstWindowsApplication\myFirstWindowsApplication\student.mdf;Integrated Security=True;Connect Timeout=30"
cn.Open()
Dim cm As SqlCommand = New SqlClient.SqlCommand("Select * from studentRecord", cn)
cm.ExecuteReader()
cn.Close()

Read Data: 

Create an object of OleDBDataReader in case of database like Oracle or MS-Access and SqlDataReader in case of MS-SQL database.

Run a while loop having condition as SqlDataReader.Read().

Read values one by one using SqlDataReader.Item() method.

Example:

Code:

Imports System.Data.SqlClient
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim cn As SqlConnection = New SqlClient.SqlConnection
cn.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\grthamad\Desktop\myFirstWindowsApplication\myFirstWindowsApplication\student.mdf;Integrated Security=True;Connect Timeout=30"
cn.Open()
Dim cm As SqlCommand = New SqlClient.SqlCommand("Select * from studentRecord", cn)
Dim dr As SqlDataReader = cm.ExecuteReader()
DataGridView1.Rows.Clear()
Dim i As Integer = 0
While dr.Read
DataGridView1.Rows.Add()
DataGridView1.Item(0, i).Value = dr.Item(0)
DataGridView1.Item(1, i).Value = dr.Item(1)
DataGridView1.Item(2, i).Value = dr.Item(2)
DataGridView1.Item(3, i).Value = dr.Item(3)
DataGridView1.Item(4, i).Value = dr.Item(4)
i = i + 1
End While
cn.Close()
End Sub
End Class