A
Beginner's Tutorial for Understanding 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 visualize ADO.NET data access using the following
diagram:
The diagram above shows that ADO.NET can be used with any kind
of application, i.e., it can be used from a Windows Forms application, an
ASP.NET application, or from a WPF and/or Silverlight application. Also, the
data store underneath can be any data store, SQL Server, Access, or Oracle. It
is just a matter of using the right set of classes specific to that data store
and the methodology will remain the same.
Using the
code
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:
1. Connecting
to the database
2. Passing
the request to the database, i.e., a command like select, insert, or update.
3. Getting
back the results, i.e., rows and/or the number of rows effected.
4. Storing
the result and displaying it to the user.
This can be visualized as:
So now we need to understand how we can achieve these
functionalities using ADO.NET.
The Connection
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 ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF;
Integrated Security=True;User Instance=True" />
</connectionStrings>
Let us see how we can use the
SqlConnection class to establish a connection with a
database.private SqlConnection con = null;
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.
Storing the Result
Before we can jump to understanding how we can execute 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. ADataSet 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 DataColumns. ADataSet or a DataTable can be used with a Command and a DataAdapter object
to store query results.
·
DataAdapter - A DataAdapter object
is used to fill a DataSet/DataTable with query results. This can be thought
of as the adapter between the connected and disconnected data models. A Command
object will be used to execute the query and a DataAdapter will
use this Command object and fill the query results coming from the database
into a DataSet/DataTable.
Note:
1. There
are more objects that can/are used to store results but we will mainly be using
these in this article.
2. The
usage and implentation of these objects are in the next section, as
understanding the Command object is required before that.
The Command
Once we have the connection ready, the next step would be to
tell the database about what operation we need to perform on the database. This
can be done using the
Command object. We will be using SqlCommandto
tell the database about the operation we need to perform. The typical commands
on a database will be:
1. Select
Command - This will return a set of rows to the application.
2. Insert
Command - This will return the number of rows inserted.
3. Delete
Command - This will return the number of rows deleted.
4. Update
Command - This will return the number of rows updated.
Note: We are only talking about data
manipulation commands in this article.
All these commands expect SQL syntax. This SQL can either be
passed from the application or can be written in the form of Stored Procedures
and executed using a
SqlCommand.
Using a
Stored Procedure with a Command
If we want to use a Stored Procedure with a Command object then
we need to specify it as:
cmd = con.CreateCommand();
// This will specify that we are passing the stored procedures name
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandName; // This will be the stored procedures name
If the Stored Procedure is expecting some parameters then we can
pass these parameters by creating instances of
SqlParameter objects as:SqlCommand cmd = con.CreateCommand();
// This will specify that we are passing the stored procedures name
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandName; // This will be the stored procedures name
SqlParameter param = new SqlParameter("@id", txtSearch.Text);
cmd.Parameters.Add(param);
Passing a
SQL query from an application using a Command
If we want to pass a SQL query from our application then we can
use the
SqlCommand as:SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text; //This will specify that we are passing query from application
string query = "select * from Authors";
cmd.CommandText = query;
There is one important thing to understand here and that is
SqlParameters.
Many a times we will need to pass parameters in our SQL query. This can be done
in two ways: we can create a query using string concatenation like:SqlCommand cmd = con.CreateCommand();
//This will specify that we are passing query from application
cmd.CommandType = CommandType.Text;
string query = "select * from Authors where authorId = '" + txtSearch.Text + "'";
cmd.CommandText = query;
This is not recommended as this approach is error prone and is
vulnerable to SQL Injection attacks. So whenever we need to pass parameters to
a query the preferred way is using
SqlParameters.
The same query can be written as:SqlCommand cmd = con.CreateCommand();
//This will specify that we are passing query from application
cmd.CommandType = CommandType.Text;
string query = "select * from Authors where authorId = @id";
cmd.CommandText = query;
SqlParameter param = new SqlParameter("@id", txtSearch.Text);
cmd.Parameters.Add(param);
Using
SqlParameters
gives a cleaner, less error prone and SQL injection safe (comparative) code.
Executing the Select Command
Now let us see how we can retrieve the result of a Select
command in the form of a
DataTable.public DataTable ExecuteSelectCommand(string CommandName, CommandType cmdType)
{
SqlCommand cmd = null;
DataTable table = new DataTable();
cmd = con.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
try
{
con.Open();
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
con.Close();
}
return table;
}
public DataTable ExecuteParamerizedSelectCommand(string CommandName,
CommandType cmdType, SqlParameter[] param)
{
SqlCommand cmd = null;
DataTable table = new DataTable();
cmd = con.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(param);
try
{
con.Open();
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
con.Close();
}
return table;
}
Executing Update, Delete, and Insert Commands
Commands like insert, update, delete are executed by calling the
ExecuteNonQuery method ofSqlCommand.
Let us see how we can write a simple function that will execute these commands.
These commands can be used by passing a query from the application or by
invoking Stored Procedures (same as we saw above).public bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)
{
SqlCommand cmd = null;
int res = 0;
cmd = con.CreateCommand();
cmd.CommandType = cmdType;
cmd.CommandText = CommandName;
cmd.Parameters.AddRange(pars);
try
{
con.Open();
res = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
con.Close();
}
if (res >= 1)
{
return true;
}
return false;
}
Using the sample application
The first thing to notice in the application is that it contains
a class that is responsible for all the ADO.NET logic. The class
DataAccess (file: DataAccess.cs)
contains all the ADO.NET classes and methods. All the pages use this class.
This class can be reused in any application with some minor application
specific changes. The class diagram for the class is:
The sample application contains four pages:
·
Authors.aspx
·
Titles.aspx
·
AddAuthors.aspx
·
AddTitles.aspx
The author pages, i.e., Authors.aspx and AddAuthors.aspx,
use Stored Procedures to perform the operations whereas the title pages pass
all the queries from the application to the database. We have mainly
implemented Select and Insert commands but Update and Delete can be implemented
on the same lines as Insert.
Some things worth mentioning about the application are:
·
This should in no way be treated as a design reference for the
data access layer. This is only to demonstrate ADO.NET logic.
·
The code is written in such a way as to provide a clear
understanding from a beginner's perspective, i.e., experienced programmers will
find a lot of possible optimizations in the code.
·
No client side or server side validations have been provided as
that was not the scope of this article.
·
There is no design (architecture wise and look wise) in this
application.
No comments:
Post a Comment