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