ExecuteReader - Calling SQL Parameters
One well-known performance tip when you're working with databases is to move frequently used tasks into stored procedures.
Stored procedures execute within the context of the database manager and therefore can be optimized for ideal performance.
Use the "ExecuteScalar", "ExecuteNonQuery", or "ExecuteReader" method to run stored procedures.
1. Add a button and a literal Web control to the Web form
private void btn_MoneyList_Click(object sender, System.EventArgs e)
{ string SQL_String;
string conn_String = "initial catalog=MyDb;user id=DavesASPNet; password=MyPassword$;packet size=4096;data source=DBSERVER2;persist security info=True";
SqlCommand myCommand = new SqlCommand();
SQL_String = "someString";
// Create connection
SqlConnection con_MyConnection = new SqlConnection(conn_String);
myCommand.CommandType = CommandType.StoredProcedure;
// Add SQL Parameter to the SqlCommand myCommand
SqlParameter sql_param = new SqlParameter();
sql_param.ParameterName = "@year";
sql_param.SqlDbType = SqlDbType.NChar;
myCommand.Parameters.Add(sql_param);
// Create reader
SqlDataReader MyReader;
myCommand.Parameters["@year"].Value = "2006";
// Open connection
con_MoneyList.Open();
myCommand.Connection = con_MyConnection;
myCommand.CommandText = SQL_String;
try
{ // Execute the stored procedure
rdr_MyReader = myCommand.ExecuteReader();
// Display a header
litData.Text = "<h3>My Header</h3>";
// Display the results on the page
while (MyReader.Read())
{ // Create an array to receive data
object[] items = {"", "", "", "", ""};
// If the row contains items
if (MyReader.GetValues(items) > 0)
{ // Add each row item to the literal control
foreach(object item in items)
litData.Text += item.ToString() + " ";
// Add a break between rows
litData.Text += "<br>";
}
}
}
catch(Exception ex)
{ litData.Text = "The following error occurred: <br>";
litData.Text += ex.Message;
}
finally
{ con_MoneyList.Close();
}
}