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.