Using Output Parameters with a Stored Procedure

One of the ways that you can use a stored procedure is to have it return data through output parameters. Then after the call to the stored procedure you can display the values returned to you. For example, you might use a stored procedure to return summary information about a table. This could be done through output parameters. The technique presented in this section of the chapter shows you how to use output parameters.USE IT A Command object contains a collection of Parameter objects. These parameters are values that the stored procedure passes back to you after the call is complete. This ASP.NET page calls a stored procedure that returns values. The values returned are the total number of records in the Employees table and the name of the employee who is paid the most.

Sending Data to a Stored Procedure Through Input Parameters

If you are working towards making your ASP.NET application more efficient and that application connects to an SQL Server database, you will want to create stored procedures that do basic things like adding records, deleting records, and other such tasks. With these types of stored procedures, you need to pass data into them. This technique shows you how to call a stored procedure that needs to have parameters passed into it.USE IT The ASP.NET page created for this technique allows visitors to add a record to the Employees table. Instead of using an Insert statement in the ASP.NET code, a stored procedure is called to add the record Notice that the stored procedure has five input parameters. Those parameters are used in the Insert statement in the stored procedure.On the ASP.NET page, visitors enter the values for the new employee record into TextBox control. When they click the Button control, this procedure fires, adding the new record by calling the stored procedure.

Working with Transactions with an SQL Server Database

Transactions provide a way for you to group together database executions as a group so that they succeed or fail together. For example, if you had an e-commerce site you may have code that allows visitors to add a quantity of an item to their shopping cart. When you do that you also want to remove the number of items ordered from your inventory.Therefore, you have two actions that you need to execute. You want to add items to a shopping cart and you want to remove items from inventory.These executions need to happen as a group. You don’t want to add items to the shopping cart if something goes wrong with removing them from inventory. And the opposite is also true.Therefore, the database executions need to be grouped in a Transaction. This technique shows youhow to use a Transaction object with an SQL Server database.This ASP.NET page contains SQL Delete statements that delete records from the Employees table. But the records are not deleted because they are in a transaction and the transaction is not committed to the database.

Updating SQL Server Data by ASP.Net

Another type of query that you will often need to run on your ASP.NET pages that include database connectivity are Update queries. Update queries allow you to modify existing records in an SQL Server database table. This technique shows you how to run an Update query from your ASP.NET code.The page presented with this technique allows the visitor to give all the employees in the Employees table a raise. The visitor enters the factor for the raise and every employee has their salary increased by that factor.Defined on the ASP.NET page is this TextBox control for entering the raise amount.

Iterating Through Records in an SQL Server Table

In many situations you no longer need to iterate through records in an SQL Server table in ASP.NET pages as you did with ASP. This is because you can now bind DataSet Tables directly to controls.

But on occasion you may still need to iterate through all the records in a DataSet Table so that they can be processed in some special way.

The DataSet Table object contains a Rows collection that allows you to iterate through each record it contains. This technique shows you how to create a loop so that you can process each record from an SQL Server table through a DataSet Table object.