If you read any of my previous ADO.NET articles you will find that I used the SqlDataReader class in many of the examples. Today we will talk more about the SqlDataReader class and use its methods and properties. Some of you may wonder how the SqlDataReader class gets access to the fields using the array-like syntax; we will talk about that as well.
If you are not familiar with the SqlDataReader class, you should know that it's one of the players on the ADO.NET team of data access classes. We have the SqlConnection class, which is used to create and open a connection to the database using the connection string that you provide, and we have the SqlCommand class, which is used to execute a T-SQL statement, like SELECT, INSERT, UPDATE or DELETE and stored procedures against a database using the connection opened by the SqlConnection object, and retrieve the result.
As you might know, one of the methods of the SqlCommand class is used to retrieve an object of type SqlDataReader; this is the SqlCommand.ExecuteReader() method. The SqlDataReader is what you are going to use to retrieve data -- to the client, for example, or to a business component like the ObjectDataSource control, which we are going to discuss in the next few articles along with the SqlDataSource class.
The SqlDataReader retrieves the data in a read-only forward-only mechanism. Read-only means that you can't use the SqlDataReader class to execute statements that update the data in your databases. Forward-only means that you don't have access to the previous row once you have read it; the SqlDataReader class retrieves the row, discards it, and then retrieves the next row and so on. You have access to the fields of the current row using indexers.
A column-ordinal based indexer uses the column number to retrieve the field's data, as in myDataReader[0]. And a string-based indexer uses the column name to access its field's value. Usually, you would use the string column-name based indexer because it eliminates any confusion about what field you are retrieving. You would write something like myDataReader["LastName"]. But it's faster to use the column-ordinal based indexer because the data reader will not perform any additional operations to retrieve the field's value; it will just go to the column ordinal you want to access and get the value. With the string column-name based indexer, however, as in myDataReader["LastName"], the SqlDataReader has to search for a column with that name, and then returns its value, which slows the performance a bit.
Let's see an example that uses the SqlDataReader class. Note that we are using the SQL Server .NET Data Provider for all the examples.
In this example, we are going to use the SqlDataReader class to retrieve data from the Jobs table in the Pubs database. We will use a DropDownList control to store the retrieved data. Start by creating a new website and add a DropDownList control along with a Label control to the form. The markup for the form would look like this:
Note that the AutoPostBack attribute, or you would say property if you are adding it in C# code, is set to true, which posts the page back to the server when you change the selected item. We need this behavior to display the selected item and its value in the Label control every time the user selects another item from the DropDownList control. In the Default.aspx.cs file add a namespace reference statement for the SQL Server .NET Data Provider as in the following line of code:
using System.Data.SqlClient;
Now add a private instance variable to hold the Connection String as follows:
We need to populate the DropDownList control only the first time the page loads because we are depending on the ASP.NET ViewState feature to preserve the control's state. If we had removed the if statement, which determines that this is the first time the page is loading, we would get something like the following screen shot if we have changed the selected item a few times:
Note how the control keeps adding the same set of items, by accessing the database and retrieving the data, every time we change our selection. I will discuss View State in a separate article along with other ASP.NET basics soon. For now let's focus on our Data Access code. The code simply creates a SqlConnection instance with a using block to ensure that the object is closed at the end of executing the block or in the case of an exception; this is the best way to close connection objects. We also have passed the connection string to the SqlConnection's constructor. We could have assigned that string to the SqlConnection.ConnectionString property, but the first way is easier and more elegant.
We used a simple SELECT statement to retrieve data from the Jobs table, SELECT job_id, job_desc FROM Jobs. We did that by passing the query string and the connection object to the SqlCommand's constructor. Now we can call the SqlConnection.Open() method to open the connection and execute the query as in the following code.
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
ListItem item = new ListItem(dataReader[1].ToString(), dataReader [0].ToString());
// or you can replace the above line of code with the following one
//ListItem item = new ListItem(dataReader["job_desc"].ToString(),
// dataReader["job_id"].ToString());
DropDownList1.Items.Add(item);
}
}
Note that we used another using block to create the SqlDataReader object; I will tell you why in a moment. We created the SqlDataReader object by calling the SqlCommand.ExecuteReader() method. You really can't create an instance of the SqlDataReader class using the new operator because its constructor has been defined as private. The SqlDataReader.Read() method is used to read the following row; it returns true if there is one more row to read, and moves the SqlDataReader to that new row. It returns false otherwise. As you might have guessed, the Read() method is placed before the first row. This makes sense because this is how we read the first row; if it's placed on the first row we wouldn't have access to that row, and it would return true to read the second row if we have a second row and so on.
Usually, you use a while block to access every row in the result set. As we have said, the SqlDataReader provides you with two indexers to access the fields of the current row. In our example, we created ListItem objects using the constructor that accepts a string that represents an item in the DropDownList control and a value for that item. Then we added it to the Items collection of the DropDownList control using the DropDownList.Items.Add() method. We used the column-ordinal based indexer; I have commented out the code that uses the column-name based indexer, which you can use as well. I know that you might wonder how those indexers get to the data; we will see how in the next section.
One more thing before we go to the next section: you must call the SqlDataReader.Close() method as soon as you finish retrieving data, because the SqlDataReader uses a live connection to the database. This means that the SqlConnection that is associated with the SqlDataReader (through the SqlCommand instance) is busy and can't be used for any other operation until the SqlDataReader instance is closed. You can close the SqlDataReader instance in one of two ways: you can call the SqlDataReader.Close() method or you can use the SqlDataReader instance in a using block, as we did in the above code example. The second method is the best solution, because it guarantees that the SqlDataReader.Close() method is called even when an exception is raised. Actually, the using block calls the SqlDataReader.Dispose() method which in turn calls SqlDataReader.Close(). In the above code we also used a using block with our SqlConnection instance, which calls its Close() method when the block's execution completes.
The implementation of the column-ordinal based indexer of the SqlDataReader class looks as follows:
public override object this[int i]
{
get
{
return this.GetValue(i);
}
}
And the SqlDataReader's column-name based indexer implementation looks like this:
public override object this[string name]
{
get
{
return this.GetValue(this.GetOrdinal(name));
}
}
As you might have noted in the code example, we have used the ToString() method with the SqlDataReader's returned fields to create the ListItem object.
ListItem item = new ListItem(dataReader[1].ToString(), dataReader[0].ToString());
// or you can replace the above line of code with the following one
//ListItem item = new ListItem(dataReader["job_desc"].ToString(),
// dataReader["job_id"].ToString());
Both the column-ordinal based indexer and the column-name based indexer return an object data type so in our code we needed to call the SqlDataReader[Column Name or Column Ordinal].ToString() method to return a string value to create the List Item. What you should note about those indexers is the methods they use to return the field you are asking for, namely the GetValue() method and the GetOrdinal() method. The GetValue() method is used to get the field's value. It accepts the index of the field and returns it. In the column-name indexer the SqlDataReader uses the syntax this.GetValue(this.GetOrdinal(name)) in order to return the value of the field. The GetValue() method returns the field as an object data type too.
The SqlDataReader.GetOrdinal() method accepts a string value, which is the column name, and returns its ordinal. The GetOrdinal() method's return value is used as an argument to the SqlDataReader.GetValue() method, that accepts the column ordinal, to get the value of the field.
I think by now you have a better understanding of how these indexers return the field's data and you also know that using the column-ordinal indexer is faster than using the column-name indexer.
We may also use the methods that the indexers use directly, as in the following example.
In this example, we will use the SqlDataReader.GetValue() method to access the job_id and job_desc columns and return their values. We assign these values to the ListItem.Value and ListItem.Text properties respectively, and then add the newly created ListItem instance to the DropDownList control. You simply need to replace the above Page_Load() event handler method with the following code:
using (SqlConnection connection = new SqlConnection (connectionString))
{
string commandText = "SELECT job_id, job_desc FROM Jobs";
SqlCommand command = new SqlCommand(commandText, connection);
connection.Open();
using (SqlDataReader dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
ListItem item = new ListItem();
item.Text = dataReader.GetValue(1).ToString();
item.Value = dataReader.GetValue(0).ToString();
DropDownList1.Items.Add(item);
}
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
Label1.Text = "The list item " + DropDownList1.SelectedItem + " has value of " +
DropDownList1.SelectedValue;
}
Note that we have used the ToString() method of the return value of the SqlDataReader.GetValue() method because it returns the value as an object data type.
We can use the column name instead of column ordinal as in the following example. Just replace the the next Page_Load() event handler method with the one above and run the example.
Label1.Text = "The list item " + DropDownList1.SelectedItem + " has value of " +
DropDownList1.SelectedValue;
}
In the code above we accessed the columns by name but we did that using one more method call, in the same way as the column-name based indexer. We used the SqlDataReader.GetOrdinal() method, which accepts the column name as a string value, and returned the ordinal of that column; that's what is needed as an argument to the SqlDataReader.GetValue() method in order to return the value of the field. Let's see the Strongly Typed Get() methods that the SqlDataReader class provides.
In the following example we use the SqlDataReader.GetString() method and the SqlDataReader.GetInt16() method to retrieve the fields of the job_desc and job_id columns in appropriate data types without performing any data type conversion operations. The GetString() method returns the field as a string value and the GetInt16() method returns the field as a short value, as in the following example.
short jobId = dataReader.GetInt16(dataReader.GetOrdinal ("job_id"));
ListItem item = new ListItem();
item.Text = jobDesc;
item.Value = jobId.ToString();
DropDownList1.Items.Add(item);
}
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
Label1.Text = "The list item " + DropDownList1.SelectedItem + " has value of " +
DropDownList1.SelectedValue;
}
We simply declared a string variable, the jobDesc variable, and a short variable called jobId, and assigned the values of the fields that we got from the SqlDataReader object to them without making any data type conversion operations. Note that we called the jobId.ToString() method to assign value to the ListItem.Value property because it accepts a string value. So if you know that you are retrieving a string value, use the GetString() method; if you know that you are retrieving a BIT data type from SQL Server table, use the GetBoolean() and so on.
Introducing ADO.NET with ASP.NET 2.0
