Using ADO.

NET with Examples

Templated Controls DataBinding

Notes Burhan Saadi

Version 1.0.4

Notes Burhan Saadi Version 1.0.4

Introduction to ADO.NET
„ ADO.NET consists of a large subset of classes in BCL
that allow us to access, manipulate and update a
single or several different data sources concurrently.

„ ADO.NET shares all basic features of the Base class

library such as JIT, Garbage collection, dynamic
caching and object oriented design.

„ ADO.NET allow us to access and manipulate different

types of data sources such as RDBMS, Text files, XML
data sources, Spread sheets etc.

Notes Burhan Saadi Version 1.0.4

Advantages of ADO.NET
„ Managed classes
„ Cross language support
„ Improved and Cleaner architecture
„ XML Support
„ Supports Disconnected (Improves scalability by not maintaining
connection for long period of time
„ Transaction support
„ Support for Connected database access

Notes Burhan Saadi Version 1.0.4

Providers & Consumers
„ ADO.NET components can be divided into two
categories

„ Providers ( 3 Providers in ADO.NET)

„ OLEDB Data providers

„ SQL Data Providers

„ ODBC Data Providers

„ Custom Providers

„ Consumers

Note: Each provider exist inside its own namespace inside System.Data namespace.
Notes Burhan Saadi Version 1.0.4
Working Modes of ADO.NET
There are two Working modes of ADO.NET. The first working
mode is connected and the second working mode is
disconnected. However connection object is required in both
modes.
„ Connected
The developer establishes a connection to a database.
Commands are then sent over that connection. Data is
returned in DataReader object.
„ Disconnected
DataSet object allows to access the data from the data
source. Work with the data offline, establish a new
connection when data is to be sent back to the database.
Notes Burhan Saadi Version 1.0.4
 Basic Objects an Interaction
ADO.NET Data Consumer

Provider classes
DataAdapter DataSet WinForm

Connection Command WebForm

DataReader Other

Note: DataSets are disconnected from original datasets and represent data

Notes Burhan Saadi Version 1.0.4

 Data Providers Objects
„ Connection
To create a connection with the data source (Required for both modes)
„ Command
Used to Execute the command against the data source and it retrieves a
dataset or a data reader. This also helps us to execute Insert, Delete or
Update command against a data source.
„ DataReader
A forward only and read only connected result set. It provides a stream of
data.
„ DataAdapter
Used to populate the DataSet with data from the data source and to update
data source.

Note: Different implementations of these components exists depending

upon the provider type we are using
Notes Burhan Saadi Version 1.0.4
Database Connectivity Methods
„ METHOD-1
The Data Form Wizard will create a form to establish a
connection, display data in control instances appearing on the
form, and update that data

„ METHOD-2
You can use controls appearing on the Data tab of the Toolbox
to establish a database connection and to update database data

„ METHOD-3
You can also create all of the necessary ADO.NET objects
programmatically

Notes Burhan Saadi Version 1.0.4

The DataSet
„ Represents Disconnected data in the memory that
can be manipulated, updated and reconciled with the
original data source.
„ DataSet can also be considered as a memory resident
disconnected data subset
„ Usually DataSet works with DataAdapter Object.
„ DataSet objects can be stored in a session variable

Notes Burhan Saadi Version 1.0.4

 Composition of DataSet Object
DataSet

Tables
DataTable Collection

DataTable

DataColumn
Collection
DataColumn
DataRow
Collection DataRow
Constraint
Collection Constraint

Relations
DataRelation Collection Notes Burhan Saadi Version 1.0.4
Naming Conventions
„ Each type of providers i.e., OLEDB, SQL or ODBC
implement common classes. So its clear that we have
three distinct connection classes that map to three
different data access technologies.

„ To differentiate between these three connection

objects each set of provider have prefixed technology
name with the Objects e.g.,

Example:
OleDBConnection
SQLConnection
Notes Burhan Saadi Version 1.0.4
Using OLE Providers
Using OLE Providers

Notes Burhan Saadi Version 1.0.4

Objective: Retrieving Data with OleDataReader
Example: 1

„ In this application we will make use of three key classes to use

connection oriented mode of ADO.NET through OLEDB provider
i.e.,

„ OleDBConnection
„ OleDBCommand
„ OleDBDataReader

Notes Burhan Saadi Version 1.0.4

 Objective: Retrieving Data with OleDataReader
Example: 1
using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
//OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\\Product.mdb");
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\Product.mdb");
con.Open();
OleDbCommand com = con.CreateCommand();//Alternative can be used
com.CommandText = "select * from Products";
OleDbDataReader data1 = com.ExecuteReader();

while(data1.Read())
{
System.Console.WriteLine("{0},{1}\n",data1["ProductID"], data1["ProductName"]);// no space
//System.Console.WriteLine("{0},{1}\n",data1[0], data1[1]);
System.Console.WriteLine("{0},{1}\n",data1.GetValue(0), data1.GetValue(1));
}
Notes Burhan Saadi Version 1.0.4
Objective: Retrieving Data with OleDataReader
Example: 1

data1.Close();
con.Close();
System.Console.ReadLine();

}
}
}

Notes Burhan Saadi Version 1.0.4

Managing Multiple Results
„ One can get more than one results by calling ExecuteReader
„ Have a look at following code; its extension of previous code
„ This technique does not work with Access but it works fine with SQL
Server.
„ Both SQL and OLEDB Readers are capable of executing multiple
queries at the spontaneously. This helps us in avoiding the network
traffic.

Notes Burhan Saadi Version 1.0.4

 Managing Multiple Results
Example 2:
sing System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string query = "select * from Products;select * from Suppliers";

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DATA

SOURCE=c:\Product.mdb");

OleDbCommand com = new OleDbCommand(query,con);

con.Open();
OleDbDataReader data1 = com.ExecuteReader();

Notes Burhan Saadi Version 1.0.4

Managing Multiple Results
Example 2:
do
{
System.Console.WriteLine("{0},{1}",data1.GetName(0),data1.GetName(1));
while(data1.Read())
System.Console.WriteLine("{0},{1}\n",data1[0], data1[1]);// no space
}while(data1.NextResult());

data1.Close();
con.Close();
System.Console.ReadLine();
}
}
}

Notes Burhan Saadi Version 1.0.4

 Manipulating Data
Example: 4
OleDbConnection connector;
OleDbCommand commands;
connector = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\zzz.mdb");
connector.Open();
System.Console.WriteLine("Database connected and opened .....");
commands = new OleDbCommand("Create Table a1(vno integer, name char(30))",connector);
commands.ExecuteNonQuery();
commands.CommandText="Insert into a1 values (1,'abc')";
commands.ExecuteNonQuery();
System.Console.WriteLine("Created a Table and added some data.....");
commands.CommandText = "update a1 set vno=3,name='good' where vno=1";
commands.ExecuteNonQuery();
//commands.CommandText = "delete from a1";
//commands.ExecuteNonQuery();

Notes Burhan Saadi Version 1.0.4

DataSet
„ We have already learned that disconnected data sets can be
filled using DataAdapter class only.

„ DataSets can be decomposed into collection of Tables that may

further be decomposed into columns and rows collections.

„ Lets have a look at a simple example that fills a dataset using a

DataAdapter and displays the data on the screen

„ Fill method of DataAdapter class allow us to fill in the dataset.

There are number of overloaded forms of the fill method. One
of the form allow us to load any particular number of rows,
even starting from a particular position.

Notes Burhan Saadi Version 1.0.4

 Loading Result into a DataSet table
Example: 1
using System;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\product.mdb";
string query = "select * from products";
OleDbConnection con = new OleDbConnection(constr);
con.Open();

Notes Burhan Saadi Version 1.0.4

 Loading Result into a DataSet table
Example: 1
OleDbDataAdapter adapter = new OleDbDataAdapter(query,con);
DataSet dataset = new DataSet();
int noofrows = adapter.Fill(dataset,"Products"); //***
//Now it contains all the information in memory & lets get required table with result
DataTable dtable = dataset.Tables["Products"];//**
foreach(DataRow row in dtable.Rows)
{
System.Console.WriteLine(row["ProductID"]);
System.Console.WriteLine(row["ProductName"]);
}
System.Console.ReadLine();//just for a wait
con.Close();

}
}
}
//Note: If you don’t pass *** 2nd parameter than you can’t use Products table name **

Notes Burhan Saadi Version 1.0.4

 Updation, Deletion and Insertion in DataSet
Example: 2
static void Main(string[] args)
{
string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=c:\product.mdb";
string query = "select * from products";
OleDbConnection con = new OleDbConnection(constr);
con.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(query,con);
OleDbCommandBuilder combuilder = new OleDbCommandBuilder(adapter);
DataSet dataset = new DataSet();
int noofrows = adapter.Fill(dataset,"Products");
//Now it contains all the information in memory
DataRow row = dataset.Tables["Products"].NewRow();
row["ProductName"]= "Hello";
dataset.Tables["Products"].Rows.Add(row);
int g = adapter.Update(dataset,"Products");
con.Close();
}
//If we don’t specify the command builder object the update statement will fail and it will ask us
//to provide insertCommand explicitly

Notes Burhan Saadi Version 1.0.4

 ASP.NET grid and binding
Example:
In this example we simply want to present the records of Product class
in a well formatted grid.

„ Drag a Data grid control on web form

„ Create a Connection and Command Objects to get a DataReader Object
„ In PageLoad get the Reader object and associate it with the Grid control
„ You can use DataSource property of the grid control to do so
„ As requirement of data-binding you must call DataBind function in the
PageLoad.
„ Set the style for the grid

Notes Burhan Saadi Version 1.0.4

SQL Enterprise Manager TIP

Notes Burhan Saadi Version 1.0.4

 ASP.NET grid and binding

STEP 1:
Get a DataGrid

Partially
Programmatic

Notes Burhan Saadi Version 1.0.4

 ASP.NET grid and binding

STEP 2:
Apply Formatting

Notes Burhan Saadi Version 1.0.4

 ASP.NET grid and binding

STEP 3:
Add Connection
Add Command
Objects

STEP 4:
Set the connection
property of the
Command object and
ConnectionString
property of the
connection object

Notes Burhan Saadi Version 1.0.4

 ASP.NET grid and binding

Step 5: Add required name spaces

using System.Data;
using System.Data.OleDb;

Step 6: Add following code for data-binding in the page

Notes Burhan Saadi Version 1.0.4

 ASP.NET grid and binding
RESULT

Notes Burhan Saadi Version 1.0.4

Paging & Visual Technique
In this example we want to learn that how we can define a
template for templated controls and how we can update data in
the grid control.

„ This time we will use DataSet instead of DataReader just for a

change.
„ We will also see that how paging can be performed
„ Add a new Web Form in Web Application and drag Grid control
on it.

Notes Burhan Saadi Version 1.0.4

 Paging & Visual Technique
1. Add Grid
2. Add Connection
3. Configure Con.
4. Add DataAdapter
5. Configure Adapter
6. Generate DataSet
7. Program PageLoad

Notes Burhan Saadi Version 1.0.4

 Paging & Visual Technique
Set the select command
option from the
properties of adapter &
Click advanced to Add
Commandtext option.
In Command text option
Type in the required
Query or quires delimited
By the ;.

Also set the connection

Property.

Now right click on adapter

Compnent in the design view and select the Generate DataSet option
Notes Burhan Saadi Version 1.0.4
Paging & Visual Technique
„ Using Property builder to bind generated DataSet with the Grid
control

Notes Burhan Saadi Version 1.0.4

 Paging & Visual Technique

Note: For DataTable we can add KeyDeleteRule for cascaded deletions.

Notes Burhan Saadi Version 1.0.4

 Paging & Visual Technique
Note: Output
Now you can see a little
problem here and the
problem is that all the
records are printed on
the same page. What
if we like to see 10 first
and then next 10 and so
on.

To achieve paging right

click on the grid and
select Property Builder.

Select Paging option

Notes Burhan Saadi Version 1.0.4
 Paging & Visual Technique
Paging
•To achieve paging
right click on the
grid and select
Property Builder.

•Select Paging option

•Turn on Allow Paging

•But doing only this will

not enable paging

•Now select grid properties

and add event handler for
PageIndexChanged Notes Burhan Saadi Version 1.0.4
 Paging & Visual Technique
Write Following code in the PageIndexChanged handle as given below

private void DataGrid1_PageIndexChanged(………..)

{
this.DataGrid1.CurrentPageIndex = e.NewPageIndex;
DataBind();
}
//e is argument received in
//handler

Notes Burhan Saadi Version 1.0.4

Using Templates
We can define templates for each row in the data-bound
templated controls like DataGrid

Easy Example:
Suppose I want URL to be a valid clickable URL. This
task can be done using different approaches, lets do it
using templates.

To Convert URL into a valid clickable URL in the GRID cell, we

must select Property builder of the grid first

Notes Burhan Saadi Version 1.0.4

 Using Templates Turn this off

In the property builder

Select the columns option
Move all fields of bound
Dataset to selected columns.

Select the WebPageURL field

And click the blue option at the
Bottom i.e.,

“Convert Column into Template ..”

Press Apply Button and Click OK

Notes Burhan Saadi Version 1.0.4

Using Templates
2

„ Now go back to WebForm designer

„ Select the Grid and Right Click for
menu
„ In Menu select the Edit Template
option
„ Now You can See this screen
„ Change label with Hyper Link
3 1

Notes Burhan Saadi Version 1.0.4

 Using Templates

•Bind the hyperlink text property

to Required field

•Bind the NavigateURL property to

the WebPageURL field of our
student table.

Notes Burhan Saadi Version 1.0.4

Result

Notes Burhan Saadi Version 1.0.4

 Editing Using Templates (Visual Technique)
„ Create a new WebApplication
„ Drag an OleDbDataAdapter on the Web
form in the design view.
„ Database Connectivity Wizard will ask
about database you want to connect to.
„ Remember that if you are using SQL
database. Do not forget to add the
ASPNET account to the SQL Servers
Security accounts.

Notes Burhan Saadi Version 1.0.4

Editing Using Templates (Visual Technique)
OleDBDataAdapter Connectivity Wizard

Notes Burhan Saadi Version 1.0.4

Editing Using Templates (Visual Technique)
OleDBDataAdapter Connectivity Wizard

Drag a DataGrid on WebForm

Notes Burhan Saadi Version 1.0.4

 Editing Using Templates (Visual Technique)
Generating Typed DataSet

After generating dataset, it can either be visually bound to the DataGrid or you may bind it programmatically
Its better to add the dataset object to the Designer view for programmatic manipulation
Always keep in mind data binding in ASP.NET is one way i.e., one can read from data source to populate grid
But changes of Grid are not reflected back to the data source.

Notes Burhan Saadi Version 1.0.4

 Editing Using Templates (Visual Technique)
Binding the Grid using Design View

Select the property builder of the

grid and attach the dataset. If you
Don’t attach dataset here then
You go to manually bind different
controls in the grid columns to
Dataset fields.

However both things work

Notes Burhan Saadi Version 1.0.4

Editing Using Templates (Visual Technique)
Binding the Grid using Design View
• Turn off Create Columns automatically
• Add Available columns (All)
• Add select button
• Add Edit, update and cancel button
• For this example convert all Selected
columns to templated columns
• To convert a column into templated
select the column you want to covert
using selected column list and then
click the hyper-link to convert

Notes Burhan Saadi Version 1.0.4

 Editing Using Templates (Visual Technique)

„ Add Edit, Update and Delete buttons using property builder

„ Select Grid Events and Add handlers for the button columns
„ In property builder convert all changeable fields into templated columns

Observe Readonly

Notes Burhan Saadi Version 1.0.4

 Using Templates: Adding Select Button

„ Open Grid Property Builder

„ From Buttons Columns add required buttons to selected columns e.g., Select
„ After adding this column when ever you click on a column it will be automatically
selected

Notes Burhan Saadi Version 1.0.4

Using Templates: Edit, Update and Delete
„ Now Edit Template and provide proper controls for editing. For example in case
of student.mdb I have converted First Name and Address column for editing
„ Add and bind textboxes in edit section of template
„ Change unchangeable columns to read only
„ Minimal code for Edit column in the Grid

private void DataGrid1_EditCommand(object source,

System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = e.Item.ItemIndex;
DataBind();
}

Notes Burhan Saadi Version 1.0.4

 Editing Using Templates (Visual Technique)

private void DataGrid1_CancelCommand(object source,

System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex = -1;
DataBind();
}

Notes Burhan Saadi Version 1.0.4

 Editing Using Templates (Visual Technique)
After Converting Field columns into templated

„ After converting field columns into

templated columns. Edit templates for
proper binding expression and also
provide proper controls where ever
required.
„ For Example, we never like to change
Student ID value because it’s a primary
key. Therefore we don’t need a TextBox
control in the Edit mode of the grid for
this particular columns. We must replace
it with a label and bind the label control
to the StudentID field of container.

Notes Burhan Saadi Version 1.0.4

 Editing Using Templates (Visual Technique)
After Converting Field columns into templated
DataBinder.Eval(Container,
"DataItem.StudentID")

You can also change the control ID’s and assign proper names
TIP: In all types of repeater controls; you can add ItemCommand Handler for
Managing events that are generated due to any buttons inside the grid
Note: Similarly check all the templated controls

Notes Burhan Saadi Version 1.0.4

Editing Using Templates (Visual Technique)
PageLoad code
if(!this.IsPostBack)
{ this.oleDbConnection1.Open();
this.oleDbDataAdapter1.Fill(dataSet11);
Session["DataSet12"] = dataSet11;
DataGrid1.EnableViewState=true;
DataGrid1.DataSource = dataSet11;
//Keep it here because you may not bind on each post back
//otherwise the data changed by the post back will be re-changed
//back to original data
DataBind();
}
else
{ dataSet11 = (DataSet1) Session["DataSet12"]; }

Notes Burhan Saadi Version 1.0.4

 Editing Using Templates (Visual Technique)
Update Command Code - 1

private void DataGrid1_UpdateCommand(object source,

System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
Label txtStudentID;
TextBox txtFirstName;
TextBox txtAddress;
TextBox txtWebPageURL;
//With 0 value it returns Non Edit Control with 1 it return Edit control in template
txtStudentID = (Label) e.Item.Cells[0].Controls[1];
txtFirstName = (TextBox) e.Item.Cells[1].Controls[1];
txtAddress = (TextBox) e.Item.Cells[2].Controls[1];
txtWebPageURL = (TextBox) e.Item.Cells[3].Controls[1];
//Another Way to Aquire data from the Grid
//TextBox tb = (TextBox) DataGrid1.Items[e.Item.ItemIndex].FindControl("Address2");
//Lets Filter to the required row
dataSet11 = (DataSet1) Session["DataSet12"];
dataSet11.Tables[0].DefaultView.RowFilter = "StudentID ="+txtStudentID.Text;
Notes Burhan Saadi Version 1.0.4
Editing Using Templates (Visual Technique)
Update Command Code - 2
if(dataSet11.Tables[0].DefaultView.Count > 0)
{
dataSet11.Tables[0].DefaultView[0]["FirstName"] = txtFirstName.Text;
dataSet11.Tables[0].DefaultView[0]["Address"] = txtAddress.Text;
dataSet11.Tables[0].DefaultView[0]["WebPageURL"] = txtWebPageURL.Text;
}
//This works only if you have used wizard to generate
//Update Command for the query otherwise you got to do it manually here

this.oleDbDataAdapter1.Update(dataSet11);
dataSet11.Tables[0].DefaultView.RowFilter = string.Empty;
DataGrid1.EditItemIndex = -1;
DataGrid1.DataSource = dataSet11;
DataBind();

}//End of function

Notes Burhan Saadi Version 1.0.4

Delete in Grid
„ How?

Notes Burhan Saadi Version 1.0.4

Stored Procedures
Why they are better

„ Stored procedures are contained in database and contain queries which

are kept in precompiled form.

„ Stored procedures improve readability of the database client program.

„ They result in reduced network traffic.

Notes Burhan Saadi Version 1.0.4

Create Stored Procedures
Using NorthWind 1

„ For Testing purposes lets create

following two stored procedures and
set there permission levels.
„ Lets use SQL Server Enterprise
manager for this purpose.
„ One stored procedure requires
parameters.

3 2

Notes Burhan Saadi Version 1.0.4

Create Stored Procedures
Don’t forget to set the permissions

Notes Burhan Saadi Version 1.0.4

 Executing Stored Procedures
Example Disconnected:

private void Page_Load(object sender, System.EventArgs e) 1

{
OleDbConnection con = new OleDbConnection(constring); This technique uses
OleDbCommand com1 = new OleDbCommand("Exec GetProducts",con); Exec statement to
OleDbDataAdapter da = new OleDbDataAdapter(com1); Execute the stored
da.Fill(ds,"Products");
DataGrid1.DataSource= ds;
Procedure
DataBind();
}

private void Page_Load(object sender, System.EventArgs e)

{
2
OleDbConnection con = new OleDbConnection(constring); This technique uses
OleDbCommand com1 = new OleDbCommand("GetProducts",con); command objects
com1.CommandType = CommandType.StoredProcedure;
OleDbDataAdapter da = new OleDbDataAdapter(com1);
CommandType
da.Fill(ds,"Products"); Property to solve
DataGrid1.DataSource= ds; the problem.
DataBind();
}

Notes Burhan Saadi Version 1.0.4

Executing Stored Procedures
Example connected:

private void Page_Load(object sender, System.EventArgs e)

{
OleDbConnection con = new OleDbConnection(constring); 3
con.Open();
OleDbCommand com1 = new OleDbCommand("GetProducts",con);
com1.CommandType = CommandType.StoredProcedure;
OleDbDataReader dr = com1.ExecuteReader();
//In connection oriented case; the datasource statements
//should be written prior to closing connection
DataGrid1.DataSource= dr;
DataBind();
dr.Close();
con.Close();
}

Notes Burhan Saadi Version 1.0.4

 Stored Procedures
With Parameters Example 1

private void Page_Load(object sender, System.EventArgs e)

{
OleDbConnection con = new OleDbConnection(constring);
con.Open();
OleDbCommand com1 = new OleDbCommand("GetProductByID",con);
com1.CommandType = CommandType.StoredProcedure;
com1.Parameters.Add("@CategoryID",OleDbType.Integer).Value = 1;
OleDbDataReader dr = com1.ExecuteReader();
//In connection oriented case; the datasource statements
//should be the first ones
DataGrid1.DataSource= dr;
DataBind();
dr.Close();
con.Close();
}

Notes Burhan Saadi Version 1.0.4

 Stored Procedures
With Parameters Example 2

private void Page_Load(object sender, System.EventArgs e)

{
OleDbConnection con = new OleDbConnection(constring);
con.Open();
OleDbCommand com1 = new OleDbCommand("Exec GetProductByID @CategoryID=1",con);
OleDbDataReader dr = com1.ExecuteReader();
//In connection oriented case; the datasource statements
//should be the first ones
DataGrid1.DataSource= dr;
DataBind();
dr.Close();
con.Close();
}

Note: For non query procedures for insert or delete; we can call ExecuteNonQuery()

Notes Burhan Saadi Version 1.0.4

 Stored Procedures
With Parameters Example 2

„ Out parameters can also be specified

com1.Parameters.Add("@val",OleDbType.Integer);
com1.Parameters["@val"].Direction = ParameterDirection.Output;

„ This sort of a parameter is required with the procedure that returns some sort of a
result.

„ By default the parameter direction is Input.

TIP:
To get back a single variable result ExecuteScalar of the Command object can be used

Notes Burhan Saadi Version 1.0.4

DataSet Merging
„ How?

Notes Burhan Saadi Version 1.0.4

DataSet Operations
„ If you want to move only modified rows from one dataset to another data set
then you need to call
dsModified = dsAbc.GetChanges()

„ If you need to get added rows only then you use

dsModified.GetChanges(DataRowState.Added)

„ To Merge a table or a dataset in another dataset or table we can use

ds.Merge(table|otherDataSet)
*See Merging to partially compatible tables MissingSchemaAction.Ignore

Notes Burhan Saadi Version 1.0.4

Using OleDbCommandBuilder

„ How?

Notes Burhan Saadi Version 1.0.4

 Transactions
„ A transaction is a group of tasks that must be performed successfully to
achieve a success job. This is a general definition of the transactions.

„ In database context, a transaction is a group of database commands that are

treated as a single unit. A failure of one command is considered to be the
failure of all commands. At failure system is left at the state prior to
execution of first command in the group. This is known as RoleBack.

„ On success of the entire group the changes are applied and this is known as
commit.

„ Transactions can contain some element whose success or failure can be

ignored.

Notes Burhan Saadi Version 1.0.4

 Transactions
„ Database programmers can find what commands belong in a transaction using
ACID test.

„ Atomic: They make up a single unit

„ Consistent: Relationships between data and database are maintained correctly.

„ Isolated: Changes made by the other clients can’t affect the current changes.

„ Durable: Once change is made it is permanent (power failure or system boot-up

will not effect it)

Notes Burhan Saadi Version 1.0.4

Transactions
„ In ADO.NET the transactions can be handled in several different ways.
„ Data sets provide transaction processing through RejectChanges
and Update methods. AcceptChanges method provides an option to
reset the records status to unchanged.

„ Database connection objects provide transaction processing

through the transaction object. Transaction objects track
commands performed on a database and provide the Rollback,
Commit or create a save point within the transaction.

„ The last method is by using ContextUtil class of

System.EnterpriseServices namespace. This class is used with
COM+ & Microsoft distributed transaction co-ordinator provided
with the MS SQL Server.

Notes Burhan Saadi Version 1.0.4

 Transactions
„ Steps to use Transaction objects
„ Open a database connection
„ Call BeginTransaction of connection object, it will create an return a
transaction object. In this method we can pass in the isolation level
for the transaction object.
„ Create a command object and pass connection and transaction
objects in its constructor
„ Execute the commands and check errors
„ If there are no errors call commit method of the transaction class
otherwise call the rollback.

Notes Burhan Saadi Version 1.0.4

 Transactions
Isolation level Behavior
ReadUncommitted Doesn’t lock the records being read.
Also known as dirty read because the data becomes inconsistent if
multiple users work with the same data.
Chaos Works like ReadUncommitted. (With Slight diff)

ReadCommitted Locks records before reading and free them up as soon as operation is
performed. New records can be added, deleted or changed.
RepeatableRead Locks the read records for entire transaction period; so that they can
not be changed by the other clients.
Serializable Locks the entire dataset and keep it locked until the transaction
completes. Even order of the data should remain the same.

Notes Burhan Saadi Version 1.0.4

 Transactions
Example 1

string constring = @"Integrated Security=SSPI;Initial Catalog=Northwind;Provider=""SQLOLEDB.1""";

string sqlcommand = "INSERT INTO Products(ProductName, CategoryID, Discontinued, ReorderLevel,
UnitsOnOrder, UnitsInStock, UnitPrice, QuantityPerUnit, SupplierID) VALUES (N'12',
1, 12, 12, 12, 12, 12, N'12', 12)";
OleDbConnection con = new OleDbConnection(constring);
con.Open();
OleDbTransaction trans = con.BeginTransaction(IsolationLevel.Serializable);
OleDbCommand command1 = new OleDbCommand(sqlcommand,con,trans);
command1.ExecuteNonQuery();
//if commit is skipped nothing will be written to database
trans.Commit();
con.Close();

Notes Burhan Saadi Version 1.0.4

 OleDbTransaction & OdbcTransaction

Methods Description
Commit Commits the database transaction
RollBack Rolls back transaction from a pending state

Notes Burhan Saadi Version 1.0.4

 SqlTransaction
Methods Description
Commit Commits the database transaction
RollBack Rolls back transaction from a pending state
Save Creates a save point in the transaction that can
be used to roll back a portion of the transaction,
and specifies the save point name.

Notes Burhan Saadi Version 1.0.4

Enterprise Transactions
„ Transactions can span to multiple webforms or components,
asp.net provides a way to work with distributed transaction
coordinator.

„ Transaction attribute can be added to the page directive either

to continue the same transaction or start a new transaction

„ ContextUtil class can be used with SetAbort or SetComplete

methods.

„ Page classes CommitTransaction and AbortTransaction events

can be used to respond to changes in the transactions status

Notes Burhan Saadi Version 1.0.4

Adding ASP.NET A/C to SQL Server

„ Don’t forget to add ASP.NET account to SQL server

security accounts for default access.

Notes Burhan Saadi Version 1.0.4

Other Topics
„ Multiple Record Editing in Grid
„ Mannual Update Commands in Grids
„ Insertions in Grids

Notes Burhan Saadi Version 1.0.4