Introduction to ADO.NET in C#

ADO.NET is a set of .NET framework provided libraries that allow you to interact with relational database systems. ADO.NET data providers, which are optimized for a particular database (like Microsoft SQL Server, Oracle, etc.) are built into the .NET framework. Some database vendors (like Oracle) provide their own custom providers.

The core types and interfaces for ADO.NET are defined in System.Data.dll assembly.

There are three main widely used approaches to database interaction in .NET based application.

  • Connected approach using data reader objects.
  • Disconnected approach using data sets, data tables and data adapters.
  • Entity framework API using strongly typed entities.

In this article we will discuss the above mentioned approaches briefly.


In connected mode, data layer connects and disconnects to the underlying data source explicitly, i.e. connection is open (even when there are no SQL operations performed) until you close the connection. Data providers provide connection, command and data reader objects to accomplish this. Connected mode is useful when you need to read large data from the database or execute queries on the database that does not return any output.

The steps involved in interacting with data source are.

  • Create the connection object and configure the connection settings on the connection object. Important settings on connection object are Data source (usually machine name with database instance name), username and password to connection to data source and Initial catalog.
  • Create a command object and associate connection object with it, pass the SQL query to the command and the run the command.
  • Process the results (if any) either using the readers or by reading the output parameters.


In disconnected mode, data layer never directly connects or disconnects to the underlying data source, adaptors do it automatically for you. An in memory snap shot of the data tables is retrieved and changes are made to it, changes are then submitted to the database. Data providers provide connection, command and data adapter objects to work in disconnected mode.

The steps involved in interacting with the data source are.

  • Create and configure connection object.
  • Create command object, associate SQL query and parameters to it.
  • Create adapter object and add command to it and also associate connection object with adapter.
  • Fill the dataset with results of the command run on the adapter.
  • Work on the in memory dataset.
  • Submit changes made in the dataset to the database.

Entity Framework

In both the approaches explained above, data in application is tightly mapped to relational schema, they deal with collection of rows and columns. Entity Framework (EF) provides wrappers to map database tables to strongly typed entities in the application. You make changes to entity objects to update rows in table, add objects to the collection to add new rows to the table, remove objects from the collection to delete rows from the table. EF related types are present in System.Data.Entity.dll assembly.

Steps involved in interacting with the data source

  • Create entity types and database context using either Database First, Model First or Code First approach.
  • Add an entity object to the context to add new record to the table.
  • Find the entity corresponding to a row in the database using the context object and make changes to it to update the database.
  • Submit changes using context object to save/add objects to the database.