ADO.NET

 ADO stands for Microsoft ActiveX Data Objects. ADO.NET is one of Microsoft’s Data Access technology using which we can communicate with different data sources. 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, update and delete data (i.e. performing CRUD operation) from data sources. ADO.NET mainly uses System.Data.dll and System.Xml.dll.

The following are some of the .NET applications where you can ADO.NET Data Access Technology to interact with a data source.

1. ASP.NET Web Form Applications
2. Windows Applications
3. ASP.NET MVC Application
4. Console Applications
5. ASP.NET Web API Applications

Components are designed for data manipulation and faster data access. 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. They are as follows:

1. Data provider and
2. DataSet.

What is .NET Data Providers?

The Database can not directly execute our C# code, it only understands SQL. So, if a .NET application needs to retrieve data or to do some insert, update, and delete operations from or to a database, then the .NET application needs to

1. Connect to the Database
2. Prepare an SQL Command
3. Execute the Command
4. Retrieve the results and display them in the application

ADO.NET code to connect to Oracle Database

The following code is for connecting to Oracle Database and retrieve data. If you notice, here we are using OracleConnection, OracleCommand, and OracleDataReader classes. That means all these classes have prefixed the word Oracle and these classes are used to communicate with the Oracle database only.

OracleConnection connection = new OracleConnection("data source=.; database=TestDB; integrated security=SSPI");

OracleCommand command = new OracleCommand("Select * from Customers", connection);

connection.Open();

OracleDataReader myReader = command.ExecuteReader();

while (myReader.Read())

{

Console.WriteLine("\t{0}\t{1}", myReader.GetInt32(0), myReader.GetString(1));

}

connection.Close();

All the above classes are present in System.Data.OracleClient namespace. So, we can say that the .NET data provider for Oracle is System.Data.OracleClient.

Note: Similarly, if you want to communicate with OLEDB data sources such as Excel, Access, etc. then you need to use OleDbConnection, OleDbCommand, and OleDbDataReader classes. So, the .NET data provider for OLEDB data sources is System.Data.OleDb

Different .NET Data Providers

ADO.NET Data Providers

Please have a look at the following image to understand the ADO.NET Data Providers in a better manner. As you can see, we have divided the diagram into three sections. The first section is the .NET Applications, the second section is the .NET Data Providers and the third section is the data sources. Based on the data source, you need to use the appropriate provider in your application.

The point that you need to remember is depending on the provider, the ADO.NET objects (Connection, Command, DataReader, and DataAdapter) have a different prefix as shown below.

1. Connection – SQLConnection, OracleConnection, OleDbConnection, OdbcConnection, etc.
2. Command – SQLCommand, OracleCommand, OleDbCommand, OdbcCommand, etc.
3. DataReader – SQLDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader, etc.
4. DataAdapter – SQLDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter, etc.
5. DataSet:The DataSet object in ADO.NET is not provider-specific. Once you connect to a database, execute the command, and retrieve the data into the .NET application. The data can then be stored in a DataSet and work independently of the database. So, it is used to access data independently from any data source. The DataSet contains a collection of one or more DataTable objects.

 The key to understanding ADO.NET is to understand the following objects.

1. Connection
2. Command
3. DataReader
4. DataAdapter
5. DataSet

In our introduction part, we discussed that Connection, Command, DataAdapter, and DataReader objects are providers specific where the DataSet is provider-independent. That means if you are going to work with SQL Server database, then you need to use SQL-specific provider objects such as SQLConnection, SqlCommand, SqlDataAdapter, and SqlDataReader objects which belong to the System.Data.SqlClient namespace.

Note: If you understand how to work with one database, then you can easily work with any other database. All you have to do is, change the provider-specific string (i.e. SQL, Oracle, Oledb, Odbc) on the Connection, Command, DataReader, and DataAdapter objects depending on the data source you are working with.

Here, in this article, I am going to discuss the SqlConnection object in detail. The concepts that we discuss here will be applicable to all the .NET data providers.

What is ADO.NET SqlConnection Class in C#?

The ADO.NET SqlConnection class belongs to System.Data.SqlClient namespace and is used to establish an open connection to the SQL Server database. The most important point that you need to remember is the connection does not close implicitly even it goes out of scope. Therefore, it is always recommended and always a good programming practice to close the connection object explicitly by calling the Close() method of the connection object

Note: The connections should be opened as late as possible, and should be closed as early as possible as the connection is one of the most expensive resources.

ADO.NET SqlConnection class Signature in C#:

Following is the signature of SqlConnection class. As you can see, it is a sealed class and inherited from DbConnection class and implement the ICloneable interface.

SqlConnection Class Constructors:

The ADO.NET SqlConnection class has three constructors which are shown in the below image.

Let us discuss each of these constructors in detail.

1. SqlConnection(): It initializes a new instance of the System.Data.SqlClient.SqlConnection class
2. SqlConnection(String connectionString): This constructor is used to initializes a new instance of the System.Data.SqlClient.SqlConnection class when given a string that contains the connection string.
3. SqlConnection(String connectionString, SqlCredential credential): It is used to initializes a new instance of the System.Data.SqlClient.SqlConnection class given a connection string, that does not use Integrated Security = true and a System.Data.SqlClient.SqlCredential object that contains the user ID and password.

C# SqlConnection class Methods:

Following are some of the important methods of the SqlConnection object.

1. BeginTransaction(): It is used to start a database transaction and returns an object representing the new transaction.
2. ChangeDatabase(string database): It is used to change the current database for an open SqlConnection. Here, the parameter database is nothing but the name of the database to use instead of the current database.
3. ChangePassword(string connectionString, string newPassword): Changes the SQL Server password for the user indicated in the connection string to the supplied new password. Here, the parameter connectionString is the connection string that contains enough information to connect to the server that you want. The connection string must contain the user ID and the current password. The parameter newPassword is the new password to set. This password must comply with any password security policy set on the server, including minimum length, requirements for specific characters, and so on.
4. Close(): It is used to closes the connection to the database. This is the preferred method of closing any open connection.
5. CreateCommand(): It Creates and returns a System.Data.SqlClient.SqlCommand object associated with the System.Data.SqlClient.SqlConnection.
6. GetSchema(): It returns schema information for the data source of this System.Data.SqlClient.SqlConnection.
7. Open(): This method is used to open a database connection with the property settings specified by the System.Data.SqlClient.SqlConnection.ConnectionString.

How to create Connection Object in C#?

You can create an instance of the SqlConnection class in three ways as there are three constructors in SqlConnection class. Here, I am going to show you the two most preferred ways of creating an instance of SqlConnection class. They are as follows:

Using the constructor which takes the connection string as the parameter.

The following image shows how to create an instance of SqlConnection class using the constructor which takes ConnectionString as the only parameter.

Using the parameterless constructor of C# SqlConnection class:

The following image shows how to create an instance of SqlConnection class using the parameterless constructor. It is a two-step process. First, you need to create an instance of SqlConnection class using the parameterless constructor, and then using the ConnectionString property of the connection object you need to specify the connection string.

Note: The ConnectionString parameter is a string made up of Key/Value pairs that have the information required to create a connection object.

Here, the “data source” is the name or IP Address of the SQL Server that you want to connect to. If you are working with a local instance of SQL Server, then you can simply put a DOT(.). If the server is on a network, then you need to use either the Name or IP address of the server.

SqlConnection Example in C#

Let us see an example to understand how to connect to an SQL Server database. We have created a Student database in our previous article and we will connect to that Student database. Please have a look at the following C# code which will create the connection object and then establish an open connection when the Open method is called on the connection object.

Note: Here, we are using the using block to close the connection automatically. If you are using the using block then you don’t require to call the close() method explicitly to close the connection. It is always recommended to close the database connection using the using block in C#.

The complete code is given below.

using System;

using System.Data.SqlClient;

namespace AdoNetConsoleApplication

{

class Program

{

static void Main(string[] args)

{

new Program().Connecting();

Console.ReadKey();

}

public void Connecting()

{

string ConnectionString = "data source=.; database=student; integrated security=SSPI";

using (SqlConnection con = new SqlConnection(ConnectionString))

{

con.Open();

Console.WriteLine("Connection Established Successfully");

}

}

}

}

Output:

What, if we don’t use using block?

If you don’t use the using block to create the connection object, then you have to close the connection explicitly by calling the Close method on the connection object. In the following example, we are using try-block instead of using block and calling the Close method in finally block to close the database connection.

using System;

using System.Data.SqlClient;

namespace AdoNetConsoleApplication

{

class Program

{

static void Main(string[] args)

{

new Program().Connecting();

Console.ReadKey();

}

public void Connecting()

{

SqlConnection con = null;

try

{

// Creating Connection

string ConnectionString = "data source=.; database=student; integrated security=SSPI";

con = new SqlConnection(ConnectionString);

con.Open();

Console.WriteLine("Connection Established Successfully");

}

catch (Exception e)

{

Console.WriteLine("OOPs, something went wrong.\n" + e);

}

finally

{ // Closing the connection

con.Close();

}

}

}

}

Output:

Here, we hard-coded the connection strings in the application code. Let us first understand what is the problem when we hard-coded the connection string within the application code and then we will see how to overcome this problem.

The problem of hard-coding the connection string in application code:

There are 3 problems when we hard-coded the connection strings in the application code. They are as follows:

1. Let say, you move your database to a different server, then you need to change the database details in the application code itself. Once you change the application code, then you need to re-build the application as well as it also required a re-deployment which is time-consuming.
2. Again if you hard-coded the connection string in multiple places, then you need to change the connection in all the places which is not only a maintenance overhead but is also error-prone.
3. In real-time applications, while developing you may point to your Development database while moving to UAT, you may have a different server for UAT and in a production environment, you need to point to the production database.

How to solve the above problems?

We can solve the above problems, by storing the connection string in the application configuration file. The configuration file in windows or console application is app.config whereas for ASP.NET MVC or ASP.NET Web API application, the application configuration file is web.config.

How to store the connection string in the configuration file?

As we are working with a console application, the configuration file is app.config. So, we need to store the connection string in the app.config file as shown below. Give a meaningful name to your connection string. As we are going to communicate with the SQL Server database, so, we need to provide the provider name as System.Data.SqlClient.

<connectionStrings>

<add name="ConnectionString"

connectionString="data source=.; database=student; integrated security=SSPI"

providerName="System.Data.SqlClient" />

</connectionStrings>

Note: You need to put the above connection string inside the configuration section of the configuration file.

How to read the connection string from the app.config file?

In order to read the connection string from the configuration file, you need to use the ConnectionStrings property of the ConfigurationManager class. The ConfigurationManager class is present in System.Configuration namespace.

Example to read the connection string from the configuration file:

Please modify the Program.cs class file as shown below read the connection string from the configuration file.

using System;

using System.Configuration;

using System.Data.SqlClient;

namespace AdoNetConsoleApplication

{

class Program

{

static void Main(string[] args)

{

try

{

string ConString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

using (SqlConnection connection = new SqlConnection(ConString))

{

connection.Open();

Console.WriteLine("Connection Established Successfully");

}

}

catch (Exception e)

{

Console.WriteLine("OOPs, something went wrong.\n" + e);

}

Console.ReadKey();

}

}

}

Output:

Note: Storing connection strings in web.config is similar to the app.config and in the same ConfigurationManager class is used to read connection string from the web.config file.

What is ADO.NET SqlCommand Class in C#?

The ADO.NET SqlCommand class in C# is used to store and execute the SQL statement against the SQL Server database. As you can see in the below image, the SqlCommand class is a sealed class and is inherited from the DbCommand class and implement the ICloneable interface. As a sealed class, it cannot be inherited.

Constructors of ADO.NET SqlCommand Class in C#

The SqlCommand class in C# provides the following five constructors.

Let us discuss each of these constructors in detail.

SqlCommand():

This constructor is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class.

SqlCommand(string cmdText):

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with the text of the query. Here, the cmdText is the text of the query that we want to execute.

SqlCommand(string cmdText, SqlConnection connection):

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with the text of the query and a System.Data.SqlClient.SqlConnection. Here, the cmdText is the text of the query that we want to execute and the parameter connection is the connection to an instance of SQL Server.

SqlCommand(string cmdText, SqlConnection connection, SqlTransaction transaction):

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with the text of the query, a SqlConnection instance, and the SqlTransaction instance. Here, the parameter cmdText is the text of the query. The parameter connection is a SqlConnection that represents the connection to an instance of SQL Server and the parameter transaction is the SqlTransaction in which the SqlCommand executes.

SqlCommand(string cmdText, SqlConnection connection, SqlTransaction transaction, SqlCommandColumnEncryptionSetting columnEncryptionSetting):

It is used to initializes a new instance of the System.Data.SqlClient.SqlCommand class with specified command text, connection, transaction, and encryption setting. We already discussed the first three parameters which are the same as the previous. Here, the fourth parameter i.e. columnEncryptionSetting is the encryption setting.

Methods of SqlCommand Class in C#

The SqlCommand class in C# provides the following methods.

1. BeginExecuteNonQuery(): This method initiates the asynchronous execution of the Transact-SQL statement or stored procedure that is described by this System.Data.SqlClient.SqlCommand.
2. Cancel(): This method tries to cancel the execution of a System.Data.SqlClient.SqlCommand.
3. Clone(): This method creates a new System.Data.SqlClient.SqlCommand object is a copy of the current instance.
4. CreateParameter(): This method creates a new instance of a System.Data.SqlClient.SqlParameter object.
5. ExecuteReader(): This method Sends the System.Data.SqlClient.SqlCommand.CommandText to the System.Data.SqlClient.SqlCommand.Connection and builds a System.Data.SqlClient.SqlDataReader.
6. ExecuteScalar(): This method Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
7. ExecuteNonQuery(): This method executes a Transact-SQL statement against the connection and returns the number of rows affected.
8. Prepare(): This method creates a prepared version of the command on an instance of SQL Server.
9. ResetCommandTimeout(): This method resets the CommandTimeout property to its default value.

Example to understand the ADO.NET SqlCommand Object in C#:

We are going to use the following student table to understand the SqlCommand object.

Please use the below SQL script to create a database called StudentDB, a table called Student with the required sample data.

CREATE DATABASE StudentDB;

GO

USE StudentDB;

GO

CREATE TABLE Student(

Id INT PRIMARY KEY,

Name VARCHAR(100),

Email VARCHAR(50),

Mobile VARCHAR(50)

)

GO

INSERT INTO Student VALUES (101, 'Anurag', 'Anurag@dotnettutorial.net', '1234567890')

INSERT INTO Student VALUES (102, 'Priyanka', 'Priyanka@dotnettutorial.net', '2233445566')

INSERT INTO Student VALUES (103, 'Preety', 'Preety@dotnettutorial.net', '6655443322')

INSERT INTO Student VALUES (104, 'Sambit', 'Sambit@dotnettutorial.net', '9876543210')

GO

Note: ExecuteReader, ExecuteNonQuery, and ExecuteScalar are the methods that are commonly used. Let us see three examples to understand these methods.

ExecuteReader Method of SqlCommand Object in C#:

As we already discussed this method is used to send the CommandText to the Connection and builds a SqlDataReader. When your T-SQL statement returns more than a single value (for example rows of data), then you need to use the ExecuteReader method. Let us understand this with an example. The following example uses the ExecuteReader method of the SqlCommand object to executes the T-SQL statement which returns multiple rows of data.

using System;

using System.Data.SqlClient;

namespace AdoNetConsoleApplication

{

class Program

{

static void Main(string[] args)

{

try

{

string ConString = "data source=.; database=StudentDB; integrated security=SSPI";

using (SqlConnection connection = new SqlConnection(ConString))

{

// Creating SqlCommand objcet

SqlCommand cm = new SqlCommand("select * from student", connection);

// Opening Connection

connection.Open();

// Executing the SQL query

SqlDataReader sdr = cm.ExecuteReader();

while (sdr.Read())

{

Console.WriteLine(sdr["Name"] + ", " + sdr["Email"] + ", " + sdr["Mobile"]);

}

}

}

catch (Exception e)

{

Console.WriteLine("OOPs, something went wrong.\n" + e);

}

Console.ReadKey();

}

}

}

Once you execute the program, you will get the following output as expected.

Understanding the ADO.NET SqlCommand Object in C#:

In our example, we are creating an instance of the SqlCommand by using the constructor which takes two parameters as shown in the below image. The first parameter is the command text that we want to execute, and the second parameter is the connection object which provides the database details on which the command is going to execute.

You can also create the SqlCommand object using the parameterless constructor, and later you can specify the command text and connection using the CommandText and the Connection properties of the SqlCommand object as shown in the below example.

using System;

using System.Data.SqlClient;

namespace AdoNetConsoleApplication

{

class Program

{

static void Main(string[] args)

{

try

{

string ConString = "data source=.; database=StudentDB; integrated security=SSPI";

using (SqlConnection connection = new SqlConnection(ConString))

{

// Creating SqlCommand objcet

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "select * from student";

cmd.Connection = connection;

// Opening Connection

connection.Open();

// Executing the SQL query

SqlDataReader sdr = cmd.ExecuteReader();

while (sdr.Read())

{

Console.WriteLine(sdr["Name"] + ", " + sdr["Email"] + ", " + sdr["Mobile"]);

}

}

}

catch (Exception e)

{

Console.WriteLine("OOPs, something went wrong.\n" + e);

}

Console.ReadKey();

}

}

}

ExecuteScalar Method of SqlCommand Object in C#:

When your T-SQL query or stored procedure returns a single(i.e. scalar) value then you need to use the ExecuteScalar method of the SqlCommand object in C#. Let us understand this with an example. Now, we need to fetch the total number of records present in the Student table. As we know it is going to return a single value, so this is an ideal situation to use the ExecuteScalar method. The following example will retrieve the total number of records present in the Student table.

using System;

using System.Data.SqlClient;

namespace AdoNetConsoleApplication

{

class Program

{

static void Main(string[] args)

{

try

{

string ConString = "data source=.; database=StudentDB; integrated security=SSPI";

using (SqlConnection connection = new SqlConnection(ConString))

{

// Creating SqlCommand objcet

SqlCommand cmd = new SqlCommand("select count(id) from student", connection);

// Opening Connection

connection.Open();

// Executing the SQL query

// Since the return type of ExecuteScalar() is object, we are type casting to int datatype

int TotalRows = (int)cmd.ExecuteScalar();

Console.WriteLine("TotalRows in Student Table : " + TotalRows);

}

}

catch (Exception e)

{

Console.WriteLine("OOPs, something went wrong.\n" + e);

}

Console.ReadKey();

}

}

}

The return type of the ExecuteScalar method is an object, so here we need to typecast it into integer type. Now, if you execute the above program, then you will get the following output.

ExecuteNonQuery Method of ADO.NET SqlCommand Object in C#:

When you want to perform Insert, Update or Delete operations and want to return the number of rows affected by your query then you need to use the ExecuteNonQuery method of the SqlCommand object in C#. Let us understand this with an example. The following example performs an Insert, Update and Delete operations using the ExecuteNonQuery() method.

using System;

using System.Data.SqlClient;

namespace AdoNetConsoleApplication

{

class Program

{

static void Main(string[] args)

{

try

{

string ConString = "data source=.; database=StudentDB; integrated security=SSPI";

using (SqlConnection connection = new SqlConnection(ConString))

{

SqlCommand cmd = new SqlCommand("insert into Student values (105, 'Ramesh', 'Ramesh@dotnettutorial.net', '1122334455')", connection);

connection.Open();

int rowsAffected = cmd.ExecuteNonQuery();

Console.WriteLine("Inserted Rows = " + rowsAffected);

//Set to CommandText to the update query. We are reusing the command object,

//instead of creating a new command object

cmd.CommandText = "update Student set Name = 'Ramesh Changed' where Id = 105";

rowsAffected = cmd.ExecuteNonQuery();

Console.WriteLine("Updated Rows = " + rowsAffected);

//Set to CommandText to the delete query. We are reusing the command object,

//instead of creating a new command object

cmd.CommandText = "Delete from Student where Id = 105";

rowsAffected = cmd.ExecuteNonQuery();

Console.WriteLine("Deleted Rows = " + rowsAffected);

}

}

catch (Exception e)

{

Console.WriteLine("OOPs, something went wrong.\n" + e);

}

Console.ReadKey();

}

}

}

Output:

So, in short, we can say that the SqlCommand Object in C# is used to prepare the command text (T-SQL statement or Stored Procedure) that you want to execute against the SQL Server database and also provides some methods (ExecuteReader, ExecuteScalar, and ExecuteNonQuery) to execute those commands.