ADO.Net Architecture

ADO.NET

ADO.NET is the strategic application-level interface for providing data access services in the Microsoft .NET Platform. You can use ADO.NET to access data sources using new .NET Data Providers, as well as existing OLE DB Data Providers using the OLE DB .NET Data Provider.

ADO.NET Does Not Depend On Continuously Live Connections

In traditional client/server applications, components establish a connection to a database and keep it open while the application is running. For a variety of reasons, this approach is impractical in many applications:

● Open database connections take up valuable system resources. In most cases, databases can maintain only a small number of concurrent connections. The overhead of maintaining these connections detracts from overall application performance.

● In ASP.NET Web applications, the components are inherently disconnected from each other. The browser requests a page from the server; when the server has finished processing and sending the page, it has no further connection with the browser until the next request. Under these circumstances, maintaining open connections to a database is not viable, because there is no way to know whether the data consumer (the client) requires further data access.

● A model based on always-connected data can make it difficult and impractical to exchange data across application and organizational boundaries using a connected architecture. If two components need to share the same data, both have to be connected, or a way must be devised for the components to pass data back and forth.

Data Can Be Cached in Datasets

A dataset is a cache of records retrieved from a data source. It works like a virtual data store: A dataset includes one or more tables based on the tables in the actual database, and it can include information about the relationships between those tables and constraints on what data the tables can contain.

Datasets Are Independent of Data Sources

Data Is Persisted as XML

Comparison of ADO.NET and ADO

In-memory Representations of Data

In ADO, the in-memory representation of data is the recordset. In ADO.NET, it is the dataset. There are important differences between them.

Number of Tables

A recordset looks like a single table

If a recordset is to contain data from multiple database tables, it must use a JOIN query, which assembles the data from the various database tables into a single result table.

In contrast, a dataset is a collection of one or more tables.

The tables within a dataset are called data tables; specifically, they are DataTable objects

If a dataset contains data from multiple database tables, it will typically contain multiple DataTable objects.

That is, each DataTable object typically corresponds to a single database table or view. In this way, a dataset can mimic the structure of the underlying database.

A dataset usually also contains relationships

A relationship within a dataset is analogous to a foreign-key relationship in a database —that is, it associates rows of the tables with each other.

Because the dataset can hold multiple, separate tables and maintain information about relationships between them, it can hold much richer data structures than a recordset, including self-relating tables and tables with many-to-many relationships.

Data Navigation and Cursors

In ADO you scan sequentially through the rows of the recordset using the ADO MoveNext method.

In ADO.NET, rows are represented as collections, so you can loop through a table as you would through any collection, or access particular rows via ordinal or primary key index.

DataRelation objects maintain information about master and detail records and provide a method that allows you to get records related to the one you are working with

A cursor is a database element that controls record navigation, the ability to update data, and the visibility of changes made to the database by other users.

ADO.NET does not have an inherent cursor object, but instead includes data classes that provide the functionality of a traditional cursor.

For example, the functionality of a forward-only, read-only cursor is available in the ADO.NET DataReader object

Minimized Open Connections

In ADO.NET you open connections only long enough to perform a database operation, such as a Select or Update. You can read rows into a dataset and then work with them without staying connected to the data source.

In ADO the recordset can provide disconnected access, but ADO is designed primarily for connected access.

There is one significant difference between disconnected processing in ADO and ADO.NET

In ADO you communicate with the database by making calls to an OLE DB provider.

In ADO.NET you communicate with the database through a data adapter (an OleDbDataAdapter or SqlDataAdapter object), which makes calls to an OLE DB provider or the APIs provided by the underlying data source.

The important difference is that in ADO.NET the data adapter allows you to control how the changes to the dataset are transmitted to the database — by optimizing for performance, performing data validation checks, or adding any other extra processing.

Sharing Data Between Applications

Transmitting an ADO.NET dataset between applications is much easier than transmitting an ADO disconnected recordset.

To transmit an ADO disconnected recordset from one component to another, you use COM marshalling

To transmit data in ADO.NET, you use a dataset, which can transmit an XML stream.

The transmission of XML files offers the following advantages over COM marshalling:

Richer data types

COM marshalling provides a limited set of data types — those defined by the COM standard. Because the transmission of datasets in ADO.NET is based on an XML format, there is no restriction on data types. Thus, the components sharing the dataset can use whatever rich set of data types they would ordinarily use.

Performance

ADO.NET offers another performance advantage, in that ADO.NET does not require data-type conversions. ADO, which requires COM marshalling to transmit records sets among components, does require that ADO data types be converted to COM data types.

Penetrating Firewalls

A firewall can interfere with two components trying to transmit disconnected ADO recordsets. Remember, firewalls are typically configured to allow HTML text to pass, but to prevent system-level requests (such as COM marshalling) from passing.

Because components exchange ADO.NET datasets using XML, firewalls can allow datasets to pass.

ADO.NET Overview

Namespaces to work with Data

System.Data

The System.Data namespace consists mostly of the classes that constitute the ADO.NET architecture

Classes

Class	Description
Constraint	Represents a constraint that can be enforced on one or more DataColumn objects.
ConstraintCollection	Represents a collection of constraints for a DataTable.
ConstraintException	Represents the exception that is thrown when attempting an action that violates a constraint.
DataColumn	Represents the schema of a column in a DataTable.
DataColumnChangeEventArgs	Provides data for the ColumnChanging event.
DataColumnCollection	Represents a collection of DataColumn objects for a DataTable.
DataException	Represents the exception that is thrown when errors are generated using ADO.NET components.
DataRelation	Represents a parent/child relationship between two DataTable objects.
DataRelationCollection	Represents the collection of DataRelation objects for this DataSet.
DataRow	Represents a row of data in a DataTable.
DataRowChangeEventArgs	Provides data for the RowChanged, RowChanging, OnRowDeleting, and OnRowDeleted events.
DataRowCollection	Represents a collection of rows for a DataTable.
DataRowView	Represents a customized view of a DataRow exposed as a fully featured Windows Forms control.
DataSet	Represents an in-memory cache of data.
DataTable	Represents one table of in-memory data.
DataTableCollection	Represents the collection of tables for the DataSet.
DataView	Represents a databindable, customized view of a DataTable for sorting, filtering, searching, editing, and navigation.

System.Data.Oledb

OleDbConnection

OleDbCommand

OleDbCommandBuilder

OleDbDataAdapter

OleDbParameter

OleDbTransaction

OleDbType-- Specifies the data type of a field, a property, or an System.Data.OleDb.OleDbParameter

System.Data.SQLClient

System.Data.Common – Common shared (or overridden ) by individual data providers

System.Data.SqlTypes – sqlsever datatypes

Working with Data Readers

Demonstration 1: Working with Connection,Command,Datareaders (ExecuteReader,ExecuteScalar,ExecuteNonQuery)

Demonstration2 : Working with DML Operations

Demonstration3: Working with Procedures

using System;

using System.Data.OleDb;

public class a

{

public static void Main()

{

OleDbConnection cn;

cn = new OleDbConnection("Provider=MSDAORA.1;Password=tiger;User ID=scott");

OleDbCommand com=new OleDbCommand();

com.Connection=cn;

com.CommandText = "in1";

com.CommandType = System.Data.CommandType.StoredProcedure; //Text,StoredProcedure,TableDirect

com.Parameters.Add("@eno",1111);

com.Parameters.Add("@ename","hello");

try

{

cn.Open();

com.ExecuteNonQuery();

Console.WriteLine("successful");

}

catch(Exception e)

{

Console.WriteLine(e);

}

finally

{

cn.Close();

}

}

}

Retrieving Return Values and Output Parameters

Ex:

Create procedure getauthorcount as

Return (select count(*) from authors)

Getauthorcount.cs

1. Open the Connection to the database- cn

Dim c As SqlCommand = New SqlCommand("getauthorcount", cn)

c.CommandType = CommandType.StoredProcedure

Dim p As SqlParameter = c.Parameters.Add("ReturnValue", SqlDbType.Int)

p.Direction = ParameterDirection.ReturnValue

cn.Open()

c.ExecuteNonQuery()

Dim i As Integer = c.Parameters("ReturnValue").Value

MsgBox(i)

cn.Close()

Directions:

Input,Output, InputOutput, returnvalue

Improving Performance with Connection Pooling:

Connection pooling is enabled for both oledb and sqlclient connections by default.

Oledbconnection pooling is handled by the oledb .net provider

Sqlclient connection pooling is handled by windows 2000 services

Close() method

Properties:

Connection LifeTime – default 0 that is conn should never get destroyed

Connection Reset - Indicates whether connections should be reset when they are returned to the pool(true)

Enlist -- Indicates whether a connection should be automatically enlisted in the current transaction context. The default value is true

Max Pool Size – 100

Min Pool Size – 0

Pooling - true

Ex:

using System;

using System.Data.SqlClient;

public class connectionpooling

{

public static void Main()

{

connectionpooling myconnectionpooling = new connectionpooling();

myconnectionpooling.Run();

}

public void Run()

{

try

{

String connString;

connString = "server=(local)\\NetSDK;Trusted_Connection=yes;database=northwind;" +

"connection reset=false;" +

"connection lifetime=5;" +

"min pool size=1;" +

"max pool size=50";

SqlConnection myConnection1 = new SqlConnection(connString);

SqlConnection myConnection2 = new SqlConnection(connString);

SqlConnection myConnection3 = new SqlConnection(connString);

// Open two connections.

Console.WriteLine ("Open two connections.");

myConnection1.Open();

myConnection2.Open();

// Now there are two connections in the pool that matches the connection string.

// Return the both connections to the pool.

Console.WriteLine ("Return both of the connections to the pool.");

myConnection1.Close();

myConnection2.Close();

// Get a connection out of the pool.

Console.WriteLine ("Open a connection from the pool.");

myConnection1.Open();

// Get a second connection out of the pool.

Console.WriteLine ("Open a second connection from the pool.");

myConnection2.Open();

// Open a third connection.

Console.WriteLine ("Open a third connection.");

myConnection3.Open();

// Return the all connections to the pool.

Console.WriteLine ("Return all three connections to the pool.");

myConnection1.Close();

myConnection2.Close();

myConnection3.Close();

}

catch (Exception e)

{

// Display the error.

Console.WriteLine(e.ToString());

}

}

}

Transaction Programming

3 levels – Database, ado.net, page

ex:

SqlTransaction tr;

SqlConnection cn=new SqlConnection(“constring”);

SqlCommand cm1=new SqlCommand(“update1”);

SqlCommand cm2=new SqlCommand(“update2”);

Cn.open();

Tr=cn.begintransaction;

Cm1.transaction=tr;

Cm2.transaction=tr;

Try

{

Cm1.executeNonQuery();

Cm2.executeNonQuery();

Tr.commit();

}

Catch(Exception e)

{

Tr.rollback();

}

Transactions

<%@ transaction=”RequiresNew” %>

Disabled – no trans

Supported – if there supports

Required – if not there create a new

RequiresNew – always new

After you enable transactions for an ASP.NET page, the page can use two methods from the ContextUtil Class to explicitly commit or rollback a transaction: the SetComplete and SetAbort

Specifying a Command Behavior:

When you call the ExecuteReader() method of the command object you can pass an optional CommandBehavior Parameter.

The command Behavior enumeration has the following values:

1)CloseConnection :- Automatically closes an open database connection after the datareader is closed

2)KeyInfo :- Retrieves column and primary key with the data

3) SchemaOnly :- Retrieves column and table schema info without retrieving the data

4) SingleRow :- Optimizes command to retrieve only a single row. If multiple rows are returned, additional rows are discarded

The CloseConnection command behavior is useful when you want to return a DataReader from a function

Ex:

Public SqlDataReader GetAuthors()

{

cn.open();

cm=new SqlCommand(“select * from authors”, cn);

return cm.ExecuteReader(CommandBehavior.CloseConnection);

}

Retrieving Table Schema Info

Dr=cm.ExecuteReader(CommandBehavior.KeyInfo or CommandBehavior.SchemaOnly)

Miscellaneous:

ExecuteXmlReader (SQL Provider Only)

As its name implies this method will execute the command and return an XmlReader object to the caller.

SQL Server permits a SQL clause to be extended with a FORXML clause.

Ex:

Dim c As SqlCommand = New SqlCommand("select * from authors FOR XML AUTO", cn)

cn.Open()

Dim xr As XmlReader = c.ExecuteXmlReader()

While (xr.Read())

MsgBox(xr.ReadOuterXml())

End While

cn.Close()

DataSets:

DataSets and DataReaders:

The two sets of classes are useful in different applications.

1)If you simply need to grab some database records and quickly display them on a webpage, you should use the command and DataReader classes

If, On the other hand you need to work with a disconnected and memory resident representation of database records, you need to use DataSets

2)Further more it to work, the DataReader must remain conncted to a database table. A datareader is tied down to its underlying data source. This means for example that you cannot cache a DataReader in your server’s memory so that the same datareader can be used on multiple pages or over multiple requests to the same page.

A dataset enables you to represent the results of a database query in yours server’s memory.

Because a DataSet provides you with a memory-resident representation of data, you can work with the result of a database query as a whole. For ex a dataset includes methods for sorting,filtering and returning a count of the records from a database query.

3)DataReader is a forward only recordset

DataSets are very useful if you want to represent the same set of records over and over again in multiple pages or across multiple requests to the same page.(Caching)

If you need to display the diff. Set of records whenever you request a page, use a datareader.

If you need to display the same set of records when u reques a page, you should use a Dataset

DataTable:

A DataTable is a memory resident representation of a database table in a dataset.

Typically, you create a DataTable from an existing database table.

However, you also have the option of creating a DataTable and add it to DataSet.

A DataTable is collection of rows, represented by its Rows property, and a collection of columns represented by columns property.

Ex:Shopping cart

Dim t As DataTable

Dim c As DataColumn

Dim r As DataRow

t = New DataTable()

c = New DataColumn("id",GetType(Int32))

t.Columns.Add(c)

c = New DataColumn("name")

t.Columns.Add(c)

c = New DataColumn("age")

t.Columns.Add(c)

c = New DataColumn("sal")

t.Columns.Add(c)

r = t.NewRow

r("id") = 1

r("name") = "sss"

r("age") = 20

r("sal") = 10000

t.Rows.Add(r)

r = t.NewRow

r("id") = 2

r("name") = "sss2"

r("age") = 23

r("sal") = 20000

t.Rows.Add(r)

For Each r In t.Rows

For Each c In t.Columns

TextBox1.Text += r(c) & " "

Next

TextBox1.Text += vbCrLf

Next

Setting DataTable Properties:

CaseSensitive-Determines whether comparisions when searching,sorting or filtering records are case sensitive

MinimumCapacity:- specifies the initial number of rows that the DataTable accepts (default is 25)

PrimaryKey-specifies the array of columns to use as the primarykeys for the DataTable

Ex:

DataColumn c=dt.columns(“productid”);

Dt.primarykey=c;

TableName-name of the DataTable

Setting DataColumn properties in DataTable:

AllowDBNull – enables or disables null values in the column(default true)

AutoIncrement – creates a column that increments its values automatically

AutoIncrementSeed – a Long value that represents the initial value for an autoincrement column

AutoIncrementStep- represents long increment value

ColumnName

DataType- specifies the .NET type of the column

DefaultValue

Expression – creates calculated or aggregate functions

MaxLength

ReadOnly – A Boolean value which indicates the column allows updates

Unique

Creating calculated and aggregate columns

1)connection cn

2)dad-select * from products

3)ds

4)dad.fill(ds..)

5)c=new DataColumn(“salePrice”)

6)c.Expression=”unitprice*0.10”

7)ds.tables(0).columns.add(c)

aggregate functions

Max,Min,Avg,Count,Sum,Var…..

--Demonstration on Multiple Tables

--Demonstration on Data Grid

--Demonstraion on XML Integration

DataRelation:

If a DataSet contains multiple DataTables , you can define relationships.

The most common type is parent/child relation

To define the relationship both tables must share a common key

A DataSet has a Relations Property that represents a collection of DataRelations class

Ex:

1)assume that u have two tables with the names products and categories and have a common column categoryid

2)ds.relations.add(“relation1”,ds.tables(“categories”).columns(“categoryid”), ds.tables(“products”).columns(“categoryid”))

3)dp,dc – datarows

4)for each dp in ds.tables(“catgories”). Rows

dp(“categoryname”)

for each dc in dp.getchildrows(“relation1”)

dc(“productname”)

next

next

DataViews:

Represents a particular view on a DataTable. You can use Dataview to display a filtered or sorted view of the rows in a data table

Filtering:

SqlDataAdapter dad= new SqlDataAdapter(“select * from products”,cn);

Dad.fill(ds,”products”);

DataView dv=ds.tables(“products”).DefaultView;

Dv.RowFilter=”categoryid=4”;

Dg.datasource=dv;

Sort property:

dv.sort=”price,discount”

Finding rows in DataView:

dv.Find(“smith”) – if it finds return the row no

-------Cached DataSets

DataConstraint

To check constraint for a table u can iterate through ConstraintsCollection class

Applying constraints

DataColumn[] pk=new DataColumn[1];

Pk[0]=dt.Columns[“productsID”];

Dt.Constraints.Add(new UniqueConstraint(“c1”,pk[0]));

Dt.PrimaryKey=pk;

ForienKeyConstraint and UniqueConstraint