ADO stands for Active Data Objects, and the extension .NET signifies a component in .NET Framework. ADO.NET plays a vital role in data fetching and sending.

ADO.NET leverages the power of XML and provides both connected and disconnected access to data. In simple terms, it's a set of classes used to connect with the database and provide access to relational data. XML here is used to retrieve data accessed. 

It acts like an object that helps us fetch data from various sources to our VB.NET or C# code or vice versa.

Post Graduate Program: Full Stack Web Development

in Collaboration with Caltech CTMEEnroll Now
Post Graduate Program: Full Stack Web Development

Advantages of Using ADO.NET

ADO.NET has several advantages over Data Access Models and other components. Some of these advantages are- 

  • Maintainability- Use N-tier application logic across additional tiers to maintain several user requests at a time.
  • Programmability- Strongly typed data and easy to write code because Microsoft Visual Studio .NET framework provides statement completion. 
  • Performance- High performance due to connected mode. 
  • Scalability- Scalability of data is possible with storage conserving and web running applications. 
  • Interoperability- XML is used to transmit data from a data source to a local in-memory data location.

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

New Course: Full Stack Development for Beginners

Learn Git Command, Angular, NodeJS, Maven & MoreEnroll Now
New Course: Full Stack Development for Beginners

ADO.NET Architecture

The ADO.NET Architecture comprises six essential components, and each element is linked with the previous one to maintain a steady data flow. These components are:

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

ADO.NET

From these six components, Connection and Command components are the most important, or the compulsory ones. 

There are few operations where these two components Command and Connection, won't be performing any major roles like during Insert, Update, Delete and Select operations.

  • Connection

The first and most crucial component of ADO.NET Architecture is the connection object. This Connection object is required to connect your machine from the backend database. This database can be SQL Server, or Oracle, or MySQL, or any other. 

The requirements to create a connection object are pretty simple. You’ll need two things. The first thing is your database location. This could be a Machine name, IP Address, or any other location where you have stored your database. 

The next comes the security credentials or the authentication requirements. Two security authentications are available one is the windows authentication and the other one username and password-based authentication.

  • Command

The next most crucial component is the command object. As the name suggests, it commands operations as per the given input. The Command object is the component where SQL queries are given as per requirement. 

SQL queries are the only way to command operations when using databases like Oracle, MySQL, or any other SQL server. 

The execution of this command object is done over the connection, which then can fetch or send data to the database.  

Note: There are two different ways available to move from Command object onwards- 

  • DataSet 
  • DataReader 

Depending upon the situation, like if you want to access (read-only) records in the forward mode only, you should prefer DataReader, or if you wish to access and edit both, you can choose DataSet. 

Full Stack Web Developer Course

To become an expert in MEAN StackView Course
Full Stack Web Developer Course

  • DataReader

DataReader is a read-only connected recordset. This recordset helps us to read the saved records in the forward mode only. DataReader has three important functionalities to remember, i.e., read-only, connected, and forward mode.

  • DataSet

When there's a need to access records in both forward and backward ways, a disconnected recordset or DataSet comes into play. DataSet also allows you to update records as per requirement. The one who fills DataSet is called DataAdapter.

  • DataAdapter

The DataAdapter is more like a bridge between the dataset and command object to maintain the command flow. DataAdapter helps DataSet take the data from the command object and then fill the dataset in both ways (forward and backward).

  • DataView Class

DataView allows you to build different views of data stored in the DataTable. DataView can also be used to show data in any sort of order in a table. Using the filter expression, you can even filter any row as per your requirement.

Side-By-Side Execution in ADO.NET

The ability to execute an application on any computer having multiple .NET Framework versions installed is called Side-by-side execution in ADO.NET. This execution is exclusively done using the application compiled version.

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.

Advance your career as a MEAN stack developer with the Full Stack Web Developer - MEAN Stack Master's Program. Enroll now!

Conclusion 

With a perfect learning map, the concept of ADO.NET is easy to grasp. To help you master ADO.NET, our Post Graduate Program in Full Stack Web Development and Global Online Coding Bootcamp with Caltech CTME. Simplilearn is the first online Bootcamp to receive the best career support, verified university Certificates, live master classes, and 24X7 learning support from recognized mentors at affordable prices. Explore and enrol in the program now and scale up your development career to new heights.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.