ADO.NET is Microsoft's latest data access technology. ADO.NET provides a rich set of .NET classes that facilitate for accessing data stored in any kind of database (relational or non-relational) and manipulating that data in ASP.NET pages and Web applications.
.NET Framework Data Access Namespaces
The .NET Framework provides a number of classes for database access. These are contained in different namespaces. The important namespaces are listed below:
|System.Data||Consists of the classes that constitute the ADO.NET architecture.|
|System.Data.OleDb||Consists of the classes that make up the .NET data provider for OLE DB-compatible data sources. These classes allow you to connect to an OLE DB data source, execute commands against the source, and read the results.|
|System.Data.SqlClient||Consists of the classes that make up the .NET data provider for SQL Server, which allows you to connect to SQL Server 7.0 (or later), execute commands, and read results. It is similar to the System.Data.OleDb namespace, but is optimized for access to SQL Server 7.0 and later.|
|System.Data.XML||Consists of the classes that provide standards-based support for processing XML.|
.NET Data Providers
A .NET data provider is used for connecting to a database, executing commands and retrieving results. The .NET data provider is designed to be lightweight, creating a minimal layer between the data source and your code, increasing performance. It includes the following three core objects:
• Connection: Establishes a connection to a specific data source. The class for connection is SqlConnection.
• Command: Executes a command (a SQL statement, or a stored procedure). The class for command is SqlCommand.
• DataReader: Reads a forward-only, read-only stream of data from a data source. There are 2 classes for Data reader. i.e. SqlDataReader and SqlDataAdapter.
The .NET Framework includes 2 data providers:
• SQL Server .NET Data Provider (for Microsoft SQL Server version 7.0 or later): It uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer.
• OLE DB .NET Data Provider: It communicates to an OLE DB data source through both the OLE DB Service component, which provides connection pooling and transaction services, and the OLE DB Provider for the data source. The providers that have been tested with ADO.NET are:
o SQLOLEDB: Microsoft OLE DB Provider for SQL Server. Used for earlier versions of SQL Server.
o MSDAORA: Microsoft OLE DB Provider for Oracle.
o Microsoft.Jet.OLEDB.4.0: OLE DB Provider for Microsoft Jet.
If you wish to use another database such as Access or Oracle, you must use the OLE DB .NET Data Provider. Its classes are located in the System.Data.OleDb namespace. They are similar to the classes in the System.Data.SqlClient namespace, except that they start with OleDb instead of Sql.
Creating, Opening a connection and Retrieving Records from a database,
Example 1: Retrieving Records
If you are looking for a specific record such as an employee’s grade, or executing SQL functions such as Count(*), which returns the number of records, you can use the ExecuteScalar() method instead of ExecuteReader().
Parameterized SQL queries.
Example 2: Using a Parameterized Query
Inserting new records, Updating existing records, Deleting records
You can insert a new record in a database table by using the SQL Insert statement, update existing records by using the SQL Update statement, and delete a record by using the SQL Delete statement. Wrap the statement in a command and execute it with the ExecuteNonQuery() method.
Example 3: Inserting Records
Example 4: Updating Records
Example 5: Deleting Records