Using the SqlDataReader Class - Retrieving data from the Pubs Database
(Page 2 of 6 )
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:
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server" Width="200px"
AutoPostBack="True">
</asp:DropDownList><br /><br />
<asp:Label ID="Label1" runat="server"
ForeColor="Red"></asp:Label>
</div>
</form>
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:
private string connectionString = "Data Source=(local);Initial
Catalog=Pubs;Integrated Security=True";
The code that performs the data access and populates the DropDownList control is written in the Page_Load event handler method as follows:
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(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);
}
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
Label1.Text = "The list item " + DropDownList1.SelectedItem + " has value of " +
DropDownList1.SelectedValue;
}
Let's explain the code.
Next: Understanding the code example >>
More ASP.NET Articles
More By Michael Youssef
