3. Using SQL Server Stored Procedures
A stored procedure is a procedure that is written
in SQL, stored in a SQL Server database, and executable by a client
program. It can have input and output parameters and returns an integer
value. A stored procedure can be as simple as a single SELECT
statement or as complex as a multitable update with parameter checking
and error handling. Stored procedures encapsulate functionality on the
server side.
One benefit of stored procedures is that they
execute much more quickly than the same logic would in a submitted batch
of SQL statements. With a stored procedure, the database engine
performs query analysis and optimization and caches the compiled query
optimization plan when the stored procedure is first created. On
subsequent executions of the stored procedure, the database engine can
bypass the analysis, optimization, and compilation. To execute a
submitted batch of SQL statements, on the other hand, the entire process
must be performed each time a batch is submitted.
Listing 2 shows a stored procedure that updates name and price information for a specified product. We use this stored procedure, named procModifyProductInfo,
during this discussion. It is not the most efficient code ever written,
but it is straightforward and readable, and it adheres to the SQL
Server convention that successfully executed procedures should return 0.
Listing 2. The procModifyProductInfo Stored Procedure
CREATE PROCEDURE procModifyProductInfo
@ProductID int = null,
@ProductName nvarchar(40) = null,
@UnitPrice money = null
AS
BEGIN
IF @ProductID is null
BEGIN
RAISERROR('Product ID not supplied.',10,1)
RETURN 1
END
IF not exists (SELECT *
FROM Products
WHERE ProductID = @ProductID)
BEGIN
RAISERROR('Product ID not on file.',10,1)
RETURN 1
END
BEGIN TRANSACTION
IF @ProductName is not null
UPDATE Products
SET ProductName = @ProductName
WHERE ProductID = @ProductID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Unable to update Products table.',10,1)
RETURN 1
END
IF @UnitPrice is not null
UPDATE Products
SET UnitPrice = @UnitPrice
WHERE ProductID = @ProductID
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Unable to update Products table.',10,1)
RETURN 1
END
COMMIT TRANSACTION
RETURN 0
END
|
Client programs execute
the stored procedure by submitting the following SQL statement, for
example, to the SQL Server. The sample statement shown here changes the
name and price of product 18 to “Carnavon Tigers” and $987.65,
respectively.
EXEC procModifyProductInfo 18, 'Carnavon Tigers', 987.65
To submit this SQL statement to the server, your application could set the statement into the CommandText property of a command object and then call the ExecuteNoResults
method. But there is a better way, one that makes it easier to
repetitively reexecute the same procedure with new parameter values each
time and to access the return value. In your application, perform the
following steps.
1. | Create the connection and command objects.
|
2. | Set the command object’s CommandType property to CommandType.StoredProcedure.
|
3. | Set the command object’s CommandText property to the name of the stored procedure.
|
4. | To the command object’s Parameters collection, add parameter objects for each parameter and for the return value.
|
5. | Set the input value for each parameter.
|
6. | Use the appropriate Execute method to cause the execution of the procedure at the server.
|
7. | Repeat steps 5 and 6 for each execution of the store procedure.
|
Step 4 is the most difficult of these steps. Once
again, if your development environment can connect to a development SQL
Server, Visual Studio .NET can generate the code for you, with some
help from you in the form of a few drag-and-drop operations, as follows.
Once you have created a stored procedure on the
development server, open a Windows Application project in Visual Studio
.NET and navigate the Server Explorer window to your development SQL
Server. Expand the server until you have reached the stored procedure;
then drag and drop the stored procedure onto the form, as shown in Figure 5.
The resulting command object is added below the
form, where you can select it and view its properties. When viewing the
properties, you may note that the command object has a Parameters collection that displays itself graphically as shown in Figure 6.
Getting
back to our example, we do not want the graphical representation of the
command; instead, we want the underlying code. So, we set a few final
properties for the command object in the Properties window, such as the
name (in this case cmndDB) and the connection, and then switch to Code View and expand the Windows Form Designer generated code region. As we proceed through the code, we come upon the generated code for our command, shown in Listing 3.
Listing 3. Designer-Generated Code to Create a Command Object
SqlCommand cmndDB = new SqlCommand();
:
:
//
// cmndDB
//
this.cmndDB.CommandText = "ModifyProductInfo";
this.cmndDB.CommandType = CommandType.StoredProcedure;
this.cmndDB.Connection = this.connNorthwind;
this.cmndDB.Parameters.Add(
new SqlClient.SqlParameter(
"@RETURN_VALUE", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false,
((System.Byte)(0)), ((System.Byte)(0)),
"", DataRowVersion.Current, null));
this.cmndDB.Parameters.Add(
new SqlClient.SqlParameter(
"@ProductID", SqlDbType.Int, 4));
this.cmndDB.Parameters.Add(
new SqlClient.SqlParameter(
"@ProductName", SqlDbType.NVarChar, 40));
this.cmndDB.Parameters.Add(
new SqlClient.SqlParameter(
"@UnitPrice", SqlDbType.Money, 4));
|
This
is exactly what we want and exactly what we did not want to write. So,
we cut and paste this code to move it from the Windows Application
project into our Smart Device Application project, pasting it into the UpdateSelectedRow routine. We remove some of the code that was in the previous version of our application, change all occurrences of SqlCe to Sql,
and add code to move values from the controls into the parameters and
to execute the command. Along the way we remove a few superfluous
references to this and add some minimal error handling. Listing 4 shows the resulting code.
Listing 4. Stored Procedure Version of the UpdateSelectedRow Routine
private void UpdateSelectedRow(int intProductID,
string strProductName)
{
// A connection and a command
SqlConnection connDB = new SqlConnection(strConn);
SqlCommand cmndDB = new SqlCommand();
try
{
// Open the connection.
connDB.Open();
// Initialize the command (including
// creating the parameters).
cmndDB.CommandText = "procModifyProductInfo";
cmndDB.CommandType = CommandType.StoredProcedure;
cmndDB.Connection = connDB;
cmndDB.Parameters.Add(
new SqlParameter(
"@RETURN_VALUE", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false,
((System.Byte)(0)), ((System.Byte)(0)),
"", DataRowVersion.Current, null));
cmndDB.Parameters.Add(
new SqlParameter(
"@ProductID", SqlDbType.Int, 4));
cmndDB.Parameters.Add(
new SqlParameter(
"@ProductName", SqlDbType.NVarChar, 40));
cmndDB.Parameters.Add(
new SqlParameter(
"@UnitPrice", SqlDbType.Money, 4));
// Assign values to the parameters.
cmndDB.Parameters["@ProductID"].Value =
int.Parse(comboKeys.SelectedItem.ToString());
cmndDB.Parameters["@ProductName"].Value =
textProductName.Text;
cmndDB.Parameters["@UnitPrice"].Value = 123.45;
// Execute the stored procedure.
cmndDB.ExecuteNonQuery();
// Check the SQL Server return value.
if((int)cmndDB.Parameters["@RETURN_VALUE"].Value != 0)
{
MessageBox.Show(
"You should have already caught a SqlException."
);
}
}
catch( SqlException exSQL )
{
foreach( SqlError errSQL in exSQL.Errors )
{
MessageBox.Show(errSQL.Message);
}
}
finally
{
// Close the connection.
connDB.Close();
}
}
|
We
placed all the code in one routine to make it more readable. In
reality, we would move any code that needed to be executed only once,
such as initializing the command and creating the parameters, into a
separate routine. What would remain is the code that had to be executed
every time, such as assigning values to the parameters, executing the
procedure, and examining the return value.
