Today we are going to start discussing on topic which is very much important in all aspects. And also we will see how it is becoming useful to us in our daily programming.

ADO.NET is a programming technology which is same as ASP.NET, but it is used for the database part, that is for client & server part programming is done in ASP.NET and whereas for the database part like RDBMS, SQLServer, Oracle, etc. programming is done in ADO.NET.

So now the question arises that what is ADO.NET and why it is used? The answer is very simple, it is a programming library for database and it is used for transferring, updating, reading, deleting the data from the database. Here the data can be fetched from the database to the server/different data sources or data from server/different data sources is transferred to database. For e.g. data transferred from C# code to the RDBMS or from RDBMS to C#.

The full form of ADO is Active Data Objects.

So now moving forward, next we will discuss that how exactly the ADO.NET works and what is the architecture it follows.

Architecture of ADO.NET:

Now every programming technology works on some definite architecture. And that working process is the main essence of that technology. So here also ADO.NET follows a definite architecture. Now this architecture contains some important components on which the working process of ADO.NET is totally depended. The image of the architecture and its flow diagram is shown below:

ado

Now looking at this diagram we can say that the ADO.NET architecture contains 6 main components which are:-

  • Connection
  • Command
  • DataReader
  • DataAdapter
  • DataSet
  • DataView

So we will see each of the components and their functions as follows.

Connection:

Now as we know that to do any type of transfer between two objects we first have to create a connection between the two of them. Similarly here also we need to first establish a connection between the data source and the database. And this component helps us to create that connection, after which we can perform any operations we want on the database.

Command:

So now after the connection is established, we have to create a command object where we can write our RDBMS operation program or an SQL operation programs. Due to this, command object helps to execute our SQL or RDBMS or whatever database we have. Commands such as Create, Insert, Update, and Delete are used for execution of the SQL or RDBMS.

DataReader:

Now as the name it saysDataReader, its function is to Read only the data. Now actually DataReader is a connected, read only forward record set. Here we can only read the data and that too in forward direction. Also only read operation is done, no update or insertion or deletion of data can happen. It is usually used when there are large numbers of records which have to be read and displayed quickly.

Now in the above diagram we see that after the command object is created there are two paths of operation are there. One is Database—Connection—Command—DataReader. Second is Database—Connection—Command—DataAdapter—DataSet—DataView.

DataAdapter:

This DataAdapter object is created because it serves as a bridge connection between the Database and the DataSet. Also after the command object is created it helps to load the DataSet object.

DataSet:

It can be defined as a disconnected recordset which can be used for reading the data forward or backward. It can also be updated, inserted and deleted. As the name says DataSet, which means apart from reading the data we can also set it. It is like in- memory database with rows, tables, fields.

DataView:

Its main function is to sort, search and filter the data of the Dataset.

Now to clearly understand this architecture we will see a sample code.

Here first we will look at the sample code of the path of DataSet.

using System;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data;
using System.Data.SqlClient;
namespace ConsoleApplication105
{
    
    class Program
    {
        static void Main(string[] args)
        {
            
           //Connection ( Where)

            SqlConnection conn = new SqlConnection(@"Data Source=localhost\SQL2008;Initial Catalog=LearnSQL;Integrated Security=True");
            conn.Open();
            //Command ( SQL )

            SqlCommand command = new SqlCommand("select * from tblCustomer",conn);
            SqlDataAdapter adapt = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            adapt.Fill(ds);
           
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                Console.WriteLine(r["CustomerName"]);
                r["CustomerName"] = "ddd";
            }
            ds.Tables[0].AcceptChanges();
            ds.AcceptChanges();
            
            conn.Close();
            // close
            

        }
    }
}

So there are mainly 3 steps to operate on the database, first a connection is created then some command is given for the execution and then the last step is of DataSet.

Here we see that the System.Data.SqlClient reference is imported. Then we can see under the connection heading the code is written and a connection is established between data source and SQLClient, here the data source is our local computer. Then under the command heading the command code is written to execute the SQL client. Here we can see that as we are following the DataSet path, we have set an DataAdapter to bridge the connection between the database and the DataSet.

Then the last step comes of dataset where the code is written for reading the data from the database and also for making some changes in the data.

Now we see the sample code for the DataReader which is the first path.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication105
{
    
    class Program
    {
        static void Main(string[] args)
        {
            
           //Connection ( Where)

            SqlConnectionobj = new SqlConnection(@"Data Source=localhost\SQL2008;Initial Catalog=LearnSQL;Integrated Security=True");
            obj.Open();

            //Command ( SQL )

            SqlCommandobjcommand = new SqlCommand("select * from tblCustomer",obj);

// DataReader
SqlDataReaderobjReader = objcommand.ExecuteReader();

While (objReader.Read())
{
//Display Customer Code and Customer Name
Console.WriteLine(objReader[”CustomerCode”] +“------“ + [“CustomerName”]);
}
objClose();
ConsoleReadLine();
}

Here in the DataReader path we see that the first two steps are almost the same only here in second step we do not use the DataAdapter. But here in the last step of DataReader we write the code for the execution and a while condition which will display the customer code and name.

The most important thing here in both the sample codes is that after writing the connection code we write objOpen() and at the end of the last step we write objClose(). It means that we are opening a connection and as soon as we get the data, we immediately close the connection. But why do we do that?

We do the opening and the closing of the connection because after we get the data we cannot leave the connection open as it will affect the performance of the Database. Now we know that at a time many users will be using the database and if every one of them just did not close their connection it will put a lot of load on the database server. So as a good habit we should always first write objOpen() and after that immediately write objClose() in the end before writing the main code. This will help because even if we forget to write objClose() there is no need to worry as it would have been already there down in the end of code.

The main difference between DataRead and Dataset is that Data read is a connected read only forward record set where we can only read the data and that too in forward direction. And DataSet is a disconnected data set where we can read the data in forward/backward and also it can be updated.

The basic important commands in the SQL database are:

Insert:

insert into tblCustomer(CustomerName) values(‘xyz’)

Update:

updatetblCustomer set CustomerName=’abc’ where Id=5 or Id=1

Delete:

delete from tblCustomer where Id=7

Advertisements