ADO.NET Basics

ADO.NET is a group of classes used  to access and manipulate the data from the database servers.

In short,  ADO.NET =  legacy Microsoft ActiveX Data Objects + Microsoft .NET framework.

ADO.NET is an upgraded version of classic ADO with support from .NET framework features i.e. CLR Integration, Memory Management, etc.

ADO.NET

What is the namespace or library we need to import to work with ADO.NET?
Import the below base library to invoke the ADO.NET classes.

Using System. Data; 

What are the main classes inside ADO.NET?

1.SqlConnection (or) OleDbConnection (or) OdbcConnection

2.SqlCommand (or) OleDbCommand (or) OdbcCommand

3.SqlDataReader (or) OleDbDataReader (or) OdbcDataReader

4.SqlDataAdapter (or) OleDbDataAdapter (or) OdbcDataAdapter

5.DataSet 

What are the Connection classes in ADO.NET?

The Connection classes are used to define the connection to a specific database server.

ADO.NET supports three types of connection classes, i.e.

1.SqlConnection

2.OleDbConnection

3.OdbcConnection

SqlConnection,OdbcConnection classes are used to connect to SQL database and

OleDbConnection the class used to connect to the Oracle database. 

What if you want to establish the connection to the SQL server?

For SQL server connectivity, import the below library.

Using System.Data.SqlClient;

 

Are there any other libraries available to connect to servers like Oracle?

The answer is yes. For Oracle server connectivity, import either of the below libraries available.

1.   Using System.Data.Odbc; 
(or) 
2.   Using System.Data.OleDb;

 

What is the connection class for the SQL server?

The ‘SqlConnection’ is the class used to create a successful connection to SQL?

Syntax: 
SqlConnection con = new SqlConnection(string connectionString, SqlCredential sqlCredentials)

 

What is SqlCredential the parameter in the SqlConnection class?

The SqlCredential is a class used to define the SQL credentials (username and password) for SQL authentication. 

What is the connectionString parameter format for the SQL connection?

If we want to login to SQL Server using SQL authentication(username and password), use the below connection string format:

<add name="connectString" connectionString="data source=servername;initial catalog:databasename;uid=username;pwd=password;" providerName="System.Data.SqlClient"/>

 

If we want to login to SQL Server using windows authentication, use the below connection string format:

<add name="connectString" connectionString="data source=servername;initial catalog:databasename;integrated security=true;" providerName="System.Data.SqlClient"/>

  

Where we need to define the connection strings?

Its recommended that we can define all the connection strings in web.config file.

Example:

<connectionStrings>
<add name="connectString" connectionString="data source=servername;initial catalog:databasename; uid=username;pwd=password;" providerName="System.Data.SqlClient"/>
</connectionStrings>

How to use the connection string in the .NET application?

Use the below steps to consume the connection string defined in the web.config file.

Step 1:

Import the below the library in the application.

Using System.Configuration

Step 2:

Get the connection string using the below code:

string connectionString = ConfigurationManager.ConnectionStrings["connectString"].ConnectionString; 
Here ‘connectString’ is name of the connection string added in web.config <connectionStrings> tag.

Step 3:

Use the above local variable in ‘SqlConnection’ class

SqlConnection con = new SqlConnection(connectionString);

 

What are the Command classes in ADO.NET? 

Command classes in ADO.NET are used to execute database queries or stored procedures on the database server from .NET code.

ADO.NET supports three types of Command classes:

1.SqlCommand

2.OleDbCommand

3.OdbcCommand

The ‘SqlCommand’ used to execute SQL queries or stored procedures on SQL server

The ‘OleDbCommand’ used to execute queries or stored procedures on Oracle server

 

How to use the Command classes in ADO.NET?

For example, if we want to use the SQL command class in ADO.NET add the below code:

Syntax: 
SqlCommand localVariablename = new SqlCommand(string cmdText,SqlConnection SqlConnection) 
Or 
SqlCommand localVariablename = new SqlCommand(string cmdText) 
Or 
SqlCommand localVariablename = new SqlCommand()

Example:

If Command Type is SQL query:

SqlCommand cmd = new SqlCommand(); 
cmd.CommandText = "select * from tablename"; 
cmd.CommandType = CommandType.Text;  
cmd.Connection = connection;

If Command Type is Sql Stored Procedure:

SqlCommand cmd = new SqlCommand(); 
cmd.CommandText = "dbo.SampleStoredProcedure"; 
cmd.CommandType = CommandType.StoredProcedure; 
cmd.Connection = connection;

 

What are the DataReader classes in ADO.NET?
DataReader classes in ADO.NET are used to read and forward only one row at a time. The main advantage of data reader is data processing is fast. It’s useful only if the data size is very less.

ADO.NET supports three types of DataReader classes:

1.SqlDataReader

2.OleDbDataReader

3.OdbcDataReader

The ‘SqlDataReader’ used to read and forward the single rows from SQL server

The ‘OleDbDataReader used to read and forward the single rows from Oracle server

SQL Example:
SqlCommand commandVar = new SqlCommand();
SqlDataReader reader = commandVar.ExecuteReader(); 
While(reader.Read())
{
//Process the single row
}

What are the DataAdapter classes in ADO.NET?
DataAdapter classes in ADO.NET are used to populate the Dataset from the databases. Also supports the updates in the database based on the changes in dataset using the properties like InsertCommand, SelectCommand, UpdateCommand, and DeleteCommand.

ADO.NET supports three types of DataAdapter classes:

1.SqlDataAdapter

2.OleDbDataAdapter

3.OdbcDataAdapter

The ‘SqlDataAdapter’ used to populate the Dataset with the SQL data from the SQL databases.
The ‘OleDbDataReader used to populate the Dataset with the Oracle data from the Oracle databases

SQL Example: 
DataSet dataSet = new DataSet(); 
SqlDataAdapter dap = new SqlDataAdapter(); 
dap.Fill(dataSet);

 

What is the DataSet class in ADO.NET?
Dataset in ADO.NET acts as in-memory temporary storage for the data pulled from the database. It works in a completely disconnected way of access. Once data pulled to in-memory, the active connection is closed. We can process the data offline instead of keeping the connection active for entire data processing.
DataSet is a collection of DataTables internally.

Syntax: 
DataSet dataset = new DataSet(); 

Conclusion

In this post, explained the very basic scenarios to provide the high- level overview on what is what on ADO.NET. Hope this is useful to understand the key concepts.