CHAPTER FIVE

5.1. VB.NET - DATABASE ACCESS

Applications 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 applications for retrieving, accessing and updating data.

5.2. 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, retrieves data for the application and update
data.
 An application accesses data either through a dataset or a data reader.
 Data sets store data in a disconnected cache and the application retrieve data from it.
 Data readers provide data to the application in a read-only and forward-only mode.

Compiled by: Tizazu B(MSc in Software Engineering 1|Page

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

No. Objects & Description

1 Connection: This component is used to set up a connection with a data source.

Command: A command is a SQL statement or a stored procedure used to retrieve,

2
insert, delete or modify data in a data source.

DataReader: Data reader is used to retrieve data from a data source in a read-only
3
and forward-only mode.

DataAdapter: This is integral to the working of ADO.Net since data is transferred

to and from a database through a data adapter. It retrieves data from a database into
4
a dataset and updates the database. When changes are made to the dataset, the
changes in the database are actually done by the data adapter.

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 EntityClient provider - enables accessing data through Entity Data Model (EDM)
applications.

5.2.2. 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 established 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 DataSet class is present in the System.Data namespace.
The following table describes all the components of DataSet:

Compiled by: Tizazu B(MSc in Software Engineering 2|Page

Sr.No. Components & Description

1 DataTableCollection: It contains all the tables retrieved from the data source.
DataRelationCollection: It contains relationships and the links between tables in a
2
data set.
ExtendedProperties: It contains additional information, like the SQL statement for
3
retrieving data, time of retrieval, etc.

DataTable: It represents a table in the DataTableCollection of a dataset. It consists of

4
the DataRow and DataColumn objects. The DataTable objects are case-sensitive.
DataRelation: It represents a relationship in the DataRelationshipCollection of the
5 dataset. It is used to relate two DataTable objects to each other through the
DataColumn objects.
6 DataRowCollection: It contains all the rows in a DataTable.

DataView: It represents a fixed customized view of a DataTable for sorting, filtering,

7
searching, editing and navigation.

8 PrimaryKey: It represents the column that uniquely identifies a row in a DataTable.

DataRow: It represents a row in the DataTable. The DataRow object and its properties
and methods are used to retrieve, evaluate, insert, delete, and update values in the
9
DataTable. The NewRow method is used to create a new row and the Add method
adds a row to the table.

10 DataColumnCollection: It represents all the columns in a DataTable.

11 DataColumn: It consists of the number of columns that comprise a DataTable.

5.3. The ADO.NET Data Architecture

Data Access in ADO.NET relies on two components: Dataset and Data Provider.
Dataset: The dataset is a disconnected, in-memory representation of data. It can be
considered as a local copy of the relevant portions of the database. The Dataset is
persisted in memory and the data in it can be manipulated and updated independent
of the database. When the use of this Dataset is finished, changes can be made back to
the central database for updating. The data in Dataset can be loaded from any valid
data source like Microsoft SQL server database, an Oracle database or from a
Microsoft Access database.

5.3.1. Component classes that make up the Data Providers

Compiled by: Tizazu B(MSc in Software Engineering 3|Page

 5.3.1.1. The Connection Object
The Connection object creates the connection to the database. Microsoft Visual Studio .NET
provides two types of Connection classes: the SqlConnection object, which is designed
specifically to connect to Microsoft SQL Server 7.0 or later, and the OleDbConnection object,
which can provide connections to a wide range of database types like Microsoft Access and
Oracle. The Connection object contains all of the information required to open a connection to
the database.

5.3.1.2. The Command Object

The Command object is represented by two corresponding classes: SqlCommand and
OleDbCommand. Command objects are used to execute commands to a database
across a data connection. The Command objects can be used to execute stored
procedures on the database, SQL commands, or return complete tables directly.
Command objects provide three methods that are used to execute commands on the
database:
 ExecuteNonQuery: Executes commands that have no return values such as
INSERT, UPDATE or DELETE
 ExecuteScalar: Returns a single value from a database query
 ExecuteReader: Returns a result set by way of a DataReader object

5.3.1.3. The DataReader Object

The DataReader object provides a forward-only, read-only, connected stream
recordset from a database. Unlike other components of the Data Provider, DataReader
objects cannot be directly instantiated. Rather, the DataReader is returned as the result
of the
Command object's ExecuteReader method. The SqlCommand.ExecuteReader method
returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method
returns an OleDbDataReader object. The DataReader can provide rows of data
directly to application logic when you do not need to keep the data cached in memory.
Because only one row is in memory at a time, the DataReader provides the lowest
overhead in terms of system performance but requires the exclusive use of an open
Connection object for the lifetime of the DataReader.
DataAdapter provides four properties that represent database commands:
 SelectCommand
 InsertCommand

Compiled by: Tizazu B(MSc in Software Engineering 4|Page

  DeleteCommand
 UpdateCommand
When the Update method is called, changes in the DataSet are copied back to the
database and the appropriate InsertCommand, DeleteCommand, or
UpdateCommand is executed.

5.3.1.4. 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, like
Microsoft Access and Oracle.
Example 1
We have a table stored in Microsoft SQL Server, named Students , in a database named
HealthScienceCollege.

Steps
 Select TOOLS → Connect to Database

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

Compiled by: Tizazu B(MSc in Software Engineering 5|Page

Create database and tables that you want to connect with Visual Basic freamwork on
your database management system and connect your server name and database that
you executed.

Compiled by: Tizazu B(MSc in Software Engineering 6|Page

  Click on the Test Connection button to check if the connection succeeded.

 Add a DataGridView on the form.

Compiled by: Tizazu B(MSc in Software Engineering 7|Page

Coloumn values directly retrieving from connected database and displays the
information if they have values from table. Like

Database Connection Code

Imports System.Data.SqlClient
Public Class StudentInfo
Dim Conn As New SqlConnection
Dim cmd As New SqlCommand
Dim i As Integer
'Dim Conn As New SqlConnection("Data
Source=ትዕዛዙ-ባይህ;Initial Catalog=Project;Integrated
Security=True")
Private Sub StudentInfo_Load(sender As Object, e As
EventArgs) Handles MyBase.Load

Compiled by: Tizazu B(MSc in Software Engineering 8|Page

 Conn.ConnectionString = "Data Source=ትዕዛዙ-
ባይህ;Initial Catalog=Project;Integrated Security=True"
If Conn.State = ConnectionState.Open Then
Conn.Close()
End If
Conn.Open()
'displayData()
End Sub
Private Sub btnCreate_Click(sender As Object, e As
EventArgs) Handles btnCreate.Click
cmd = Conn.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "insert into StudentInfo
values('" + txtSID.Text + "','" + txtFirstname.Text + "','" +
txtLastname.Text + "','" + txtGender.Text + "', '" +
txtAddress.Text + "','" + txtDepartment.Text + "')"
cmd.ExecuteNonQuery()
'displayData()
MessageBox.Show(" You inserted successfully")
display_data()
txtSID.Clear()
txtFirstname.Clear()
txtLastname.Clear()
txtGender.Clear()
txtAddress.Clear()
txtDepartment.Clear()
End Sub
Private Sub btnDelete_Click(sender As Object, e As
EventArgs) Handles btnDelete.Click
If Conn.State = ConnectionState.Open Then
Conn.Close()

Compiled by: Tizazu B(MSc in Software Engineering 9|Page

 End If
Conn.Open()
cmd = Conn.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "delete from StudentInfo
where FiristName'" + txtFirstname.Text + "'"
display_data()
End Sub

Private Sub FillByToolStripButton_Click(sender As

Object, e As EventArgs) Handles
FillByToolStripButton.Click
Try

Me.StudentInfoTableAdapter.FillBy(Me.ProjectDataSet.
StudentInfo)
Catch ex As System.Exception

System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub
Public Sub display_data()
cmd = Conn.CreateCommand()
cmd.CommandType = CommandType.Text
cmd.CommandText = "select * from StudentInfo"
cmd.ExecuteNonQuery()
Dim dt As New DataTable()
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
DataGridView1.DataSource = dt
display_data()
End Sub
Compiled by: Tizazu B(MSc in Software Engineering 10 | P a g e
 Private Sub DataGridView1_CellContentClick(sender
As Object, e As DataGridViewCellEventArgs) Handles
DataGridView1.CellContentClick
Try
If Conn.State = ConnectionState.Open Then
Conn.Close()
End If
Conn.Close()
i=
Convert.ToInt32(DataGridView1.SelectedCells.Item(0).V
alue.ToString())
cmd = Conn.CreateCommand()
cmd.CommandText = "select * from StudentInfo
where SID=" & i & ""
cmd.ExecuteNonQuery()
Dim dt As New DataTable
Dim da As New SqlClient.SqlDataAdapter(cmd)
da.Fill(dt)
Dim Dr As SqlClient.SqlDataReader
Dr =
cmd.ExecuteReader(CommandBehavior.CloseConnectio
n)
While Dr.Read
txtSID.Text = Dr.GetString(1).ToString()
txtFirstname.Text = Dr.GetString(2).ToString()
txtLastname.Text = Dr.GetString(3).ToString()
txtGender.Text = Dr.GetString(4).ToString()
txtAddress.Text = Dr.GetString(5).ToString()
txtDepartment.Text =
Dr.GetString(6).ToString()
End While
Catch ex As Exception
Compiled by: Tizazu B(MSc in Software Engineering 11 | P a g e
 End Try
End Sub

Private Sub btnRead_Click(sender As Object, e As

EventArgs) Handles btnRead.Click
display_data()
End Sub

Private Sub btnUpdate_Click(sender As Object, e As

EventArgs) Handles btnUpdate.Click
If Conn.State = ConnectionState.Open Then
Conn.Close()
End If
Conn.Close()
i=
Convert.ToInt32(DataGridView1.SelectedCells.Item(0).V
alue.ToString())
cmd = Conn.CreateCommand()
cmd.CommandText = "update StudentInfo set
FirstName='" & txtFirstname.Text & "', LastName='" &
txtLastname.Text & "',Gender='" & txtGender.Text &
"',Address='" & txtAddress.Text & "',Department='" &
txtDepartment.Text & "' where SID=" & i & ""
cmd.ExecuteNonQuery()
display_data()
End Sub
End Class

Compiled by: Tizazu B(MSc in Software Engineering 12 | P a g e