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

Using DataSet and DataAdapter

 

In this way we have to follow the below steps:

 

Steps

1. Establish a connection to the 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 the OK button.

 

After connecting to the new database file create an object of OleDBConnection class in case of a 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 the connected database) to created objects 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 the Database and click the Next button.

 

Select Dataset and click the Next button.

 

Choose the data connection and click the Next button.

 

Save the connection and click the Next button.

 

Select data tables from the connected database and click the Finish button.

 

Retrieve, Insert, Update, Delete Data

 

Create an object of OleDBDataAdapter class in case of a 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 an object of the 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 the below steps: 

 

Steps:

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

Establish Database Connection

 

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

 

Execute Command

 

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

 

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

 

Note: You have to open the connection before the execution of the 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 a database like Oracle or MS-Access and SqlDataReader in the case of the MS-SQL database.

 

Run a while loop having the 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