ADO.NET

Difference between ADO.net and ADO
1. ADO works with connected data.
ADO.NET uses data in a disconnected fashion. When you access data, ADO.NET makes a copy of the data using XML. ADO.NET only holds the connection open long enough to either pull down the data or to make any requested updates. This makes ADO.NET efficient to use for web applications. It's also decent for desktop applications.
2. ADO allows you to create client-side cursors only, whereas ADO.NET gives you the choice of either using client-side or server-side cursors.
3. ADO has one main object that is used to reference data, called the Recordset object. This object basically gives you a single table view of your data, although you can join tables to create a new set of records. With ADO.NET, you have various objects that allow you to access data in various ways. The DataSet object will actually allow you to store the relational model of your database. This allows you to pull up customers and their orders, accessing/updating the data in each related table individually.
4. Whereas ADO allows you to persist records in XML format, ADO.NET allows you to manipulate your data using XML as the primary means. This is nice when you are working with other business applications and also helps when you are working with firewalls because data is passed as HTML and XML.

What is the cursor and its types? Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis
If you want a dynamic cursor, you must choose server-side. If you choose a client-side cursor then it will be a static cursor.
Cursor types supported by SQL Server are:
Static cursorsa static cursor always displays the result set as it was when the cursor was opened. Static cursors are always read-only.Dynamic CursorsDynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor.Forward-only CursorsA forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursorKeyset-driven CursorsThe keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened

If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.
Here are some alternatives to using a cursor:
· Use WHILE LOOPS
· Use temp tables
· Use derived tables
· Use correlated sub-queries
· Use the CASE statement
· Perform multiple queries

What are the differences recordset and Dataset?
Recordset provides data one row at a time.
Dataset is a data structure which represents the complete table data at same time.
Recordset has the logic to update and manipulate data
Dataset is just a data

What are the differences between ADO.net 1.0 and ADO.net 2.0?
1. Ado.net 2.0 provides MARS (multiple active resultsets) which allows you to have two datareader on one connection open at same time.2. Ado.net 2.0 provide bulk copy facility.3. Ado.net 2.0 provides table adapters and hence allows easy to create DAL.

When to use datareader?
If your Data access operations is mainly fetching and displaying the records and doesn’t involve insert/update/delete statements and other manipulations (forward only, read only) actions, go for the DataReader.DataReader offers a forward only read stream of access to the records. It is very useful in cases where we just grab the data from the database to display in DataGrid, Label and other Webcontrols.However, DataReader requires the connection with the database open until its operation is completed.

When to use dataset?
If your Data access operations extend to as simple as an Insert/Update/Delete statement to as complex as using it as a return type from a web service, goes for the DataSet. The DataSet has the following advantages.
I) Complex Data type supporting numerous methods such as ReadXML which reads new XML data to form a dataset, WriteXML which provides an XML format of the existing data, etc.
II) Works on the disconnected architecture i.e. the connection doesn’t need to be on for the DataSet to perform the manipulations and other actions and is required only at the time of updating the records to the Database Table.
III) Provides an In-memory representation of the Data. Can contain one or more DataTable objects that have primary key, foreign key, and constraints between them and can enforce constraints such as unique or be configured to ignore them
IV) Have Individual elements such as DataTable, DataRow and DataColumn to access specific values.
V) While using DataSet and DataAdapter, you don't need to explicitly open the connection. DataAdapter automatically opens and closes the connection as and when required.

Difference between data set and data reader
1. Data Set is a connectionless service and Data reader is a connection oriented service
2. Dataset is used to store the data, it contains collections of Datatable. Datareader is used to connect to the database for retrieving data.
3. Data Reader - Forward only where as Dataset - Can loop through data
4. Data Reader - Connected Recordset where as DataSet - Disconnected Recordset
5. Data Reader - Less Memory Occupying where as DataSet - It occupies more memory
6. Data Reader - Only Single Table can be used where as Dataset - Datatable Concept allows data to be stored in multiple tables.
7. Data Reader - Read only where as DataSet - Can add/update/delete using the dataset
8. Data Reader - No relationship can be maintained where as DataSet - Relationship can be maintained.
9. Data Reader - No Xml Storage available where as DataSet - Can be stored as XML.

Difference between data adapter and data reader
1. DataReader directly access the table and it is only used for reading the records from it where as dataadapter, used to maintain connection, is always used with dataset as dataset is of disconnected-architecture in nature.
2. DataReader is an forward only and read only cursor type if you are accessing data through DataRead it shows the data on the web form/control but you can not perform the paging feature on that record(because it's forward only type). Reader is best fit to show the Data (where no need to work on data)
3. DataAdapter is not only connect with the Database (through Command object) it provide four types of command (InsertCommand, UpdateCommand, DeleteCommand, SelectCommand), It supports to the disconnected Architecture of .NET show we can populate the records to the DataSet. Where as Dataadapter is best fit to work on data.
4. Datareader is connected mode. And read and forward-only Data.
5. DataAdapter is Bridge between Database and DataSet.
6. DataReader Works in Connected Mode Forward Only, Read Only
DataAdapterWorks in Disconnected ModeBoth Side Movements, Read/Write.

What are managed providers?
It performs operation of communicating with the database. ADO.NET provides 2 distinct managed providers.
SQL Server managed provider – to communicate with SQL Server.
OLEDB managed provider – to communicate with any OLEDB compliant database.

What are the data access namespaces in .NET?
Data access namespaces are:-
System.Data
System.Data.OledDb
System.Data.SQLClient
System.Data.SQLTypes
System.Data.XML

What are main objects in ADO.Net?
Objects in ADO.NET are:-
OlebDbConnection / SQLConnection
OlebDbCommand / SQLCommand
OlebDbDataReader / SQLDataReader
OlebDbDataAdapter / SQLDataAdapter
OlebDbParameter / SQLParameter
DataSet
DataTable
DataView
DataRow
DataColumn