Difference between : ADO vs ADO.NET

Difference between : ADO vs ADO.NET

ADO	ADO.NET
Connection-oriented Models used mostly.	Disconnected models are used.
Provided by Record set	Provided by Data Adapter and Data set
Limited	Robust Support
Client application needs to be connected always to data-server while working on the data, unless using client-side cursors or a disconnected Record set.	Client disconnected as soon as the data is processed. DataSet is disconnected at all times.
ADO objects communicate in binary mode.	ADO.NET uses XML for passing the data.
Includes implicit behaviors that may not always be required in an application and that may therefore limit performance.	Provides well-defined, factored components with predictable behavior, performance, and semantics.
Derives information about data implicitly at run time, based on metadata that is often expensive to obtain.	Leverages known metadata at design time in order to provide better run-time performance and more consistent run-time behavior.
DO allows you to create client-side cursors only	ADO.NET gives you the choice of either using client-side or server-side cursors

DataReader in ADO.NET

What it DataReader in ADO.NET?

ADO.NET DataReader is used to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader.

We cannot perform any insert, update and delete operations in the DataReader.

DataReader is opposite of DataSet.

We cannot move previous in the DataReader.

DataReader requires open connection.

DataReader can hold one table at a time.

DataReader can increase application performance both by retrieving data as soon as it is available, and storin only one row at a time in memory, reducing system overhead.

DataReader have item collection.

A DataReader object is used to sequentially read data from a data source.

Connection Object in ADO.NET

What is Connection Object in ADO.NET?

In ADO.NET architecture connection object represents a connection to Data Source.

The connection object represents the physical connection to a Data Source.

The connection object contains all the information to establish the connection to Data Source.

Basic steps are:

• Create Database
• Create connection object
• Set the provider
• Specify the data source

Connection object have properties specify user credentials and location of the Data Source and name of it for the connection.

You can manage the connection by using the method of the connection object.

To acquire the functionality of ADO.NET architecture, you have to use System.Data namespace.

Connection object have ConnectionString property. Depends on the parameter specified in the Connection String, ADO.NET connection object connects to the specified database.

OLEDB DataProvider connection string required two parameters:

• Provider = Microsoft.Jet.OLEDB.4.0
• DataSource = DatabaseName

Create OLDE DB Connection object:

     OleDbConnection con = new OleDbConnection();

ConnectionString Property:

• A ConnectionString property has group of attributes separated by semi-colon (;). This attributes are information that is required to connect with Data Source.
• Syntax for C#:
  String ConString = “key1=value1;key2=value2;key3=value3;…..;”;
• ConnectionString is different for each .NET Data Provider.
• The attributes of the ConnectionString property depends on the DataProvider and Data Source you are using to connect with Data Source.
• Example for C#:
  String ConString = “Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\ProjectDatabase\dbStudent.accdb”
• By default, ConnectionString property is an empty string.

Properties of OleDbConnection object:

Property	Data Type	Description
ConnectionString	String	Controls the string used to connect with Data Source.
ConnectionTimeout	Int	Specifies the time, in seconds, that how long the connection object will try to connect with Data Source. (Read-only).
Database	String	Returns the name of the database you are, or will be, connected to. (Read-only).
DataSource	String	Returns the server name of the Data Source(Read-only).
Provider	String	Gets the name of the OLE DB provider specified in the “Provider=” clause of the ConnectionString. (Read-only).
ServerVersion	String	Returns the version of the server of your Data Source. (Read-only)
State	ConnectionState	Returns the current state of the connection. (Read-only)

Methods of the OleDbConnection object:

• Open(): it opens the connection to our database.
• Close(): it closes the database connection.
• Dispose(): it releases the resources on the connection object.
• State(): returns 0 for not connected or 1 for connected. Returns state of connection object.

About DataSet in ADO.NET

Short note on: DataSet.-OR- Expain Dataset?

Dataset object is centralized to ADO.NET architecture to support disconnected and distributed data scenarios.

Dataset object is used to manage the data of Data Source locally.

Dataset can be used with the multiple and different data sources or even with the XML data also.

Dataset represents the complete set of related data, constraints and the relationship among tables just like Data Source have.

Dataset Provides same representation of data regardless of Data Source and provides consistent relational programming model of Data Source.

It can be considered as a local copy of the some portions of the database.

The DataSet contains a collection of one of more DataTable objects made up of rows and columns of data.

Tables can be identified in DataSet using DataTable’s Tables property.

The DataSet is not connected to a data source after it is populated.

Dataset has two components:

• DataTableCollection
• DataRelationCollection

1. DataTableCollection

   DataTableCollection contains all the DataTable objects in a Dataset.

   A DataTable is defined in the “System.Data” namespace.

   DataTable provides representation of single table inside DataTableCollection.

   DataTable contains the collection of,

   • Row
   • Column
   • Constraint

   Collection of row represented by the DataRowCollection.

   Collection of column represented by the DataColumnCollection.

   Collection of constraint represented by the ConstraintCollection.

   Column and Constraint collection represents the schema of the table.

2. DataRelationCollection

   A relationship associates rows in one DataTable with the rows in other DataTable.

   A relationship is represented by the DataRelation objects.

   A relationship is a join path between Primary Key and ForeignKey columns in a relational database.

   DataRelation identifies matching columns in two tables of a Dataset.

   Relationship enables navigation from one DataTable to other DataTable in Dataset.

   The elements of DataRelation are,

   • Name of the relationship
   • Name of the tables being related
   • Related column in each table

   Relationship can be built with more than one column per table. You can do it by specifying an array of DataColumn objects as key columns.

   When you add a relationship to DataRelationCollection, you can optionally add a UniqueKey Constraint and a ForeignKey Constraint to enforce integrity constraints when changes are made to related column values.

ADO.NET Architecture

Short note on: ADO.NET Components. -OR- Short note on: ADO.NET Architecture. ADO.NET Architecture provides the connected objects and disconnected objects. ADO.NET Architecture has two major components: Data Provider (Connected objects or connection oriented objects) Dataset (Disconnected objects or connectionless objects) Data Provider: The .NET Data Provider provides mechanism to manipulate data of Database Server. Data Provider is responsible for the connection to Database and managing that connection. The resultant data may be used directly, passed to Dataset or remotes between the tiers of the application. Advantage of Data Provider are, Lightweight High performance Create minimal layer between Data Source and Code Various data providers included in the .NET Framework are, SQL Server OLEDB ODBC Oracle There are four core objects of .NET Framework Data Provider. Connection Command Data Adapter Data Reader Dataset: Dataset object is centralized to ADO.NET architecture to support disconnected and distributed data scenarios. Dataset object is used to manage the data of Data Source locally. Dataset can be used with the multiple and different data sources or even with the XML data also. Dataset has two components: DataTableCollection DataRelationCollection DataTableCollection DataTableCollection contains all the DataTable objects in a Dataset. DataTable contains the collection of, Row Column Constraint DataRelationCollection A relationship associates rows in one DataTable with the rows in other DataTable. The elements of DataRelation are, Name of the relationship Name of the tables being related Related column in each table

ADO.NET Question

Question list of ADO.NET

1. What is ADO.NET?
2. Difference between ADO and ADO.NET?
3. Explain ADO.NET Architecture?
4. Explain Disconnected Data Architecture?
5. What are the core objects of ADO.NET?
6. List ADO.NET Data Providers?
7. What is Connection Objects?
8. What is Command Objects?
9. What is Data Adapter Object?
10. What is Data Reader?
11. What is Dataset?
12. What is DataTable?
13. What is Connection String?
14. What are the states of connection object?
15. What the createcommand() method do?
16. Methods of Connection object?
17. Methods of Command object?
18. Methods of Data Adapter object?
19. Methods of DataReader?
20. Methods of Dataset object?
21. Methods of DataTable object?