ADO stands for Microsoft ActiveX Data Objects. ADO.NET is one of Microsoft’s Data Access Technologies 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 (Console, WCF, WPF, Windows, MVC, Web Form, etc.) and different data sources.
What types of Applications use ADO.NET?
ADO.NET can be used to develop any kind of .NET application. The following are some of the .NET applications where you can use ADO.NET Data Access Technology to interact with a data source.
- ASP.NET Web Form Applications
- Windows Applications
- ASP.NET MVC Application
- Console Applications
- ASP.NET Web API Applications
- ASP.NET Core Applications
Components of ADO.NET
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:
- Data Provider and
- DataSet.
What are .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
- Connect to the Database
- Prepare an SQL Command
- Execute the Command
- Retrieve the results and display them in the application
And this is possible with the help of .NET Data Providers.
ADO.NET Code to Connect with SQL Server Database
The following image shows the sample ADO.NET code which is connecting to SQL Server Database and retrieves data. If you notice in the below image, here, we are using some predefined classes such as SQLConnection, SQLCommand, and SQLDataReader. These classes are called .NET Provider classes and these classes are responsible for interacting with the database and performing the CRUD operation. If you further notice all the classes are prefixed with the word SQL, it means these classes are going to interact with only the SQL Server database.
All these classes are present in System.Data.SqlClient namespace. We can also say that the .NET data provider for the SQL Server database is System.Data.SqlClient.
ADO.NET code to connect with Oracle Database
The following code is for connecting to Oracle Database and retrieving 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 Database 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, here, 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 .NET 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.
- Connection – SQLConnection, OracleConnection, OleDbConnection, OdbcConnection, etc.
- Command – SQLCommand, OracleCommand, OleDbCommand, OdbcCommand, etc.
- DataReader – SQLDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader, etc.
- DataAdapter – SQLDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter, etc.
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.
ADO.NET using SQL Server
Open SQL Server Management Studio Tool
Once you open SSMS (SQL Server Management Studio), It will prompt you the connect to the server window. Here, you need to provide the server name and authentication details (I am going with the Windows Authentication), select Database Engine as the server type, and finally, click on the Connect button as shown in the below image.
Once you click on the Connect button, it will connect to the SQL Server Database and after a successful connection, it will display the following window.
Creating Database in SQL Server
In order to create a database using GUI, you need to select the database option from object explorer and then right-click on it. It pops up an options menu and here, you need to click on the New Database option as shown in the below image.
Once you click on the New Database option, then it will open the following New Database window. Here, you just need to provide the database name and click on the OK button. Here, I created a database with the name Student. But it is up to you, you can provide any meaningful name as per your choice.
Once you click on the OK button, then it will create a Student database and you can see the Student database in the object explorer as shown in the below image.
That’s it. Our database part is over. Now let us move to the ADO.NET part.
Establish a connection to SQL Server database and create a table using ADO.NET
Once the Student Database is ready, now, let’s move and create a table (Student table) by using the ADO.NET Provider and C# code. Open visual studio 2017 (you can use any version of visual studio), then create a new .NET console application project. Once you create the project, then modify the Program.cs class file as shown below. In this article, I am not going to explain the code. Here in this article, I am just going to show you how to communicate with SQL Server database. From our next article onwards, I will explain each and everything in detail.
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().CreateTable();
Console.ReadKey();
}
public void CreateTable()
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection(“data source=.; database=student; integrated security=SSPI”);
// writing sql query
SqlCommand cm = new SqlCommand(“create table student(id int not null, name varchar(100), email varchar(50), join_date date)”, con);
// Opening Connection
con.Open();
// Executing the SQL query
cm.ExecuteNonQuery();
// Displaying a message
Console.WriteLine(“Table created Successfully”);
}
catch (Exception e)
{
Console.WriteLine(“OOPs, something went wrong.” + e);
}
// Closing the connection
finally
{
con.Close();
}
}
}
}
Now, execute the program and you should see the following message on the console.
We can see the created table in Microsoft SQL Server Management Studio also. It shows the created table as shown below.
See, we have the Student table within the Student database. As of now, the Student table is empty. Let us insert one record into the Student table using ADO.NET and C#.
Inserting Record using C# and ADO.NET:
Please modify the Program.cs class file as shown below. Here, we will insert a record into the student table.
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().InsertRecord();
Console.ReadKey();
}
public void InsertRecord()
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection(“data source=.; database=student; integrated security=SSPI”);
// writing sql query
SqlCommand cm = new SqlCommand(“insert into student (id, name, email, join_date) values (‘101’, ‘Ronald Trump’, ‘ronald@example.com’, ‘1/12/2017’)”, con);
// Opening Connection
con.Open();
// Executing the SQL query
cm.ExecuteNonQuery();
// Displaying a message
Console.WriteLine(“Record Inserted Successfully”);
}
catch (Exception e)
{
Console.WriteLine(“OOPs, something went wrong.” + e);
}
// Closing the connection
finally
{
con.Close();
}
}
}
}
Once you run the application, you will get the following output.
Retrieve Record using C# and ADO.NET
Here, we will retrieve the inserted data from the Student table of the student database. Please modify the Program.cs class file as shown below.
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().DisplayData();
Console.ReadKey();
}
public void DisplayData()
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection(“data source=.; database=student; integrated security=SSPI”);
// writing sql query
SqlCommand cm = new SqlCommand(“Select * from student”, con);
// Opening Connection
con.Open();
// Executing the SQL query
SqlDataReader sdr = cm.ExecuteReader();
// Iterating Data
while (sdr.Read())
{
// Displaying Record
Console.WriteLine(sdr[“id”] + ” ” + sdr[“name”] + ” ” + sdr[“email”]);
}
}
catch (Exception e)
{
Console.WriteLine(“OOPs, something went wrong.” + e);
}
// Closing the connection
finally
{
con.Close();
}
}
}
}
You will get the following output when you run the above program.
Deleting Record from SQL Server database using C# and ADO.NET
As of now, the student table contains one record. Let us delete that record using ADO.NET and C#. Please modify the Program.cs class file code as shown below which will delete the record from the Student table.
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
class Program
{
static void Main(string[] args)
{
new Program().DeleteData();
Console.ReadKey();
}
public void DeleteData()
{
SqlConnection con = null;
try
{
// Creating Connection
con = new SqlConnection(“data source=.; database=student; integrated security=SSPI”);
// writing sql query
SqlCommand cm = new SqlCommand(“delete from student where id = ‘101’”, con);
// Opening Connection
con.Open();
// Executing the SQL query
cm.ExecuteNonQuery();
Console.WriteLine(“Record Deleted Successfully”);
}
catch (Exception e)
{
Console.WriteLine(“OOPs, something went wrong.” + e);
}
// Closing the connection
finally
{
con.Close();
}
}
}
}
It will display the following output once you execute the program.
Now, if you verify the student table, then you will see that the record is deleted. In this article, I didn’t explain a single line of code intentionally. I will explain each and everything in detail from our next article.
In the next article, I am going to discuss ADO.NET SqlConnection Class in detail. Here, in this, I try to explain ADO.NET using SQL Server i.e. how to connect to SQL server using ADO.NET. I hope you enjoy this article. I would like to have your feedback. Please post your feedback, question, or comments about this article.
ADO.NET SqlConnection
Back to: ADO.NET Tutorial For Beginners and Professionals
ADO.NET SqlConnection Class in C# with Examples
In this article, I am going to discuss the ADO.NET SqlConnection Class in C# with Examples. Please read our previous article where we discussed ADO.NET using SQL Server. As part of this article, we are going to discuss the following pointers in detail.
- What is the ADO.NET SqlConnection class?
- How to instantiate the SqlConnection object
- Using the SqlConnection object
- Why is it important to close a database connection
- How to properly close a connection
- What is the problem with hard-coding the connection string in the application?
- How to store and retrieve the connection string from the configuration file?
What we discussed in the Introduction Part of this article?
Let us first recap what we discussed in our introduction to ADO.NET Article. We discussed the different .NET data providers. The key to understanding ADO.NET is to understand the following objects.
In our introduction part, we discussed that Connection, Command, DataAdapter, and DataReader objects are providers specific whereas the DataSet is provider-independent. That means if you are going to work with the 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 if 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, inherited from DbConnection class, and implement the ICloneable interface.
https://googleads.g.doubleclick.net/pagead/ads?client=ca-pub-5996718494925719&output=html&h=400&adk=1627668767&adf=3832474069&w=839&lmt=1680294023&rafmt=12&channel=9442797445&format=839×400&url=https%3A%2F%2Fdotnettutorials.net%2Flesson%2Fado-net-sqlconnection-class%2F&wgl=1&uach=WyJXaW5kb3dzIiwiMTUuMC4wIiwieDg2IiwiIiwiMTExLjAuNTU2My4xNDciLFtdLGZhbHNlLG51bGwsIjY0IixbWyJHb29nbGUgQ2hyb21lIiwiMTExLjAuNTU2My4xNDciXSxbIk5vdChBOkJyYW5kIiwiOC4wLjAuMCJdLFsiQ2hyb21pdW0iLCIxMTEuMC41NTYzLjE0NyJdXSxmYWxzZV0.&dt=1680311524469&bpp=2&bdt=411&idt=2&shv=r20230329&mjsv=m202303280101&ptt=9&saldr=aa&abxe=1&cookie=ID%3D2ca54fe5a990354f-22dc8f28dedc00ad%3AT%3D1680311161%3AS%3DALNI_MZTfqJT3580Gz2kIeiOZe7qGh_ldQ&gpic=UID%3D00000a3ae4d0f1da%3AT%3D1680311161%3ART%3D1680311161%3AS%3DALNI_MaH3Cu84bXJC4HJYPPDWxiPmKf43g&prev_fmts=0x0&nras=1&correlator=2037974830673&frm=20&pv=1&ga_vid=417109815.1680311161&ga_sid=1680311524&ga_hid=426242812&ga_fc=1&u_tz=330&u_his=9&u_h=864&u_w=1536&u_ah=816&u_aw=1536&u_cd=24&u_sd=1.25&dmc=8&adx=520&ady=2013&biw=1519&bih=746&scr_x=0&scr_y=0&eid=44759876%2C44759927%2C44759837%2C44773809%2C31071755%2C31073378%2C31073508%2C44786919%2C31073349%2C31067147&oid=2&pvsid=3885401420046842&tmod=1252662495&uas=0&nvt=1&ref=https%3A%2F%2Fdotnettutorials.net%2Flesson%2Fado-net-using-sql-server%2F&fc=1920&brdim=0%2C0%2C0%2C0%2C1536%2C0%2C1536%2C816%2C1536%2C746&vis=1&rsz=%7C%7CEebr%7C&abl=CS&pfx=0&cms=2&fu=256&bc=31&td=1&ifi=2&uci=a!2&btvi=1&fsb=1&xpc=EzqJslEObt&p=https%3A//dotnettutorials.net&dtd=15
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.
- SqlConnection(): It initializes a new instance of the System.Data.SqlClient.SqlConnection class
- SqlConnection(String connectionString): This constructor is used to initialize a new instance of the System.Data.SqlClient.SqlConnection class when given a string that contains the connection string.
- SqlConnection(String connectionString, SqlCredential credential): It is used to initialize 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.
- BeginTransaction(): It is used to start a database transaction and returns an object representing the new transaction.
- 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.
- 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.
- Close(): It is used to close the connection to the database. This is the preferred method of closing any open connection.
- CreateCommand(): It Creates and returns a System.Data.SqlClient.SqlCommand object associated with the System.Data.SqlClient.SqlConnection.
- GetSchema(): It returns schema information for the data source of this System.Data.SqlClient.SqlConnection.
- 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 a 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:
- Let’s 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 rebuild the application as well as it also required a re-deployment which is time-consuming.
- 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.
- 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. By default, this System.Configuration DLL is not included in our application, so we need to add this DLL first.
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 the same ConfigurationManager class is used to read connection strings from the web.config file.
In the next article, I am going to discuss ADO.NET SqlCommand Class in detail. Here, in this article, I try to explain the ADO.NET SqlConnection class in C# with examples. I hope this C# SqlConnection article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.