Creating and Using Parameterized Stored Procedures

The qryStudentsOfMarshaFranklin query is not useful except for the single instance where you want to know the IDs of that teacher's students. You certainly wouldn't want to create separate queries for each teacher. If you were creating a SQL string to perform the query, you would probably create the query with a function call. For example, you might create a function that accepts a teacher's first and last names and returns the appropriate SQL query:

public String makeStudentsForTeacherQuery(String firstname, String lastname) { return "SELECT StudentID " +

"FROM TeacherStudent INNER JOIN Teachers " + "ON TeacherStudent.TeacherID=Teachers.TeacherID " + "WHERE Teachers.LastName='" & lastname & "'" + "AND Teachers.FirstName='" & firstname & "'";

Using the preceding function, you could create a query for any teacher.

Parameterized stored procedures work exactly the same way. You define and pass the appropriate parameters, and SQL Server combines the parameters with the query to retrieve the appropriate data.

Creating a Parameterized Stored Procedure

To create a stored procedure with parameters, define the parameters and types after the stored procedure name but before the as clause:

CREATE PROCEDURE qryGetStudentIDsFromTeacherName @lastname char(20), @firstname char(20)

SELECT StudentID

FROM TeacherStudent INNER JOIN Teachers ON TeacherStudent.TeacherID=Teachers.TeacherID WHERE [email protected] AND [email protected]

You can paste that text into the Enterprise Manager Stored Procedure Properties dialog and click OK to save the stored procedure. SQL Server creates a new stored procedure named qryGetstudent-IDsFromTeacherName.

Warning The SQL script that creates the ClassRecords database does not create the qryGetStudentIDsFromTeacherName stored procedure—you should create it before you continue. You can find the code in the qryGetStudentIDsFromTeacherName.sql file in the Ch14 folder of the CSharpASP project on www.sybex.com.

The new stored procedure accepts two string parameters named @lastname and @firstname. The parameters are required because they don't have a default value, but you can create parameters that have default values.

You can test the new procedure by running it in the Query Analyzer. Select the classRecords database and type this text:

exec qryGetStudentIDsFromTeacherName 'Franklin', 'Marsha'

To run the text, press F5 or click the Run button on the Query Analyzer toolbar. The results look like Figure 14.6.

nj it cfc > Tfcoh

- f. '"J

fi^ £i H - n - ^ » lu 11 TiOi SB

Jjr-Jtt^fc 4

4uh 9: jma: 1 :<< rc-.-Tiv-'Ui bin ■ r i i | *

j Bimn-Tijvjw; ;c *

J rV^CLJLiiWL J= , v. 0 Wv* f N CMitfWnh » J -Kfc^

■ J Cvdjp - f^nrti ■• _J Ciirffi-yiw

i(r

|j'.*4CH> it

4 JHi'^l'iif'Hh • J'■awnt-r^^. ■ ^ H*?taHivr«nn #■ J Fmtwn ■ * JiiRlifMkn 1

V _j ': — T , »• _J *rl wp im _j ^Mri -I

: -3

«1 1 "1

I n

^JOwwIFlW*«!

JJTHW i(.EMli«H4r| pHm-MU^rtwrrtp-.T: |*WMP J-M bl «r

i:

Figure 14.6: Testing a parameterized stored procedure in the Query Analyzer Using a Parameterized Stored Procedure

Figure 14.6: Testing a parameterized stored procedure in the Query Analyzer Using a Parameterized Stored Procedure

To use the qryGetstudentiDsFromTeacherName procedure, you follow the same pattern as you did to run the simple stored procedure except that you must add the parameter definitions and values to the Command object's Parameters collection. The Web Form chl4-3 contains an example. Drag a ListBox and a DataGrid server control onto the design surface. You want to display the names of the teachers in the ListBox. When a user clicks a name in the ListBox, you want to return to the server and look up the studentiD values associated with that teacher.

To force the form to submit each time the user clicks a teacher name, set the

ListBox.AutoPostBack property to true. Set the ListBox's ID property to lstTeachers and the DataGrid's ID property to dgStudentIDs .

When the page loads the first time, you need to fill the list of teachers. Because you want any teacher the user selects to remain selected after getting the associated studentiDs, you should leave the ListBox.EnableViewState property set to true. By doing that, you implicitly agree to send all the teacher names back and forth for every request, but it means that you don't have to requery the database to get the teacher names for each request, nor do you have to maintain the ListBox state manually. Because there are only a few teacher names, that's probably a good decision. If there were hundreds of names, you'd probably want to maintain the state manually and avoid sending hundreds of names back and forth for each request.

You know that you're going to have to open a database connection each time the page loads, so you can define the SqlConnection variable at Page scope.

public class chl4 3 : System.Web.UI.Page {

protected System.Web.UI.WebControls.Label Labell; protected System.Web.UI.WebControls.ListBox lstTeachers; protected System.Web.UI.WebControls.DataGrid dgStudentIDs; protected System.Web.UI.WebControls.Label lblSelectedTeacher; SqlConnection conn = new SqlConnection

(ConfigurationSettings.AppSettings.Get("ClassRecordsSql")); // more code here

To fill the ListBox during the initial page load, you need to query the database for the names. Create a Command object and a DataReader. Set the Command properties, retrieve the data, and loop through the values.

private void Page Load(object sender, System.EventArgs e) { SqlDataReader dr = null; SqlCommand cm = new SqlCommand(); cm.Connection = conn; conn.Open(); if (!isPostBack) {

cm.CommandText = "Select firstname + ' ' + lastname as " +

"Name from Teachers order by lastname, firstname asc"; cm.Connection = conn; dr = cm.ExecuteReader(); while (dr.Read()) {

lstTeachers.items.Add(dr.GetString(0));

For now, the code uses a dynamic SQL query that tells SQL Server to concatenate each teacher's first and last names, placing a space between the names, and to sort the names by last name and then by first name, in ascending order. You could just as easily retrieve them in lastname, firstname order by concatenating a comma into the string. The as Name clause tells SQL Server to create an alias name. The returned column is a derived column that doesn't exist in the database. SQL Server names virtual columns with automatically generated and nonintuitive names, so you should get in the habit of setting alias names for all derived columns.

Now you've completed the code to display the teacher names. When a user clicks a teacher name, you want to run the qryGetStudentiDsFromTeachername stored procedure and display the Student-iDs associated with the selected teacher. You can place the code into the lstTeachers_Selectedindex-changed event, which fires whenever the user changes the ListBox selection.

private void lstTeachers SelectedindexChanged(object sender, System.EventArgs e) { String aName;

String[] names = new String[2]; SqlCommand cm = new SqlCommand(); SqlDataReader dr = null; if (lstTeachers.Selectedindex >= 0) { cm.Connection = conn;

aName = lstTeachers.Selecteditem.ToString(); lblSelectedTeacher.Text = "Students of " + aName; names = aName.Split(' '); /* One way to define the parameters.

* comment the following four lines to test the

* alternate version */

cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "qryGetStudentiDsFromTeachername"; cm.Parameters.Add(new SqlParameter("@lastname", names[1])); cm.Parameters.Add(new SqlParameter("@firstname", names[0]));

/* An alternate way to define parameters.

* uncomment the following five lines to test

* the alternate method.

* Be sure to comment the first version, above, before

* testing the alternate method */

//SqlParameter param = null;

//param = cm.Parameters.Add("@lastname",

SqlDbType.Char,20);

//param = cm.Parameters.Add("@firstname",

SqlDbType.Char,20);

dr = cm.ExecuteReader(); dgStudentIDs.DataSource = dr; dgStudentIDs.DataBind(); dr.Close();

The code first checks to ensure that the user has selected some item from the ListBox. In this case, because the database won't allow either the LastName or the FirstName field to be null, you can be sure that you'll have a valid name for each item. As written, you need to extract the individual names from the selected item. The code uses the Split method to retrieve a string array called names() containing the first and last names.

Next, the code creates a Command object, sets its Connection and CommandType properties (is this becoming familiar?), creates two Parameter objects, and appends them to the Command object's Parameters collection.

cm.Parameters.Add(new SqlParameter("@lastname", names[1])); cm.Parameters.Add(new SqlParameter("@firstname", names[0]));

There are several ways to create Parameter objects. At minimum, you must specify the parameter name and value. The Command object always has a Parameters collection. Use the Add method to add new parameters to the collection. The Add method returns a reference to the new Parameter object. In this case, because I already know the parameter value, I can create the Parameter and set its properties in one step. However, sometimes you want to create the Command object in advance and set the Parameter values later. To do that, you must specify the parameter name, type, and—for data types that vary in size—the size, in bytes. Here's an alternate version (commented out in the preceding code snippet) that defines and adds the two parameters before specifying the value.

SqlParameter param = null;

param = cm.Parameters.Add("@lastname", SqlDbType.Char, 20); param.Value = names[1];

param = cm.Parameters.Add("@firstname", SqlDbType.Char, 20); param.Value = names[0];

For large applications, it's sometimes useful to define all the Commands and cache them. In that case, you'll need to set the Parameter values by "finding" the parameters in the Parameters collection. For example:

// Define Command parameters during application initialization SqlParameter param = null;

cm.Parameters.Add("@lastname", SqlDbType.Char, 20); cm.Parameters.Add("@firstname", SqlDbType.Char, 20);

//... later, set the parameters by index or by name cm.Parameters[0].Value = <last name value>; cm.Parameters[1].Value = <first name value>;

// or cm.Parameters.Item[0].Value = <last name value>; cm.Parameters.Item[1].Value = <first name value>;

// or cm.Parameters.Item["@lastname"].Value = <last name value>; cm.Parameters.Item["@firstname"].Value = <first name value>;

Note Don't forget to set the Command object's CommandType property to

CommandType.StoredProcedure.

After appending the parameters to the Command object's Parameters collection, the lstTeachers_SelectedIndexChanged event code creates a DataReader and binds the DataGrid to it to display the values returned from the stored procedure query.

Finally, remember to close the Connection object. You can do that during the Page_Unload event.

private void Page Unload(object sender, System.EventArgs e) { if (conn! = null) {

if (conn.State == ConnectionState.Open) { conn.Close();

conn.Dispose();

When you run the ch14-3.aspx Web Form in a browser, it looks like Figure 14.7.

Figure 14.7: The ch14-3.aspx Web Form running in a browser

Was this article helpful?

0 0

Post a comment