One-Stop Solution - ADO.NET: An Ultimate Guide With Examples

What Is ADO.NET?

Database access technology developed by Microsoft as part of the .NET framework known as ActiveX Data Object or ADO.NET may access any data source it can find. Class objects that provide a comprehensive collection of data components for building high-performance client-server programs and networked contexts via the Internet and intranets are part of this framework.

Data from a database may be accessed using ADO.NET's SQL queries or stored procedures. When you make SQL statements, the ADO.NET Command object outputs data based on either a DataReader or a DataSet object. After the connection has been closed, you can work with the data using DataSet objects and re-connect to the data source when it needs to be updated.

We can now go on to the next chapter to learn about ADO.NET Architecture.

What Is the ADO.NET Architecture?

ADO.NET offers a link between the front-end interfaces and the back-end databases. All data access activities are encapsulated as ADO.NET objects, which are then interacted with by controls to show data while concealing the specifics of data transfer.

The ADO.NET Architecture is depicted graphically in the following figure:

It Consists of Five Major Object Components- Let's Discuss them One by one.

Connection Object

ADO.NET's Connection object should be our initial port of call. It is through connections that ADO.NET is connected to data sources. Between a DataAdapter and a DataSource, there is a Connection object. When a Command object is attached, it may perform SQL commands that can be used to get data from a data source, update, or remove data.

An important part of a transaction is the creation of the connection. The good functionality for working with transactions, like commit and rollback, is kept in transaction objects.

Command Object

Executing SQL statements and data structures is possible with the Command object. A DataSet or a DataReader object returns data from SQL statements. SELECT, INSERT, UPDATE, and DELETE Database queries are used to obtain, add, and remove data. These statements may be found in a DataAdapter created using VS .NET IDE.

DataAdapter Object

The DataAdapter's function is implicit in its title: it conducts the operations required to transfer data from the server's data source to the database maintained by the DataSet. DataAdapter: We may define instructions for retrieving and updating data using the DataAdapter. To connect a data source to the Dataset, use the DataAdapter. The DataAdapter is aware of the DataSet and the appropriate methods for populating it. It is also known to the Adapter that a link to the data source has been established.

DataReader Object

One of the two techniques offered by ADO.NET, the DataReader object, is used to read information from the data store. As we will recall, the DataReader object offers high efficiency, a read-only, forward-only mechanism for retrieving data as a stream of data from a data repository while maintaining a connection to the data source. The DataReader is constrained yet extremely efficient.

DataSet Object

The ADO.NET DataReader object is one of the two techniques provided by ADO.NET. This object is utilized to obtain data from the data store. As we will recall, the DataReader object offers a read-only, forward-only, high-performance mechanism for retrieving information from the data store as a data stream while maintaining a connection with the data source. This framework is read-only because it can only read data written after it has been read. The DataReader has several limitations, but it's very well tuned.

Now that we understand ADO.NET and its Architecture let's try to implement the Entity Data Model using ADO.NET.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

How Does ADO.NET Work?

ADO.NET works either in a connected or disconnected way. Connected mode is more popular as it has faster performance than disconnected mode because it works on the forward read-only method. 

The performance degradation in disconnected mode occurs due to multiple operations like insert, update, delete, and select data. 

Also, connected mode holds records from one table, whereas disconnected mode holds records from multiple tables. The working path will be easier to get when you know Data Provider and Connections. 

ADO.NET Data Provider

Data Provider is used to connect with the database and then retrieve data as per command execution. It's like a lightweight component with multiple roles. 

Some of the popular data providers and frameworks are listed below:

ADO.NET Data Providers:

• OleDb (System.Data.OleDb)
• SqlClient (System.Data.SqlClient)
• Odbc (System.Data.Odbc)
• OracleClient (System.Data.OracleClient)

ADO.NET Entity Framework:

• LINQ to Entities
• EntityClient (System.Data.EntityClient)
• Typed ObjectQuery

Implementation of ADO.NET Entity Data Model

Let's start by making an ASP.NET Web Application (.NET framework) Project. 

Let's open Visual Studio 2019, click on New Project, and then choose Asp.Net Web Application.

Then we'll call the project "ADO.NETDemo," choose one destination, and pick ".NET Framework 4.7.2" as the.NET framework. Then we'll press the "create" toggle.

Next, we'll be able to choose a template or leave it empty and click "Create."

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Now, we'll start by making a database. So let's open server explorer and click "connect to database."

Now, Microsoft SQL Server will request us to choose the Data Source.

Now, we'll type "(LocalDB)MSSQLLocalDB" for the server name and "SimpliADO.NET" for the database name, then click "OK."

Then, a pop-up message will tell us to make a new database with the name we suggested above. So we're going to click yes.

Then we'll look around this new database, select the table, and choose the new table.

You may write the following queries into the query sheet, or we will generate the table mentioned above by filling in the cells.

Query:

CREATE TABLE Student

(

    [Id] INT NOT NULL PRIMARY KEY, 

    [Name] NVARCHAR(50) NULL, 

    [Fees] INT NULL, 

    [City] NVARCHAR(50) NULL

)

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Then, we'll enter the managed Nuget package window, add and install Nuget Packages to our project, explore the tab, and look for EntityFramework. Installing version 5.0.0 will be done.

The database will then be revised once we click the update DataBase button.

The ADO.Net Entity Data Model would now be added to our project. Click on our project's name and select Add > New Item to add an ADO.Net Entity Data Model.

Choose ADO.Net Entity Data Model, give it the name “ADO.NETModel,” and click Add.

Then we will select "EF Designer from DataBase" and hit Next.

After selecting the database name, we must ensure that the "Save Connection Setting in web.config" box is checked. The connection will subsequently be given the name "SimpliADONETDBContext."

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

Then we will select Entity framework 5.0 And hit next. 

Now we will check the table option and hit Finish.

Now that we have successfully implemented the ADO.NET Data Entity Model let's examine a few benefits of ADO.NET.

ADO.NET Code Examples

• Retrieving Data Using a DataReader

Open connection to SQL Server database

SQLServerConnection Conn;

try

{

Conn = new SQLServerConnection("host=nc-star;port=1433;

User ID=XXXXX;Password=XXXX; Database Name=Retrieve");

Conn.Open();

Console.WriteLine ("Connection successful!");

}

catch (Exception ex)

{

// Connection failed

Console.WriteLine(ex.Message);

return;

}

try

{

// Create a SQL command

string strSQL = "CONDITION";// Condition you need

SQLServerCommand DBCmd = new SQLServerCommand(strSQL, Conn);

SQLServerDataReader myDataReader;

myDataReader = DBCmd.ExecuteReader();

while (myDataReader.Read())

{

Console.WriteLine("");// To print your output

}

myDataReader.Close();

// Close the connection

Conn.Close();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

return;

}

• Updating Data in a DataSet

[C#]

string updateSQL As String = "UPDATE emp SET sal = ?, job = ? +

= WHERE empno = ?;

Here table name= emp.

Benefits of ADO.NET

There are many benefits of ADO.NET.

• ADO.NET has one object-oriented class. All data providers use the same programming model to operate with diverse data sources.
• The managed classes used by ADO.NET are classes. They utilize all of the .NET CLR's benefits, including automated resource management and language independence.
• XML is used to transport ADO.NET data. This data may be shared by all components and apps and sent over HTTP.
• Visual Studio .NET provides ADO.NET components and data-bound controls for use in visual form.
• Performance and scalability are two important considerations when creating web-based apps and services.
• Data that can only be read and sent from a database is retrieved using the ADO.NET DataReader.

By now, you have a good grip on ADO.NET's technical and theoretical aspects of ADO.NET. 

Master front-end and back-end technologies and advanced aspects in our Post Graduate Program in Full Stack Web Development. Unleash your career as an expert full stack developer. Get in touch with us NOW!

Next Steps

The next lesson in your C# training can be "Collections in C#." Collection classes are specialized data storage and retrieval classes. These classes support stacks, queues, lists, and hash tables. Collection classes perform various functions, such as dynamic allocating memory to components and retrieving a list of objects by an index. These classes construct collections of Object objects, which serve as the foundation for all data types in C#.

Simplilearn is the world's most popular online Bootcamp for learning digital economy skills, and it's here to help you do that. Digital marketing and data science are just a few subjects we cover in-depth in our online courses.

You've come to the right place if you're interested in learning more about software development and working in the field. The Caltech CTME and the Indian Institute of Technology, Kanpur, have collaborated with Simplilearn to deliver their Software Development courses. In addition to more advanced topics like Competitive Programming, these courses teach the fundamentals of data structures and algorithms. As a software developer, data structures, including trees, graphs, and queues, will be taught to you.

The comments section below is open for questions and comments about this "ADO.NET" tutorial. Happy learning!