ADO.NET

	What is the namespace in which .NET has the data functionality classes? Following are the namespaces provided by .NET for data management :- 1. 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. 2. 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. 3. 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. 4. System.XML - This Contains the basic objects required to create, read, store, write, and manipulate XML documents according to W3C recommendations.
	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:- 1. 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. 2. Dataset is a disconnected architecture while datareader is connected architecture. 3. 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 1. 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. 2. Locking is not supported due to disconnected model. 3. 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. 1. An OleDbConnection object is used with an OLE-DB provider. 2. A SqlConnection object uses Tabular Data Services (TDS) with MS SQL Server
	What is the use of command objects and what are the methods provided by the command object? They are used to connect connection object to Datareader or dataset. Following are the methods provided by command object :- 1. 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. 2. 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. 3. 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 the DataSet, or pushes the changes in the DataSet back into the data store. 1. An OleDbDataAdapter object is used with an OLE-DB provider 2. 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 :- 1. 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 285 made to the data in the original datasource if there is a primary key in the table in the DataSet. 2. 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. 3. 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 connect to Microsoft Access , Foxpro , Oracle etc? Microsoft provides System.Data.OleDb namespace to communicate with databases like scess , Oracle etc. In short any OLE DB-Compliant database can be connected using System.Data.OldDb namespace.
	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 or a single value? 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.
	What are the various methods provided by the dataset object to generate XML? 1. ReadXML - Read’s a XML document in to Dataset. 2. GetXML - This is a function which returns the string containing XML document. 3. 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 ? 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 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".
	How can we add/remove row’s in "DataTable" object of "DataSet"? "Datatable" provides "NewRow" method to add new row to "DataTable". "DataTable" has "DataRowCollection" object which has all rows in a "DataTable" object. Following are the methods provided by "DataRowCollection" object :- 1. Add - Adds a new row in DataTable. 2. Remove - It removes a "DataRow" object from "DataTable". 3. RemoveAt - It removes a "DataRow" object from "DataTable" depending on index position of the "DataTable".
	What is basic use of "DataView"? "DataView" represents a complete table or can be small section of rows depending on some criteria. It is best used for sorting and finding data with in "datatable". Dataview has the following method’s :- Find It takes a array of values and returns the index of the row. FindRow This also takes array of values but returns a collection of "DataRow". If we want to manipulate data of "DataTable" object create "DataView" (Using the "DefaultView" we can create "DataView" object) of the "DataTable" object and use the following functionalities :- AddNew Adds a new row to the "DataView" object. 297 Delete Deletes the specified row from "DataView" object.
	What is the difference between "DataSet" and "DataReader"? Following are the major differences between "DataSet" and "DataReader" :- 1. "DataSet" is a disconnected architecture, while "DataReader" has live connection while reading data. If we want to cache data and pass to a different tier "DataSet" forms the best choice and it has decent XML support. 2. When application needs to access data from more than one table "DataSet" forms the best choice. 3. If we need to move back while reading records, "datareader" does not support this functionality. 4. But one of the biggest drawbacks of DataSet is speed. As "DataSet" carry considerable overhead because of relations, multiple tables etc speed is slower than "DataReader". Always try to use "DataReader" wherever possible, as it’s meant specially for speed performance.
	How can we load multiple tables in a DataSet? objCommand.CommandText = "Table1" objDataAdapter.Fill(objDataSet, "Table1") objCommand.CommandText = "Table2" objDataAdapter.Fill(objDataSet, "Table2"). Above is a sample code which shows how to load multiple "DataTable" objects in one "DataSet" object. Sample code shows two tables "Table1" and "Table2" in object ObjDataSet. <control>.DataSource = objDataSet.Tables("Table1").DefaultView In order to refer "Table1" DataTable, use Tables collection of DataSet and the Defaultview object will give you the necessary output.
	How can we add relation’s between table in a DataSet? Dim objRelation As DataRelation objRelation=New DataRelation("CustomerAddresses",objDataSet.Tables("Customer").Columns("Custid") ,objDataSet.Tables("Addresses").Columns("Custid_fk")) objDataSet.Relations.Add(objRelation) Relations can be added between "DataTable" objects using the "DataRelation" object. Above sample code is trying to build a relationship between "Customer" and "Addresses" "Datatable" using "CustomerAddresses" "DataRelation" object.
	What is the use of CommandBuilder? CommandBuilder builds "Parameter" objects automatically. Below is a simple code which uses commandbuilder to load its parameter objects. Dim pobjCommandBuilder As New OleDbCommandBuilder(pobjDataAdapter) pobjCommandBuilder.DeriveParameters(pobjCommand) Be careful while using "DeriveParameters" method as it needs an extra trip to the Datastore which can be very inefficient.
	What’s difference between "Optimistic" and "Pessimistic" locking? In pessimistic locking when user wants to update data it locks the record and till then no one can update data. Other user’s can only view the data when there is pessimistic locking. In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days browser based application is very common and having pessimistic locking is not a practical solution.
	How many ways are there to implement locking in ADO.NET? Following are the ways to implement locking using ADO.NET :- 1. When we call "Update" method of DataAdapter it handles locking internally. If the DataSet values are not matching with current data in Database it raises concurrency exception error. We can easily trap this error using Try..Catch block and raise appropriate error message to the user. 2. Define a Datetime stamp field in the table.When actually you are firing the UPDATE SQL statements compare the current timestamp with one existing in the database. Below is a sample SQL which checks for timestamp before updating and any mismatch in timestamp it will not update the records. This is the best practice used by industries for locking. Update table1 set field1=@test where LastTimeStamp=@CurrentTimeStamp 3. Check for original values stored in SQL SERVER and actual changed values. In stored procedure check before updating that the old data is same as the current. Example in the below shown SQL before updating field1 we check that is the old field1 value same. If not then some one else has updated and necessary action has to be taken. Update table1 set field1=@test where field1 = @oldfield1value. Locking can be handled at ADO.NET side or at SQL SERVER side i.e. in stored procedures.
	How can we perform transactions in .NET? The most common sequence of steps that would be performed while developing a transactional application is as follows:- 1. Open a database connection using the Open method of the connection object. 2. Begin a transaction using the Begin Transaction method of the connection object. This method provides us with a transaction object that we will use later to commit or rollback the transaction. Note that changes caused by any queries executed before calling the Begin Transaction method will be committed to the database immediately after they execute. Set the Transaction property of the command object to the above mentioned transaction object. 3. Execute the SQL commands using the command object. We may use one or more command objects for this purpose, as long as the Transaction property of all the objects is set to a valid transaction object. 4. Commit or roll back the transaction using the Commit or Rollback method of the transaction object. 5. Close the database connection.
	What is difference between Dataset.clone and Dataset.copy? Clone - It only copies structure, does not copy data. Copy - Copies both structure and data.
	Can you explain the difference between an ADO.NET Dataset and an ADO Recordset? There two main basic differences between recordset and dataset :- 1. With dataset you an retrieve data from two databases like oracle and sql server and merge them in one dataset , with recordset this is not possible 2. All representation of Dataset is using XML while recordset uses COM. 3. Recordset can not be transmitted on HTTP while Dataset can be.
	What is Maximum Pool Size in ADO.NET Connection String? Maximum pool size decides the maximum number of connection objects to be pooled. If the maximum pool size is reached and there is no usable connection available the request is queued until connections are released back in to pool. So it’s always a good habit to either call the close or dispose method of the connection as soon as you have finished work with the connection object.
	How to enable and disable connection pooling? For .NET it is enabled by default but if you want to just make sure set Pooling=true in the connection string. To disable connection pooling set Pooling=false in connection string if it is an ADO.NET Connection. If it is an OLEDBConnection object set OLE DB Services=-4 in the connection string.