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

The SqlConnection Object

 

This lesson describes the SqlConnection object and how to connect to a data base.  Here are the objectives of this lesson:

• Know what connection objects are used for.
• Learn how to instantiate a SqlConnection object.
• Understand how the SqlConnection object is used in applications.
• Comprehend the importance of effective connection lifetime management.

Introduction

The first thing you will need to do when interacting with a data base is to create a connection.  The connection tells the rest of the ADO.NET code which data base it is talking to.  It manages all of the low level logic associated with the specific data base protocols.  This makes it easy for you because the most work you will have to do in code is instantiate the connection object, open the connection, and then close the connection when you are done.  Because of the way that other classes in ADO.NET are built, sometimes you don't even have to do that much work.

Although working with connections is very easy in ADO.NET, you need to understand connections in order to make the right decisions when coding your data access routines.  Understand that a connection is a valuable resource.  Sure, if you have a stand-alone client application that works on a single data base on one machine, you probably don't care about this.  However, think about an enterprise application where hundreds of users throughout a company are accessing the same data base.  Each connection represents overhead and there can only be a finite amount of them.  To look at a more extreme case, consider a Web site that is being hit with hundreds of thousands of hits a day.  Applications that grab connections and don't let them go can have seriously negative impacts on performance and scalability.

Creating a SqlConnection Object

A SqlConnection is an object, just like any other C# object.  Most of the time, you just declare and instantiate the SqlConnection all at the same time, as shown below:

SqlConnection conn = new SqlConnection(
    "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

The SqlConnection object instantiated above uses a constructor with a single argument of type string.  This argument is called a connection string.  table 1 describes common parts of a connection string.

table 1.  ADO.NET Connection Strings contain certain key/value pairs for specifying how to make a data base connection.  They include the location, name of the database, and security credentials.

Connection String Parameter Name	Description
Data Source	Identifies the server.  Could be local machine, machine domain name, or IP Address.
Initial Catalog	Data base name.
Integrated Security	Set to SSPI to make connection with user's Windows login
User ID	Name of user configured in SQL Server.
Password	Password matching SQL Server User ID.

Integrated Security is secure when you are on a single machine doing development.  However, you will often want to specify security based on a SQL Server User ID with permissions set specifically for the application you are using.  The following shows a connection string, using the User ID and Password parameters:

SqlConnection conn = new SqlConnection(
"Data Source=DatabaseServer;Initial Catalog=Northwind;User ID=YourUserID;Password=YourPassword");

Notice how the Data Source is set to DatabaseServer to indicate that you can identify a data base located on a different machine, over a LAN, or over the Internet.  Additionally, User ID and Password replace the Integrated Security parameter.

Using a SqlConnection

The purpose of creating a SqlConnection object is so you can enable other ADO.NET code to work with a data base.  Other ADO.NET objects, such as a SqlCommand and a SqlDataAdapter take a connection object as a parameter.  The sequence of operations occurring in the lifetime of a SqlConnection are as follows:

1. Instantiate the SqlConnection.
2. Open the connection.
3. Pass the connection to other ADO.NET objects.
4. Perform data base operations with the other ADO.NET objects.
5. Close the connection.

We've already seen how to instantiate a SqlConnection.  The rest of the steps, opening, passing, using, and closing are shown in Listing 1.

Listing 1.  Using a SqlConnection

using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Demonstrates how to work with SqlConnection objects
/// </summary>
class SqlConnectionDemo
{
    static void Main()
    {
        // 1. Instantiate the connection
        SqlConnection conn = new SqlConnection(
            "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");

        SqlDataReader rdr = null;

        try
        {
            // 2. Open the connection
            conn.Open();

            // 3. Pass the connection to a command object
            SqlCommand cmd = new SqlCommand("select * from Customers", conn);

            //
            // 4. Use the connection
            //

            // get query results
            rdr = cmd.ExecuteReader();

            // print the CustomerID of each record
            while (rdr.Read())
            {
                Console.WriteLine(rdr[0]);
            }
        }
        finally
        {
            // close the reader
            if (rdr != null)
            {
                rdr.Close();
            }

            // 5. Close the connection
            if (conn != null)
            {
                conn.Close();
            }
        }
    }
}

As shown in Listing 1, you open a connection by calling the Open() method of the SqlConnection instance, conn.  Any operations on a connection that was not yet opened will generate an exception.  So, you must open the connection before using it.

Before using a connection, you must let the ADO.NET code know which connection it needs.  In Listing 1, we set the second parameter to the SqlCommand object with the SqlConnection object, conn.    Any operations performed with the SqlCommand will use that connection.

The code that uses the connection is a SqlCommand object, which performs a query on the Customers table.  The result set is returned as a SqlDataReader and the while loop reads the first column from each row of the result set, which is the CustomerID column.  We'll discuss the SqlCommand and SqlDataReader objects in later lessons.  For right now, it is important for you to understand that these objects are using the SqlConnection object so they know what database to interact with.

When you are done using the connection object, you must close it.  Failure to do so could have serious consequences in the performance and scalability of your application.  There are a couple points to be made about how we closed the connection in Listing 1:  the Close() method is called in a finally block and we ensure that the connection is not null before closing it.

Notice that we wrapped the ADO.NET code in a try/finally block.  As described in Lesson 15:  Introduction to Exception Handling of the C# Tutorial, finally blocks help guarantee that a certain piece of code will be executed, regardless of whether or not an exception is generated.  Since connections are scarce system resources, you will want to make sure they are closed in finally blocks.

Another precaution you should take when closing connections is to make sure the connection object is not null.  If something goes wrong when instantiating the connection, it will be null and you want to make sure you don't try to close an invalid connection, which would generate an exception.

This example showed how to use a SqlConnection object with a SqlDataReader, which required explicitly closing the connection.  However, when using a disconnected data model, you don't have to open and close the connection yourself.  We'll see how this works in a future lesson when we look at the SqlDataAdapter object.

Summary

SqlConnection objects let other ADO.NET code know what data base to connect to and how to make the connection.  They are instantiated by passing a connection string with a set of key/value pairs that define the connection.  The steps you use to manage the lifetime of a connection are create, open, pass, use, and close.  Be sure to close your connection properly when you are done with it to ensure you don't have a connection resource leak.

Read More