ADO .Net
ADO.NET is a set of classes that comes with the Microsoft .NET framework to facilitate data
access from managed languages. ADO.NET has been in existence for a long time and it provides
a comprehensive and complete set of libraries for data access. The strength of ADO.NET is firstly
that it lets applications access various types of data using the same methodology. If I know how
to use ADO.NET to access a SQL Server database then the same methodology can be used to
access any other type of database (like Oracle or MS Access) by just using a different set of
classes. Secondly, ADO.NET provides two models for data access: a connected model where I can
keep the connection with the database and perform data access, and another way is to get all
the data in ADO.NET objects that let us perform data access on disconnected objects.
Let us try to understand a few ADO.NET classes and methodologies by writing a small web
application. This application uses a sample database from Microsoft (subset of the Pubs database)
and we will use this database for understanding the various classes and methods of ADO.NET. We will
be using ADO.NET classes specific to SQL Server but once it is understood, the basic philosophy
remains the same and can be applied with any data store.
Before jumping into the code, we will have to understand some of the important objects of ADO.NET.
In a typical scenario requiring data access, we need to perform four major tasks:
● Connecting to the database through SqlConnection class
● Creating SQLCommand object that holds query to execute like select, insert, or update.
● Open the connection
● Execute the query and collect the result
● Close & dispose the connection
Connected Mode
● Connected mode is connection oriented.
● In Connection mode, we read data from a database by using a DataReader object.
● Connected mode methods provide faster performance.
● Connected mode can hold the data of single table.
● Connected mode is read only, we can't update the data.
, Disconnected mode
● Disconnected mode is disconnected connection oriented.
● In Disconnection mode, we read data from a database by using a DataSet object.
● Disconnection mode get low in speed and performance.
● Disconnection mode can hold multiple tables of data.
● We can perform all option as like update, insert, delete etc.
Why we use Disconnected mode
In Disconnected mode, we are used DataSet for retrieving data from database. so we are not need to
maintaining the connection also. we can be performed all the operations with the data. It "wont
cause traffic problem" while working with database.
The way of database connectivity
The ADO.NET Connection class is used to establish a connection to the database. The Connection
class uses a ConnectionString to identify the database server location, authentication parameters,
and other information to connect to the database. This ConnectionString is typically stored in the
web.config.
<connectionStrings>
<add name="MyConnectionString"
connectionString ="Server=ServerName;Database=DatabaseName; Integrated Security=True;” />
</connectionStrings>
Let us see how we can use the SqlConnection class to establish a connection with a database.
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
Now we have a connection ready with our database. Whenever we want to retrieve data, we just
need to open the connection, perform the operation, and close the connection.
Before executing commands on a database, we first need to understand how we can store the results
and these results can be displayed to the user. To get the hang of how we can store the results, we
need to understand a few ADO.NET objects.
● DataReader - A DataReader is an object that can be used to access the results sequentially
from a database. The DataReader is used to get forward only sequential results as the query
executes. This is used with the Command object (we will see the usage shortly).
● Dataset - The Dataset can be thought of as an in-memory representation of a database. A
DataSet is a disconnected data access object. The result of the query can be stored in a
Dataset. The DataSet contains DataTables. The DataTables contain DataRow and
ADO.NET is a set of classes that comes with the Microsoft .NET framework to facilitate data
access from managed languages. ADO.NET has been in existence for a long time and it provides
a comprehensive and complete set of libraries for data access. The strength of ADO.NET is firstly
that it lets applications access various types of data using the same methodology. If I know how
to use ADO.NET to access a SQL Server database then the same methodology can be used to
access any other type of database (like Oracle or MS Access) by just using a different set of
classes. Secondly, ADO.NET provides two models for data access: a connected model where I can
keep the connection with the database and perform data access, and another way is to get all
the data in ADO.NET objects that let us perform data access on disconnected objects.
Let us try to understand a few ADO.NET classes and methodologies by writing a small web
application. This application uses a sample database from Microsoft (subset of the Pubs database)
and we will use this database for understanding the various classes and methods of ADO.NET. We will
be using ADO.NET classes specific to SQL Server but once it is understood, the basic philosophy
remains the same and can be applied with any data store.
Before jumping into the code, we will have to understand some of the important objects of ADO.NET.
In a typical scenario requiring data access, we need to perform four major tasks:
● Connecting to the database through SqlConnection class
● Creating SQLCommand object that holds query to execute like select, insert, or update.
● Open the connection
● Execute the query and collect the result
● Close & dispose the connection
Connected Mode
● Connected mode is connection oriented.
● In Connection mode, we read data from a database by using a DataReader object.
● Connected mode methods provide faster performance.
● Connected mode can hold the data of single table.
● Connected mode is read only, we can't update the data.
, Disconnected mode
● Disconnected mode is disconnected connection oriented.
● In Disconnection mode, we read data from a database by using a DataSet object.
● Disconnection mode get low in speed and performance.
● Disconnection mode can hold multiple tables of data.
● We can perform all option as like update, insert, delete etc.
Why we use Disconnected mode
In Disconnected mode, we are used DataSet for retrieving data from database. so we are not need to
maintaining the connection also. we can be performed all the operations with the data. It "wont
cause traffic problem" while working with database.
The way of database connectivity
The ADO.NET Connection class is used to establish a connection to the database. The Connection
class uses a ConnectionString to identify the database server location, authentication parameters,
and other information to connect to the database. This ConnectionString is typically stored in the
web.config.
<connectionStrings>
<add name="MyConnectionString"
connectionString ="Server=ServerName;Database=DatabaseName; Integrated Security=True;” />
</connectionStrings>
Let us see how we can use the SqlConnection class to establish a connection with a database.
SqlConnection con = new
SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
Now we have a connection ready with our database. Whenever we want to retrieve data, we just
need to open the connection, perform the operation, and close the connection.
Before executing commands on a database, we first need to understand how we can store the results
and these results can be displayed to the user. To get the hang of how we can store the results, we
need to understand a few ADO.NET objects.
● DataReader - A DataReader is an object that can be used to access the results sequentially
from a database. The DataReader is used to get forward only sequential results as the query
executes. This is used with the Command object (we will see the usage shortly).
● Dataset - The Dataset can be thought of as an in-memory representation of a database. A
DataSet is a disconnected data access object. The result of the query can be stored in a
Dataset. The DataSet contains DataTables. The DataTables contain DataRow and
