ADO.

NET Basics
Course Instructor :Saadia Shehryar

Overview
What is ADO.NET?

Disconnected vs. connected data access models

ADO.NET Architecture
ADO.NET Core Objects
Steps of Data Access

ADO. Net
ADO.NET is the technology that .NET applications use to

interact with a database.

A data-access technology that enables applications to
connect to data stores and manipulate data contained in
them in various ways

Data Providers
ADO.NET Data Providers are class libraries that allow a common

way to interact with specific data sources .

SQL Server provider: Provides optimized access to a SQL Server
database .
OLE DB provider: Provides access to any data source that has an
OLE DB driver
Oracle provider: Provides optimized access to an Oracle database
ODBC provider: Provides access to any data source that has an
ODBC (Open Database Connectivity) driver

Importing the ADO.NET Namespaces

Core namespace: System.Data
For SQL Server:
Imports System.Data
Imports System.Data.SQLClient

Data Provider

SQL Server

Namespace
System.Data.SqlClient

OLE DB

System.Data.OleDb

ODBC

System.Data.Odbc

Oracle

System.Data.OracleClient

ADO.NET Objects
.
Object

Object Description

Connection

Establishes a connection to a specific data source.

Command

Executes a command against a data source

DataReader

Reads a forward-only, read-only stream of data from a data

source.

DataAdapter

Populates a DataSet and resolves updates with the data source.

DataSet

DataSet objects are in-memory representations of data

Direct Data Access vs Disconnected Data

Access
The easiest way to interact with a database is to use direct

data access..
When you query data with direct data access, you dont
keep a copy of the information in memory. Instead, you
work with it for a brief period of time while the database
connection is open, and then close the connection as soon as
possible.
This is different than disconnected data access, where you
keep a copy of the data in the DataSet object so you can
work with it after the database connection has been closed.

Steps of Data Acces : Connected

Environment
Create connection
Create command (select-insert-update-delete)
Open connection
If SELECT -> use a DataReader to fetch data
If UPDATE,DELETE, INSERT -> use command objects methods
Close connection

The SqlConnection Object

To interact with a database, you must have a connection to it.
The connection string helps identify the database server, the

database name, user name, password, and other parameters that are
required for connecting to the data base.

SqlConnection conn = new SqlConnection (

"Data Source=(local) ; Initial Catalog=Northwind ;Integrated
Security=SSPI );
Connection String
(blue portion)

SqlConnection - Parameter Names

ADO.NET Connection Strings contain certain key/value pairs for specifying how to

make a database connection. They include the location, name of the database, and security
credentials.
Connection
String Parameter. Name

Description

Data Source

Identifies the server. Could be local machine,

machine domain name

Initial Catalog

Database name.

Integrated Security

Set to SSPI to make connection with user's Windows

login

User ID

Name of user configured in SQL Server.

Password

Password matching SQL Server User ID.

Storing the connection String

Typically, all the database code in your application will use the same connection

string. For that reason, it usually makes the most sense to store a connection string in
a config file.
The <connectionStrings> section of the web.config file is a handy place to store
your connection strings. Heres an example:

<configuration> <connectionStrings>
<add name="Pubs" connectionString=
"Data Source=localhost;Initial Catalog=Pubs;Integrated
Security=SSPI"/>
</connectionStrings>
</configuration>

Retreiving Connection String

You can then retrieve your connection string by name.
First, import System.Web.Configuration namespace.

Then, you can use code like this :

string connection =
WebConfigurationManager.ConnectionStrings["Pubs"].
ConnectionString;

Open and Closing Connections

You must first open a connection and close it at the end.
SqlConnection conn = new SqlConnection
(server=localhost; database=pubs; IntegratedSecurity=SSPI;);

try{

conn.Open();
..
}
catch (SqlConnection ex){ ..}
finally {

conn.Close(); }

The SqlCommand Object

Command object is used to send SQL statements to the database.
A command object uses a connection object to figure out which

database to communicate with.

SqlCommand cmd = new SqlCommand(SELECT

CategoryName FROM Categories", conn);

The SqlDataReader Object

Once youve defined your command, you need to decide

how you want to use it. The simplest approach is to use a

DataReader, which allows you to quickly retrieve all your
results.
The DataReader uses a live connection and should be used
quickly and then closed. The DataReader is also extremely
simple. It supports fast-forward-only read-only access to
your results, which is generally all you need when retrieving
information.
Before you can use a DataReader, make sure youve opened
the connection:
myConnection.Open();
.

The SqlDataReader Object

Getting an instance of a SqlDataReader is a little different

than the way you instantiate other ADO.NET objects.You must call
ExecuteReader on a command object, like this:
SqlDataReader rdr = cmd.ExecuteReader();
The ExecuteReader method of the SqlCommand object, cmd returns
a SqlDataReader instance.
Once you have the reader, you retrieve a single row at a time

using the Read() method:

myReader.Read(); // The first row in the result set is now
available.

SqlDataReader: ReadingData
The typical method of reading from the data stream returned by
the SqlDataReader is to iterate through each row with a while loop.

SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read()) {
// get the results of each column
string contact = (string)rdr["ContactName"];
string company = (string)rdr["CompanyName"];
string city = (string)rdr["City"];
// print out the results
Response.Write(contact + " " + city + " " + company);
}

The DataReader
As soon as youve finished reading all the results you

need, close the DataReader and Connection:

myReader.Close();
myConnection.Close();

SqlCommand: Querying Data

// 1. Instantiate a new command with a query and connection

SqlCommand cmd = new SqlCommand(SELECT

CategoryName FROM Categories", conn);
// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();
Results some rows that you can iterate through using rdr.Read()

method

ExecuteNonQuery
To execute operations where database is changed
Example: insert, update, delete

Returns number of rows affected by the operation

string inserting =INSERT INTO Categories

(CategoryName, Description) VALUES ('Miscellaneous', write
here')";

SqlCommand cmd = new SqlCommand (inserting,conn);

Int i = cmd.ExecuteNonQuery();
Updates the Database table and returns the number of rows affected

SqlCommand: Inserting Data

// prepare command string

string insertString = INSERT INTO Categories(CategoryName,

Description) VALUES ('Miscellaneous', elsewhere')";
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(insertString,
conn);
// 2. Call ExecuteNonQuery to send command
int affectedRows = cmd.ExecuteNonQuery();
Updates the Database table and returns the number of rows affected

SqlCommand: Updating Data

// prepare command string
string updateString = UPDATE Categories

SET CategoryName = 'Other'

WHERE CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString,
conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
Updates the Database table and returns the number of rows
affected

SqlCommand: Deleting Data

// prepare command string

string deleteString = "delete from Categories where

CategoryName = 'Other'";
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand(deleteString,
conn);
// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
Updates the Database table and returns the number of rows
affected

Putting It All Together

// 4. Use the connection
using System;
using System.Data;
using System.Data.SqlClient;
class SqlConnectionDemo
{
static void Main()
{
// 1. Instantiate the connection
SqlConnection conn = new SqlConnection(
"Data Source=(local);Initial
Catalog=Northwind;Integrated Security=SSPI");
try
{
// 2. Open the connection
conn.Open();
// 3. Pass the connection to a command object
SqlCommand cmd = new SqlCommand("select *
from Customers", conn);

// get query results

SqlDataReader rdr = cmd.ExecuteReader();
// print the CustomerID of each record
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
} }

Finally
// close the reader
if (rdr != null)
try

{
rdr.Close();

// 5. Close the connection

if (conn != null)
{

conn.Close();

} } } }

Putting It All Together

The next example demonstrates how you can use all the ADO.NET

ingredients together to create a simple application that retrieves

information from the Authors table.You can select an author record by
last name using a drop-down list box, as shown in Figure

Updating Data
To execute an Update, an Insert, or a Delete statement, you need to

create a Command object

.You can then execute the command with the ExecuteNonQuery()
method. This method returns the number of rows that were affected,
which allows you to check your assumptions.
For example, if you attempt to update or delete a record and are
informed that no records were affected, you probably have an error in
your Where clause that is preventing any records from being selected.
Before you can update and insert records, you need to make a change to the previous

example. Instead of displaying the field values in a single, fixed label, you need to
show each detail in a separate text box. Figure shows the new page. It includes two
new buttons that allow you to update the record (Update) or delete it (Delete), and
two more that allow you to begin creating a new record (Create New) and then
insert it (Insert New).

Updating a Record
When the user clicks the Update button, the information in the text boxes is applied

to the database as follows:

protected void cmdUpdate_Click(Object sender, EventArgs e)
{
// Define ADO.NET objects.
string updateSQL;
updateSQL = "UPDATE Authors SET au_fname=@au_fname,
au_lname=@au_lname,phone=@phone, address=@address, city=@city,
state=@state,zip=@zip, contract=@contract WHERE au_id=@au_id_original";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);

Updating a Record
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract",
Convert.ToInt16(chkContract.Checked));
cmd.Parameters.AddWithValue("@au_id_original",
lstAuthor.SelectedItem.Value);

// Try to open database and execute the update.

Try { con.Open();
int updated = cmd.ExecuteNonQuery();
lblStatus.Text = updated.ToString() + " record updated.";
}
catch (Exception err) {
lblStatus.Text = "Error updating author. ";
lblStatus.Text += err.Message; }
Finally { con.Close(); }

Adding a Record
To start adding a new record, click Create New to clear all the text boxes.

Technically this step isnt required, but it simplifies the users life:
protected void cmdNew_Click(Object sender, EventArgs e)
{
txtID.Text = "";
txtFirstName.Text = "";
txtLastName.Text = "";
txtPhone.Text = "";
txtAddress.Text = "";
txtCity.Text = "";
txtState.Text = "";
txtZip.Text = "";
chkContract.Checked = false;
lblStatus.Text = "Click Insert New to add the completed record.";
}

Deleting a Record
When the user clicks the Delete button, the author information

is removed from the database. The number of affected records is

examined, and if the delete operation was successful, the
FillAuthorList() function is called to refresh the page.
protected void cmdDelete_Click(Object sender, EventArgs e)
{ // Define ADO.NET objects.
string deleteSQL;
deleteSQL = "DELETE FROM Authors ";
deleteSQL += "WHERE au_id=@au_id";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(deleteSQL, con);

Deleting a Record
cmd.Parameters.AddWithValue("@au_id ",
lstAuthor.SelectedItem.Value);
// Try to open the database and delete the record.
int deleted = 0;
Try {
con.Open();
deleted = cmd.ExecuteNonQuery();
}
catch (Exception err)
{ lblStatus.Text = "Error deleting author. ";
lblStatus.Text += err.Message; }
Finally {
con.Close(); }

Disconnected Data Access

When you use disconnected data access, you keep a copy of your data

in memory using the DataSet.You connect to the database just long

enough to fetch your data and dump it into the DataSet, and then you
disconnect immediately.
There are a variety of good reasons to use the DataSet to hold onto
data in memory. Here are a few:
1. You need to do something time-consuming with the data. By dumping
it into a DataSet first, you ensure that the database connection is kept
open for as little time as possible.

Steps of Data Access:

Disconnected Environment
Defining the connection string
Defining the connection
Defining the command
Defining the data adapter
Creating a new DataSet object
SELECT -> fill the dataset object with the result of
the query through the data adapter
Reading the records from the DataTables in the
datasets using the DataRow and DataColumn
objects
UPDATE, INSERT or DELETE -> update the database
through the data adapter

Disconnected Data Access

You fill the DataSet in much the same way that you connect a DataReader.

However, although the DataReader holds a live connection, information in

the DataSet is always disconnected.
The following example shows how you could rewrite the FillAuthorList()
method from the earlier example to use a DataSet instead of a DataReader.
The changes are highlighted in bold.
private void FillAuthorList()
{
// Define ADO.NET objects.
string selectSQL;
selectSQL = "SELECT au_lname, au_fname, au_id FROM Authors";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(selectSQL, con);

Disconnected Data Access

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet dsPubs = new DataSet();
Try { con.Open();
adapter.Fill(dsPubs, "Authors");
}
catch (Exception err)
{ lblStatus.Text = "Error reading list of names. ";
lblStatus.Text += err.Message; }
Finally { con.Close();
}
foreach (DataRow row in dsPubs.Tables["Authors"].Rows)
{
string firstname = (string)row[" au_lname "];
string last = (string)row[" au_fname "];
string id = (string)row["au_id"];
// print out the results
Response.Write(firstname + " " + last + " " + id );

Sending user to new page

Anchor Tag

Click <a href="newpage.aspx">here</a> to go to

newpage.aspx.
Response.Redirect(newpage.aspx");
When you use the Redirect() method, ASP.NET immediately
stops processing the page and sends a redirect message back to
the browser. Any code that occurs after the Redirect() call wont
be executed. When the browser receives the redirect message, it
sends a request for the new page.