Connecting Databases Using ADO.NET In VB.NET

In this article you will learn how to connect 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.

connection_to_db

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.

connection_properties

Code

  1. Dim cn As SqlConnection = New SqlConnection  
  2. 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.

dataSet1
Select Dataset and click Next button.

dataSet2
Choose data connection and click Next button.

dataSet3
Save the connection and click Next button.

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

dataSet5
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:

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

db_program

Code:

  1. Imports System.Data.SqlClient  
  2. Public Class Form1  
  3. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
  4. Dim cn As SqlConnection = New SqlConnection  
  5. cn.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\grthamad\Desktop\myFirstWindowsApplication\myFirstWindowsApplication\student.mdf;Integrated Security=True;Connect Timeout=30"  
  6. Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from studentRecord", cn)  
  7. Dim ds As studentDataSet = New studentDataSet  
  8. da.Fill(ds.studentRecord)  
  9. DataGridView1.DataSource = ds.Tables(0)  
  10. End Sub  
  11. 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:

  1. Dim cn As SqlConnection = New SqlClient.SqlConnection  
  2. cn.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\grthamad\Desktop\myFirstWindowsApplication\myFirstWindowsApplication\student.mdf;Integrated Security=True;Connect Timeout=30"  
  3. cn.Open()  
  4. Dim cm As SqlCommand = New SqlClient.SqlCommand("Select * from studentRecord", cn)  
  5. cm.ExecuteReader()  
  6. 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:

db_program

Code:

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