C# and Dot net framework

Chapter-5
Data Access with ADO.NET
Definition:
ADO.NET is a module of .net framework which is used to established connection
between front end application and data sources.

ADO.NET provides a bridge between the front end controls and the back end database.

Data sources can be such as SQL Server and XML. ADO.NET consists of classes that
can be used toconnect, retrieve, insert and delete data.

ADO is a Microsoft technology

ADO stands for ActiveX Data Objects

ADO is a Microsoft Active-X component

ADO is automatically installed with Microsoft IIS

ADO is a programming interface to access data in a database

GFGC, Tumkur Dept. of BCA Page 1

 C# and Dot net framework

Architecture of ADO.NET
The following figure shows the ADO.NET objects:

ADO.NET has two main components that are used for accessing and manipulating data.
They are
 Data Provider
 Data Set

Data Providers:

These are the components that are designed and data manipulation and fast access to data.
It provides various objects such as
Connection,Command, Data Reader and Data Adapter that are used to perform
database operations.

Connection It is used to establish a connection to a specific data base.

GFGC, Tumkur Dept. of BCA Page 2

 C# and Dot net framework

Command It is used to execute queries to perform database operations.

Data Reader It is used to read data from data source. The Data Reader is a
base class for all Data Reader objects.

Data The Data Adapter object acts as a mediator between the

Adapter Dataset object and the database and is used to perform
manipulation operations on data.

Types of Data Providers:

The ADO.NET Framework provides the following data providers that we can use in our
application.

.NET Framework Description

dataprovider

.NET Framework It provides data access for Microsoft SQL Server.

Data Provider for It requiresthe System. Data. SqlClient
SQL Server namespace.

.NET Framework It is used to connect with OLE DB. It requires

DataProvider for the System.Data. OleDb namespace.
OLE DB

.NET Framework It is used to connect to data sources by using

DataProvider for ODBC. It requiresthe System. Data. Odbc
ODBC namespace.

.NET Framework It is used for Oracle data sources. It uses the

DataProvider for System. Data.OracleClient namespace.
Oracle

It provides data access for Entity Data Model

Entity Client Provider applications. Itrequires the System. Data.
EntityClient namespace.

GFGC, Tumkur Dept. of BCA Page 3

 C# and Dot net framework

Data Set:

It is a subset of the database; it does not have continuous connection to the database.

To update the database a reconnection is required.

Hence it requires 2 objects. They are

 Data Relational Collection

 Data Table Collection

Data Relational Collection:

It represents relationship between two tables.

DataTableCollection:

The Data Table class represents the tables in the database. It has the following
important properties;most of these properties are read only properties except the
Primary Key property:

Properties

Columns Returns the Columns collection.

Constraints Returns the Constraints collection.

Primary Key Gets or sets an array of columns as the primary

key for thetable.

Rows Returns the Rows collection (set of columns values).

Which one should we use Data Reader or Dataset?

We should consider the following points to use Dataset.

o It caches data locally at our application, so we can manipulate it.

GFGC, Tumkur Dept. of BCA Page 4

 C# and Dot net framework

o It interacts with data dynamically such as binding to windows forms control.

o It allows performing processing on data without an open connection. It means it can

work whileconnection is disconnected.

If we required some other functionality mentioned above, we can use Data

Reader to improveperformance of our application.

Data Reader does not perform in disconnected mode. It requires Data Reader object
to be connectedand It receives only one table and view at a time. Whereas Dataset
can receives multiple tables at a time.

How to Connect Strings Objects

If we want to connect String objects then we need to take 3 fields like

o Data Source: means Server name with instance

o AttacheDbFilename: means Database name
o Integrated Security: Always true
Syntax:

Connection object= New SqlConnection(“Data Source; AttachDbFilename; Integrated

Security=True”)

Creating Command Object

After we are establishing Connection then we can provide which command we have
to pass as anargument.
Syntax:

Command object= connectionobject. CreateCommand()

Command object. CommandType= CommandType.Text
Command object. CommandText = " Query / Command “

Open the Connection

Syntax:
Connection Object . Open()

GFGC, Tumkur Dept. of BCA Page 5

 C# and Dot net framework

Close the Connection

Syntax:
Connection Object . Close()
Insertion Operation Query

Insert Command is used to insert tuples into the table.

Syntax:
Insert into table name values (&colname1, ‘&colname2’, &colname3… );

Here first we need create Dataset and create an object for this dataset.

Next open the Connection.

Fill the details.

Close the connection

Deletion Operation Query

Delete Command is used to delete selected tuple or all tuples from the table.
Syntax:
Delete tablename; // delete all tuples but not structure
Delete tablename where condition / (colname= value);

Updating Operation Query

Update Command is used to modify existing data with new data.

Syntax:
Update table name set column name = value where condition;

Steps to Create Database Connectivity

Step 1: First User Should Create a data base for that

Go to “Solution Explorer” widow => right click on the project name => click on
“Add” option =>Select “add new item” => select “service Based database” => click
GFGC, Tumkur Dept. of BCA Page 6
 C# and Dot net framework

on “Add”.

Now data base will be added to our project.

Step 2: If we wants to rename database => right click on the project => using rename
option we canrename the database.

Step 3: now we need to create a table in the database. For this => double click on the
data base => willopen “server explorer” window => select “table” option => right click
on this => select “add new table” => new table will be added to the database.

Step 4: If we want to change the table name then we can change as “dbo.Student”
and will add somecolumns into that table like “ID, SName and Smarks etc.”

Step 5: After entering these details in to table then we need to Update the table by using
“update”

option => the click on “update database”.

Step 6: We need to design the Front end application like C#.net or VB.net etc.
Step 7: we need to create one form with 3 labels, 3 textboxes and 3 button controls for
entering data.

GFGC, Tumkur Dept. of BCA Page 7

 C# and Dot net framework VVFGC, Tumkur

Step 8: We need to create / Establish Connection between Front end Form and Back end
Database forthat write the coding part.

Step 9: Double click on the form and write the coding

Imports System.Data.SqlClient
Public Class Form1
Dim cn As SqlConnection
Dim cmd As
SqlCommand
Private Sub Form1_Load(sender As Object, e As EventArgs) HandleMyBase.Load
cn = New SqlConnection("Data
Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\vvfgc\source\re
pos\Simpl e Database Connectivity\Simple Database Connectivity\
VVFGC.mdf;Integrated Security=True")
cn.Open()

MsgBox("connected successfully")

cn.Close()
End Sub

Step 10: Run the Project then it display “Connected successfully”.

Step 11: Now we insert tuples or rows in to the table.

Query:
Insert into table name values (&colname1, ‘&colname2’, &colname3 );

Step 12: for Inserting data in to the Table through VB.NET Coding

Double click on the Insert button and write the coding

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

cn.Open()
cmd = cn.CreateCommand()

GFGC, Tumkur Dept. of BCA Page 8

 C# and Dot net framework VVFGC, Tumkur
cmd.CommandType = CommandType.Text

GFGC, Tumkur Dept. of BCA Page 9

 C# and Dot net framework VVFGC, Tumkur

cmd.CommandText = "insert into Student values(" & TextBox1.Text & " , ' " &
TextBox2.Text &" ' , " & TextBox3.Text & ")"
cmd.ExecuteNonQuery()
MsgBox("record inserted successfully")
cn.Close()
End Sub

Step 13: Run the Project then it display “Connected Successfully” and insert some rows in
table.
Step 14: Run the query as select * from Student;
Step 14: Now we Update tuple or rows in table.
Query:
Update table name set column name1= value, column name2=value where
colname=value;
Step 15: For Updating Existing data in the Table through VB.NET Coding

Double click on the Update button and write the coding

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

cn.Open()

cmd = cn.CreateCommand()

cmd.CommandType = CommandType.Text
cmd.CommandText = "update Student set sname=' " & TextBox2.Text & " ' ,
smarks=" &TextBox3.Text & " where id=" & TextBox1.Text & ""
cmd.ExecuteNonQuery()
MsgBox("record updated successfully")
cn.Close()
End Sub

Step 16: Run the Project then it display “Connected Successfully” and update rows.

GFGC, Tumkur Dept. of BCA Page 10

 C# and Dot net framework VVFGC, Tumkur
Step 17: Now we delete data from table

Query:

Delete from table name where column name = value;

Step 18: For Deleting data from the Table through VB.NET Coding

Double click on the Delete button and write the coding

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

cn.Open()
cmd = cn.CreateCommand()
cmd.CommandType =
CommandType.Text
cmd.CommandText = "delete from Student where id= “ & TextBox1.Text & " “
cmd.ExecuteNonQuery()
MsgBox("record deleted successfully")
cn.Close()
End Sub

What is OLEDB?
Object Linking and Embedding Database (OLE DB) is a connectivity method similar to
Open Database Connectivity (ODBC) it represents a unique connection to a data source.

What is ODBC?
Open Database Connectivity (ODBC) is a standard application programming
interface (API) for accessing database management systems (DBMS).

*********

GFGC, Tumkur Dept. of BCA Page 11