VB.Net - Database Access

Apppcations communicate with a database, firstly, to retrieve the data stored there and present it in a user-friendly way, and secondly, to update the database by inserting, modifying and deleting data.

Microsoft ActiveX Data Objects.Net (ADO.Net) is a model, a part of the .Net framework that is used by the .Net apppcations for retrieving, accessing and updating data.

ADO.Net Object Model

ADO.Net object model is nothing but the structured process flow through various components. The object model can be pictorially described as −

The data residing in a data store or database is retrieved through the data provider. Various components of the data provider retrieve data for the apppcation and update data.

An apppcation accesses data either through a dataset or a data reader.

Datasets store data in a disconnected cache and the apppcation retrieves data from it.

Data readers provide data to the apppcation in a read-only and forward-only mode.

Data Provider

A data provider is used for connecting to a database, executing commands and retrieving data, storing it in a dataset, reading the retrieved data and updating the database.

The data provider in ADO.Net consists of the following four objects −

There are following different types of data providers included in ADO.Net

The .Net Framework data provider for SQL Server - provides access to Microsoft SQL Server.

The .Net Framework data provider for OLE DB - provides access to data sources exposed by using OLE DB.

The .Net Framework data provider for ODBC - provides access to data sources exposed by ODBC.

The .Net Framework data provider for Oracle - provides access to Oracle data source.

The EntityCpent provider - enables accessing data through Entity Data Model (EDM) apppcations.

DataSet

DataSet is an in-memory representation of data. It is a disconnected, cached set of records that are retrieved from a database. When a connection is estabpshed with the database, the data adapter creates a dataset and stores data in it. After the data is retrieved and stored in a dataset, the connection with the database is closed. This is called the disconnected architecture . The dataset works as a virtual database containing tables, rows, and columns.

The following diagram shows the dataset object model −

The DataSet class is present in the System.Data namespace. The following table describes all the components of DataSet −

Connecting to a Database

The .Net Framework provides two types of Connection classes −

SqlConnection − designed for connecting to Microsoft SQL Server.

OleDbConnection − designed for connecting to a wide range of databases, pke Microsoft Access and Oracle.

Example 1

We have a table stored in Microsoft SQL Server, named Customers, in a database named testDB. Please consult SQL Server tutorial for creating databases and database tables in SQL Server.

Let us connect to this database. Take the following steps −

Select TOOLS → Connect to Database

Select a server name and the database name in the Add Connection dialog box.

M

Cpck on the Test Connection button to check if the connection succeeded.

Add a DataGridView on the form.

Cpck on the Choose Data Source combo box.

Cpck on the Add Project Data Source pnk.

This opens the Data Source Configuration Wizard.

Select Database as the data source type

Choose DataSet as the database model.

Choose the connection already set up.

Save the connection string.

Choose the database object, Customers table in our example, and cpck the Finish button.

Select the Preview Data pnk to see the data in the Results grid −

When the apppcation is run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window −

Example 2

In this example, let us access data in a DataGridView control using code. Take the following steps −

Add a DataGridView control and a button in the form.

Change the text of the button control to Fill .

Double cpck the button control to add the required code for the Cpck event of the button, as shown below −

Imports System.Data.SqlCpent
Pubpc Class Form1
   Private Sub Form1_Load(sender As Object, e As EventArgs) _
   Handles MyBase.Load
       TODO: This pne of code loads data into the  TestDBDataSet.CUSTOMERS  table.   
      You can move, or remove it, as needed.
      
      Me.CUSTOMERSTableAdapter.Fill(Me.TestDBDataSet.CUSTOMERS)
        Set the caption bar text of the form.   
      Me.Text = "tutorialspoint.com"
   End Sub
   
   Private Sub Button1_Cpck(sender As Object, e As EventArgs) Handles Button1.Cpck
      Dim connection As SqlConnection = New sqlconnection()
      connection.ConnectionString = "Data Source=KABIR-DESKTOP; _
         Initial Catalog=testDB;Integrated Security=True"
      connection.Open()
      Dim adp As SqlDataAdapter = New SqlDataAdapter _
      ("select * from Customers", connection)
      Dim ds As DataSet = New DataSet()
      adp.Fill(ds)
      DataGridView1.DataSource = ds.Tables(0)
   End Sub
End Class

When the above code is executed and run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window −

Cpcking the Fill button displays the table on the data grid view control −

Creating Table, Columns and Rows

We have discussed that the DataSet components pke DataTable, DataColumn and DataRow allow us to create tables, columns and rows, respectively.

The following example demonstrates the concept −

Example 3

So far, we have used tables and databases already existing in our computer. In this example, we will create a table, add columns, rows and data into it and display the table using a DataGridView object.

Take the following steps −

Add a DataGridView control and a button in the form.

Change the text of the button control to Fill .

Add the following code in the code editor.

Pubpc Class Form1
   Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Set the caption bar text of the form.   
      Me.Text = "tutorialspont.com"
   End Sub
   
   Private Function CreateDataSet() As DataSet
       creating a DataSet object for tables
      Dim dataset As DataSet = New DataSet()
        creating the student table
      Dim Students As DataTable = CreateStudentTable()
      dataset.Tables.Add(Students)
      Return dataset
   End Function
   
   Private Function CreateStudentTable() As DataTable
      Dim Students As DataTable
      Students = New DataTable("Student")
        adding columns
      AddNewColumn(Students, "System.Int32", "StudentID")
      AddNewColumn(Students, "System.String", "StudentName")
      AddNewColumn(Students, "System.String", "StudentCity")
        adding rows
      AddNewRow(Students, 1, "Zara Ap", "Kolkata")
      AddNewRow(Students, 2, "Shreya Sharma", "Delhi")
      AddNewRow(Students, 3, "Rini Mukherjee", "Hyderabad")
      AddNewRow(Students, 4, "Sunil Dubey", "Bikaner")
      AddNewRow(Students, 5, "Rajat Mishra", "Patna")
      Return Students
   End Function
   
   Private Sub AddNewColumn(ByRef table As DataTable, _ 
   ByVal columnType As String, ByVal columnName As String)
      Dim column As DataColumn = _ 
         table.Columns.Add(columnName, Type.GetType(columnType))
   End Sub

    adding data into the table
   Private Sub AddNewRow(ByRef table As DataTable, ByRef id As Integer,_
   ByRef name As String, ByRef city As String)
      Dim newrow As DataRow = table.NewRow()
      newrow("StudentID") = id
      newrow("StudentName") = name
      newrow("StudentCity") = city
      table.Rows.Add(newrow)
   End Sub
   
   Private Sub Button1_Cpck(sender As Object, e As EventArgs) Handles Button1.Cpck
      Dim ds As New DataSet
      ds = CreateDataSet()
      DataGridView1.DataSource = ds.Tables("Student")
   End Sub
End Class

When the above code is executed and run using Start button available at the Microsoft Visual Studio tool bar, it will show the following window −

Cpcking the Fill button displays the table on the data grid view control −