Our Blog

ADO.NET

ADO is ActiveX Data Object. The ADO.NET components have been designed for faster data access. There are two central components of ADO.NET that accomplish this.

• DataSet
• .NET Framework Data Provider

The DataSet is the core component of ADO.NET. The DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and different data sources. The dataset contains a collection of one or more DataTable objects made up of rows and columns of data.
Another element of ADO.NET  architecture is the .NET Framework Data Provider. Its components are explicitly designed for data manipulation, fast, forward-only and read-only access to data. The connection object provides connectivity to a DataSource.The command object enables access to database commands to return data, modify data, run stored procedures and to send or retrieve parameter information.The DataReader provides a high-performance stream of data from the DataSource.The DataAdapter provides the bridge between the DataSet object and the DataSource.

ADO.NET Command

The Command object in ADO.NETexecutes SQL statements and stored procedures. The Command object requires an instance of a Connection object for executing the SQL statements. That is, for retrieving data or execute an SQL statement against a DataSource.The Command object has a property called CommandText, which contains a string value that represents the command that will be executed in the DataSource.

Types of Commands:

• ExecuteNonQuery
• ExecuteReader
• ExecuteScalar

ExecuteNonQuery

ExecuteNonQuery is used for executing statements that don’t return ResultSet. It performs data definition tasks as well as data manipulation tasks.The data definition tasks like creating Stored Procedures, Views etc.. Also data manipulation tasks like Insert, Update, Delete etc.

ExecuteReader

ExecuteReader sends the SQL statement to the connection object and populate a SQLDataReader Object based on the SQL statement. When the ExecuteReader method executes, it will instantiate SQLClient.SQLDataReader object.The SQLDataReader object is a stream-based, forward-only, read-only, retrieval of query results from the DataSource, which doesn’t update the data.

ExecuteScalar

ExecuteScalar is used for getting a single value from database after its execution. It executes SQL statements or stored procedures and return a scalar value on first column of first row in the ResultSet. If the ResultSet is empty, it returns a null reference.

DataAdapter

DataAdapter is an integral part of  ADO.NET. A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework Data Provider to connect to a data source, and it uses Command objects to retrieve data from and resolve changes to the data source.

DataAdapters that are  available for use with database are:

• OleDbDataAdapter
• SQLDataAdapter
• OdbcDataAdapter
• OracleDataAdapter

OleDbDataAdapter

OleDbDataAdapter object is suitable for use with any data source exposed by OleDb Provider.

SQLDataAdapter

SQLDataAdapter object is specific to SQL Server. It is faster than the OleDbDataAdapter.

OdbcDataAdapter

OdbcDataAdapter object is optimized for accessing ODBC data sources.

OracleDataAdapter

OracleDataAdapter Object is optimized for accessing Oracle Databases.

Fig: 1 OleDb Managed Provider

Fig: 2 SQL Managed Provider

Fig:3 Odbc Managed Provider

Fig:4 Oracle Managed Provider

DataSet

DataSet is used to hold all of your information from the Database. That is, the DataSet will hold a copy of the information from the Database.
The DataSet is not something you can draw on your form like a button or a textbox. The DataSet is something that is hidden from view and just stored in memory. Consider a grid with rows and columns. Then each row of the DataSet represents a row of information in your database and each column represent a column of information’s in your database.

Reading and updating with DataAdapter

The primary purpose of the DataAdapter is to communicate data between DataSource and a DataSet. If you just want to read data, you don’t have to store it in a DataSet. Instead, we can read it out directly from the database into an application.
For updating with DataAdapter:

• Retrieve rows from a DataSet into corresponding Data Tables. For that, use the fill method on a DataAdapter object like, OleDb, SQL, ODBC, Oracle.When you invoke the fill method, it transmit a SELECT Statement to the DataSet.
• To transmit data in a DataTable to a DataSet use the UPDATE method. When you invoke the UPDATE method, it executes whatever INSERT, UPDATE, DELETE statements as needed.

About the Author:

Ranilekshmi G, is a Computer Science Diploma Student from Engineer’s Training Center, Kottayam. She is a talented artist who is passionate about IT Industry and is keen to finding creative programming solutions. Currently, she is doing an internship in .NET at IPSR.