ADO.NET – Basics

What is ADO.NET?

ADO stands for Microsoft ActiveX Data Objects. ADO.NET is one of Microsoft’s Data Access technology. It is a part of the .Net Framework which is used to establish a connection between the .NET Application and data sources. The Data sources can be SQL Server, Oracle, MySQL, and XML, etc. ADO.NET consists of a set of classes that can be used to connect, retrieve, insert and delete data from data sources. ADO.NET mainly uses System.Data.dll and System.Xml.dll.

Components of ADO.NET

Components are designed for data manipulation and fast access to data. Connection, Command, DataReader, DataAdapter, DataSet, and DataView are the components of ADO.NET that are used to perform database operations. ADO.NET has two main components that are used for accessing and manipulating data.

1. Data provider and
2. DataSet.

SQL Command Object

A SqlCommand object allows you to specify what type of interaction you want to perform with a database. SqlCommand object allows us to query and send commands to a database (its has many transact SQL statement and stored procedure to execute against Database). This class cannot be inherited.  For example, you can do select, insert, modify, and delete commands.

How to declare sqlcommand object ?

SqlCommand cmd = new SqlCommand();

Inserting Data

To insert, update and delete data into a database, use the ExecuteNonQuery method of the SqlCommand object.

cmd.ExecuteNonQuery();

Querying data

SqlDataReader rdr = cmd.ExecuteReader();

A SqlCommand object allows us to query and send commands to a database. It has methods that are specialized for different commands. The ExecuteReader method returns a SqlDataReader object for viewing the results of a select query. For insert, update, and delete SQL commands, you use the ExecuteNonQuery method. If you only need a single aggregate value from a query, the ExecuteScalar is the best choice.

SqlConnection Class

The SqlConnection class handles database connections. It initiates a connection to your SQL database. The first thing you will need to do when interacting with a database is to create a connection. The connection tells the rest of the ADO.NET code which database it is talking to. It manages all of the low-level logic associated with the specific database protocols.

Creating a SqlConnection Object

SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

Operations occurring in the lifetime of a SqlConnection are :

1. Instantiate the SqlConnection.
2. Open the connection.
3. Pass the connection to other ADO.NET objects.
4. Perform database operations with the other ADO.NET objects.
5. Close the connection.

SqlDataAdapter

The SqlDataAdapter, serves as a bridge between a DataSet and SQL Server for retrieving and saving data. The SqlDataAdapter is a class that represents a set of SQL commands and a database connection. It can be used to fill the DataSet and update the data source.

Fill Method. Adds or refreshes rows in the DataSet to match those in the data source using the DataSet name, and creates a DataTable named “Table”. The Fill method retrieves rows from the data source using the SELECT statement specified by an associated SelectCommand property.

SqlDataAdapter adapter = new SqlDataAdapter();
adapter.Fill(ds);

DataTable

Like a table in SQL, the DataTable is also going to represent the relational data in tabular form and this data is going to be store in memory.

DataSet 

The DataSet represents a subset of the database in memory. That means the ADO.NET DataSet is a collection of data tables that contains the relational data in memory in tabular format.

SqlDataReader

The ADO.NET SqlDataReader class in C# is used to read data from the SQL Server database in the most efficient manner. It reads data in the forward-only stream. The SqlDataReader is connection-oriented. It means it requires an open or active connection to the data source while reading the data. The data is available as long as the connection with the database exists. SqlDataReader is read-only. It means it is also not possible to change the data using SqlDataReader. You also need to open and close the connection explicitly.

You can not create the instance of SqlDataReader using the new keyword. Then the question is how we get or create the instance of SqlDataReader class. In order to create the instance of SqlDataReader class, what you need to do is, call the ExecuteReader method of the SqlCommand object which will return an instance of SqlDataReader class as shown in the below image.

SqlDataReader sdr=cmd.ExecuteReader();