ADO.net Q/A Part 1

Introduction
What is the namespace in which .NET has the data

functionality classes ?
Following are the namespaces provided by .NET for data management :-
System.data
This contains the basic objects used for accessing and storing relational data, such as DataSet,DataTable, and DataRelation. Each of these is independent of the type of data source and the way we connect to it.
System.Data.OleDB
It contains the objects that we use to connect to a data source via an OLE-DB provider,such as OleDbConnection, OleDbCommand, etc. These objects inherit from the common base classes, and so have the same properties, methods, and events as the SqlClient equivalents.

System.Data.SqlClient:
This contains the objects that we use to connect to a data source via the Tabular Data Stream (TDS) interface of Microsoft SQL Server (only). This can generally provide better performance as it removes some of the intermediate layers required by an OLE-DB connection.
System.XML
This Contains the basic objects required to create, read, store, write, and manipulate XML documents according to W3C recommendations.

Can you give a overview of ADO.NET architecture ?
The most important section in ADO.NET architecture is “Data Provider”. Data Provider provides access to datasource (SQL SERVER, ACCESS, ORACLE).In short it provides object to achieve functionalities like opening and closing connection, retrieve data and update data. In the below figure you can see the four main sections of a data provider :-
√ Connection.
√ Command object (This is the responsible object to use stored procedures)
√ Data Adapter (This object acts as a bridge between datastore and dataset).
√ Datareader (This object reads data from data store in forward only mode).
Dataset object represents disconnected and cached data. If you see the diagram it is not in direct connection with the data store (SQL SERVER, ORACLE etc) rather it talks with Data adapter, who is responsible for filling the dataset. Dataset can have one or more Datatable and relations.

What are the two fundamental objects in ADO.NET ?
Datareader and Dataset are the two fundamental objects in ADO.NET.

What is difference between dataset and datareader ?
Following are some major differences between dataset and datareader :-
√ DataReader provides forward-only and read-only access to data, while the
DataSet object can hold more than one table (in other words more than one
rowset) from the same data source as well as the relationships between them.
√ Dataset is a disconnected architecture while datareader is connected
architecture.
√ Dataset can persist contents while datareader can not persist contents, they
are forward only.

What are major difference between classic ADO and ADO.NET ?
Following are some major differences between both
√ As in classic ADO we had client and server side cursors they are no more present in ADO.NET. Note it's a disconnected model so they are no more applicable.
√ Locking is not supported due to disconnected model.
√ All data persist in XML as compared to classic ADO where data
persisted in Binary format also.

What is the use of connection object ?
They are used to connect a data to a Command object.
√ An OleDbConnection object is used with an OLE-DB provider
A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server

What is the use of command objects ?
They are used to connect connection object to Datareader or dataset. Following are the methods provided by command object :-
√ ExecuteNonQuery :- Executes the command defined in the CommandText
property against the connection defined in the Connection property for a query
that does not return any row (an UPDATE, DELETE or INSERT). Returns an Integer indicating the number of rows affected by the query.
√ ExecuteReader :- Executes the command defined in the CommandText property
against the connection defined in the Connection property. Returns a "reader"
object that is connected to the resulting rowset within the database, allowing
the rows to be retrieved.
√ ExecuteScalar :- Executes the command defined in the CommandText property
against the connection defined in the Connection property. Returns only
single value (effectively the first column of the first row of the resulting rowset)
any other returned columns and rows are discarded. It is fast and efficient
when only a "singleton" value is required

What is the use of dataadapter ?
These are objects that connect one or more Command objects to a Dataset object. They provide logic that would get data from the data store and populates the tables in theDataSet, or pushes the changes in the DataSet back into the data store.
√ An OleDbDataAdapter object is used with an OLE-DB provider
√ A SqlDataAdapter object uses Tabular Data Services with MS SQL Server.

What are basic methods of Dataadapter ?
There are three most commonly used methods of Dataadapter :-
Fill :- Executes the SelectCommand to fill the DataSet object with data from the data source. It an also be used to update (refresh) an existing table in a DataSet with changes made to the data in the original datasource if there is a primary key in the table in the DataSet.
FillSchema :- Uses the SelectCommand to extract just the schema for a table from the data source, and creates an empty table in the DataSet object with all the corresponding constraints.
Update:- Calls the respective InsertCommand, UpdateCommand,or DeleteCommand for each inserted, updated,or deleted row in the DataSet so as to update the original data source with the changes made to the content of the DataSet. This is a little like the UpdateBatch method provided by the ADO Recordset object, but in the DataSet it can be used to update more than one table.

What is Dataset object?
The DataSet provides the basis for disconnected storage and manipulation of relational data. We fill it from a data store,work with it while disconnected from that data store,then reconnect and flush changes back to the data store if required.

What are the various objects in Dataset ?
Dataset has a collection of DataTable object within the Tables collection. Each DataTable object contains a collection of DataRow objects and a collection of DataColumn objects.There are also collections for the primary keys, constraints, and default values used in this table which is called as constraint collection, and the parent and child relationships between the tables. Finally, there is a DefaultView object for each table. This is used to create a DataView object based on the table, so that the data can be searched, filtered or otherwise manipulated while displaying the data.

How can we force the connection object to close after
my datareader is closed ?
Command method Executereader takes a parameter called as CommandBehavior where in we can specify saying close connection automatically after the Datareader is close.
pobjDataReader=pobjCommand.ExecuteReader(CommandBehavior.CloseConnection)

I want to force the datareader to return only schema of the datastore rather than data ?
pobjDataReader =pobjCommand.ExecuteReader(CommandBehavior.SchemaOnly)

How can we fine tune the command object when we are
expecting a single row ?
Again CommandBehaviour enumeration provides two values SingleResult and SingleRow.If you are expecting a single value then pass “CommandBehaviour.SingleResult” and the query is optimized accordingly, if you are expecting single row then pass “CommandBehaviour.SingleRow” and query is optimized according to single row.

Which is the best place to store connectionstring in .NET
projects ?
Config files are the best places to store connectionstrings. If it is a web-based application“Web.config” file will be used and if it is a windows application “App.config” files will be used.
What are the various methods provided by the dataset object to generate XML?
Note:- XML is one of the most important leap between classic ADO and ADO.NET.
So this question is normally asked more generally how can we convert any data to XML
format. Best answer is convert in to dataset and use the below methods.
√ ReadXML
Read’s a XML document in to Dataset.
√ GetXML
This is a function which returns the string containing XML document.
√ WriteXML
This writes a XML data to disk.
How can we save all data from dataset ?
Dataset has “AcceptChanges” method which commits all the changes since last time “Acceptchanges” has been executed.

How can we check that some changes have been made to dataset since it was loaded ?
Twist :- How can we cancel all changes done in dataset ?
Twist :- How do we get values which are changed in a 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 Acceptchanges was executed.
HasChanges
This property indicates that has any changes been made since the dataset was loaded or acceptchanges method was executed.If we want to revert or abandon all changes since the dataset was loaded use “RejectChanges”.
Note:- One of the most misunderstood things about these properties is that it tracks the changes of actual database. That is a fundamental mistake; actually the changes are related to only changes with dataset and have nothing to with changes happening in actual database.As dataset are disconnected and do not know anything about the changes happening in actual database.