How to work with ADO.NET and SQL connection in .NET

Hello readers, before we start discussing the topic lets first understand what ADO.NET is. It’s a platform where you can access your data from Microsoft .NET which provides a communication connection between Relational and non-relational database system. It is not a different technology. Also, ADO.NET stands for ActiveX Data Objects which is used to connect application system and database server.

ADO.NET is a set of classes, that uses XML (eXtensible Markup Language) sources to store and transfer data in the running application. It also provides the fastest data access for your desktop version and certain applications. In addition, There are some different types of applications which make use of ADO.NET, that connect a database to execute several commands and retrieve data like ASP.NET Web, Windows, and Console Applications.

ADO.NET Architecture

Let’s have a look at the ADO.NET architecture as follows.

Difference between ADO and ADO.NET

These are some important key factors which define the differentiation between ADO and ADO.NET as follows

  • ADO is totally based on Component Object Modelling technology. Whereas ADO.NET is based on Common Language Runtime technology.
  • In ADO, you can store data in binary format. Whereas, ADO.NET, you can store data in XML format like parsing of data.
  • ADO supports limited data types. On another hand, ADO.NET supports rich and large data types.
  • ADO contains limited access to XML that is the reason it cannot be integrated with XML. On another hand, ADO.NET can be integrated with the only XML and also contains robust access to XML in.NET.
  • Data is provided by Record set in ADO. Whereas Data is provided by DataSet or Data Adapter.
  • ADO always requires for continuous connection in active mode or we can say it is connection oriented. Whereas ADO.NET does not require an active continuous connection.
  • ADO provides rows as single data table view and it scans these rows with a MoveNext method. Whereas ADO.NET provides a major collection of data table view and you can access multiple records through table variable loop.
  • You can create the only client-side cursor in ADO. Whereas you can create both client and server side cursor in ADO.NET.
  • In ADO, by the use of single connection, you cannot handle multiple transactions. Whereas by the use of single connection you can handle multiple transactions in ADO.NET.
  • In ADO, you can face a problem of firewall because it stops many requests. Whereas there is no problem of firewall because of XML completely firewall proof.

Some useful advantage

  • ADO.NET is the general name in the classes and the interfaces of the inbuilt namespace System.Data. ADO.NET data components in Visual Studio environment enclose data access function in many ways which help you to invent new applications with minor errors.
  • ADO.Net avails performance by its Disconnected behavior because it is a sufficient and scalable architecture in .NET framework. The DataSet class in ADO.Net operates disconnected behavior. This model allows data resources which can be plugged into code without any hassle in the future.
  • In ADO.NET, programs contain advantage of the data flexibility and broad nature of Extensible Markup Language (XML). XML is the only way of transferring datasets among the network. So, any data component which can read the XML format also can process data. In fact, with XML that they simply cannot exist or function without utilizing the technology.
  • ADO.NET provides direct data manipulation through SQL. ADO.NET involves Server Data Provider in SQL which is optimized for high interaction with SQL Server. It also uses SQL Server’s own Tabular Data Stream (TDS) format with exchange information
  • ADO.NET contains rich object model. The entire architecture ADO.NET is made up of class inheritance and interface connection. Once you need a namespace, then you can find that the logical features of inheritance and base class object support that makes the whole system easy to use.

What Is .NET Data Providers

Databases only understand SQL commands. If a .NET application frameworks (Web, Windows, Console) to fetch data, then the application needs to Connect to the Database then prepare SQL statements to execute the Command which further retrieve the results and display in the application in the window.

Sample ADO.NET code to connect SQL server database and fetch data. Note that, we are making use of SQL connection, its command and SQL data reader class set. All classes are present in System.Data.SqlClient namespace. That is the only reason we say that .NET data provider is System.Data.SqlClient.

SqlConnection con = new SqlConnection("data source=.; database=Sample; integrated security=SSPI");

SqlCommand cmd = new SqlCommand("Select * from tblProduct", con);

con.Open();

SqlDataReader rdr = cmd.ExecuteReader();

GridView1.DataSource = rdr;

GridView1.DataBind();

con.Close();

Sample ADO.NET code which connects Oracle database and fetches data. We are containing Oracle Connection and Oracle DataReader. All classes are represented data in System.Data.OracleClient namespace. That is the only reason we say that .NET data provider is System.Data.OracleClient.

OracleConnection con = new OracleConnection("Oracle Database Connection String");

OracleCommand cmd = new OracleCommand("Select * from tblProduct", con);

con.Open();

OracleDataReader rdr = cmd.ExecuteReader();

GridView1.DataSource = rdr;

GridView1.DataBind();

con.Close();

If you want to connect to OLEDB data sources like Excel, Access. So, you can use these classes like OleDbConnection, OleDbCommand, and OleDbDataReader etc. Hence, the .NET data provider for OLEDB is System.Data.OleDb.

Different .NET Data Providers

  • This is for SQL Server – System.Data.SqlClient
  • Data Provider for Oracle – System.Data.OracleClient
  • This is for OLEDB – System.Data.OleDb
  • Data Provider for ODBC – System.Data.Odbc

Note: Depends on the provider, following are the ADO.NET objects with the different prefix as follows.

  • Connection – SQLConnection, OracleConnection, OleDbConnection, OdbcConnection.
  • Command – SQLCommand, OracleCommand, OleDbCommand, OdbcCommand.
  • DataReader – SQLDataReader, OracleDataReader, OleDbDataReader, OdbcDataReader.
  • DataAdapter – SQLDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter.

Once you connect to a database server, then execute a command, and fetch data into a .NET web application. Then the data can be stored in a DataSet and work independently of the database.

How SQLConnection works in ADO.NET

First of all, when working with the database, then you need to create a connection object. There are two ways to create an instance of the SqlConnection set of class as follows.

For creating an instance of SqlConnection constructor class which takes ConnectionString parameter.

SqlConnection connection = new SqlConnection("data source=.; database=SampleDB; integrated security=SSPI");

//First create an instance of SqlConnection class by using the parameter with less constructor

SqlConnection connection = new SqlConnection();

//After that, set the ConnectionString property of the connection object

connection.ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";

The ConnectionString parameter is a string that is a combination of Key-Value pairs that requires information to create a connection object.

To create a connection object with windows authentication

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

To create a connection object with SQL Server authentication.

string ConnectionString = "data source=.; 

database=SampleDB; 

user id=MyUserName; 

password=MyPassword";

The “data source” contains the name or IP Address of the SQL Server that you need to connect with. If you ever work with a local instance of SQL database server, you need to specify DOT(.) symbol. If the server is on a local network, then you can use Name or IP address.

A sample for ADO.NET code as follows.

1. First of all, create a connection

2. Once you create a single connection then an object is passed to the command object class. So, that the command object class knows on which SQL server connection needs to execute this command.

3. Then execute the command, and set the command results set with the source for the data grid view control.

4. Then, call the DataBind() function method.

5. Finally, close the connection in the last block. Always remember connections are limited and much valuable so close it in a proper way to get better performance and scalability.

Note: Connections must be open late, and must be close early.

protected void Page_Load(object sender, EventArgs e)
{

//Now create the connection object

SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI");;

try

{

// Here, pass the connection to the command object class. So, the command object knows on which connection needs to execute the command

SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);

// First, Open the connection. Else you get a runtime error statement. An open connection is required to execute the command

connection.Open();

GridView1.DataSource = cmd.ExecuteReader();

GridView1.DataBind();

}

catch (Exception ex)

{

// if any exceptions occur then handle it.

}

finally

{

// The finally block is to execute even if there is an exception and make sure that the connections closed properly.
connection.Close();

}

}

We can also use “using” command to close the connection in the more appropriate way as follows. We don’t need to call Close() function method when using function is being used. Then, the connection will close automatically.

protected void Page_Load(object sender, EventArgs e)

{

using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI"))

{
SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);

connection.Open();

GridView1.DataSource = cmd.ExecuteReader();

GridView1.DataBind();

}

}

Connection Strings in a web.config configuration file

There are two major issues with connection strings in application code system.
1. For this reason, if we need to point an application to a different server, Then, you just need to change the application code. Once you change the application code, the application requires a re-develop which is a time waste.

2. All of the pages which contain the connection string needs to change. This one leads you to the maintenance of database server.

In the web application, the configuration strings always store in a web.config file, as given below. You need to give an appropriate name to the connection string. As you know, we are making use of SQL server, the provider name must System.Data.SqlClient. Here is the coding as follows.

<connectionStrings>

<add name="DatabaseConnectionString"

connectionString="data source=.; database=Sample_Test_DB; Integrated Security=SSPI"

providerName="System.Data.SqlClient" />

</connectionStrings>

Reading connection string from the web.config file

First of all, use the property ConnectionStrings of the ConfigurationManager class to fetch the connection string value from web.config file in window explorer in visual studio. ConfigurationManager class present itself in System.Configuration namespace.

protected void Page_Load(object sender, EventArgs e)

{

string ConnectionString = ConfigurationManager.co

.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

using (SqlConnection connection = new SqlConnection( ConnectionString ))

{

SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);

connection.Open();

GridView1.DataSource = cmd.ExecuteReader();

GridView1.DataBind();

}

}

The configuration file is App.config in a windows application server. App.config is same as web.config when you store the connection strings. Also ConfigurationManager class used to read connection string from App.config file windows application. It shows read connection strings from App.config file and combined the data in the Data Gridview control in a windows application server as follows.

private void Form1_Load(object sender, EventArgs e)

{

string ConnectionString =

ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

using (SqlConnection connection = new SqlConnection( ConnectionString ))

{

SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);

connection.Open();

BindingSource source = new BindingSource();

source.DataSource = cmd.ExecuteReader();

dataGridView1.DataSource = source;

}

}

How SQL command works in ADO.NET

In above example, we have discussed SqlConnection class. Now, we will go through SqlCommand class. SqlCommand class is the function that is used to make an SQL statement or unique Stored Procedure that we want to run on a SQL Server database platform. Moreover, we will learn about T-SQL in SQL database server.

These are the most common methods of the SqlCommand class as follows.

ExecuteReader – When the T-SQL statement gives more than a single value in return then you can use ExecuteReader command. E.g, if the query returns rows of data.

ExecuteNonQuery – When you need to perform an Insert, Update or Delete function then you can use ExecuteNonQuery command.

ExecuteScalar – When the query returns a scalar value then you can use this function. For example, queries that returns the total number of rows in a table.

For this reason, let’s assume a table tblProductInventory, and we will use this table in our example as follows.

Here is the table tblProductInventory

The testing sample code is given below, when you execute a T-SQL query statement, that returns multiple rows of data by using ExecuteReader() method function. Let’s understand with an example, we will create an instance of SqlCommand class, and by passing in the command text and the connection object in just one line. For this reason, we will make a use of an overloaded constructor of the SqlCommand class that takes two parameters which as (cmdText, connection). So, let us understand through the coding.

protected void Page_Load(object sender, EventArgs e)

{

string ConnectionString =

ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI"))

{

//Here, create an instance of SqlCommand class, and we are giving T-SQL command that you want execute and the connection object

SqlCommand cmd = new SqlCommand("Select Id,ProductName,QuantityAvailable from tblProductInventory", connection);

connection.Open();

//As you know, that the T-SQL query command that you want to execute which returns multiple rows of data, and use ExecuteReader() method function of the command object

GridView1.DataSource = cmd.ExecuteReader();

GridView1.DataBind();

}

}

Also, you can create an instance of SqlCommand class with the help of the parameterless constructor, and then specify the text command and connection string by using the CommandText and Connection properties of the SqlCommand object as follows.

protected void Page_Load(object sender, EventArgs e)

{

string ConnectionString =

ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI"))

{

//Here creating an instance of SqlCommand class with the help of parameter less constructor

SqlCommand cmd = new SqlCommand();

//Specifying the command that we want to run by using the CommandText property

cmd.CommandText = "Select Id,ProductName,QuantityAvailable from tblProductInventory";

//Specifying the connection where we want to execute the command by using the Connection property in windows application

cmd.Connection = connection;

connection.Open();

// As you know, that the T-SQL query command that you want to execute which returns multiple rows of data and use ExecuteReader() method function of the command object.

GridView1.DataSource = cmd.ExecuteReader();

GridView1.DataBind();

}

}

In another example, we are using ExecuteScalar() method function, as the T-SQL statement returns a single value.

protected void Page_Load(object sender, EventArgs e)
{

string ConnectionString =

ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;

using (SqlConnection connection = new SqlConnection("data source=.; database=Sample; integrated security=SSPI"))

{

// Here creating an instance of SqlCommand class and specifying the T-SQL command that needs to be executed and the connection object

SqlCommand cmd = new SqlCommand("Select Count(Id) from tblProductInventory", connection);

connection.Open();

//As the T-SQL statement return a single value in return and use ExecuteScalar() method function of the command object. And,  we are  having type casting to int datatype

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

Response.Write("Total Rows = " + TotalRows.ToString());

}

}

This example needs to performs an Insert, Update and Delete commands on a SQL server database by using the ExecuteNonQuery() method function of the SqlCommand object.

protected void Page_Load(object sender, EventArgs e)

{

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

using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI"))

{

//Creating an instance of SqlCommand class, which specify the T-SQL command that you want to execute and the connection object

SqlCommand cmd = new SqlCommand("insert into tblProductInventory values (103, 'Apple Laptops', 100)", connection);

connection.Open();

//Hence, we are doing an insert command, and making use of ExecuteNonQuery() method function of the command object. It returns only an integer value that specifies the number of rows inserted.

int rowsAffected = cmd.ExecuteNonQuery();

Response.Write("Inserted Rows = " + rowsAffected.ToString() + "<br/>");

//Now, Set to CommandText in the update statement. Here, again we need to use command object rather creating a new command object.

cmd.CommandText = "update tblProductInventory set QuantityAvailable = 101 where Id = 101";

//use ExecuteNonQuery() method function to run the update command on the database server

rowsAffected = cmd.ExecuteNonQuery();

Response.Write("Updated Rows = " + rowsAffected.ToString() + "<br/>");

//Now, Set to CommandText to the delete statement. Again we are making use of command object, rather than creating a new command object.

cmd.CommandText = "Delete from tblProductInventory where Id = 102";

//use ExecuteNonQuery() method to delete the row from the database

rowsAffected = cmd.ExecuteNonQuery();

Response.Write("Deleted Rows = " + rowsAffected.ToString() + "<br/>");

}

}

SqlDataReader object in ADO.NET

SqlDataReader only reads data in an efficient way. How it reads the data in the SqlDataReader means, once you read a single unit of data and jump to the just another data record. Also you must remember, once  you jump to another record there is no way out to go back to the old or previous data record and  also you cannot change the record by using SqlDataReader because it is a connection-oriented function, which means it requires a connection to the data source in an active mode, while reading data.

You cannot create an instance in SqlDataReader if you are using the new operator. If you use this instance, then you will face a compilation error statement like- The type ‘System.Data.SqlClient.SqlDataReader’ has no constructors defined.

SqlDataReader rd = new SqlDataReader();

The SqlCommand object’s ExecuteReader() method create an instance and returns an instance of SqlDataReader.

SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection);

SqlDataReader reader = command.ExecuteReader();

It means that the SqlDataReader is just closed in a timely manner and that we do not need to run out of available connections to the database server.

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

using (SqlConnection connection = new SqlConnection(ConnectionString))

{

connection.Open();

SqlCommand command = new SqlCommand("Select * from tblProductInventory", connection);

using (SqlDataReader reader = command.ExecuteReader())

{

ProductsGridView.DataSource = reader;

ProductsGridView.DataBind();

}

}

For this reason, if you want to loop through in the SqlDataReader, then you need to use the Read() method function, which gives true as long as there are rows to read in return. If no more rows contain to read, then this function will return false. In the given example, we will loop through each row in the SqlDataReader.

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

using (SqlConnection connection = new SqlConnection(ConnectionString))

{

connection.Open();

SqlCommand command = new SqlCommand("Select * from tblProductInventory",connection);

using (SqlDataReader reader = command.ExecuteReader())

{

DataTable sourceTable = new DataTable();

sourceTable.Columns.Add("ID");

sourceTable.Columns.Add("Name");

sourceTable.Columns.Add("Price");

sourceTable.Columns.Add("DiscountedPrice");

while (reader.Read())

{

int OriginalPrice = Convert.ToInt32(reader["UnitPrice"]);

double DiscountedPrice = OriginalPrice * 0.9;

DataRow datarow = sourceTable.NewRow();

datarow["ID"] = reader["ProductId"];

datarow["Name"] = reader["ProductName"];

datarow["Price"] = OriginalPrice;

datarow["DiscountedPrice"] = DiscountedPrice;

sourceTable.Rows.Add(datarow);

}

ProductsGridView.DataSource = sourceTable;

ProductsGridView.DataBind();

}

}

SQL script to create the table as follows.

Create table tblProductInventory

(

ProductId int identity primary key,

ProductName nvarchar(50),

UnitPrice int

)

Insert records into the table tblProductInventory

Insert into tblProductInventory values('iPhone',350)

Insert into tblProductInventory values('Apple Laptops',1250)

Insert into tblProductInventory values('Books',110)

Insert into tblProductInventory values('Acer Laptops',1150)

Insert into tblProductInventory values('iPads',450)

Note: Please use these namespaces while declaring in the code behind.

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

Finally, you get the output as follows

Conclusion

Now you have the complete overview of ADO.NET in .NET framework. You can use in your visual studio C# web applications. Also, the .NET framework provides a great facility of DataSet object where you can work directly with the data. This object is more flexible to present the data in any way you required for. In this article, you may find how ADO is somewhere different from ADO.NET because it really helps you for making your programming in an effective manner.

The .NET Framework also provides 2 data providers that used to define accessing data sources which as OLE-DB Data Provider and the SQL  Database Server. Data Provider.Data provider only works with DataReader, ConnectionCommand, and DataAdapter methods involved in the data provider. By using these data provider you can access your data easily.

Thank you!!

1 thought on “How to work with ADO.NET and SQL connection in .NET”

  1. Thank you for you very well explained information. Coming from the ADO timeframe this makes it way more clear.
    I have one question, when you work on 2 databases, or even, 2 SQL servers, what is the best practise? I want to add data from SQLserver 1 to SQLserver 2 but check only on new records on SQLserver 1. Open 2 connections and get the data in a dataset and compare them? Isn’t that going to take forever? Or address the two tables in the different SQLservers in the SQL? e.g. INSERT into SERVER1.SCHEMA1.NAMES A select * from SERVER2.SCHEMA2.NAMES B where B.ID not in (select C.ID from SERVER1.SCHEMA1.NAMES C)

    Reply

Leave a Comment