Company Confidential

1

Working with Data and ADO.Net

Objectives

• To understand the Underlying Technologies of Data and ADO.NET

– Overview of ADO.NET– Creating Connection to the Database– Displaying data into various controls– Usage of Datagrid using Dataset– Accessing Data with DataSets– Accessing Data with DataReaders– Using SqlDataReader

What is ADO.NET

ADO.NET is essentially a collection of .NET Classes that expose methods and attributes used to manage communication between an application and a data store.

ADO.NET is set to become a core component of any data driven .NET application or Web Service.

ADO.NET

ADO.NET is Microsoft Latest Data Access Technology to retrieve, manipulate and update data in many different ways.

ADO.NET is an integral part of .NET Framework that’s why it shares many of its features such as multi-language support, garbage collection, object oriented design etc.

ADO.NET

• It is a model used by .NET applications to communicate with a database for retrieving, accessing, and updating data.

• It is a set of classes

• Can be used with any .NET language

• Can handle Disconnected Data Stores

• Can interact with XML

What is Connected Architecture?

Application

Step 1: Connect to database

Step 2: Access data

Step 3: Close the Connection Database

Introduction to Disconnected Architecture

Application

Step 1: Connect

Step 2: Retrieve data and store it locally

Step 3: Disconnect Database

Step 5: Connect,

Update the database with changes in Local Copy, Disconnect

DataSet

Step 4: Manipulate data

at Local Copy

ADO.NET Object Model

DATASET

.NET APPLICATION (WINDOWS/ WEB FORM)

DATA PROVIDER

Accessing retrieved data

Accessing retrieved data

CONNECTION

COMMAND

DATA ADAPTER

DATA READER

Establishes connection with the database

Filling dataset with data

Transfers data to the dataset and reflects the changes made in the dataset to the database

DATABASE

Retrieves data in a read-only, forward only mode

Executes a command to retrieve data from the database

Architecture of ADO.NET

ADO.NET architecture consists of three main components:

1. Data Provider2. DataSet3. DataView

`

UIUI

DataViewDataViewDataRelationDataRelation

DataColumn

DataColumn

DataColumn

DataColumn

DataRow

DataRow

DataSet

DataTable

DataRow

DataRow

DataColumn

DataColumn

DataTable

Architecture of ADO.NET

Data Provider

DataReaderDataReader DataAdapterDataAdapter

CommandCommand

ConnectionConnection

DB

MSDAORA

SQLOLEDB

Earlier Scenario of Database Access

Win Application

Oracle

Native API

ODBC Manager

SQL Server

ODBC Driver

OLE DB Provider

ADO

RDO

DAO

ODBCDirect

Declare

Data Providers

The link to the database is created and maintained by the Data Provider. A Data Provider is not a single component, but a set of related components that work together.

The components of Data Provider are followings:1. Connection2. Command3. DataReader4. DataAdapter

.NET Data Providers

ADO.NET

MS-SQLSqlDataProvider

Oracle

OracleDataProvider

OLE DB Data source

OledbDataProvider

OdbcDataProvider

ODBC Data source

System.Data.SqlClient

System.Data.Odbc

System.Data.OleDb

System.Data.OracleClient

.NET Application

Layer

Layer

Layer

Components of Connected Architecture

• SqlConnection for establishing connection with the database

• SqlCommand for executing a command to retrieve data from the database

• SqlDataReader for retrieving data in a read-only and forward-only mode

Connection

• To work with database connection• Few Properties

– ConnectionString is a string that represents the properties of a database connection

– State ( Read Only ) provides the current state of the connection

– ConnectionTimeout ( Read Only )– Database ( Read Only )– DataSource ( Read Only )

• Few Methods– Open()– Close()

ConnectionString

• SqlConnection connection = new SqlConnection(

“Initial Catalog=pubs;

User ID=sa;Password=;

Data Source=localhost”);

• SqlConnection connection = new SqlConnection(

“Initial Catalog=pubs;

integrated security=SSPI;

Data Source=localhost”);

• Integrated security uses the current windows OS user name and password to connect to database server.

Command

• To interact with database using SQL queries and Stored procedure

• Few Properties– CommandText– Connection– CommandType– CommandTimeout

– Parameters

.NET Application

Connection Object

Command Object Data

Base

Command

• Few Methods– int ExecuteNonQuery() for INSERT, UPDATE, DELETE

and CREATE queries.

– SqlDataReader ExecuteReader() for retrieving multiple rows from the database

– object ExecuteScalar() for retrieving single data from database

.NET Application

Connection Object

Command Object Data

Base

Command

Command object can be used to execute SQL Commands and Stored Procedures.

The command object can run all type of SQL statements SELECT, INSERT, UPDATE, DELETE etc.

.NET provides two types of Command classes:

1. SqlCommand2. OleDbCommand

Creating a Command

Dim sql As String = “SELECT * FROM Products"

Dim myCommand As New SqlCommand( sql, myConn )

Or

Dim myCommand As New SqlCommand()

myCommand.CommandText = “SELECT * FROM Products”

Running a Command

Command object provides three methods that are used to execute commands on the database:

ExecuteNonQuery() – Execute commands that have no return value such as DELETE, INSERT, UPDATE.

ExecuteScaler() – Returns a single value from a database query.

ExecuteReader() – Return a DataReader object which is a result set returned by SELECT statement.

Running a Command

Dim myConn As New SqlConnection()

myConn.ConnectionString = "Data Source=localhost; Integrated Security=SSPI; Initial Catalog=Northwind";

Dim sql As String = “SELECT * FROM Products"

Dim myCommand As New SqlCommand( sql, myConn )

myConn.Open()

SqlDataReader result = myCommand.ExecuteReader()

myConn.Close()

DataReader

• Data reader

– Is used to retrieve data from a data source in a read-only

and forward-only mode.

– Stores a single row at a time in the memory.

• Few Properties

– FiledCount gets the total number of fields or columns in the row

retrieved.

– HasRows is used to check if data exists.

DataReader

• Few methods:

– Read() is be used to read the next available row in the

result and will return true if row is available else false

– Close() is used to close the DataReader.

– NextResult() is used for Batch Transaction queries. it

will advance the DataReader to the next query’s

result.

Constructing SqlCommand object with SQL query

SqlCommand cmd = new SqlCommand(

“SQL Query ",

ConnectionObject );

SqlCommand cmd = new SqlCommand(

“SQL Query ",

ConnectionObject );

SqlCommand cmd = new SqlCommand();

cmd.CommandText = “SQL Query “;

cmd.Connection = ConnectionObject;

SqlCommand cmd = new SqlCommand();

cmd.CommandText = “SQL Query “;

cmd.Connection = ConnectionObject;

ExecuteNonQuery() Method

• int ExecuteNonQuery() - to execute commands that return no records , such as INSERT, UPDATE, DELETE, CREATE statements.

• It returns the number of rows affected. • SqlCommand cmd = new SqlCommand("Update

authors SET City=‘Noida‘ ", connection );• int RowAffected = cmd.ExecuteNonQuery();

Note: Before calling the Execute methods of Command, The connection must be opened.

ExecuteScalar() Method

• object ExecuteScalar() - to retrieve single value. • If more rows are retrieved from database, the

value from the first column of the first row will be returned.

• SqlCommand cmd = new SqlCommand("SELECT count(*) FROM authors",connection );

• int RowCount = (int) cmd.ExecuteScalar();

ExecuteReader() Method

• SqlCommand cmd = new SqlCommand("SELECT * FROM authors",

connection );

• SqlDataReader reader = cmd.ExecuteReader();

• The Read() method of DataReader can be used to access the results row by row.

To access every single row, the Read() method has to be invoked.

• The column values can be accessed as

– reader.GetString(0) - returns .NET string type value

– reader.GetSqlString(0) - returns SqlDB string type value

– reader["au_fname"] – return object type.

• The reader has to be closed. Otherwise the connection can not be used for

further database interaction.

Multi-Query as CommandText

SqlCommand cmd = new SqlCommand(

“SELECT Query1;SELECT Query2;SELECT Query n ", ConnectionObject );

SqlCommand cmd = new SqlCommand(

“SELECT Query1;SELECT Query2;SELECT Query n ", ConnectionObject );

do

{ while( reader.Read() ) // Move to next row, if available.

{

// Access the current row data

}

}

while( reader.NextResult() ); // Move to next query’s result, if available.

do

{ while( reader.Read() ) // Move to next row, if available.

{

// Access the current row data

}

}

while( reader.NextResult() ); // Move to next query’s result, if available.

MyEmployee Table

Column Name Type

Eid int

Ename VarChar

Salary Decimal

Connected Architecture – Update Query

SqlConnection connection = new SqlConnection("Initial Catalog=pubs;User ID=sa;Password=;Data source=localhost");

connection.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = “Update myEmployee set salary=50000”;

cmd.Connection = connection;

int rowAffected = cmd.ExecuteNonQuery();

connection.Close();

/* The other types of query applicable in this scenario INSERT, DELETE, CREATE TABLE */

Connected Architecture – Query with Aggregate function

SqlConnection connection = new SqlConnection("Initial Catalog=pubs;User ID=sa;Password=;Data Source=localhost");

connection.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandText = “SELECT sum( salary) from myEmployee”;cmd.Connection = connection;

int TotalSalary = (int) cmd.ExecuteScalar();

connection.Close();

Connected Architecture – Retrieving set of rows

/* Create connection and cmd objects as it was done in the previous slides */

SqlDataReader reader = cmd.ExecuteReader();

while( reader.read() )

{

int eid = (int) reader.GetSqlInt32( 0 );

string name = (string) reader.GetSqlString( 1 );

double salary = (double) reader.GetSqlDecimal( 2 );

}

reader.Close();

connection.Close();

Connected Architecture – Retrieving set of rows

/* Create connection and cmd objects as it was done in the previous slides */

SqlDataReader reader = cmd.ExecuteReader();

while( reader.read() )

{

int eid = (int) reader[“Eid”];

string name = (string) reader[“Ename”];

decimal salary = (decimal) reader[“salary”];

}

reader.Close();

connection.Close();

Components of Disconnected Architecture

• DataSet – Local database in the application

• SqlDataAdapter – interacts with database for DataSet

• The communication for data between database and DataSet happens through DataAdapter

• Initially, the data are retrieved from database and loaded into DataSet.

• After the business logic has processed the data in the DataSet, only the modified data are updated in the database.

Application

DataSet DataAdapter

Database

The General Object Model of DataSetDataSet

DataTableCollection

DataTable

DataRelationCollection

DataColumnCollection

DataRowCollection

ConstraintCollection

DataAdapter

The DataAdapter serves as a bridge between a DataSet and data source for retrieving and saving data.

DataAdapter provides four properties that represent database commands:

1. SelectCommand2. InsertCommand3. DeleteCommand4. UpdateCommand

The DataAdapter use:

Fill() method to load data from the data source into the DataSet

Update() method to send changes made in the DataSet back to the data source.

.NET provides two types of DataAdapter classes:

1. SqlDataAdapter2. OleDbDataAdapter

DB

DataSet

DataAdapter

SelectCommandSelectCommand

DeleteCommandDeleteCommand

InsertCommandInsertCommand

UpdateCommandUpdateCommand

Fill( )

Update( )

DataSet

The DataSet acts as a container in which all the data coming from the database is dumped for the local machine to use and manipulate.

DB DataSet

DataSet

ADO.NET uses the disconnected approach using the DataSet.

DB DataSet

Connection Break

DataSet

The DataSet is explicitly designed for data access independent of any data source.

Oracle

DataSet

SQL Server 2000

Access

XMLFile

DataSet

The database is then later updated back from the DataSet.

DB DataSet

Data Updated

The DataSet contains a collection of one or more DataTable objects, DataTables are similar to tables or relations in DBMS. A DataTable consists 0 or more DataColumn and DataRow objects.

DataTables

DataColumn

DataRow

DataSet

DataSetDataSet

TablesTables

DataTableDataTable

ColumnsColumns

DataColumnDataColumn

ConstraintsConstraints

DataConstraintDataConstraint

RowsRows

DataRowDataRow

RelationsRelations

DataRelationDataRelation

DataSet also has DataRelation object which maintain relationship between two or more tables in DataSet just like Database.

DataTable object in DataSet has DataConstraint object which apply Primary Key and Foreign Key Constraint on DataColumns in DataSet just like Database.

DataRelation and DataConstraint

Emp_ID Name Salary Date

Emp_IDDate AmountOrder_ID

DataTable (Employee)

DataTable (Order)

DataConstraint (Emp_ID)

DataConstraint (Emp_ID)

DataRelation

DataSet

Another Feature of DataSet is that it tracks changes that are made to the data it holds before updating the source data.

DataSet are also fully XML featured. They contain methods such as GetXml( ) and WriteXml( ) that respectively produce and consume XML data easily.

Due to XML support ADO.NET DataSet can be used in n-tier architecture.

Business Tier Data Tier

Presentation Tier

Windows Forms

Web Forms

Business to Business

Data Object (Class)

DataSet

DataSetDataSet

InternetIntranet

Data Adapter

Data Adapter

(BizTalk, for example)

XML

MyApp.Exe

IE

DataSet Life Cycle

1. Extract tables from database and store it in DataSet.

2. Setup Relationship between Tables in DataSet.

3. Make changes to Data.

4. Update database from DataSet changes.

OriginalDatabase

DataSet

Table1

Table2

Sets uprelationships

DataSet

Table1

Table2

Makes changesto data

DataSet

Table1

Table2

Resolves changeswith ManagedProvider

Extracts tablesWith Managed

Provider

Connected Operations

DisconnectedOperations

DataView

DataView class acts as a view onto a single DataTable.

DataView provides the following features:

1. Sorting of data based on any column criteria.2. Filtering based on any combination of column values.

DataSet

Emp_ID Name Salary Date Phone EmailEmp_ID Name Salary Date Phone EmailEmp_ID Name Salary Date Phone Email

Data Change Management in DataSet

• Data changes in DataSet is maintained by– RowState property of DataRow object indicates the type

of modification happened to the DataRow– DataRowVersion helps to maintain multiple copies of a

DataRow record• Using the RowState and DataRowVersion, only the

modified data in DataSet are updated in the database.• The data in DataSet can be modified by following operation

– Inserting a new row – Deleting a row – Updating column values of a row

RowState Property

• RowState ( DataRowState Enumerator type ) has following values– Added ( a DataRow object that is added to DataSet)– Deleted ( a DataRow object that is deleted in

DataSet) – Detached ( a DataRow object that is instantiated but

not added to the DataSet )– Modified ( a DataRow object whose column values

are changed in Dataset)– Unchanged ( a DataRow object that is not modified in

the DataSet )

DataRowVersion

• It is an enumerator• It has following values

– Current (Active version) - It contains all changed and unchanged rows.

– Original (It contains all data from database without any changes.)

– Proposed ( It contains result of data from middle of an update. )

– Default ( Default values of a DataRow Column )

• Right after loading the data into DataSet, the data will be kept as Current and Original version.

Updating Database with Data from DataSet

• The data from DataRow objects – that have RowState as Added, Modified or Deleted

are updated to the database.– Before updating or deleting a database row, the

original version of the corresponding DataRow’s data is matched with database data.

– If there is a match, database operations will be carried out.

– Otherwise, DBConcurrencyException will be thrown.• When a row is being updated, it is locked. So the

other users can’t access it.• This kind of update procedure is called as Optimistic

Concurrency with Save All Values Approach.

Object Model of DataAdapter

Application Database

DataSet

InsertCommand

DeleteCommand

UpdateComand

SelectCommand

DataAdapter

• In SqlDataAdapter, the InsertCommand, DeleteCommand, UpdateCommand and SelectCommand properties are objects of SqlCommand.

• If the application needs only reading the data from the database, the DataAdapter can be used with initializing the SelectCommand alone.

Fill() Method of DataAdapter

Application

DataSet

SelectCommand

DataAdapter

Database

• Fill() method of DataAdapter loads DataSet with data from database by executing SELECT command in DataAdapter

• DataAdapterObj.Fill( DataSetObj)

DataAdapterObj.Update ( DataSetObj)

Application Database

DataSet

DeleteCommand

UpdateComand

DataAdapter

InsertCommand

Rows added to dataset are inserted into databaseDeleted rows in dataset are deleted in databaseRows modified in dataset are updated in database

Connection Handling in Fill() & Update()

• Opening the Connection– The Fill() & Update() methods invocation will open the

connection automatically, before performing the operation, If the connection is not already opened.

– If the connection is opened already, then the opened connection will be used.

• Closing the Connection– After fill()ing or update()ing the dataset , if the connection

is automatically opened then the automatically opened connection will be closed.

– If the connection is not automatically opened, then it won’t be closed automatically after the filling or updating the dataset.

Best Practice usage of Fill() &

Update() • Assume that all the necessary objects of

Connection, DataAdapter and DataSet are created.• The connection object is not Open()ed. • If this scenario is followed by,

– myDataAdapterobj1.Fill( myDataSet, “MyTable1”);– myDataAdapterobj2.Fill( myDataSet, “MyTable2”);– myDataAdapterobj3.Fill( myDataSet, “MyTable3”);

• This code opens & closes database connection for 3 times.

Best Practice usage of Fill() &

Update() • Opening database connection is one of the costliest

database operation.• Solution: When there is more than one Fill()

invocation, – Open() the connection before invoking the Fill(). – And Close() the connection object after Fill()ing

the DataSet.

Constructing DataAdapter

• Creating DataAdapter with Select, Update, Insert and Delete Command objects

SqlDataAdapter myDataAdapter = new SqlDataAdapter();

myDataAdapter.SelectCommand = mySelectCommandObj;

myDataAdapter.UpdateCommand = myUpdateCommandObj;

myDataAdapter.InsertCommand = myInsertCommandObj;

myDataAdapter.DeleteCommand = myDeleteCommandObj;

• The CommandText property of update, insert and delete command objects should be parameterized queries.

• The Adapter can be created in the design time itself.

Constructing DataAdapter using Wizard

Typed DataSet

• Standard DataSet objects are inherently weakly typed.• It is called as Un-typed dataset.• The data points like DataTable and DataColumn are

accessed as Collection like,

dataSet11.Tables[0].Columns[0]

( or )

dataSet11.Tables["Authors"].Columns[“au_id”]• The Typed DataSet helps to access the DataTable and

DataColumns as

dataSet11.Authors[0].Au_id• The Typed DataSet can be created using GUI Wizard.

Appendix: OLE DB layers

Appendix: Oracle with OLE DB and ODBC layers

II Unit Plan

• Database manipulation using Disconnected Architecture

• Databinding

• DataView

• Transaction

• DBConcorrencyException

• ADO.NET and XML

• Connection Pooling

MyEmployee Table

Column Name Type

Eid int

Ename VarChar

Salary Decimal

Disconnected - Insert

/* Create connection, DataAdapter and DataSet objects */

sqlDataAdapter1.Fill( dataSet11, ”MyEmployee”);

DataTable myTable = dataSet11.Tables[“MyEmployee”];

DataRow Row = myTable.NewRow();

Row[“Eid”] = 123;

Row[“Ename”] = “Sunil”;

Row[“Salary”] = 40000;

myTable.Rows.Add( row );

sqlDataAdapter1.Update( dataSet11, “MyEmployee”);

Disconnected – Print All Rows

/* Create connection, DataAdapter and DataSet objects */

sqlDataAdapter1.Fill( dataSet11, :”MyEmployee”);

DataTable myTable = dataSet11.Tables[“MyEmployee”];

for(int i=0; I < myTable.Rows.Count; i++)

{

DataRow Row = myTable. Rows[i];

Console.WriteLine( Row[“Eid”] );

Console.WriteLine( Row{“Ename”] );

Console.WriteLine( Row[“Salary”] );

}

Disconnected – Update / Delete

/* Create connection, DataAdapter and DataSet objects and Fill() the Adapter*/

DataTable myTable = dataSet11.Tables[“MyEmployee”];

for(int i=0; I < myTable.Rows.Count; i++)

{ DataRow row = myTable. Rows[i];

/* Assign new value */

Row[“Eid”] = 123;

Row[“Ename”] = “Sunil”;

Row[“Salary”] = 40000;

/* Or delete */

Row.Delete();

} sqlDataAdapter.Update( dataSet11, “MyEmployee”);

Data Binding

• Data binding is the process of binding the retrieved data to a control on a Windows Form to be displayed in a customized format.

• Data binding is of two types:– Simple Data Binding – Complex Data Binding

• Saves lot of coding effort.

Objects Involved in DataBinding

• Relationship between the BindingContext class, CurrencyManager class, and Windows Form

• A single data source can be used for binding more than one GUI control in a single Windows Form.

• DataBinding can be done to any property of the GUI control.

Windows FormControl 1Control 2Control 3

BindingContext object

CurrencyManager 1

CurrencyManager 2

CurrencyManager 3

DATA SOURCE 1

DATA SOURCE 2

DATA SOURCE 3

Simple Binding

• ControlObject.DataBindings.Add( “Property”, DataSource, “Data member” );

• Binding TextBox with au_id column from DataSet

textBox1.DataBindings.Add( "Text", myDataSet1.Tables[0], "Au_id");

• Binding TextBox with string array called myData

textBox1.DataBindings.Add( "Text", myData, "");

• By default, the first record from data source is bound.

Simple Binding

• To bind, the other records,

this.BindingContext[ DataSource ].Position = the position of the records

Example– this.BindingContext[ myDataSet1.Tables[0]

].Position = 10;– this.BindingContext[ myData ].Position =

10;

Complex Binding Example with ComboBox

comboBox1.DataSource = dataSet11.Tables[“Authors”];

comboBox1.DisplayMember = "au_fname";

comboBox1.ValueMember = "au_id";

Stored Procedure/Function

• Create the Command object for the stored

Procedure

SqlCommand cmd = new SqlCommand( “Stored

Procedure Name",ConnectionObj );

• Set the CommandType property of Command

object for stored procedure

cmd.CommandType = System.Data.CommandType.

StoredProcedure;

Stored Procedure/Function

• Create the parameter objects and add them to the Parameters collection

of Command object.

cmd.Parameters.Add( “ParamName”, ParamType,

ParamSize);

• Assign the value to the parameter

cmd.Parameters[“ParamName”].value = MyValue;

• Assign the parameter direction

cmd.Parameters[“ParamName”].Direction =

ParameterDirection.Output;

Parameterized Query

• SQL queries can be used like stored procedure with a help of parameters.• SQL queries with parameters are called Parameterized Query.• Parameterized Query with SQL Provider

– SELECT au_id from Authors WHERE au_fname = @Au_fname and au_lname= @au_lname

– The parameters are passed to the query based on parameter’s name from the ParameterCollection

• Parameterized Query with OleDB or ODBC Provider

– SELECT au_id from Authors WHERE au_fname = ? and au_lname= ?

– The parameters are passed to the query based on the position of the parameters in the ParameterCollection

Behavior of Parameters with Update()ing DataSet

• The CommandText of the DataAdpater’s Command objects are parameterized queries.

• When Update()ing the DataSet for every changed row, the values of the column are taken and passed as the parameters to the respective Command object.

• The version of values passed as parameters can be controlled by setting SourceVersion Property of the parameter.

• After updating the changes in one DataRow to the Database, If the Parameter’s Direction is Output or InputOutput then that parameter’s value will be stored in the corresponding DataRow’s column.

Transaction

• Set of database commands are executed with ALL or NOTHING approach

• If any of a single command fails, roll back of all the executed commands from the set.

• The Methods of SqlTransaction are– Commit();– Rollback();– Save(string) to create save point

• It also has property called IsolationLevel.

Transaction

1. Open Connection2. Start Transaction

SqlTransaction myTrans = myCon.BeginTransaction();3. Assign Commands to the transactionmyCommand1.Transaction = myTrans;myCommand2.Transaction = myTrans;try{ // Execute all commands. Successful execution moves to the

next instructionmyTrans.Commit();

}catch{ myTrans.Rollback(); }finally{ // Close Connection }

Solution for DataSet update() concurrency problem

sqlConnection1.Open(); SqlTransaction myTrans = sqlConnection1.BeginTransaction(); sqlDataAdapter2.UpdateCommand.Transaction = myTrans;sqlDataAdapter2.DeleteCommand.Transaction = myTrans;sqlDataAdapter2.InsertCommand.Transaction = myTrans;try{

sqlDataAdapter2.Update( dataSet21 ); myTrans.Commit();

}catch(DBConcurrencyException exp){

myTrans.Rollback(); }finally{ sqlConnection1.Close(); }

DataSet and XML

• DataSet to XML

/* To write data in DataSet into a XML file */

myDataSet.WriteXml( “FilePath”);

/* To write the schema of the data in DataSet into a Schema file */

myDataSet.WriteXmlSchema( “FilePath” );

• XML to Empty DataSet

/* To write data in XML file into a DataSet */

myDataSet.ReadXml( “FilePath”);

/* To write the schema of the XML document into a DataSet */

myDataSet.ReadXmlSchema( “FilePath” );

System.Xml.XmlDataDocument

• Helps to work with a data in DataSet and XML Document at same time• Any data modification in XmlDataDocument gets reflected in DataSet• Any data modification in DataSet gets reflected in XmlDataDocument

Data from

Database or XML document

XmlDataDocument DataSet

Data Manipulation

Building XmlDataDocument using DataSet

• Every XmlDataDocument has an associated DataSet.

• It manipulates loaded data using DOM

/* Creating XmlDataDocument */

XmlDataDocument myXmlDoc = new XmlDataDocument( myDataSet );

/* Creating XmlDataDocument */

XmlDataDocument myXmlDoc = new XmlDataDocument();

myXmlDoc.DataSet = myDataSet;

Building XmlDataDocument using XML data

/* Step 1: Creating XmlDataDocument */

XmlDataDocument myXmlDoc = new XmlDataDocument();

/*Step 2: Load XML data Schema into associated DataSet */

myXmlDoc.DataSet. ReadXmlSchema( “FilePath”) ;

/*Step 3: Load XML data into XmlDataDocument */

myXmlDoc. Load( “FilePath”) ;

Connection Pooling

• Connection Pooling provides already created connections.

• Instead of creating a connection whenever it is required, it can be taken from Connection Pool.

• Usage of Connection Pool results in better performance and scalability.

• Each Connection Pool is associated with a ConnectionString.

• . When a connection creation is initiated,

– If the ConnectionString of both the requested connection and Connection Pool matches than connection is taken from the Connection Pool

– If no match, Connection Pool is created.

Properties of Connection Pool

• The Properties of Connection Pool is supplied in the ConnectionString.

• Pooling = true; enables Connection Pooling. It’s default value is true.

• Min Pool Size = x ; defines the minimum number of connections that are created and maintained in the connection pool. It’s default value is 0.

• Max Pool Size = x ; defines the maximum number of connection object that can exist in the connection pool. It’s default value is 100.

Connection maintenance in Connection Pool

• If a connection is requested from a Connection Pool,

– If one/few of the minimum number of connections is/are free, the available connection will be supplied from Connection Pool.

– If all the minimum number of connections are under use and the Max Pool Size is not reached then a new connection is created in the Pool and supplied.

– If the Max Pool Size is reached, the new connection request will be queued.

• The Connection object will be returned to the Connection Pool under following any of the reason.

– Close() connection is called.

– Dispose() connection is called.

– Connection lifetime is expired.

Behaviors of Connection Pool

• The Open() and Close() methods behaves differently, if the connection is supplied by the Connection Pool.

• Calling Open() method of connection object retrieves connection from the Connection Pool

• Calling Close() method of connection object returns the connection back to connection Pool

• Connection Pool has connection object under Transaction context and Non-Transaction context.

• When a connection is required to participate in a transaction, it will be taken from Connection Pool’s Transaction Context.

96

Data Binding

• Single Value Data Binding – Involves binding server controls which can display

single data at a time

TextBox,Label etc

DEMO

• Multi Record Data Binding – Involves binding server controls to

structures ,ArrayList,DataView ,DataSet etc

DropDownList,ListBox,DataGrid,Repeater etc

DEMO

Thank You