Executing SQL Server Stored Procedures phần 2

The output from this program is as follows: New ProductID = 81 Of course, depending on the existing rows in your Products table
Nội dung trích xuất từ tài liệu:
Executing SQL Server Stored Procedures phần 2The output from this program is as follows:New ProductID = 81Of course, depending on the existing rows in your Products table, youll get a differentresult.Executing the AddProduct2() Stored ProcedureAs youll see, the AddProduct2() procedure is similar to AddProduct(), except that it usesa RETURN statement instead of an OUTPUT parameter to return the ProductID for thenew row. Listing 8.13 shows the AddProduct2.sql script that creates the AddProduct2()procedure. Youll need to run this script before running the C# program.Listing 8.13: ADDPRODUCT2.SQL/* AddProduct2.sql creates a procedure that adds a row to the Products table using values passed as parameters to the procedure. The procedure returns the ProductID of the new row using a RETURN statement*/CREATE PROCEDURE AddProduct2 @MyProductName nvarchar(40), @MySupplierID int, @MyCategoryID int, @MyQuantityPerUnit nvarchar(20), @MyUnitPrice money, @MyUnitsInStock smallint, @MyUnitsOnOrder smallint, @MyReorderLevel smallint, @MyDiscontinued bitAS - declare the @MyProductID variable DECLARE @MyProductID int - insert a row into the Products table INSERT INTO Products ( ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued ) VALUES ( @MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit, @MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel, @MyDiscontinued ) - use the SCOPE_IDENTITY() function to get the last - identity value inserted into a table performed within - the current database session and stored procedure, - so SCOPE_IDENTITY returns the ProductID for the new row - in the Products table in this case SET @MyProductID = SCOPE_IDENTITY() RETURN @MyProductIDNotice the RETURN statement at the end to return @MyProductID. BecauseAddProduct2() doesnt return a result set of rows, you use the same four steps shown inthe previous section to execute the procedure using ADO.NET. The only difference is inthe construction of your EXECUTE command when setting the CommandText propertyin step 1. To call AddProduct2() you set the CommandText property of your Commandobject as follows:mySqlCommand.CommandText = EXECUTE @MyProductID = AddProduct2 @MyProductName, + @MySupplierID, @MyCategoryID, @MyQuantityPerUnit, + @MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, + @MyReorderLevel, @MyDiscontinued;Notice the change in the position of the @MyProductID parameter: it is shifted to justafter the EXECUTE and set equal to the value returned by AddProduct2(). This change ismade because Add-Product2() uses a RETURN statement to output the @MyProductIDvalue. The rest of the C# code required to call AddProduct2() is the same as that shownearlier in Listing 8.12.Note Because only the EXECUTE is different, Ive omitted the program that calls AddProduct2() from this book. You can see this program in the ExecuteAddProduct2.cs file Ive provided. Feel free to examine and run it.Executing a Stored Procedure that Does Return a Result SetIf your procedure does return a result set, then you use the following steps to execute it: 1. Create a Command object and set its CommandText property to an EXECUTE statement containing your procedure call. 2. Add any required parameters to your Command object, remembering to set the Direction property for any output parameters to ParameterDirection.Output. 3. Execute your command using the ExecuteReader() method, storing the returned DataReader object. 4. Read the rows in the result set using your DataReader object. 5. Close your DataReader object. You must do this before you can read any output parameters. 6. Read the values of any output parameters.In the following example, youll see a stored procedure named AddProduct3() that willreturn a result set along with an output parameter using a RETURN statement.The AddProduct3() procedure is similar to AddProduct2(), except that it also returns aresult set using a SELECT statement. This SELECT contains the ProductName andUnitPrice columns for the new row added to the Products table. This result set is returnedin addition to the ProductID of the new row, which is returned using the RETURNstatement. Listing 8.14 shows the AddProduct3.sql script that creates the AddProduct3()procedure. Youll need to run this script before running the C# program.Listing 8.14: ADDPRODUCT3.SQL/* AddProduct3.sql creates a procedure that adds a row to the Products table using values passed as parameters to the procedure. The procedure returns the ProductID of the new row using a RETURN statement and returns a result set containing the new row*/CREATE PROCEDURE AddProduct3 @MyProductName nvarchar(40), @MySupplierID int, @MyCategoryID int, @MyQuantityPerUnit nvarchar(20), @MyUnitPrice money, @MyUnitsInStock smallint, @MyUnitsOnOrder smallint, @MyReorderLevel smallint, @MyDiscontinued bitAS - declare the @MyProductID variable DECLARE @MyProductID int - insert a row into the Products table INSERT INTO Products ( ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued ) VALUES ( @MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit, @MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel, @MyDiscontinued ) - use the SCOPE_IDENTITY() function to get the last - identity value inserted into a table performed within - the current database session and stored procedure, - so SCOPE_IDENTITY returns the ProductID for the new row - in the Products table in this case SET @MyProductID = SCOPE_IDENTITY() - return the result set SELECT ProductName, UnitPrice FROM Products WHERE ProductID = @MyProductID - return @MyProductID RETURN @MyProductIDSince youve al ...