C# DROPDOWN LIST
Executing Commands on a database
The database connection object provides these three command methods:
1. ExecuteScalar - Performs query commands that return a single value, such as counting the number of records in a table (It returns the value of the first column and first row of the information set)
2. ExecuteNonQuery - Performs commands that change the database but do not return a specific value, including adding, and deleting items from a database. The "ExecuteNonQuery" method returns the number of rows affected by teh command
3. ExecuteReader - Reads records sequentially from the database
To use these methods, follow these steps:
A. Create a connection to the database
B. Open the connection
C. Create a command object containing the SQL command or stored procedure to execute.
D. Execute the method on the command object.
E. Close the database connection.
NOTE: Because any command that executes on a database has at least some potential to fail, you should always use exception handling to ensure that the database connection is closed whether or not the command succeeds.
Calling the connection's "Close" method from a "Finally" exception-handling clause ensures that the database connection is closed before the code continues.
The following code grabs the GUID that is stored in the "Value" field of a dropdown list and queries the db for the object
private void drp_MyDropDown_SelectedIndexChanged(object sender, System.EventArgs e)
{
string SQL_String;
string conn_String = "initial catalog=MyDatabase;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 Object is selected, display the Item
try
{
// Create SQL String
SQL_String = "SELECT Item FROM MyTable " +
"WHERE MyGUID = '" +
drp_MyDropDown.SelectedItem.Value + "'";
// Open Connection, pass string and execute query
con_Dropdown.Open();
myCommand.Connection = con_Dropdown;
myCommand.CommandText = SQL_String;
// Grab the value from the Execution object
string str_Item = myCommand.ExecuteScalar().ToString();
// Populate a textbox with the data
txt_TextBox.Text = str_Item;
}
catch (Exception ex)
{ string str = ex.Message; }
finally
{ con_Dropdown.Close(); }
}