By Sriram . B

Module 13 : ADO.NET 2.0 By SRIRAM . B

Objective 

Introduction to ADO.NET & Features of ADO.NET



ADO.NET Architecture



Connecting database through Connection Object



Executing Queries through Command Object



Retrieving records through DataReader



Connect to a database by using DataAdapter



Dataset Object Model



Accessing Databases through Dataset



Command Builder



Data Relation Object



ADO.NET 2.0 Features

Introduction to ADO.NET

Introduction to ADO.NET 

It is a set of classes used with Microsoft.NET Framework language to access data in a relational, table oriented format.



ADO.NET classes are located in the System.Data.dll assembly.



This includes all of the System.Data Namespace and one class from the System.XML Namespace.



ADO.NET takes its name from ADO(ActiveX Data Objects)



Is a model used by Visual Basic .NET / C# applications to communicate with a database for retrieving, accessing, and updating data.



Uses a structured process flow to interact with a database

Diff. between ADO & ADO.NET ADO.NET

ADO 

Connected Model



Disconnected Model



Join



Data Relation Object



Recordset



DataSet



Data Transmission by TCP/IP





Stateless

Data Transmission by XML



Limited Security



Stateful



Locking is possible



Better Security



Data persist in Binary format



Locking is not reqd.



Data persist in XML

Diff. between ADO Recordset & ADO.NET Dataset ADO Recordset

ADO.NET Dataset 

Holds more than one table



You can retrieve data from two databases like oracle, Sql server



Holds only one table



Not Possible



Representation using COM



Cannot be transmitted on HTTP

and merge them in one dataset



Representation using XML



Can be transmitted on HTTP

Namespaces in ADO.NET 

System.Data



System.Data.OleD B



System.Data.SqlCl ient



System.XML

Features of ADO.NET

Features of ADO.NET 

Disconnected data architecture — Applications connect to the database only while retrieving and updating data.



Data cached in datasets — ADO.NET is based on a disconnected data structure. Therefore, the data is retrieved and stored in datasets.



Data transfer in XML format — ADO.NET uses XML for transferring information from a database into a dataset and from the dataset to another component.



Interaction with the database is done through

ADO.NET Architecture

ADO.NET Architecture

Data Provider & Data Consumer Object Data Provider Object

Data Consumer Object



Connection



Data Set



Command



Data Table



Command Builder



Data Row



DataReader



Data Column



DataAdapter



Data Relation

ADO.NET Data Provider

DataProvider 

Classes are specific to a data source



Is used for connecting to a database, retrieving data, and storing the data.



Is of two types: 



OLE DB data provider -> Provides optimized access to SQL Server 6.5, Oracle, Sybase, DB2/400 & Microsoft Access. SQL Server data provider –> Provides optimized access to SQL Server 2000/7.0

Components of Data Provider

Components of DataProvider 

Connection



Command



Command Builder



DataReader



DataAdapter

Connection Object

Connection Object 

Used to establish a connection with a data source



A OLEDB Connection object is used with an OLEDB Provider



A SQL Connection object uses TDS with Microsoft SQL Server

Some commonly used properties , methods and events :property -

ConnectionString property

method -

Open()method, Close()method

event

StateChange event

-

Connection String Property 

Provides information that defines a connection to a data store by using string of parameters



Parameters 

Provider



Connection



Initial Catalog



Data Source



Password



User ID



Integrated Security

Connection String Property For SQL Server 2000/2005 

For Windows based authentication ( By Active Directory Service)

SqlConnection conn = new SqlConnection("DataSource=stg1\\sqlexpress;

Initial

Catalog=Northwind;Integrated Security=True"); 

For SQL Server authenication

SqlConnection conn = new SqlConnection("DataSource=stg1\\sqlexpress; Catalog=Northwind;Integrated Security=True");

Initial

Connection String Property For Oracle, MS Access & SQL Server 6.5 

For Oracle OleDbConnection o = new OleDbConnection(“Provider = MSDAORA; Data Source= ORACLE817; Userid = OLEDB; Password= OLEDB;”)



For MS Access

OleDbConnection o = new OleDbConnection(“Provider = Microsoft.Jet.OLEDB 4.0;

Database Source=

c:\bin\LocalAccess40.mdb; ”) 

For SQL Server 6.5 OleDbConnection o = new OleDbConnection(“Provider = SQLOLEDB; Data Source= STG1; Initial Catalog= pubs; Userid = sa ; Password= sa;”)

Connection Pooling 

Is the process of keeping connections active and pooled so that they can be efficiently reused



Connection with identical connection strings are pooled together and can be reused without re-establishing the connection.



If the connection string is different, then a new connection will be opened, and connection pooling won't be used.



Advantage :- Improved Application performance & Scalability Enhancement.



Parameters :- Connection Lifetime, Max Pool Size, Min Pool Size, Pooling

Connection Pooling Action

Command Object

Command Object 

Is a SQL statement or a stored procedure that is used to retrieve, insert, delete, or modify data from a data source.



Is an object of the OleDbCommand or SQLCommand class.

Command Object Properties 

Connection

->

A reference to a connection object that the

command will use to communicate with the database. You can select an existing connection from the list or create a new connection 

CommandType

->

A value specified by the CommandType

enumeration, indicating what type of command you want to execute Text. 

It may be a SQLStatement /StoredProcedure/TableDirect

CommandText

->

The command is to execute. It depends on the

value of the command type property. 

Parameters

->

A collection of objects of the type SQLParameter

or OLEDbParameter. You use this collection to pass parameters into the command and to retrieve output parameters from the command.

Command Object - Methods 

Methods 

ExecuteScalar() -> Executes a command that returns a single value



ExecuteReader() -> Executes a command that returns a set of rows



ExecuteNonQuery() -> Executes a command that updates the database or changes the database structure. The methods returns the number of rows affected.



ExecuteXMLReader() -> Executes a command that returns an XML result

Example 1 – Execute Scalar using System; using System.Data; using System.Data.SqlClient; namespace SriConsole.ADO.NET { public class ExecuteScalar1 { static void Main(string[] args) { String empname; SqlConnection Con = new SqlConnection("data source=.\\sqlexpress;initial catalog=db; Integrated Security=SSPI"); Con.Open();

Example 1 – Execute Scalar SqlCommand Cmd = new SqlCommand("Select empname from emp where empid=2",Con); empname = Cmd.ExecuteScalar().ToString (); Console.WriteLine(empname); Con.Close(); Console.ReadLine(); } } }

Example 2 – Execute Scalar using System; using System.Data; using System.Data.SqlClient; namespace SriConsole.ADO.NET { public class ExecuteScalar2 { static void Main(string[] args) { String empname; int exp; SqlConnection Con = new SqlConnection("data source=.\\sqlexpress;initial catalog=db; Integrated Security=SSPI"); Con.Open();

Example 2 – Execute Scalar SqlCommand Cmd = new SqlCommand("Select max(exp) from emp ",Con); empname = Cmd.ExecuteScalar().ToString (); exp= Convert.ToInt32(Cmd.ExecuteScalar ()); Console.WriteLine(empname); Console.WriteLine (exp); Con.Close(); Console.ReadLine (); } } }

Example 1 – Execute Reader using System; using System.Data; using System.Data.SqlClient; namespace SriConsole.ADO.NET { public class ExecuteReader { static void Main(string[] args) { SqlConnection Con = new SqlConnection("data source=.\\sqlexpress;initial catalog=db; Integrated Security=SSPI"); Con.Open();

Example 1 – Execute Reader.. SqlCommand Cmd = new SqlCommand("Select * from emp",Con); SqlDataReader dr = Cmd.ExecuteReader(); while(dr.Read()) { int empid = Convert.ToInt32(dr.GetValue(0)); string empname= dr.GetString(1); int exp = Convert.ToInt32(dr.GetValue(2)); Console.WriteLine (empid+""+empname+""+exp); } dr.Close(); Con.Close(); Console.ReadLine (); } } }

Example 1 – Execute NonQuery() using System; using System.Data; using System.Data.SqlClient; namespace SriConsole.Ado.net { public class SqlInsert { static void Main(string[] args)

{

SqlConnection Con = new SqlConnection("data source=.\\sqlexpress; initial catalog=db; Integrated Security=SSPI"); Con.Open();

Example 1 – Execute NonQuery().. SqlCommand Cmd = new SqlCommand("insert into emp values(10,'J',10,'d3')",Con); Cmd.ExecuteNonQuery(); Con.Close(); Console.ReadLine(); } } }

Example 2 – Execute NonQuery() using System; using System.Data; using System.Data.SqlClient; namespace SriConsole.ADO.NET { public class SQLUserInput { static void Main(string[] args) { SqlConnection Con = new SqlConnection("data source=.\\sqlexpress;initial catalog=db; Integrated Security=SSPI"); Con.Open();

Example 2 – Execute NonQuery().. for(int i=0;i<=2;i++) { Console.WriteLine("Enter the eid"); int empid = Convert.ToInt32(Console.ReadLine()); Console.WriteLine("Enter the ename"); string empname = Console.ReadLine (); Console.WriteLine("Enter the sal"); int exp = Convert.ToInt32(Console.ReadLine()); Console.WriteLine("Enter the dept name"); string dname = Console.ReadLine();

Example 2 – Execute NonQuery().. SqlCommand cmd = new SqlCommand ("insert into emp values("+empid+",'"+empname+"',"+exp+")",Con); cmd.ExecuteNonQuery(); Con.Close(); } } } }

Command Builder

Command Builder 

It builds “Parameter” objects automatically



Used to build SQL commands for data modifications from objects based on a single table query.



Are of two types :

SQLCommandBuilder for SQL Server



OleDbCommandBuilder for OLEDB

Example – Command Builder using System; using System.Data; using System.Data.SqlClient; namespace SriConsole.ADO.NET { public class CBuilder { public static void Main() { SqlConnection c = new SqlConnection("data source=.\\sqlexpress;initial catalog=db;Integrated Security=SSPI"); c.Open();

Example – Command Builder.. SqlDataAdapter da = new SqlDataAdapter("select * from employee",c); DataSet ds=new DataSet(); da.Fill(ds); DataRow dr=ds.Tables[0].NewRow(); dr["empid"]=107; dr["empname"]="k"; ds.Tables[0].Rows.Add(dr); SqlCommandBuilder cb=new SqlCommandBuilder(da); da.Update(ds.Tables[0]); Console.WriteLine(ds.Tables[0].Rows[0][1]); Console.WriteLine(ds.Tables[0].Rows.Count); }

}

}

DataReader

Data Reader 

Is used to retrieve data from a data source in a readonly and forward-only mode.



Stores a single row at a time in the memory.



Are of two types :•SQLDataReader for SQL Server •OleDbDataReader for OLEDB

Commonly used methods: •

Read()

•

Close()

•

NextResult()

DataAdapter

DataAdapter  Creates a dataset and updates the database.  Handles data transfer between the database and the dataset through its properties and methods.  Displays the data through the process of table mapping.  Are of two types: •

SqlDataAdapter

•

OleDbDataAdapter

 Methods :- Fill, FillSchema, Update

DataAdapter Methods 

Fill

Executes the command and fill the Dataset object with data from the datasource 

FillSchema

Uses the Select command to extract just the schema for a table from the datasource, and creates an empty table in the Dataset object with all the corresponding constraints. 

Update

Calls the respective Insert Command, Update Command or Delete Command for each inserted, updated or deleted row in the dataset so as to update the original datasource with the changes made to the content of the DataSet. update more than one table.

Dataset can

Example 1 - DataAdapter using System; using System.Data; using System.Data.SqlClient; namespace SriConsole.ADO.NET { public class SelectAdapter { static void Main(string[] args) { SqlConnection Con = new SqlConnection("data source=.\\sqlexpress;initial catalog=db; user id=sa"); Con.Open();

Example 1 - DataAdapter.. SqlDataAdapter da = new SqlDataAdapter ("Select * from emp",Con); DataSet ds = new DataSet(); da.Fill(ds,"emp"); Console.WriteLine(ds.Tables[0].Rows[0] [0].ToString()); Console.ReadLine();

}

}

}

Data Consumer Object 

Data Set



Data Table





Data Row



Data Column

Data Relation

DataSet

DataSet Object Model DATASET

DataRelationCollection

DataRelation

DataRowCollection

Data Row

DataTableCollection

ExtendedProperties

DataTable

DataView

PrimaryKey

DataColumnCollection

DataColumn

DataSet 



Is present as a DataSet class in the System.Data namespace. Has its own object model.



Is a disconnected, cached set of records that are retrieved from a database.



DataSet Object •Collection of DataTable (Collection of Data Row & Data Column) •Constraints Collection (Primary Keys, Constraints,Default Values) •Parent -Child Relation between tables by DRO •Dataview( Data can be searched, filtered or manipulated while displaying data )

Types of DataSet 

Typed DataSet



Untyped DataSet

Typed DataSet 

Is derived from the DataSet class and has an associated XML schema, which is created at the time of the creation of the dataset.



Can be customized after creation.



Supports Intellisense and auto‑completion for the elements of the syntax while writing code.

Untyped of DataSet 

Does not have any associated XML schema, therefore, the structure of an untyped dataset is not known at the compile time.



Represents tables and columns as collections.



Does not support Intellisense and auto‑completion for the elements of the syntax.

DataSet Methods 

Accept Changes It commits all the changes since last time “Accept Changes” has been executed.



Reject Changes It uncommits the changes

Tracking Changes in DataSet For tracking down changes Dataset has two methods which comes as rescue “GetChanges” and “HasChanges”. 

GetChanges Returns dataset which are changed since it was loaded or since Accept changes was executed.



HasChanges Indicates that has any changes been made since the dataset was loaded or accept changes method was executed.

Diff. Between Dataset & DataReader 

Dataset is a disconnected architecture, while DataReader has a live connection while reading data. To catch data & pass to different tier “Dataset” forms the best choice using XML.



When application needs to access data from more than one table “Dataset” forms the best choice where as “DataReader” holds only one table.



DataReader provides Forward only & Read only access to data And does not support for moving back while reading records.



Dataset can persist contents while DataReader can't persists contents.



Dataset is slower than DataReader because it has relations, multiple tables etc..

DataTable

DataTable 

Provides a “NewRow” method to add a new row to DataTable.



It has “DataRowCollection” object which has all rows in a DataTable object.



Methods of “DataRowCollection” object :

Add

-> Add a new row in DataTable



Remove -> Removes a DataRow object from DataTable



RemoveAt -> Removes a DataRow object from DataTable depending on index position of the DataTable.

DataView

DataView 

Represents a complete table or can be small section of rows depending on the some criteria



Used for sorting and finding data in the DataTable.



Methods :

Find :of the row

Takes array of values and returns the index



FindRow :Takes array of values but returns a collection of DataRow



AddNew :-

Adds a new row to the DataView



Delete :-

Deletes the specified row from DataView

If we want to manipulate data of DataTable object create DataView of the DataTable object and use these functionalities :- AddNew, Delete

DataRelation Object

DataRelationObject 

It defines the navigational relationship between two tables. Typically, two tables are linked through a single field that contains the same data.

Example 1 – DataRelation Object using System; using System.Data; using System.Data.SqlClient; namespace SriConsole.ADO.NET { class DRO { static void Main(string[] args) { try { SqlConnection con=new SqlConnection("data source=.\\sqlexpress;initial catalog=db;Integrated Security=SSPI"); con.Open();

Example 1 – DataRelation Object.. DataSet ds=new DataSet(); SqlDataAdapter da1=new SqlDataAdapter("select * from dept",con); da1.Fill(ds,"dept"); SqlDataAdapter da2=new SqlDataAdapter("select * from emp",con); da2.Fill(ds,"emp"); DataRelation dr=ds.Relations.Add("emp details",ds.Tables["dept"].Columns["dno"],ds.Tables[ "emp"].Columns["dno"]);

Example 1 – DataRelation Object.. foreach(DataRow dt in ds.Tables["dept"].Rows) { Console.WriteLine("\t Department No:"+dt["dno"]); foreach(DataRow dt1 in dt.GetChildRows(dr)) { Console.WriteLine("\t Employee No:"+dt1["empid"]); Console.WriteLine("\t Employee Name:"+dt1["empname"]); } } con.Close(); } catch(Exception e) { Console.WriteLine(e.Message); } } } }

ADO.NET 2.0 Features

ADO.NET 2.0 Features 

Bulk Copy



Asynchronous Processing



Generic Coding with DB Provider Factories



Transaction Management



State Management

Bulk Copy

Bulk Copy 



The SqlBulkCopy feature in ADO.NET 2.0 enables us to copy a large volume of data between a source data store and a destination data table. This class can be used to specify the source and the target data sources for this copy operation.

Asynchronous Processing

Synchronous with Asynchronous Calls

Asynchronous Processing 

In the earlier version of ADO.NET, the ExecuteReader, ExecuteScalar and the ExecuteNonQuery methods used to block the current executing thread. However, ADO.NET 2.0 supports asynchronous data access mode.



In ADO.NET 2.0, these methods come with Begin and End methods that support asynchronous execution.

Async Operations in ASP.NET

Async Operations in ASP.NET

WaitAll

Wait Any

Async Implemented Methods

Common Provider Factory

Common Provider Factory 

In the earlier version of ADO.NET, if we wanted to implement a provider independent Data Access Layer, we had to implement the Factory Design Pattern where a class would have been responsible for returning the specific type of Command,Data Reader, DataAdapter or Connection.



In ADO.NET 2.0 we can create provider-independent data access code even without referencing the provider-specific classes using the System.Data.Common namespace that exposes a number of factory classes.



The DbProviderFactory class contains two methods called the GetFactoryClasses method and the Getfactory method. While the former is responsible for retrieving all the providers supported, the later can be used to retrieve the specific provider.

Code - DB Provider Factory DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory("System.Data.SqlClie nt"); DbConnection dbConnection = dbProviderFactory.CreateConnection();

Hierarchy

System.Data.Common.DbProvider.Fa ctory

DB Provider Factory

Transactions

DB Transactions

Transaction – Concurrency Problems

Transactions Isolation Level

Settings Isolation Level

Transactions in ASP.NET 1.1

Reset Isolation Levels

Save Point in Transactions

Transactions Demo

Setting the Save Point

Transaction Handling

Transaction Scope

Transactions Completed Event

Setting Isolation Level

Session State Configuration

Session State Configuration

Session State Management

In Process Mode

State Server Mode

SQL Server Mode

Disabling Session State

Demo

Session Ends

Exercise

Relax