C# 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 teh database manager and therefore can be optimized for ideal performance.
Use the "ExecuteScalar", "ExecuteNonQuery", or "ExecuteReader" method to run stored procedures. For example, the following
code executes a stored procedure that returns the Information in the database
1. Add a button and a literal Web control to the Web form
private void btn_MyObject_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_MyObject = 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 rdr_MyObject;
myCommand.Parameters["@year"].Value = "2006";
// Open connection
con_MyObject.Open();
myCommand.Connection = con_MyObject;
myCommand.CommandText = SQL_String;
try
{ // Execute the stored procedure
rdr_MyObject = myCommand.ExecuteReader();
// Display a header
litData.Text = "<h3>My Header</h3>";
// Display the results on the page
while (rdr_MyObject.Read())
{ // Create an array to receive data
object[] items = {"", "", "", "", ""};
// If the row contains items
if (rdr_MyObject.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_MyObject.Close();
}
}