SqlDataReader and Asp.Net ListBox

SqlClient namespace contains classes required for interacting with Sql Server 7 and above.

SqlConnection class is used for establishing a live session between front end and back end. In this example we are using Integrated Security, ie. using the windows credential for connecting to Sql Server.

SqlDataReader is a read-only forward only recordset.

In the SqlCommand's constructor we are passing the Sql Command. Inorder to create an SqlDataReder the ExecuteReader method of SqlCommand have to be used.

SqlDataReader Read() Method
After creating the SqlDataReader we use the Read method for iterating throug the SqlDataReader and folling the ListBox.

using System.Data.SqlClient;

SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr;
private void Form1_Load(object sender, EventArgs e)
{
cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind");
cnn.Open();
cmd = new SqlCommand("select * from Categories", cnn);
dr = cmd.ExecuteReader();
while (dr.Read())
lstCategories.Items.Add(dr["CategoryName"].ToString());
}

ListBox DataSouce and DataTextField Properties

Instead of using the Read() method of SqlDataReader and invoking the Items.Add() for filling the ListBox we can use DataSource and DataTextField Properties of ListBox for filling a ListBox which is a better approach.

SqlConnection cnn;
SqlCommand cmd;
SqlDataReader dr;
protected void Page_Load(object sender, EventArgs e)
{
cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind");
cnn.Open();
cmd = new SqlCommand("select * from Categories", cnn);
dr = cmd.ExecuteReader();
lstCategories.DataSource = dr;
lstCategories.DataTextField = "CategoryName";
DataBind();
}

Related Blogs

DataTable DataColumn and DataRow (Asp.Net)