UNIT-3 (Chapter-2 ADO.

NET Connectivity)

Introduction to ADO.NET
• ADO.NET provides a bridge between the front-end controls and the back-end
database.
• ADO.NET is a module of .Net Framework which is used to establish connection
between application and data sources.
• Data sources can be such as SQL server and XML
• ADO.NET consists of classes that can be used to connect, retrieve, insert and delete
data.
• All the ADO.NET classes are located into System.Data.dll and integrated with XML
classes located into System.xml.dll.
• ADO.NET stands for ActiveX Data Object.
• It is a database access technology created by Microsoft as part of its .NET framework
that can access any kind of data source.
• It’s a set of object-oriented classes that provides a rich set of data components to
create high-performance, reliable and scalable database applications.
• The ADO.NET is one of the Microsoft’s data access technology which is used to
communicate between the .NET Application (Console, WCF, WPF, Windows, MVC,
Web Form, etc.) and data sources such as SQL Server, Oracle, MySQL, XML
document, etc
• ADO.NET consists of a set of classes that can be used to connect, retrieve, insert,
update and delete data (i.e. performing CRUD operation) from data sources.
• ADO.NET mainly uses System.Data.dll and System.Xml.dll
What types of Applications use ADO.NET?
• ADO.NET can be used to develop any type of .NET application. The following are
some of the .NET applications where you can ADO.NET Data Access Technology to
interact with a data source.
• ASP.NET Web Form Applications
• Windows Form Applications
• ASP.NET MVC Applications
• Console Applications
• ASP.NET Web API Applications

Components of ADO.NET
• Components are designed for data manipulation and faster data access
• Connection, Command, Data Reader, Data Adapter, Data Set, and Data View are the
Components of ADO.NET that are used to perform database operations.

HBA ASST PROF CS DEPT, PESSAC MANDYA 1

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

ADO vs ADO.NET
ADO ADO.NET

It is a COM(Component Object Modelling) based It is a CLR(Common Language Runtime) based

Library. Library.

ADO works in the connected mode to access data. ADO.Net works in the disconnected mode to
access data.

Locking features is available Locking features is not available

Data is stores in XML

Data is stored in Binary Format

XML integration is not possible XML integration is possible

It uses RecordSet to store the data from datasource It uses Dataset to store the data from datasource

In ADO, You can create only Client side cursor. In ADO.Net, You can create both Client & Server
side cursor

ADO.Net has firewall proof and its execution will

Firewall might prevent execution of Classic ADO never be interrupted

You cannot send multiple transaction using a single You can send multiple transaction using a single
connection instance connection instance

In ADO.NET performance is much better as

ADO have a poor performance. compared to ADO.

ADO is less secured. ADO.NET much secured as compared to ADO.

It has a less number of data types. It has a huge and rich collection of data types.

ADO.NET address a multi-tiered architecture.

ADO was geared for a two-tiered architecture.

HBA ASST PROF CS DEPT, PESSAC MANDYA 2

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

ADO.NET Architecture:

ADO.NET consist of a set of Objects that expose data access services to the .NET environment.
It is a data access technology from Microsoft .Net Framework , which provides communication
between relational and non relational systems through a common set of components .

System.Data namespace is the core of ADO.NET and it contains classes used by all data
providers. ADO.NET is designed to be easy to use, and Visual Studio provides several wizards
and other features that you can use to generate ADO.NET data access code.

Data Providers and DataSet

The two key components of ADO.NET are Data Providers and DataSet . The Data Provider
classes are meant to work with different kinds of data sources. They are used to perform all data-

HBA ASST PROF CS DEPT, PESSAC MANDYA 3

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

management operations on specific databases. DataSet class provides mechanisms for managing
data when it is disconnected from the data source.

Data Providers

The .Net Framework includes mainly three Data Providers for ADO.NET. They are the
Microsoft SQL Server Data Provider , OLEDB Data Provider and ODBC Data Provider . SQL
Server uses the SqlConnection object , OLEDB uses the OleDbConnection Object and ODBC
uses OdbcConnection Object respectively.

ASP.NET SQL Server Connection

ASP.NET OLEDB Connection

ASP.NET ODBC Connection

ASP.NET ODBC Connection

A data provider contains Connection, Command, DataAdapter, and DataReader objects. These
four objects provides the functionality of Data Providers in the ADO.NET.

Connection

The Connection Object provides physical connection to the Data Source. Connection object
needs the necessary information to recognize the data source and to log on to it properly, this
information is provided through a connection string.

Command

The Command Object uses to perform SQL statement or stored procedure to be executed at the
Data Source. The command object provides a number of Execute methods that can be used to
perform the SQL queries in a variety of fashions.

DataReader

HBA ASST PROF CS DEPT, PESSAC MANDYA 4

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

The DataReader Object is a stream-based , forward-only, read-only retrieval of query results

from the Data Source, which do not update the data. DataReader requires a live connection
with the databse and provides a very intelligent way of consuming all or part of the result set.

DataAdapter

DataAdapter Object populate a Dataset Object with results from a Data Source . It is a special
class whose purpose is to bridge the gap between the disconnected Dataset objects and the
physical data source.

DataSet

DataSet provides a disconnected representation of result sets from the Data Source, and it is
completely independent from the Data Source. DataSet provides much greater flexibility when
dealing with related Result Sets.

DataSet contains rows, columns,primary keys, constraints, and relations with other DataTable
objects. It consists of a collection of DataTable objects that you can relate to each other with
DataRelation objects. The DataAdapter Object provides a bridge between the DataSet and the
Data Source.

• Data reader:
DataReader Object in ADO.NET is a stream-based , forward-only, read-only retrieval of query
results from the Data Sources , which do not update the data. The DataReader cannot be created
directly from code, they can created only by calling the ExecuteReader method of a Command
Object.

After creating an instance of the Command object, you have to create a DataReader by calling
Command.ExecuteReader to retrieve rows from a data source.

HBA ASST PROF CS DEPT, PESSAC MANDYA 5

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

VB.Net
Dim reader As SqlDataReader = cmd.ExecuteReader

C#

SqlDataReader reader = cmd.ExecuteReader();

When the ExecuteReader method in the SqlCommand Object execute , it will instantiate a
SqlClient.SqlDataReader Object. When we started to read from a DataReader it should always be
open and positioned prior to the first record. The Read() method in the DataReader is used to read
the rows from DataReader and it always moves forward to a new valid row, if any row exist . You
should always call the Close method when you have finished using the DataReader object.

The following ASP.NET program execute sql statement and read the data using SqlDataReader.

Default.aspx

<head>
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
<br />
<asp:ListBox ID="ListBox1" runat="server"></asp:ListBox>
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>

default.aspx.cs

using System;
using System.Data ;
using System.Data.SqlClient ;
using System.Configuration;

public partial class _Default : System.Web.UI.Page

{
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString =
ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();

HBA ASST PROF CS DEPT, PESSAC MANDYA 6

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

SqlConnection connection = new SqlConnection(connectionString);

string sql = "select pub_id,pub_name from publishers";
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ListBox1.Items.Add(reader.GetValue(0) + " - " + reader.GetValue(1));
}
reader.Close();
connection.Close();
}
catch (Exception ex)
{
Label1.Text = "Error in SqlDataReader " + ex.ToString();
}
}
}

default.aspx.vb

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As

System.EventArgs) Handles Button1.Click
Dim connectionString As String
Dim connection As SqlConnection
connectionString =
ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString
connection = New SqlConnection(connectionString)
Dim sql As String = "select pub_id,pub_name from publishers"
Try
connection.Open()
Dim cmd As New SqlCommand(sql, connection)
Dim reader As SqlDataReader = cmd.ExecuteReader
While reader.Read()
ListBox1.Items.Add(reader.Item(0) & " - " &
reader.Item(1))
End While
reader.Close()
connection.Close()

HBA ASST PROF CS DEPT, PESSAC MANDYA 7

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

Catch ex As Exception
Label1.Text = "Error in SqlDataReader " & ex.ToString
End Try
End Sub
End Class

• Data adopter:
DataAdapter serves as a bridge between a DataSet and SQL Server for retrieving and saving
data. We can use SqlDataAdapter Object in combination with Dataset Object. DataAdapter
provides this combination by mapping Fill method, which changes the data in the DataSet to
match the data in the data source, and Update, which changes the data in the data source to match
the data in the DataSet.

• VB.Net
Dim adapter As New SqlDataAdapter(sql, connection)
adapter.Fill(ds)

• C#
SqlDataAdapter adapter = new SqlDataAdapter(sql,connection );
adapter.Fill(ds);

The SqlDataAdapter Object and DataSet objects are combine to perform both data access and
data manipulation operations in the SQL Server Database. When the user perform the SQL
operations like Select , Insert etc. in the data containing in the Dataset Object , it wont directly
affect the Database, until the user invoke the Update method in the SqlDataAdapter.

The DataAdapter can perform Select , Insert , Update and Delete SQL operations in the Data
Source. The SelectCommand property of the DataAdapter is a Command Object that retrieves
data from the data source. Other command properties of the DataAdapter are Command objects
that manage updates to the data in the data source according to modifications made to the data
in the DataSet.

The following ASP.NET program shows a select operation using SqlDataAdapter.

Default.aspx
<html>
<head>
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
<br />
<asp:ListBox ID="ListBox1" runat="server"></asp:ListBox>
<br />

HBA ASST PROF CS DEPT, PESSAC MANDYA 8

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>

</div>
</form>
</body>
</html>

default.aspx.cs
using System;
using System.Data ;
using System.Data.SqlClient ;
using System.Configuration;

public partial class _Default : System.Web.UI.Page

{
protected void Button1_Click(object sender, EventArgs e)
{
string connectionString =
ConfigurationManager.ConnectionStrings["SQLDbConnection"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
DataSet ds = new DataSet ();
string sql = "select pub_name from publishers";
try
{
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql,connection );
adapter.Fill(ds);
for (int i = 0;i < ds.Tables[0].Rows.Count -1;i++)
{

ListBox1.Items.Add(ds.Tables[0].Rows[i].ItemArray[0].ToString ());
}
connection.Close();
}
catch (Exception ex)
{
Label1.Text = "Error in execution " + ex.ToString();
}
}
}

default.aspx.vb

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Partial Class _Default

HBA ASST PROF CS DEPT, PESSAC MANDYA 9

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As

System.EventArgs) Handles Button1.Click
Dim connectionString As String
Dim connection As SqlConnection
Dim ds As New DataSet
Dim i As Integer
connectionString =
ConfigurationManager.ConnectionStrings("SQLDbConnection").ToString
connection = New SqlConnection(connectionString)
Dim sql As String = "select pub_name from publishers"
Try
connection.Open()
Dim adapter As New SqlDataAdapter(sql, connection)
adapter.Fill(ds)
For i = 0 To ds.Tables(0).Rows.Count - 1
ListBox1.Items.Add(ds.Tables(0).Rows(i).Item(0))
Next
connection.Close()
Catch ex As Exception
Label1.Text = "Error in execution " & ex.ToString
End Try
End Sub
End Class

• Accessing Data with ADO.NET

ADO.NET has two main Components that are used for accessing and manipulating data. they
are as follows:
• Data provider
• Data Set

Data Providers:

The .Net Framework includes mainly three Data Providers for ADO.NET. They are the
Microsoft SQL Server Data Provider , OLEDB Data Provider and ODBC Data Provider . SQL
Server uses the SqlConnection object , OLEDB uses the OleDbConnection Object and ODBC
uses OdbcConnection Object respectively.

ASP.NET SQL Server Connection

ASP.NET OLEDB Connection
ASP.NET ODBC Connection

HBA ASST PROF CS DEPT, PESSAC MANDYA 10

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

The four Objects from the .Net Framework provide the functionality of Data Providers in
ADO.NET. They are Connection Object, Command Object , DataReader Object and
DataAdapter Object. The Connection Object provides physical connection to the Data Source.
The Command Object uses to perform SQL statement or stored procedure to be executed at the
Data Source.

The DataReader Object is a stream-based , forward-only, read-only retrieval of query results

from the Data Source, which do not update the data. Finally the DataAdapter Object , which
populate a Dataset Object with results from a Data Source . The following link shows how to
use these Objects in ASP.NET applications.

ASP.NET Connection
ASP.NET Command
ASP.NET DataReader
ASP.NET DataAdapter

DataSet:
• The DataSet class is very important class of ADO.NET.
• it is used in disconnected architecture.
• it does not require a open or active connection to the database.
• It stores record of one or more data tables.
• It is a collection of data tables that contain the data.
• DataSet is tabular representation of data.
• Tabular representation means it represents data into row and column format.
• It represent records in the form of Database table (Row and Column) format.
• We can use DataSet in combination with DataAdapterClass
• The DataSet contains the copy of the data we requested
• The DataSet contains more than one table at a time.
• A DataSet is a local copy of your Database Table that gets populated in client PC.
• It is independent of Data Source and because it exits in the local system, it makes application
fast and reliable.
• We can set up Data Relations between these tables within the Dataset.
• The DataAdapterObject allows us to populate DataTables in a DataSet.
• We can use Fill method of the DataAdapter for populating data in a Dataset
• The ADO.NET DataSet class belongs to the System.Data namespace
• DataSet is an in-memory representation of a collection of database objects including related
tables, constraints and relationships among the tables.
• We can say that the DataSet is a small database because it stores the schema and data in the
application memory area.

HBA ASST PROF CS DEPT, PESSAC MANDYA 11

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

Example: (Your labset 10th program)

Programming Web Applications with Web Forms

Rather than writing traditional Windows desktop and client-server applications, more and
more developers are now writing web-based applications, even when their software is for
desktop use. There are many obvious advantages. For one, you do not have to create as much
of the user interface: you can let Internet Explorer and Netscape Navigator handle a lot of it
for you. Another, perhaps bigger advantage is that distribution of revisions is faster, easier,
and less expensive. When I worked at an online network that predated the Web, we estimated
our cost of distribution for each upgrade at $1 million per diskette (remember diskettes?).
Web applications have virtually zero distribution cost. The third advantage of web
applications is distributed processing. With a web-based application, it is far easier to provide
server-side processing. The Web provides standardized protocols (e.g., HTTP, HTML, and
XML) to facilitate building n-tier applications.

The .NET technology for building web applications (and dynamic web sites) is ASP.NET,
which provides a rich collection of types for building web applications in
its System.Web and System.Web.UI namespaces.

ASP.NET Web Forms

Web Forms are web pages built on the ASP.NET Technology. It executes on the server and
generates output to the browser. It is compatible to any browser to any language supported by
.NET common language runtime. It is flexible and allows us to create and add custom controls.

We can use Visual Studio to create ASP.NET Web Forms. It is an IDE (Integrated
Development Environment) that allows us to drag and drop server controls to the web forms.
It also allows us to set properties, events and methods for the controls. To write business logic,
we can choose any .NET language like: Visual Basic or Visual C#.

Web Forms are made up of two components: the visual portion (the ASPX file), and the code
behind the form, which resides in a separate class file.

HBA ASST PROF CS DEPT, PESSAC MANDYA 12

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

How to Create Web Application using webform

1. start Microsoft visual studio 2010
2. on the menu bar, choose File, New Project
The New Project dialog box open
3. Expand Installed Templates and select Visual Basic and then choose
ASP.NET Empty web Application
4. In the Name box, specify a name for your project and then choose the ok button
The New project appear in solution Explorer
How to add new webform in VB.NET

project Name (appear in solution Explorer) - right click - add NewItem - webForm - add

Example:

<html>
<head > OUTPUT:
<title></title>
</head>
<body>
<form>
<div>
<h2>Welcome to the Web Forms!</h2>
</div>
</form>
</body>
</html>

HBA ASST PROF CS DEPT, PESSAC MANDYA 13

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

ASP.NET applications with ADO.NET

We can create a web form that has ADO.NET connectivity. A simple web form that has
form controls can be submitted to the server. ADO.NET allows us to store the
submitted values to store into SQL Server database.

Here, we are creating a web form application that connects to the SQL Server database.

This web form contains the following source code.

WebForm
WebFormAdoNet.aspx

WebFormAdoNet.aspx.cs

using System;
using System.Data.SqlClient;
namespace ado.netWebFormExample
{
public partial class WebFormAdoNet : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void ButtonId_Click(object sender, EventArgs e)
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection("data source=.; database=student; integrated security=SSPI");
// Writing insert query
string query = "insert into student(name,email,contact)values('"+UsernameId.Text+ "',

HBA ASST PROF CS DEPT, PESSAC MANDYA 14

 UNIT-3 (Chapter-2 ADO.NET Connectivity)

'" + EmailId.Text + "','" + ContactId.Text + "')";

SqlCommand sc = new SqlCommand(query,con);
// Opening connection
con.Open();
// Executing query
int status = sc.ExecuteNonQuery();
Label1.Text = "Your record has been saved with the following details!";
// ----------------------- Retrieving Data ------------------ //
SqlCommand cm = new SqlCommand("select top 1 * from student", con);
// Executing the SQL query
SqlDataReader sdr = cm.ExecuteReader();
sdr.Read();
Label2.Text = "User Name"; Label5.Text = sdr["name"].ToString();
Label3.Text = "Email ID"; Label6.Text = sdr["email"].ToString();
Label4.Text = "Contact"; Label7.Text = sdr["contact"].ToString();
}
catch (Exception ex)
{
Console.WriteLine("OOPs, something went wrong." + ex);
}
// Closing the connection OUTPUT:
finally
{
con.Close();
}
}
}
}

HBA ASST PROF CS DEPT, PESSAC MANDYA 15