Lesson 07: Using Stored Procedures

This lesson shows how to use stored procedures in your data access code. Here are the objectives of this lesson:

• Learn how to modify the SqlCommand object to use a stored procedure.
• Understand how to use parameters with stored procedures.

Introduction

A stored procedures is a pre-defined, reusable routine that is stored in a database. SQL Server compiles stored procedures, which makes them more efficient to use. Therefore, rather than dynamically building queries in your code, you can take advantage of the reuse and performance benefits of stored procedures. The following sections will show you how to modify the SqlCommand object to use stored procedures. Additionally, you'll see another reason why parameter support is an important part of the ADO.NET libraries.

Lesson 06: Adding Parameters to Commands

This lesson shows you how to use parameters in your commands.2 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.

Lesson 05: Working with Disconnected Data - The DataSet and SqlDataAdapter

This lesson explains how to work with disconnected data, using the DataSet and SqlDataAdapter objects. Here are the objectives of this lesson:

• Understand the need for disconnected data.
• Obtain a basic understanding of what a DataSet is for.
• Learn to use a SqlDataAdapter to retrieve and update data.

Introduction

In Lesson 3, we discussed a fully connected mode of operation for interacting with a data source by using the SqlCommand object. In Lesson 4, we learned about how to read data quickly an let go of the connection with the SqlDataReader. This Lesson shows how to accomplish something in-between SqlConnection and SqlDataReader interaction by using the DataSet and SqlDataAdapter objects.
A DataSet is an in-memory data store that can hold numerous tables. DataSets only hold data and do not interact with a data source. It is the SqlDataAdapter that manages connections with the data source and gives us disconnected behavior. The SqlDataAdapter opens a connection only when required and closes it as soon as it has performed its task. For example, the SqlDataAdapter performs the following tasks when filling a DataSet with data:

1. Open connection
2. Retrieve data into DataSet
3. Close connection

and performs the following actions when updating data source with DataSet changes:

1. Open connection
2. Write changes from DataSet to data source
3. Close connection

In between the Fill and Update operations, data source connections are closed and you are free to read and write data with the DataSet as you need. These are the mechanics of working with disconnected data. Because the applications holds on to connections only when necessary, the application becomes more scalable.

Lesson 04: Reading Data with the SqlDataReader

This lesson explains how to read data with a SqlDataReader object. Here are the objectives of this lesson:

• Learn what a SqlDataReader is used for.
• Know how to read data using a SqlDataReader.
• Understand the need to close a SqlDataReader.

Introduction

A SqlDataReader is a type that is good for reading data in the most efficient manner possible. You can *not* use it for writing data. SqlDataReaders are often described as fast-forward firehose-like streams of data.
You can read from SqlDataReader objects in a forward-only sequential manner. Once you've read some data, you must save it because you will not be able to go back and read it again.
The forward only design of the SqlDataReader is what enables it to be fast. It doesn't have overhead associated with traversing the data or writing it back to the data source. Therefore, if your only requirement for a group of data is for reading one time and you want the fastest method possible, the SqlDataReader is the best choice. Also, if the amount of data you need to read is larger than what you would prefer to hold in memory beyond a single call, then the streaming behavior of the SqlDataReader would be a good choice.
Note: Observe that I used the term "one time" in the previous paragraph when discussing the reasons why you would use a SqlDataReader. As with anything, there are exceptions. In many cases, it is more efficient to use a cached DataSet. While caching is outside the scope of this tutorial, we will discuss using DataSet objects in the next lesson.

Creating a SqlDataReader Object

Getting an instance of a SqlDataReader is a little different than the way you instantiate other ADO.NET objects. You must call ExecuteReader on a command object, like this:

    SqlDataReader rdr = cmd.ExecuteReader();

The ExecuteReader method of the SqlCommand object, cmd , returns a SqlDataReader instance. Creating a SqlDataReader with the new operator doesn't do anything for you. As you learned in previous lessons, the SqlCommand object references the connection and the SQL statement necessary for the SqlDataReader to obtain data.

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.

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.

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:
http://www.microsoft.com/sql/msde/downloads/download.asp
MSDE contains documentation on how to perform an installation. However, for your convenience, here are quick instructions on how to install MSDE:
http://www.asp.net/msde/default.aspx?tabindex=0&tabid=1
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:
http://www.microsoft.com/sql/msde/