ADO DOTNET

ADO.NET

One problem with ADO is that it was originally created as a connected database access object model. This means that developers primarily use a Recordset object while connected to the data source. Microsoft modified the recordset to allow disconnected access, but this solution did not work as well as if it had been designed as a disconnected data access model from the beginning. The recordset object can contain only one result set. This means we cannot relate result sets using a primary key / foreign key type of relationship and we cannot relate result sets from different data sources.

With .NET, Microsoft developed an entirely new data model that uses the provider wrapper technology but eliminates the old-style recordset object. That new data model is ADO.NET.

ADO.NET still uses Connection and Command types, but it replaces the Recordset with two data types- DataReader and DataSet objects.

The DataReader object provides connected data access. The DataSet, the core object of ADO.NET is an in-memory cache of disconnected data.

ADO.NET is a set of classes that allow .NET based applications to read and update information in databases and other data sources. You can access these classes through the System.Data namespace provided by the .NET framework.

ADO.NET provides consistent access to a wide variety of data sources including Microsoft SQL Server database, OLEDB compliant databases, non-relational sources such as Microsoft Exchange Server and XML documents.

Benefits of ADO.NET:

          ADO.NET provides many benefits to experienced developers including-

1. Similar Programming model to that of ADO
2. Designed for Disconnected Data-

ADO.NET is designed for working with disconnected data in a multitier environment.

It uses XML as the format for transmitting disconnected data, which makes it easier to communicate with client applications that are not based on windows.

3. Intrinsic to the .NET Framework-

Because ADO.NET is intrinsic to the .NET Framework, you have all the advantages of using the .NET framework, including ease of cross-language development.

4. Supports XML-

ADO and XML have previously been incompatible. ADO was based on relational data, and XML is based on hierarchical data. ADO.NET brings together these two data access techniques and allows you to integrate hierarchical and relational data, as well as alternate between XML and relational programming models.

ADO.NET Class Structure:

          We can divide the ADO.NET related classes into three groups.

·         .NET Provider Classes:

These classes interface with the database and provide some features for manipulating the retrieved data. Each data provider will have a different set of classes. For example, the SQL provider has sqlConnection, sqlCommand etc., we can easily pass data between these classes and the consumer classes.

·         Data Consumer Classes:

We use these classes for manipulating data. They are independent of the data source and we can manipulate data using these classes without an open connection to the databases.

·         Other .NET Framework Classes:

These classes are related to data handling but are not part of the System.Data namespace. For example, Data-Bound controls. These are part of the System.Windows.Forms namespace. We can easily pass data between these classes and Data Consumer Classes.

.NET Data Providers:

          In ADO.NET each particular type of data source has a .NET data provider. ADO.NET is providing managed providers for accessing data sources. They are:

·         SQL Server .NET Provider:

          System.Data.SqlClient.dll à Assembly

          sqlConnection, sqlCommand, sqlDataReader, sqlTransaction, sqlParameter are the classes under this namespace.

·         OLEDB .NET Provider:

This provider is used to access oracle, MS-Access and other data sources that support the Microsoft OLEDB data component interface.

System.Data.OleDb.dll àAssembly

OleDbConnection, OleDbCommand, OleDbDataReader etc., are the classes.

·         ODBC .NET provider:

System.Data.Odbc.dll à Assembly

OdbcConnection, OdbcCommand, OdbcDataReader etc., are the classes.

·         ORACLE .NET Provider:

System.Data.OraClient.dll à Assembly

OraConnection, OraCommand, OraDataReader etc., classes.

.NET Provider Classes:

          The provider classes for the SQL Server .NET Provider are derived from System.Data.SqlClient namespace. All begin with sql keyword. The classes for the OLEDB provider begin with OleDb etc., The classes for the OraClient provider begin with Ora and the classes with ODBC provider begins with Odbc. The classes are:

xxxConnection Class:

          The connection objects are the first objects that use and they are required before using any of the other ADO.NET provider objects. They provide the basic connection to your data source. If you are using a database that required a UserID and Password, or one on a remote network server, the connection object takes care of the details of establishing the connection and logging in.

Common Properties and methods of the Connection Class:

MemberName	Propter(p)/ Method(m)	Description
ConnectionString	P	Establishes the connection to the data source. Provider specific.
ConnectionTimeOut	P	Specifies the length of time(in sec) to attempt to connect
State	P	Get the state of the connection – Connected, Connecting, notConnected….
Provider	P	Used by oledb connection object to specify which oledb provider will be sued to communicate
Data Source/server	P	Server name
Initial catalog	P	Name of the database
Userid	P
Password	P
Interated Securith	P	Whether a connection is a secure connection
Open	M	Attempts to open the connection object based on the connection string.
Close	M	Closes the connection to the data source
BeginTransaction	M	Begins a database transaction.

Ex:       sqlConnection Con=new SqlConnection();

           

//using SQL Data Provider with Windows Authentication

            Con.ConnectionString=”Integrated Security=true;data source=(local);initial catalog=university”

//using SQL Data Provider with SQL Server Authentication

            Con.ConnectionString=”Integrated Security=true;data source=(local);initial catalog=university;uid=sqlclass;pwd=sql”

//using OleDb Data Provider

            Con.ConnectionString=”provider=sqloledb;data source=(local);intitial catalog=university;uid=;pwd=”;

            Con.Open();

//using Oracle

            OleDbConnection oleCon=new OleDbConnection();

             OleCon.ConnectionString=”provider=msdaora.1;userid=; pwd=”;

             OleCon.open()

XXXCommand Class:

            You can use the ADO.NET command object to execute commands and optionally, to return data from a data source.

Steps in using Command object:

1.    create a command object.

2.    Use the ‘CommandType’ property to specify the type of command to be executed.

CommandType.StoredProcedure

CommandType.Text

CommandType.Table

3.    Use ‘CommandText’ property and specify the command to be executed in case of Text type and name of the object in case of stored procedure or table name.

4.    Use one of the Execute menthod to execute the command.

Executing Commands:

            You can execute a command within a valid and open connection. The command object provides 3 methods that you can use to execute commands.

ExecuteReader():

            Use this method when the query will return a stream of data such as a Select Statement returning a set of records. This method returns the records in a DataReader object.

ExecuteScalar():

            Use this method when the query will return a singleton value. For example, a select statement returning an aggregate value. It executes the query and returns the first column of the first row in the result set, ignoring any other data that is returned.

ExecuteNonQuery():

            Use this method when the query will not return a result;ex: insert statement.

Ex:

            sqlCommand cmd=new sqlCommand();

            cmd.CommandType=CommandType.Text;

            cmd.CommandText=”select * from emp”;

            sqlDataReader dr;

            dr=cmd.ExecuteReader();

DataReader Class:

            DataReader object is used to create a read-only, forward-only stream of data. This is an efficient method for accessing data that you only need to read through code. You can improve the app performance by using this object. Because, it holds only a single row at a time in memory instead of caching the entire set of records.

Reading Data:

            You can instantiate the DataReader object by using the ExecuteReader method of command object. After you create the DataReader, you can call the “Read” method to obtain data in the rows. You can access the columns by name, ordinal number or native type in conjunction with ordinal number.

            Ex: While dr.Read()

                        {

                                    ------

                        }

            You must ensure that you sued the ‘Close’ method of the DataReader object before accessing any output or return parameter from a StoredProcedure.

Retrieving Data:

Assume that dr is the DataReader, then we can use any one of the following ways to retrieve the data.

dr.Item[0];

dr[0];

dr[“Empid”];

dr.GetString[0];

Using Command Object with Stored Procedure:

     I.        Create a Command object

sqlCommand cmd=new sqlCommand();

    II.        Set the CommandType property of command object to stored proecure.

cmd.CommandType=CommandType.StoredProcedure;

  III.        Specify the procedure name in CommandText property.

cmd.CommandText=”spGetEmployee”;

   IV.        Use the sqlParameter class to create a parameter object depending on the type and direction of the parameter in the procedure

sqlParameter param1=new sqlParameter(“@eid”,sqlDbType.int);

param1.Direction=ParameterDirection.Input;

    V.        Set the value of the parameter;

Param1.Value=Convert.ToInt32(txtEmpid.Text);

   VI.        Add the parameter to the parameter Collection.

cmd.Parameters.Add(param1);

 VII.        Execute the command object;

dr=cmd.ExectueReader();

DataSet

            DataSets are the primary objects that you will work with when accessing disconnected sets of data. They are similar in concept to groups of ADO disconnected recordsets, but in ADO.Net there are many enhancements including the ability to relate tables together.

DisConnected Data in RDO and ADO:

            RDO and ADO introduced the concept of disconnected data. This was implemented so that you could retrieve a set of records, disconnected from the data source, and work with the data locally. You could reconnect and submit your changes to the database. The recordsets were marshaled between the tiers as COM objects, requiring that both the server and client computer could handle COM Components

Disconnected Data in ADO.NET:

            ADO.Net is a designed for use in the internet world, where as COM may not be supported by all tiers, and may not be transmitted through fire walls.  ADO.Net uses XML as its transmission format. This is a text based format. It avoids the problems associated with the transmission of COM objects and ensuring true cross-platform interoperability.

            ADO.Net provides you with a new object for the caching of data on the client computer. This object is known as “DataSet”. This subject is automatically disconnected from the datasource but maintain the ability to later update the source based on changes made at the client.

            DataSet acts like in-memory database for the client application by maintaining collection of tables belonging to different data sources in the form of XML Storage.

            Dataset is always disconnected and the information will be filled into the dataset.

Creating DataSets:

Syntax:

            DataSet ds=new DataSet();     (or) 

            DataSet ds=new DataSet(“Table1”);

Populating DataSets:

            You can use the DataAdapter objects “Fill” method to access data stored in the datasource, and store the data in DataTables within a DataSet in your application.

Syn:     DataAdapter1.Fill(DataSetName,TableName)

Ex:       da.Fill(ds,”Table1”);

DataAdapter:

            You can use the DataAdapter to exchange data between a data source and a DataSet. You can use it to retrieve appropriate data and insert it into DataTable objects within a DataSet and to update changes from the DataSet back to the datasource.

Creating a DataAdapter:

            Ex: sqlDataAdapter da=new sqlDataAdapter(“Select * from table1”,con)

Fill method is used to populate the dataset with datasource information.

Update method is used to save the changes from dataset back to the datasource.

The Fill method will perform the following tasks:

·         It will establish the connection to the database server

·         It sends the Select Statement to the database server for execution

·         It creates a table in the dataset and gets the information from database server using implicitly DataReader

·         It will closes the connection.

Updating Data in DataSet:

            After you have created a DataSet of DataTables, you might want to add, update and delete data. Any changes you  make to the data are stored in memory and later used to apply changes to the data source.

Adding Rows:

1.    Instantiate a DataRow object by using ‘NewRow’ method tof the DataTable

2.    Populate the columns with data.

3.    Call the ‘Add’ method of the DataRows collection, passing the DataRow object.

Editing Rows:

1.    Call the BeginEdit method of the row

2.    Change the data in the Columns

3.    Call EndEdit or CancelEdit to accept or reject changes

Deleting Data:

            Use either of the following methods to delete a row.

·         Remove

            Call the Remove method of the DataRow collection. This permanently removes the row from the dataset

·         Delete

            Call the Delete method of the DataRow object. This only makes the row for deletion in the dataset and calling reject changes will undo the deletion.

Confirming Changes:

            To update the DataSet, you use the appropriate method to edit the table and then you call AcceptChanges or RejectChanges for the individual rows or for the entire table.

            You can discover whether any changes have been made to a row since AcceptChanges was last called by querying its ‘RowState’ property. It contains the following values.

Enumeration	Desc	Value
UnChanged	No changes have been made	2
Added	The row has been added to the table	4
Modified	Something in the row has been changed	16
Deleted	The row has been deleted by the delete method	8
Detached	The row has been deleted with ‘Remove’ method or row has been created, but the add method has not been called	1

Updating Data at the Source:

            After you have updated the tables in your dataset, you will want to replicate those changes to the underlying data source. To do this, you use the ‘Update’ method of the DataAdatper object.

            The Update method like fill method takes two parameters.

1.    The Dataset in which the changes have been made

2.    The name of the table in which changes are made.

            This method determines the changes to the data and execute appropriate SQL Command against the source data.

Automatically Generating Changes:

            If your datatable is generated from only one table in the datasource, you can use the commandBuilder object to automatically create the InsertCommand, DeleteCommand, UpdateCommand properties

Ex:

            sqlCommandBuilder cb=new sqlCommandBuilder(da);

            da.Update(ds,”table1”);