C# ExecuteReader
The "ExecuteReader" method performs commands that return records, such as SQL SELECT. Each record is returned as a
data reader object, which is sort ofa read-only version of a data set. Because the "ExecuteReader" method acts directly
on the database connection, there are two versions of the data reader object: "OleDbDataReader" and "SqlDataReader"
Using "ExecuteReader" to create data reader objects provides better performance than creating a data set from a data
adapter object, but it doesn't provide you with much flexibility.
Data reader objects are read-only and they only read forward, one record at a time. Data sets allow you to get records in
any order and, more importantly, allow you to write changes back to the database.
The following code gets the players FirstName, LastName and Hometown and puts it in a dropdown list box's "Text" property
The GUID is placed in the "Value" property.
Because the Hometown column in the database has "NULLS", you have to screen the value and put it in a string instead of
writing it directly to the newItem object for the dropdown list.
private void Page_Load(object sender, System.EventArgs e)
{
// drop down from ExecuteReader
if (!IsPostBack)
{
RefreshList();
}
}
private void RefreshList()
{
string SQL_String;
string conn_String = "initial catalog=MyEmployee;user id=DavesASPNet; password=MyPassword$;packet size=4096;data source=DBSERVER2;persist security info=True";
SqlCommand myCommand = new SqlCommand();
// Create connection
SqlConnection con_Dropdown = new SqlConnection(conn_String);
// When an Employee is selected, display the info
try
{
// Clear the list
drp_Reader.Items.Clear();
// Create SQL command to get table
// Create SQL String
SQL_String = "SELECT * FROM Employees" ;
// Open Connection, pass string and execute query
con_Dropdown.Open();
myCommand.Connection = con_Dropdown;
myCommand.CommandText = SQL_String;
SqlDataReader readEmployees = myCommand.ExecuteReader();
// Read names and hometown
while (readEmployees.Read())
{
// Create a new list item
ListItem newItem = new ListItem();
// Set the item's values
string my_Info;
// If values are null, an exception will be raised
if (readEmployees.IsDBNull(20))
my_Info = "----";
else
my_Info = readEmployees.GetString(20).ToString();
newItem.Text = readEmployees.GetString(2).ToString() + " " +
readEmployees.GetString(4).ToString() + " " +
my_Info;
newItem.Value = readEmployees.GetGuid(0).ToString();
// Add the new line to the list
drp_Reader.Items.Add(newItem);
}
}
catch(Exception ex)
{ string str = ex.Message; }
finally
{ con_DropDown.Close(); }
}