Wednesday 31 August 2011

Lesson 01: 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 database, but it could also be a text file, an Excel spreadsheet, 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 databases 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. 
You can download the Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) here:
MSDE contains documentation on how to perform an installation.  However, for your convenience, here are quick instructions on how to install MSDE:
MSDE 2000 is a scaled down version of SQL Server.  Therefore, everything you learn in this tutorial and all code will work with SQL Server.  The examples will use the Northwind database.  This is a tutorial is specifically for ADO.NET.  MSDE is not part of ADO.NET, but it is one of the many data sources you can interact with by using ADO.NET If you need help with MSDE 2000, I refer you to the Microsoft Web site, where you can find pertinent information on licensing and technical assistance:
Data Providers
We know that ADO.NET allows us to interact with different types of data sources and different types of databases.  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 Databases.
SQL Data Provider
Sql
For interacting with Microsoft SQL Server.
Borland Data Provider
Bdp
Generic access to many databases 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 database, 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 database, you must have a connection to it.  The connection helps identify the database server, the database 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 database to execute the command on.
The SqlCommand Object
The process of interacting with a database 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 database.  A command object uses a connection object to figure out which database 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 database 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 database 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 database.  A data adapter contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database.  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 database for you.  All you need to do is tell the data adapter when to load from or write to the database.

Lesson 02: 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 database it is talking to.  It manages all of the low level logic associated with the specific database 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 database one 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 database.  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 database 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
Database 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 database 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 database.  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 database 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 SqlCommand, 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.

Lesson 03: The SqlCommand Object



This lesson describes the SqlCommand object and how you use it to interact with a database.  Here are the objectives of this lesson:
  • Know what a command object is.
  • Learn how to use the ExecuteReader method to query data.
  • Learn how to use the ExecuteNonQuery method to insert and delete data.
  • Learn how to use the ExecuteScalar method to return a single value.
Introduction
A SqlCommand object allows you to specify what type of interaction you want to perform with a database.  For example, you can do select, insert, modify, and delete commands on rows of data in a database table.  The SqlCommand object can be used to support disconnected data management scenarios, but in this lesson we will only use the SqlCommand object alone.  A later lesson on the SqlDataAdapter will explain how to implement an application that uses disconnected data.  This lesson will also show you how to retrieve a single value from a database, such as the number of records in a table.
Creating a SqlCommand Object
Similar to other C# objects, you instantiate a SqlCommand object via the new instance declaration, as follows:
    SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
The line above is typical for instantiating a SqlCommand object.  It takes a string parameter that holds the command you want to execute and a reference to a SqlConnection object.  SqlCommand has a few overloads, which you will see in the examples of this tutorial.
Querying Data
When using a SQL select command, you retrieve a data set for viewing.  To accomplish this with a SqlCommand object, you would use the ExecuteReader method, which returns a SqlDataReader object.  We'll discuss the SqlDataReader in a future lesson.  The example below shows how to use the SqlCommand object to obtain a SqlDataReader object:
// 1. Instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);

// 2. Call Execute reader to get query results
SqlDataReader rdr = cmd.ExecuteReader();
In the example above, we instantiate a SqlCommand object, passing the command string and connection object to the constructor.  Then we obtain a SqlDataReader object by calling the ExecuteReader method of the SqlCommand object, cmd. 
This code is part of the ReadData method of Listing 1 in the Putting it All Together section later in this lesson.
Inserting Data
To insert data into a database, use the ExecuteNonQuery method of the SqlCommand object.  The following code shows how to insert data into a database table:
// prepare command string
 
string insertString = @"
     insert into Categories
     (CategoryName, Description)
     values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";

 // 1. Instantiate a new command with a query and connection
 
SqlCommand cmd = new SqlCommand(insertString, conn);

 // 2. Call ExecuteNonQuery to send command
 
cmd.ExecuteNonQuery();
The SqlCommand instantiation is just a little different from what you've seen before, but it is basically the same.  Instead of a literal string as the first parameter of the SqlCommand constructor, we are using a variable, insertString.  The insertString variable is declared just above the SqlCommand declaration. 
Notice the two apostrophes ('') in the insertString text for the word "doesn''t".  This is how you escape the apostrophe to get the string to populate column properly. 
Another observation to make about the insert command is that we explicitly specified the columns CategoryName and Description.  The Categories table has a primary key field named CategoryID.  We left this out of the list because SQL Server will add this field itself.  trying to add a value to a primary key field, such as CategoryID, will generate an exception.
To execute this command, we simply call the ExecuteNonQuery method on the SqlCommand instance, cmd.
This code is part of the Insertdata method of Listing 1 in the Putting it All Together section later in this lesson.
Updating Data
The ExecuteNonQuery method is also used for updating data.  The following code shows how to update data:
// prepare command string
 
string updateString = @"
     update Categories
     set CategoryName = 'Other'
     where CategoryName = 'Miscellaneous'";
 
 // 1. Instantiate a new command with command text only
 
SqlCommand cmd = new SqlCommand(updateString);
 
 // 2. Set the Connection property
 
cmd.Connection = conn;
 
 // 3. Call ExecuteNonQuery to send command
 
cmd.ExecuteNonQuery();
Again, we put the SQL command into a string variable, but this time we used a different SqlCommand constructor that takes only the command.  In step 2, we assign the SqlConnection object, conn, to the Connection property of the SqlCommand object, cmd. 
This could have been done with the same constructor used for the insert command, with two parameters.  It demonstrates that you can change the connection object assigned to a command at any time.
The ExecuteNonQuery method performs the update command.
This code is part of the UpdateData method of Listing 1 in the Putting it All Together section later in this lesson.
Deleting Data
You can also delete data using the ExecuteNonQuery method.  The following example shows how to delete a record from a database with the ExecuteNonQuery method:
// prepare command string
 
string deleteString = @"
     delete from Categories
     where CategoryName = 'Other'";
 
 // 1. Instantiate a new command
 
SqlCommand cmd = new SqlCommand();
 
 // 2. Set the CommandText property
 
cmd.CommandText = deleteString;
 
 // 3. Set the Connection property
 
cmd.Connection = conn;
 
 // 4. Call ExecuteNonQuery to send command
 
cmd.ExecuteNonQuery();
This example uses the SqlCommand constructor with no parameters.  Instead, it explicity sets the CommandText and Connection properties of the SqlCommand object, cmd. 
We could have also used either of the two previous SqlCommand constructor overloads, used for the insert or update command, with the same result.  This demonstrates that you can change both the command text and the connection object at any time. 
The ExecuteNonQuery method call sends the command to the database.
This code is part of the DeleteData method of Listing 1 in the Putting it All Together section later in this lesson.
Getting Single values
Sometimes all you need from a database is a single value, which could be a count, sum, average, or other aggregated value from a data set.  Performing an ExecuteReader and calculating the result in your code is not the most efficient way to do this.  The best choice is to let the database perform the work and return just the single value you need.  The following example shows how to do this with the ExecuteScalar method:
// 1. Instantiate a new command
 
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
 
 // 2. Call ExecuteNonQuery to send command
 
int count = (int)cmd.ExecuteScalar();
The query in the SqlCommand constructor obtains the count of all records from the Categories table.  This query will only return a single value.  The ExecuteScalar method in step 2 returns this value.  Since the return type of ExecuteScalar is type object, we use a cast operator to convert the value to int.
This code is part of the GetNumberOfRecords method of Listing 1 in the Putting it All Together section later in this lesson.
Putting it All Together
For simplicity, we showed snippets of code in previous sections to demonstrate the applicable techniques .  It is also useful to have an entire code listing to see how this code is used in a working program.  Listing 1 shows all of the code used in this example, along with a driver in the Main method to produce formatted output.
Listing 1.  SqlConnection Demo
 using System;
 using System.Data;
 using System.Data.SqlClient;

 /// <summary>
 ///
Demonstrates how to work with SqlCommand objects
 
/// </summary>
 
class SqlCommandDemo
 {
     SqlConnection conn;
 
 
    public SqlCommandDemo()
     {
         // Instantiate the connection
 
        conn = new SqlConnection(
            "Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
     }
 
 
    // call methods that demo SqlCommand capabilities
 
    static void Main()
     {
         SqlCommandDemo scd = new SqlCommandDemo();

         Console.WriteLine();
         Console.WriteLine("Categories Before Insert");
         Console.WriteLine("------------------------");
 
 
        // use ExecuteReader method
 
        scd.ReadData();
 
 
        // use ExecuteNonQuery method for Insert
 
        scd.Insertdata();
         Console.WriteLine();
         Console.WriteLine("Categories After Insert");
         Console.WriteLine("------------------------------");

        scd.ReadData();

         // use ExecuteNonQuery method for Update
 
        scd.UpdateData();

         Console.WriteLine();
         Console.WriteLine("Categories After Update");
         Console.WriteLine("------------------------------");

         scd.ReadData();

         // use ExecuteNonQuery method for Delete
 
        scd.DeleteData();

         Console.WriteLine();
         Console.WriteLine("Categories After Delete");
         Console.WriteLine("------------------------------");

         scd.ReadData();

         // use ExecuteScalar method
 
        int numberOfRecords = scd.GetNumberOfRecords();

         Console.WriteLine();
         Console.WriteLine("Number of Records: {0}", numberOfRecords);
     }

     /// <summary>
 
    /// use ExecuteReader method
 
    /// </summary>
 
    public void ReadData()
     {
        SqlDataReader rdr = null;
 
 
        try
 
        {
             // Open the connection
 
            conn.Open();
 
 
            // 1. Instantiate a new command with a query and connection
 
            SqlCommand cmd = new SqlCommand("select CategoryName from Categories", conn);
 
 
            // 2. Call Execute reader to get query results
 
            rdr = cmd.ExecuteReader();

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

     /// <summary>
 
    /// use ExecuteNonQuery method for Insert
 
    /// </summary>
 
    public void Insertdata()
     {
         try
        
{
             // Open the connection
 
            conn.Open();
 
 
           // prepare command string
 
           string insertString = @"
                 insert into Categories
                 (CategoryName, Description)
                 values ('Miscellaneous', 'Whatever doesn''t fit elsewhere')";
 
 
            // 1. Instantiate a new command with a query and connection
 
            SqlCommand cmd = new SqlCommand(insertString, conn);
 
 
            // 2. Call ExecuteNonQuery to send command
 
            cmd.ExecuteNonQuery();
         }
         finally
        
{
             // Close the connection
 
            if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 
 
    /// <summary>
 
    /// use ExecuteNonQuery method for Update
 
    /// </summary>
 
    public void UpdateData()
     {
         try
        
{
             // Open the connection
 
           conn.Open();
 
 
            // prepare command string
 
            string updateString = @"
                 update Categories
                 set CategoryName = 'Other'
                 where CategoryName = 'Miscellaneous'";
 
 
           // 1. Instantiate a new command with command text only
 
            SqlCommand cmd = new SqlCommand(updateString);

             // 2. Set the Connection property
 
            cmd.Connection = conn;
 
 
            // 3. Call ExecuteNonQuery to send command
 
            cmd.ExecuteNonQuery();
        }
         finally
        
{
             // Close the connection
 
           if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 
 
    /// <summary>
 
    /// use ExecuteNonQuery method for Delete
 
    /// </summary>
 
    public void DeleteData()
     {
         try
        
{
             // Open the connection
 
            conn.Open();
 
 
            // prepare command string
 
            string deleteString = @"
                 delete from Categories
                 where CategoryName = 'Other'";
 
 
            // 1. Instantiate a new command
 
            SqlCommand cmd = new SqlCommand();
 
 
           // 2. Set the CommandText property
 
            cmd.CommandText = deleteString;
 
 
           // 3. Set the Connection property
 
            cmd.Connection = conn;
 
 
            // 4. Call ExecuteNonQuery to send command
 
            cmd.ExecuteNonQuery();
         }
         finally
        
{
             // Close the connection
 
            if (conn != null)
             {
                 conn.Close();
             }
         }
     }

     /// <summary>
 
    /// use ExecuteScalar method
 
    /// </summary>
 
    /// <returns>number of records</returns>
 
    public int GetNumberOfRecords()
     {
         int count = -1;
 
         try
        
{
             // Open the connection
 
            conn.Open();
 
 
            // 1. Instantiate a new command
 
            SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
 
 
            // 2. Call ExecuteScalar to send command
 
            count = (int)cmd.ExecuteScalar();
         }
         finally
        
{
            // Close the connection
 
            if (conn != null)
             {
                 conn.Close();
             }
         }
         return count;
     }
 }
In Listing 1, the SqlConnection object is instantiated in the SqlCommandDemo structure.  This is okay because the object itself will be cleaned up when the CLR garbage collector executes.  What is important is that we close the connection when we are done using it.  This program opens the connection in a try block and closes it in a finally block in each method.
The ReadData method displays the contents of the CategoryName column of the Categories table.  We use it several times in the Main method to show the current status of the Categories table, which changes after each of the insert, update, and delete commands.  Because of this, it is convenient to reuse to show you the effects after each method call.