Using T-SQL Stored Procedures with ASP.NET 2.0 - Creating a Stored Procedure with Input and Output Parameters
(Page 4 of 4 )
Stored procedures are similar to C# methods in that they define parameters; when you call them you pass values to those parameters. Stored procedures also pass values back to your code in the form of output parameters. One of the common uses of output parameters is with tables that are based on identity columns. In other words, you use an output parameter with a stored procedure that inserts a new row into a table, like the Northwind's Employees table, to return to the user (of your stored procedure) the new identity value for the EmployeeID column.
We will see how you can get the value of the OUTPUT parameter. This makes sense because you can't enter values for identity columns and, at the same time, you need to tell the user about the new identity value inserted into the table for this row. You normally will need this identity value for other operations in your code. Let's create a stored procedure called InsertEmployee that inserts a new row into the Employees table. The following is the stored procedure's code, which you need to execute in SQL Server Management Studio with Northwind as the current database.
CREATE PROCEDURE InsertEmployee
(@LastName NVARCHAR(20),
@FirstName NVARCHAR(10),
@Title NVARCHAR(30),
@City NVARCHAR(15),
@Country NVARCHAR(15),
@EmployeeID INT OUTPUT)
AS
INSERT INTO Employees(LastName,FirstName,Title,City,Country)
VALUES(@LastName,@FirstName,@Title,@City,@Country)
SET @EmployeeID = SCOPE_IDENTITY()
As you can see, the syntax for using input parameters with stored procedures is very easy. You simply define each parameter by preceding its name with the @ sign and define the data type for the parameter; you also separate the parameters using the colon. Note that we use one output parameter, which is the @EmployeeID, and we do that by using the keyword OUTPUT after the parameter name and its data type.
In the stored procedure's body we have used the INSERT statement to insert a new row based on the parameters passed to the stored procedure. Note how we assign the output parameter's value of the Stored Procedure. The SCOPE_IDENTITY() function is used to get the last IDENTITY value inserted at a column defined as IDENTITY in the current scope, and the scope in our case is the stored procedure. We simply assign the return value of this function to the output parameter @EmployeeID. Let's see how we can execute such a stored procedure.
DECLARE @NewEmployeeID INT
EXECUTE InsertEmployee @LastName = 'Joseph',
@FirstName = 'Joseph',
@Title = 'Sales Representative',
@City = 'London',
@Country = 'UK',
@EmployeeID = @NewEmployeeID OUTPUT
SELECT @NewEmployeeID
We declare a variable to hold the output parameter's value and use the EXECUTE keyword to execute the stored procedure. Note how we pass the values of the input parameters, simply by using the syntax parameter-name = value. Also note that we have used the keyword OUTPUT when passing the variable that will hold the output parameter's value, after the stored procedure is executed, so the syntax is outout-parameter-name = variable-name OUTPUT. Then we print the new IDENTITY value using the SELECT statement.
Executing the stored procedure from an ASP.NET page is a different issue. Let's see how we can do it. We will use SqlParameter objects and the SqlCommand.ExecuteNonQuery() method to do that. The following is the code of the Default.aspx, which contains the text boxes and the button that executes the code.
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="LastNameLabel" runat="server" Text="Last
Name"></asp:Label>
<asp:TextBox ID="LastNameTextBox" runat="server"></asp:TextBox>
<br />
<asp:Label ID="FirstNameLabel" runat="server" Text="First
Name"></asp:Label>
<asp:TextBox ID="FirstNameTextBox" runat="server"></asp:TextBox>
<br />
<asp:Label ID="TitleLabel" runat="server"
Text="Title"></asp:Label>
<asp:TextBox ID="TitleTextBox" runat="server"></asp:TextBox>
<br />
<asp:Label ID="CityLabel" runat="server"
Text="City"></asp:Label>
<asp:TextBox ID="CityTextBox" runat="server"></asp:TextBox>
<br />
<asp:Label ID="CountryLabel" runat="server"
Text="Country"></asp:Label>
<asp:TextBox ID="CountryTextBox"
runat="server"></asp:TextBox><br />
<asp:Button ID="InsertEmployeeButton" runat="server"
OnClick="InsertEmployeeButton_Click"
Text="Insert New Employee" /><br />
<asp:Label ID="ResultLabel" runat="server"></asp:Label></div>
</form>
</body>
</html>
In design view, double click on the InsertEmployeeButton and write the following code in the event handler method named InsertEmployeeButton_Click.
string connectionString = "Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True";
try
{
using (SqlConnection connection = new SqlConnection
(connectionString))
{
string commandText = "InsertEmployee";
SqlCommand command = new SqlCommand(commandText, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@LastName",
LastNameTextBox.Text);
command.Parameters.AddWithValue("@FirstName",
FirstNameTextBox.Text);
command.Parameters.AddWithValue("@Title", TitleTextBox.Text);
command.Parameters.AddWithValue("@City", CityTextBox.Text);
command.Parameters.AddWithValue("@Country",
CountryTextBox.Text);
SqlParameter outParameter = new SqlParameter();
outParameter.ParameterName = "@EmployeeID";
outParameter.Direction = ParameterDirection.Output;
outParameter.DbType = DbType.Int32;
command.Parameters.Add(outParameter);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 1)
{
ResultLabel.Text = "1 rows have been affected with this
operation";
}
ResultLabel.Text += "<br />" + "Employee ID = " +
command.Parameters["@EmployeeID"].Value;
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
When you run the page and enter values for the text boxes you will get a similar result to the one shown in the next screenshot.
I think by now you must be familiar with the SqlCommand and SqlParameter objects. In this event handler we have used the shortcut method AddWithValue() which accepts the name of the parameter, as a string value, and its value. Then it creates a SQLParameter object using this information and adds it to the Parameters collection of this command. we have passed the values that the user entered in the text boxes as values for those parameters. Note the code we have used to define the output parameter and get its value.
SqlParameter outParameter = new SqlParameter();
outParameter.ParameterName = "@EmployeeID";
outParameter.Direction = ParameterDirection.Output;
outParameter.DbType = DbType.Int32;
command.Parameters.Add(outParameter);
and the line
ResultLabel.Text += "<br />" + "Employee ID = " +
command.Parameters["@EmployeeID"].Value;
We created a SqlParameter object using the default constructor and set the parameter name using the SqlParameter.ParameterName property. Then we used the enumeration value ParameterDirection.Output as a value to the SqlParameter.Direction property to tell the command that this is an OUTPUT parameter that needs to be manipulated differently from input parameters; the default value for this property is ParameterDirection.Input. You must assign a data type for the output parameter or the command will not be executed.
We know that the @EmployeeID parameter is created as an INT data type on the server, so we used the enumeration value DbType.Int32 as the value for the SqlParameter.DbType property. We then added the output parameter to the SqlCommand.Parameters collection using the Add() method.
After calling the SqlCommand.ExecuteNonQuery() method the value of the output parameter is set and you can access it using the SqlCommand.Parameters[parametername].Value syntax as we did in the example.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
