Managed Commands with Stored Procedures
Although the example in Listing 3.5 allows you to evaluate data and output only what is desired, a more economical way to get a restricted result set is to use a SQL statement with a WHERE clause. Rather than querying the data store and bringing back all of the records in the table, you can write a SQL statement or stored procedure to bring back only the data you want. Stored procedures typically give better performance to your application than a SQL statement passed via a command.
You can use the Managed Command to call a stored procedure. In many cases, the stored procedure will have the parameters you pass it to determine the result set, such as the country abbreviation in the previous examples.
Listing 3.6 is the syntax for a SQL stored procedure. In the SQL Server Enterprise Manager, create a new stored procedure in the Northwind database named GetCustomersByCountry.
Warning
Listing 3.6 shows a stored procedure called GetCustomersByCountry. This must be added to the Northwind database before the following samples will work.
Listing 3.6. GetCustomersByCountry Stored Procedure for SQL Server Northwind Database
CREATE PROCEDURE [GetCustomersByCountry] @country varchar (50) AS SELECT * FROM Customers WHERE Country = @country |
When using a Managed Command to execute a stored procedure you must set the CommandType property. The default value of the CommandType property is Text. To execute a stored procedure you set the CommandType property to CommandType.StoredProcedure and pass in the name of the stored procedure.
Just as classic ADO command objects have a Parameters collection for passing parameters to the stored procedure, ADO.NET Managed Command classes (SqlCommand and OleDbCommand) also have a Parameters collection. You can call to a stored procedure, passing the required parameters in the collection using the following steps:
1. | |
2. |
Set the Managed Command's CommandType property to CommandType.StoredProcedure. |
3. |
Declare a parameter variable (SqlParameter or OleDbParameter). |
4. |
Add a new instance of the parameter class to the Managed Command's Parameters collection, passing in its name and data type (SqlDbType for SqlParameter and OleDbType for OleDbParameter—see
Appendix B for a list of valid SqlDbTypes and OleDbTypes). |
5. |
Set the parameter's Direction property. (Optional—“Input” is the default.) |
6. |
Set the Parameter's Value property. |
7. |
Repeat steps 4-6 for additional parameters. |
8. |
Execute the Managed Command. |
Listing 3.7 demonstrates these steps.
Warning
When you're using the SqlCommand object, the names of the parameters added to the Parameters collection must match the names of the markers in the stored procedure. The SQL Managed Provider will treat the parameters as named parameters, and it will look for markers in the stored procedure of the same name.
Listing 3.7. Calling a Parameterized Stored Procedure with a SqlCommand
[VB]
01: <%@ Page Language="VB" %>
02: <%@ Import Namespace="System.Data" %>
03: <%@ Import Namespace="System.Data.SqlClient" %>
04: <script runat="server">
05: Sub Page_Load(Sender As Object, E As EventArgs)
06: Dim myConnection As SqlConnection
07: Dim myCommand As SqlCommand
08: Dim myParameter As SqlParameter
09:
10: myConnection = New SqlConnection("server=localhost; database=Northwind; uid=sa; pwd=;")
11: myCommand = New SqlCommand("GetCustomersByCountry", myConnection)
12: myCommand.CommandType = CommandType.StoredProcedure
13: myParameter = myCommand.Parameters.Add(New SqlParameter("@country", SqlDbType
|
.VarChar, 50))
14: myParameter.Direction = ParameterDirection.Input
15: myParameter.Value = "USA"
16: myConnection.Open()
17: Dim myDataReader As SqlDataReader = myCommand.ExecuteReader()
18:
19: myDataGrid.DataSource = myDataReader
20: myDataGrid.DataBind()
21:
22: myConnection.Close()
23: End Sub
24: </script>
[C#]
01: <%@ Page Language="C#" %>
02: <%@ Import Namespace="System.Data" %>
03: <%@ Import Namespace="System.Data.SqlClient" %>
04: <script runat="server">
05: void Page_Load(Object sender, EventArgs e){
06: SqlConnection myConnection;
07: SqlCommand myCommand;
08: SqlParameter myParameter;
09:
10: myConnection = new SqlConnection("server=localhost; database=Northwind; uid=sa; pwd=;");
11: myCommand = new SqlCommand("GetCustomersByCountry", myConnection);
12: myCommand.CommandType = CommandType.StoredProcedure;
13: myParameter = myCommand.Parameters.Add(new SqlParameter("@country", SqlDbType
In Listing 3.7, you create a SqlConnection, SqlCommand, and SqlDataReader, the same as in previous listings. On line 8, you declare a SqlParameter object that will be used to create and add parameters to the SqlCommand. On line 13, you instantiate the SqlParameter object and set its name to @country, which is the same name given to the input parameter in the stored procedure in Listing 3.6. Additionally, you set the data type to SqlDbType.VarChar, 50, as required by the database table. On line 14, you set the SqlParameter's direction to ParameterDirection.Input, and you set the SqlParameter's Value to “USA” on line 15. Lastly, you open the connection and execute the command.
Note
In Listing 3.5 you created a series of new DataRow objects, and assigned their values after all the DataRows were created. The same approach can be used for SqlParameters.
myCommand.Parameters["@country"].Value = "USA";
Note
Input is the default direction for a Managed Command's parameter. There's no need to set a parameter's direction to Input explicitly. Listing 3.7 demonstrated how to set the parameter's Direction property. The possible values are ParameterDirection.Input and ParameterDirection.Output.