Introduction to ADO.NET

This lesson is an introduction to ADO.NET.  It introduces primary ADO.NET concepts and objects that you will learn about in later lessons.  Here are the objectives of this lesson:

• Learn what ADO.NET is.
• Understand what a data provider is.
• Understand what a connection object is.
• Understand what a command object is.
• Understand what a DataReader object is.
• Understand what a DataSet object is.
• Understand what a DataAdapter object is.

Introduction

ADO.NET is an object-oriented set of libraries that allows you to interact with data sources.  Commonly, the data source is a data base, but it could also be a text file, an Excel spread sheet, or an XML file.  For the purposes of this tutorial, we will look at ADO.NET as a way to interact with a data base.

As you are probably aware, there are many different types of data bases available.  For example, there is Microsoft SQL Server, Microsoft Access, Oracle, Borland Interbase, and IBM DB2, just to name a few.  To further refine the scope of this tutorial, all of the examples will use SQL Server. 

Data Providers

We know that ADO.NET allows us to interact with different types of data sources and different types of data bases.  However, there isn't a single set of classes that allow you to accomplish this universally.  Since different data sources expose different protocols, we need a way to communicate with the right data source using the right protocol.  Some older data sources use the ODBC protocol, many newer data sources use the OleDb protocol, and there are more data sources every day that allow you to communicate with them directly through .NET ADO.NET class libraries. 

ADO.NET provides a relatively common way to interact with data sources, but comes in different sets of libraries for each way you can talk to a data source.  These libraries are called Data Providers and are usually named for the protocol or data source type they allow you to interact with.  table 1 lists some well known data providers, the API prefix they use, and the type of data source they allow you to interact with.

table 1.  ADO.NET Data Providers are class libraries that allow a common way to interact with specific data sources or protocols.  The library APIs have prefixes that indicate which provider they support.

Provider Name	API prefix	Data Source Description
ODBC Data Provider	Odbc	Data Sources with an ODBC interface.  Normally older data bases.
OleDb Data Provider	OleDb	Data Sources that expose an OleDb interface, i.e. Access or Excel.
Oracle Data Provider	Oracle	For Oracle Data Bases.
SQL Data Provider	Sql	For interacting with Microsoft SQL Server.
Borland Data Provider	Bdp	Generic access to many data bases such as Interbase, SQL Server, IBM DB2, and Oracle.

An example may help you to understand the meaning of the API prefix.  One of the first ADO.NET objects you'll learn about is the connection object, which allows you to establish a connection to a data source.  If we were using the OleDb Data Provider to connect to a data source that exposes an OleDb interface, we would use a connection object named OleDbConnection.  Similarly, the connection object name would be prefixed with Odbc or Sql for an OdbcConnection object on an Odbc data source or a SqlConnection object on a SQL Server data base, respectively.  Since we are using MSDE in this tutorial (a scaled down version of SQL Server) all the API objects will have the Sql prefix.  i.e. SqlConnection.

ADO.NET Objects

ADO.NET includes many objects you can use to work with data.  This section introduces some of the primary objects you will use.  Over the course of this tutorial, you'll be exposed to many more ADO.NET objects from the perspective of how they are used in a particular lesson.  The objects below are the ones you must know.  Learning about them will give you an idea of the types of things you can do with data when using ADO.NET.

The SqlConnection Object

To interact with a data base, you must have a connection to it.  The connection helps identify the data base server, the data base name, user name, password, and other parameters that are required for connecting to the data base.  A connection object is used by command objects so they will know which data base to execute the command on.

The SqlCommand Object

The process of interacting with a data base means that you must specify the actions you want to occur.  This is done with a command object.  You use a command object to send SQL statements to the data base.  A command object uses a connection object to figure out which data base to communicate with.  You can use a command object alone, to execute a command directly, or assign a reference to a command object to an SqlDataAdapter, which holds a set of commands that work on a group of data as described below.

The SqlDataReader Object

Many data operations require that you only get a stream of data for reading.  The data reader object allows you to obtain the results of a SELECT statement from a command object.  For performance reasons, the data returned from a data reader is a fast forward-only stream of data.  This means that you can only pull the data from the stream in a sequential manner.  This is good for speed, but if you need to manipulate data, then a DataSet is a better object to work with.

The DataSet Object

DataSet objects are in-memory representations of data.  They contain multiple Datatable objects, which contain columns and rows, just like normal data base tables.  You can even define relations between tables to create parent-child relationships.  The DataSet is specifically designed to help manage data in memory and to support disconnected operations on data, when such a scenario make sense.  The DataSet is an object that is used by all of the Data Providers, which is why it does not have a Data Provider specific prefix.

The SqlDataAdapter Object

Sometimes the data you work with is primarily read-only and you rarely need to make changes to the underlying data source.  Some situations also call for caching data in memory to minimize the number of data base calls for data that does not change.  The data adapter makes it easy for you to accomplish these things by helping to manage data in a disconnected mode.  The data adapter fills a DataSet object when reading the data and writes in a single batch when persisting changes back to the data base.  A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the data base.  Additionally, the data adapter contains command object references for SELECT, INSERT, UPDATE, and DELETE operations on the data.  You will have a data adapter defined for each table in a DataSet and it will take care of all communication with the data base for you.  All you need to do is tell the data adapter when to load from or write to the data base.

Summary

ADO.NET is the .NET technology for interacting with data sources.  You have several Data Providers, which allow communication with different data sources, depending on the protocols they use or what the data base is.  Regardless, of which Data Provider used, you'll use a similar set of objects to interact with a data source.  The SqlConnection object lets you manage a connection to a data source.  SqlCommand objects allow you to talk to a data source and send commands to it.  To have fast forward-only read access to data, use the SqlDataReader.  If you want to work with disconnected data, use a DataSet and implement reading and writing to/from the data source with a SqlDataAdapter.

Read More

Adding Parameters to Commands

This lesson shows you how to use parameters in your commands.  Here are the objectives of this lesson:

• Understand what a parameter is.
• Be informed about the benefits of using parameters. 
• Learn how to create a parameter.
• Learn how to assign parameters to commands.

Introduction

When working with data, you'll often want to filter results based on some criteria.  Typically, this is done by accepting input from a user and using that input to form a SQL query.  For example, a sales person may need to see all orders between specific dates.  Another query might be to filter customers by city.

As you know, the SQL query assigned to a SqlCommand object is simply a string.  So, if you want to filter a query, you could build the string dynamically, but you wouldn't want to.  Here is a bad example of filtering a query.

            // don't ever do this!

            SqlCommand cmd = new SqlCommand(

                        "select * from Customers where city = '" + inputCity + "'";

Don't ever build a query this way!  The input variable, inputCity, is typically retrieved from a TextBox control on either a Windows form or a Web Page.  Anything placed into that TextBox control will be put into inputCity and added to your SQL string.  This situation invites a hacker to replace that string with something malicious.  In the worst case, you could give full control of your computer away.

Instead of dynamically building a string, as shown in the bad example above, use parameters.  Anything placed into a parameter will be treated as field data, not part of the SQL statement, which makes your application much more secure.

Using parameterized queries is a three step process:

1. Construct the SqlCommand command string with parameters.
2. Declare a SqlParameter object, assigning values as appropriate.
3. Assign the SqlParameter object to the SqlCommand object's Parameters property.

The following sections take you step-by-step through this process.

preparing a SqlCommand Object for Parameters

The first step in using parameters in SQL queries is to build a command string containing parameter placeholders.  These placeholders are filled in with actual parameter values when the SqlCommand executes.  Proper syntax of a parameter is to use an '@' symbol prefix on the parameter name as shown below:

            // 1. declare command object with parameter

            SqlCommand cmd = new SqlCommand(

                        "select * from Customers where city = @City", conn);

In the SqlCommand constructor above, the first argument contains a parameter declaration, @City.  This example used one parameter, but you can have as many parameters as needed to customize the query.  Each parameter will match a SqlParameter object that must be assigned to this SqlCommand object.

Declaring a SqlParameter Object

Each parameter in a SQL statement must be defined.  This is the purpose of the SqlParameter type.  Your code must define a SqlParameter instance for each parameter in a SqlCommand object's SQL command.  The following code defines a parameter for the @City parameter from the previous section:

            // 2. define parameters used in command object

            SqlParameter param  = new SqlParameter();

            param.ParameterName = "@City";

            param.Value         = inputCity;

Notice that the ParameterName property of the SqlParameter instance must be spelled exactly as the parameter that is used in the SqlCommand SQL command string.  You must also specify a value for the command.  When the SqlCommand object executes, the parameter will be replaced with this value.

Associate a SqlParameter Object with a SqlCommand Object

For each parameter defined in the SQL command string argument to a SqlCommand object, you must define a SqlParameter.  You must also let the SqlCommand object know about the SqlParameter by assigning the SqlParameter instance to the Parameters property of the SqlCommand object.  The following code shows how to do this:

            // 3. add new parameter to command object

            cmd.Parameters.Add(param);

The SqlParameter instance is the argument to the Add method of the Parameters property for the SqlCommand object above.  You must add a unique SqlParameter for each parameter defined in the SqlCommand object's SQL command string.

Putting it All Together

You already know how to use SqlCommand and SqlDataReader objects.  The following code demonstrates a working program that uses SqlParameter objects.  So, everything should be familiar by now, except for the new parts presented in this article:

Listing 1: Adding Parameters to Queries

using System;

using System.Data;

using System.Data.SqlClient;

 class ParamDemo

 {

            static void Main()

            {

                        // conn and reader declared outside try

                        // block for visibility in finally block

                        SqlConnection conn   = null;

                        SqlDataReader reader = null;

                         string inputCity = "London"; 

                        try

                        {                                     // instantiate and open connection

                                     conn =  new 

                                                 SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");

                                     conn.Open();

                                      // don't ever do this!

//                                  SqlCommand cmd = new SqlCommand(

//                                              "select * from Customers where city = '" + inputCity + "'";

                                      // 1. declare command object with parameter

                                     SqlCommand cmd = new SqlCommand(

                                                 "select * from Customers where city = @City", conn);

                                      // 2. define parameters used in command object

                                     SqlParameter param  = new SqlParameter();

                                     param.ParameterName = "@City";

                                     param.Value         = inputCity;

                                      // 3. add new parameter to command object

                                     cmd.Parameters.Add(param);

                                      // get data stream

                                     reader = cmd.ExecuteReader();

                                     // write each record

                                     while(reader.Read())

                                     {

                                                 Console.WriteLine("{0}, {1}", 

                                                             reader["CompanyName"], 

                                                             reader["ContactName"]);

                                     }

                        }

                        finally

                        {

                                     // close reader

                                     if (reader != null)

                                     {

                                                 reader.Close();

                                     }

                                      // close connection

                                     if (conn != null)

                                     {

                                                 conn.Close();

                                     }

                        }

            }

}

The code in Listing 1 simply retrieves records for each customer that lives in London.  This was made more secure through the use of parameters.  Besides using parameters, all of the other code contains techniques you've learned in previous lessons.

Summary

You should use parameters to filter queries in a secure manner.  The process of using parameter contains three steps:  define the parameter in the SqlCommand command string, declare the SqlParameter object with applicable properties, and assign the SqlParameter object to the SqlCommand object.  When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.

Read More