Operating data 74 in asp.net 2.0: Creating Stored procedures and user-defined functions (the next section) with managed code (Part I) Self-study process

Step eighth: Call managed Stored from the presentation layer procedures

When the data access layer and business logic layer are expanded to support the invocation of the 2 managed stored procedures of getdiscontinuedproducts and Getproductswithpricelessthan, We can show the results of these stored procedures on a asp.net page.

Open the Managedfunctionsandsprocs.aspx page in the Advanceddal folder, drag a GridView control from the Toolbox to the designer, set its ID to discontinuedproducts, and bind it to a smart tag named Disconti Nuedproductsdatasource ObjectDataSource Control that sets the Getdiscontinuedproducts method for calling the Productsbllwithsprocs class class.

Figure 20: Calling the Productsbllwithsprocs class

Figure 21: Calling the Getdiscontinuedproducts method in the Select tab

Since we only need to display the product information, select "(None)" In the update, INSERT, and delete tags, then finish the configuration. Visual after completion Studio automatically adds BoundField columns or CheckBoxField columns to the columns of the Productsdatatable table. Delete all except ProductName and discontinued. So your GridView and ObjectDataSource declaration codes look like the following:

 <asp:gridview id= "discontinuedproducts" runat= "Server" autogeneratecolumns= " False "datakeynames=" ProductID "datasourceid=" Discontinuedproductsdatasource "> <Columns> <asp: BoundField datafield= "ProductName" headertext= "ProductName" sortexpression= "ProductName"/> <asp: CheckBoxField datafield= "discontinued" headertext= "discontinued" sortexpression= "discontinued"/> </Columns > </asp:GridView> <asp:objectdatasource id= "Discontinuedproductsdatasource" runat= "Server" oldvaluesparameterformatstring= "original_{0}" selectmethod= "Getdiscontinuedproducts" TypeName= " Productsbllwithsprocs "> </asp:ObjectDataSource> 

Take the time to log in to the page in your browser. When you log on, the ObjectDataSource control will call the Productsbllwithsprocs class's Getdiscontinuedproducts method. As we saw in step seventh, the method calls the Getdiscontinuedproducts method of the Productsdatatable class class in the DAL layer, The method also invokes the stored procedure getdiscontinuedproducts. The stored procedure returns products that are in the "discontinued" state. The result returned by the stored procedure is populated with a productsdatatable of the DAL layer, which is then returned to the BLL, which is then returned to the presentation layer and bound to a GridView control to display.

Photo: "Discontinued" products are listed

We can continue to practice more, such as placing a TextBox control and a GridView control on the page. Enter a number in the TextBox control, and the GridView control invokes the Getproductswithpricelessthan method of the Productsbllwithsprocs class class to display products that have a price lower than that number.

Step Nineth: Create and Invoke T-SQL UDFs

A user-defined function-called UDF-is a database object that is similar to a function definition in a programming language. Like the functions in C #, a UDF can contain a series of input parameters and return a specific type of value. A UDF either returns scalar data (scalar)- For example, a string, an integer, and so on; or return a table column data (tabular). Let's take a quick look at these 2 types of UDF, starting with the scalar data type first.

The following UDF is used to calculate the total price of a particular product. It has 3 input parameters--unitprice, UnitsInStock, Discontinued. It returns a value of a money type. It gets the total price by multiplying the UnitPrice by UnitsInStock, and in the "Discontinued" state, the total price is halved.

CREATE FUNCTION udf_computeinventoryvalue
(
 @UnitPrice money,
 @UnitsInStock smallint,
 @ Discontinued bit
)
RETURNS money
as
BEGIN
 DECLARE @Value decimal

 SET @Value = ISNULL (@ UnitPrice, 0) * ISNULL (@UnitsInStock, 0)

 IF @Discontinued = 1
 SET @Value = @Value * 0.5 return
 
 @Value
End

After you add the UDF to the database, we open management Studio, open the Programmability folder, open the Functions folder, and then open the Scalar-value functions folder. You can see the UDF. We can use this in a select query:

SELECT ProductID, ProductName, Dbo.udf_computeinventoryvalue
 (UnitPrice, UnitsInStock, discontinued) as Inventoryvalue from the products order by
Inventoryvalue DESC

I have added the Udf_computeinventoryvalue user function to the Northwind database. Figure 23 is the output from the call to the Select query in Management Studio.

Figure 23: The total price for each product is listed

The UDF can also return table column data. For example, we can create a UDF to return all products belonging to a category:

CREATE FUNCTION Dbo.udf_getproductsbycategoryid
( 
 @CategoryID int
)
RETURNS TABLE
as Return
(
 SELECT ProductID, ProductName, SupplierID, CategoryID,
  QuantityPerUnit, UnitPrice, UnitsInStock , UnitsOnOrder,
  ReorderLevel, discontinued from the products
 WHERE CategoryID = @CategoryID
)

The Udf_getproductsbycategoryid user function takes a @categoryid input parameter and returns the result of the select query. Once created, the UDF can be in the from (or JOIN) of a select query . The following example returns the ProductID, Productname,categoryid value of each product to which the beverage class belongs:

SELECT ProductID, ProductName, CategoryID from
Dbo.udf_getproductsbycategoryid (1)

I have added the Udf_getproductsbycategoryid user function to the Northwind database. Figure 24 shows the results of running the select query in Management Studio. The UDF that returns the table column data is placed in the Table-value functions folder.

Figure 24: ProductID of beverage products, Productname,categoryid are listed.

　　Note: For more information on creating and using UDF, see the article "Intro to user-defined Functions" and "dvantages and drawbacks of user-defined functions"

Tenth step: Create a managed UDF

The Udf_computeinventoryvalue and Udf_getproductsbycategoryid user functions created in the previous example are all T-SQL database objects. SQL Server 2005 also supports the managed UDF, We can add it to the Manageddatabaseconstructs project, as we did in the third and fifth steps. In this step, we will execute the Udf_computeinventoryvalue user function with managed code.

Right-click in the Solution Explorer, select "Add a new Item", choose user-defined Function Template in the dialog box, and name the new UDF file Udf_computeinventoryvalue_managed.cs.

Figure 25: Add a managed UDF to the Manageddatabaseconstructs project

The user-defined function template creates a partial class classes named Userdefinedfunctions, along with a method that has the same name as the class file (for this example, the Udf_ computeinventoryvalue_managed). The method has a SqlFunction attribute, which indicates that the method is a managed UDF.

Using System;
Using System.Data;
Using System.Data.SqlClient;
Using System.Data.SqlTypes;
Using Microsoft.SqlServer.Server;

public partial class Userdefinedfunctions
{
 [Microsoft.SqlServer.Server.SqlFunction] public
 static SqlString udf_computeinventoryvalue_managed ()
 {
 //Put your code here to return to
 new SqlString ("Hello");
 }
}

The Udf_computeinventoryvalue method currently returns a SqlString object and does not accept any input parameters. We'll update it to include 3 parameters--unitprice, UnitsInStock, and discontinued, and returns a SqlMoney object. The method uses the same logic as the Udf_computeinventoryvalue user function of the T-SQL type above.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_computeinventoryvalue_managed
 (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
 SqlMoney inventoryvalue = 0;

 if (! Unitprice.isnull &&! Unitsinstock.isnull)
 {
 Inventoryvalue = UnitPrice * UnitsInStock;

 if (discontinued = = true)
  Inventoryvalue = inventoryvalue * New SqlMoney (0.5);
 }

 return inventoryvalue;
}

We note that the input parameters for the UDF method are the corresponding SQL types: The UnitPrice type is SqlMoney, the UnitsInStock type is SqlInt16, The type of discontinued is SqlBoolean. These types reflect the types defined in the Products table: UnitPrice columns are of type money, UnitsInStock columns are of type smallint, The type of the Discontinued column is bit.

The code first creates an instance of the SqlMoney type named Inventoryvalue and assigns a value of 0. Because the Products table allows the values of the Unitsinprice and UnitsInStock columns to be null, So we first check to see if these 2 columns are packets of NULL values by SqlMoney the IsNull property of the object. If the values of these 2 columns are NOT NULL, then UnitPrice times UnitsInStock Gets the Inventoryvalue value, and if discontinued is true, the Inventoryvalue value is halved.

　　Note: Since the SqlMoney object only allows 2 SqlMoney instances to multiply, it does not allow a SqlMoney instance to be multiplied by a floating-point number (literal floating-point). So in the code we multiply the Inventoryvalue by a SqlMoney instance with a value of 0.5.

11th Step: Configure the managed UDF

Now that we have created a managed UDF, we will configure it to the Northwind database. As we saw in step fourth, in Solution Explorer, right-click the project name and select "Deploy".

When you are done, return to SQL Server Management Studio and refresh the scalar-valued functions folder. You'll see 2 entities:

. dbo.udf_computeinventoryvalue--the T-SQL UDF created in step Nineth

. dbo.udf computeinventoryvalue_managed--The Managed UDF we just created in step 10th

Test the managed UDF and execute the following query in Management Studio:

SELECT ProductID, ProductName,
 dbo.udf_computeinventoryvalue_managed (
   UnitPrice,
   UnitsInStock,
   Discontinued
  ) as Inventoryvalue from the
inventoryvalue DESC

The command uses the UDF computeinventoryvalue_managed function instead of the Udf_computeinventoryvalue function, but the output is the same, and you can view the screenshot of Figure 23.

12th step: Debug Managed Database Objects

In the 72nd chapter we explored 3 modes of debugging SQL Server through Visual Studio: Direct Database debugging, application debugging, debugging through SQL Server project. Managed Database Objects cannot be debugged in direct database mode, but can be debugged from a client program and SQL Server project. For debugging to work correctly, SQL Server 2005 database requirements must allow sql/ CLR Debugging. Remember when we first created the Manageddatabaseconstructs project, Visual Studio asked us whether to activate SQL/CLR debugging (see Figure 6 in step 2nd). We can be in the server Right-click on the database in the Explorer window to modify the configuration.

Figure 26: Ensuring database activation SQL/CLR debugging

Imagine that we want to debug Getproductswithpricelessthan stored procedures. We first set breakpoints in the code of the Getproductswithpricelessthan method.

Figure 27: Setting breakpoints in the Getproductswithpricelessthan method

First we examine debugging managed database objects from SQL Server project.

Because our Solution Explorer contains 2 engineering--manageddatabaseconstructs SQL Server project and our website. To debug from SQL Server Project, When debugging, we need to guide Visual Studio to open Manageddatabaseconstructs SQL Server Project. Manageddatabaseconstructs in the Solution Explorer Right-click on Project to select the Set as StartUp project item.

When you open Manageddatabaseconstructs project from the debugger, it executes the SQL statements of the Test.sql file, which is located in the test Scripts folder. For example, to test the Getproductswithpricelessthan stored procedure, replace the contents of the Test.sql file with the following statement. These statement call Getproductswithpricelessthan stored procedures whose input parameters @categoryid a value of 14.95:

EXEC Getproductswithpricelessthan 14.95

Once you have typed the above script into the Test.sql file, click the "Start Debugging" item in the Debug menu, or press F5 or the green icon on the toolbar to start debugging. This will build the project in the Explorer, and the managed database Objects is configured to the Northwind database and then executes the Test.sql script. At this point, you will encounter breakpoints, we can enter the Getproductswithpricelessthan method, check the value of the input parameters, and so on.

Figure 28: Hit the breakpoint in the Getproductswithpricelessthan method

In order to debug a SQL database object from a client program, the database must be configured to support application debugging. In Server Explorer, right-click on the database to ensure that the application debugging item is selected. In addition, we will combine the ASP.net application with the SQL debugger and close the connection pool. These steps are discussed in detail in the 2nd step of chapter 74th.

Once you have configured the ASP.net application and database. Set the ASP.net website as the startup scheme. If you log in to a page that invokes the managed objects of the breakpoint, the program will hit the breakpoint and switch to the debugger, where you can enter the code, Just like Figure 28.

13th step: Manually compile and configure the managed Database Objects

With SQL Server Projects, we can easily create, compile, and configure managed database objects. Unfortunately, only the professional and team in Visual Studio Systems These 2 versions can use SQL Server Projects. If you are using the visual Web Developer or Standard Edition version, and you intend to use managed database objects, You need to create and configure them manually. This will include 4 steps:

1. Create a file to store the source code of managed database object

2. To compile the object

3. Register the compiled files with the SQL Server 2005 database

4. Create a database object in SQL Server and point to the corresponding method in the compiled file

For demonstration purposes, we will create a new managed stored procedure that returns products with UnitPrice values above the specified value. Create a name on your computer named GetProductsWithPriceGreaterThan.cs New file and type the following code (you can use Visual Studio, Notepad, or any text editor):

 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTy
pes

Using Microsoft.SqlServer.Server; Public partial class StoredProcedures {[Microsoft.SqlServer.Server.SqlProcedure] public static void Getproductswithpri
 Cegreaterthan (SqlMoney price) {//Create the command SqlCommand mycommand = new SqlCommand (); myCommand.CommandText = @ "Select ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, Unitsins

 tock, UnitsOnOrder, ReorderLevel, discontinued from the products WHERE UnitPrice > @MinPrice ";

 MyCommand.Parameters.AddWithValue ("@MinPrice", price);
 Execute the command and send back the results SqlContext.Pipe.ExecuteAndSend (mycommand); }
}

The code is very similar to the code we created in step fifth for the Getproductswithpricelessthan method. The only difference is that the method name is different, where the sentence is different, and the name of the parameter used by the query is different. Returns to the Getproductswithpricelessthan method where the WHERE clause is "where UnitPrice < @MaxPrice." And here, in the Getproductswithpricegreaterthan method, the code is "WHERE UnitPrice > @MinPrice."

We now need to compile the class. Navigate to the root directory of your GetProductsWithPriceGreaterThan.cs file in the command line and compile using the C # compiler (csc.exe):

Csc.exe/t:library/out:manuallycreateddbobjects.dll GetProductsWithPriceGreaterThan.cs

If the folder containing csc.exe is not located in the system path, you will have to fully reference its path,%windows%/microsoft.net/framework/version/, for example:

C:/windows/microsoft.net/framework/v2.0.50727/csc.exe/t:library/out:manuallycreateddbobjects.dll GetProductsWithPriceGreaterThan.cs

Figure 29: Compiling the GetProductsWithPriceGreaterThan.cs file

where the/t tag specifies that the C # class class is compiled into a DLL instead of an executable file. The/out tag specifies the name of the compiled file.

　　Note: In addition to using the command line to compile GetProductsWithPriceGreaterThan.cs class classes, we can also use Visual C # Express Edition or in Visual Studio Standard Create a separate class Library project in the edition version. S?ren Jacob Lauritsen has provided us with a Visual C # Express Edition project, which contains Getproductswithpricegreaterthan stored procedures, as well as our 3rd, The 2 managed stored procedures and UDF created in steps 5 and 10 also contain the T-SQL commands required to add the corresponding database objects.

After compiling the code, we need to register it with the SQL Server 2005 database. You can use the T-SQL, command create ASSEMBLY, or through SQL Server Management Studio. Let's look at using Management Studio.

In Management Studio, expand the Programmability folder in the Northwind database, which has a assemblies folder. Right-click on the folder and select New Assembly. This will open the New Assembly dialog box (see Figure 30), click the Browse button, Select the ManuallyCreatedDBObjects.dll file we just compiled and click OK to complete the Add. You should be able to see the ManuallyCreatedDBObjects.dll file in Object Explorer.

Figure 30: Adding ManuallyCreatedDBObjects.dll to the database

Figure 31:manuallycreateddbobjects.dll Show in Object Explorer

When we're done, we'll link a stored procedure to the Getproductswithpricegreaterthan method in the compiled file. To do this, open a new query window and execute the following script:

CREATE PROCEDURE [dbo]. [Getproductswithpricegreaterthan]
(
 @price [numeric] (0)) with
EXECUTE as CALLER as
EXTERNAL NAME [manuallycreateddbobjects].[ StoredProcedures]. [Getproductswithpricegreaterthan]
Go

This creates a new stored procedure named Etproductswithpricegreaterthan in the Northwind database and links it to the Getproductswithpricegreaterthan method ( This method belongs to the compilation file manuallycreateddbobjects)

After the script is executed, refresh the stored Procedures folder in Object Explorer. You will see a new stored procedure--getproductswithpricegreaterthan with a lock icon next to the stored procedure. Test the stored procedure, type and execute the following script in the query window:

EXEC Getproductswithpricegreaterthan 24.95

As shown in Figure 32, the above command shows those products with a price above 24.95.

Figure 32: List of Display in Object Explorer

Conclusion:

Microsoft SQL Server 2005 consolidates the common Language Runtime (CLR), which allows you to create database objects with managed code. Previously, to create a database object, we could only use T-SQL, But now we can use. NET programming languages, such as C # to create. In this article we created 2 managed stored procedures and one managed user-defined Function.

Visual Studio's SQL Server project type makes it easy to create, compile, configure managed database objects, and also supports multiple debugging. Unfortunately, the SQL Server project type is only available in visual Studio's professional and team systems versions are available. For the visual Web Developer or standard version of the user, to manually complete these steps, as we saw in the 13th step.

I wish you a happy programming!

Introduction of the author

Scott Mitchell, author of this series of tutorials, has six asp/asp. NET book, is the founder of 4GuysFromRolla.com, has been applying Microsoft Web technology since 1998. You can click to see all Tutorials "[translation]scott Mitchell asp.net 2.0 data tutorial," I hope to learn asp.net help.