Using the SqlDataReader Class - Using the SqlDataReader.GetValue() and GetOrdinal() methods
(Page 5 of 6 )
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:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
try
{
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.
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
try
{
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(dataReader.GetOrdinal
("job_desc")).ToString();
item.Value = dataReader.GetValue(dataReader.GetOrdinal
("job_id")).ToString();
DropDownList1.Items.Add(item);
}
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
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.
Next: Returning Fields in appropriate data types >>
More ASP.NET Articles
More By Michael Youssef
