Operating data 73 in asp.net 2.0: Creating Stored procedures and user-defined functions (above) with managed code (part) _ Self-Study process

Introduction:

databases, such as Microsoft's SQL Server 2005, use transact-structured Query Language (T-SQL) to insert, modify, Retrieves data. Most database systems contain constructs to group A series of SQL statements that can be executed as separate units. A stored procedure is an example, and another example is a user-defined function (UDFs). We will discuss in detail in the 9th step.

SQL is designed to handle a range of data. Select,update, and DELETE statements apply to all records of the corresponding table and are filtered by the WHERE clause. There are also a number of features that are designed to process one record at a time, or to manipulate scalar data (scalar). For example, cursors allows all records to be traversed at one time. String manipulation functions, such as left, CHARINDEX, and patindex are used to process scalar data. SQL also contains control flow declarations, such as if and while.

Prior to Microsoft SQL Server 2005, stored procedures and user-defined function UDFs can only be created as a T-SQL statements set, while SQL Server 2005 design contains common Language Runtime ( CLR). Therefore, we can use managed code to create a stored procedure and user-defined method in a SQL Server 2005 database. It also says that you can create a stored procedure or user-defined function in a C # class. This allows us to use these stored procedures or methods within the. NET framework or your own defined classes.

In this article we examine how to create stored procedures and user-defined functions, and how to integrate them into the database Northwind. Let's get started.

　　Note: Managing Database Objects (Managed DB objects) has some advantages over the corresponding database objects contained in the SQL database, mainly in the following: Language richer and more familiar ; You can use existing code and logic. But it may be less efficient when dealing with a series of data that does not contain a lot of logic . For the advantage of Managed Code compared to T-SQL, see the article "Advantages of Using Managed code to Create Database Objects" (http:// Msdn2.microsoft.com/en-us/library/k2e1fb36 (vs.80). aspx)

First step: Move the Northwind database out of the App_Data folder

The tutorial has so far used a database of Microsoft SQL Server Express version in the App_Data folder.

In this tutorial, however, we move the Northwind database out of the App_Data folder, and then use an instance that is registered as a SQL Server, express version of the database. Although we can perform these steps in this article without moving out, these steps will be much simpler if you register it as an instance of a SQL Server Express version database.

The download code for this article contains 2 database files: Northwnd. MDF and Northwnd_log. LDF, put them in a folder called Datafiles, First close visual Studio, then Northwnd.mdf and Northwnd_log. LDF files are moved from the App_Data folder in the root directory to a folder other than the root directory. When we are done, we need to register the Northwind database as an instance of the SQL Server Express version database. For this we are going to use SQL Server Management Studio. If you do not install it, you can download and install it here: (http://www.microsoft.com/downloads/details.aspx? displaylang=en&familyid=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796).

Open SQL Server Management Studio, as shown in Figure 1, Management Studio first asks us what server to connect to, type "localhost/sqlexpress" in Server name, Select "Windows Authentication" in the authentication Drop-down list. Point Connect.

Figure 1: Connecting the appropriate database instance

Once connected, the Object Explorer window will display information about the SQL Server version database instance, such as databases, security information, management options, etc. .

We need to use the Northwind database in the Datafiles folder as an instance of the SQL Server Express version database. Right-click in the Databases folder and select the "Attach" item. This opens the Attach Databases dialog box. Click the Add button, locate the Northwnd.mdf file, and then click OK. In this way, your screen looks similar to Figure 2:

Figure 2: Connecting to the appropriate database instance

Note: The Attach Databases dialog box does not allow you to browse the user's private files directory when you connect to a database instance of SQL Server Express version via Management Studio (user profile directories , such as my Documents. Therefore, be sure to Northwnd.mdf and Northwnd_log. The LDF file is placed in a non-user private file directory.

When OK is finished, the Attach Databases dialog box closes, and Object Explorer lists the newly added database. The problem is, its name may be: 9fe54661b32fdd967f51d71d0d5145cc_line articles/datatutorials/volume 3/csharp/73/aspnet_data_ Tutorial_75_cs/app_data/northwnd. MDF, we rename it to "Northwind" by right-clicking the database and selecting "Rename".

Figure 3: Renaming the database to "Northwind"

Step Two: Create a new solution and SQL Server Project in Visual Studio

To create an administrative stored procedure or user-defined function in SQL Server 2005, we write these stored procedures and user-defined functions in a class with C # code. Once written, we need to compile the class into a. dll file, register the compiled file with a SQL Server database, and then create a stored procedure or user-defined function in the database to point to the corresponding method in the compiled file. These steps are to be done manually. We can write code in a text editor, compile it on the command line using the C # compiler (csc.exe), and register it with the Create ASSEMBLY command or from Management Studio. The method for adding a stored procedure or user-defined function is similar. Luckily, The professional and Team Systems version of Visual Studio contains a SQL Server project type that can automate these tasks. In this article, we will use SQL Server Project type to create an administrative stored procedure and user-defined function.

　　Note: If you are using Visual Studio's visual Web Developer or standard version, you will have to do it manually. In step 13, we will detail the details of the manual completion. We encourage you to see steps 12 from Steps 2, and then step 13. Because steps 2 to steps 12 contains important SQL Server configuration instructions, no matter what version you are using.

Open Visual Studio. From the File menu, select New Project to open the New Project dialog box (see Figure 4). Point to Database project type, on the right of the templates list, choose to create a new SQL Server Project. I named it manageddatabaseconstructs and put it in a solution called Tutorial75.

Figure 4: Creating a new SQL Server Project

Click the OK button in the New Project dialog box to create the solution and SQL Server Project.

A SQL Server project relies on a specific database. So, next we want to specify this information. As shown in Figure 5, the New Database Reference dialog box points to the Northwind database, which is the SQL Server Express version database instance that we registered in the first step.

Figure 5: Linking SQL Server project to the Northwind database

In order to debug the management stored procedures and user-defined functions that we are going to create in this project, we need to activate SQL/CLR debugging support. Whenever you associate a SQL Server project with a new database (as we did in Figure 5), Visual Studio will ask us whether to activate SQL/CLR debugging (Figure 6) and select Yes.

Figure 6: Activating SQL/CLR debugging

At this point, this new SQL Server project has been added to the solution. It contains a test scripts folder with a Test.sql file in the folder. It is used to debug the Management database object added in this project, we will examine and debug in the 12th step.

We can now add new administrative stored procedures and user-defined functions to the project. But before we do, we want to include existing Web applications in the solution. Select the Add item in the File menu, and then select existing Web Site. Browse to the appropriate folder and click OK. As shown in Figure 7, this will update the solution to include 2 projects: that is, website and SQL named Manageddatabaseconstructs Server Project.

Figure 7: The solution now contains 2 projects

The northwndconnectionstring value in the Web.config file currently refers to the Northwnd.mdf in the App_Data folder. Since we have removed it from the App_Data folder and registered it as an instance of the SQL Server Express version database, we need to update the northwndconnectionstring value accordingly. Open the Web.config file and change the northwndconnectionstring value, like this: "Data source=localhost/sqlexpress;initial catalog=northwind;i Ntegrated security=true ".

When you're done, the <connectionStrings> node of your Web.config file looks similar to the following:

<connectionStrings>
 <add name= "northwndconnectionstring" connectionstring=
  "Data source= Localhost/sqlexpress;initial Catalog=northwind;
   Integrated security=true; Pooling=false "
  providername=" System.Data.SqlClient "/>
</connectionStrings>

Note: As discussed in the previous chapter, when debugging a SQL Server object from a client program such as a asp.net website, we need to close the connection pool. In the connection string above we closed the connection pool ("Pooling=false"). Activate the connection pool if you do not intend to debug the managed stored procedures and user-defined functions from ASP.net website.

Step three: Create a managed Stored Procedure

To add an administrative stored procedure to the Northwind database, we first want to create a stored procedure as a method in this SQL Server project. From the Solution Explorer, right-click the Manageddatabaseconstructs project and select Add New item, which will show the Add the Item dialog box, which lists the types of management database objects that can be added to the project, as shown in Figure 8, including the stored Procedures, user-defined Functions and so on. Let's create a stored procedure that simply returns products that are in the discontinued state and names the stored procedure file as GetDiscontinuedProducts.cs.

Figure 8: Adding a new stored procedure named GetDiscontinuedProducts.cs

This creates a new C # class class file, as follows:

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

public partial class StoredProcedures
{
 [Microsoft.SqlServer.Server.SqlProcedure] public
 static void Getdiscontinuedproducts ()
 {
  //Put your code here
 }
}

We note that the stored procedure executes as a static method and is located within a partial class (partial Class) file named StoredProcedures. In addition, the Getdiscontinuedproducts method has a SqlProcedure attribute, which indicates that the method is a stored procedure.

The following code creates a SqlCommand object and sets its CommandText to a select query to return products All discontinued in the table are listed as 1 records. It then executes the command and returns the results to the client program. Add the code to the Getdiscontinuedproducts method.

Create the command
SqlCommand mycommand = new SqlCommand ();
myCommand.CommandText =
  @ "Select ProductID, ProductName, SupplierID, CategoryID,
    QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
    ReorderLevel, discontinued from the products
  WHERE discontinued = 1 ";

 
Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend (mycommand);

All management database objects can use the SqlContext object, which shows the contents of the caller (caller), and SqlContext can access a SqlPipe object through its pipe property, which is used in SQL The server database communicates with the caller, and the Executeandsend method executes the incoming SqlCommand object, as its name implies, and returns the result to the client program.

　　Note: Managing database objects is best for such stored procedures and user-defined functions-using procedural logic logic instead of set-based logic logic. The so-called procedural logic logic includes processing a series of rows (on a Row-by-row basis data or processing scalar data (scalar). However, the Getdiscontinuedproducts method we just created does not use procedural logic logic. In fact, the best way to do this is as a T-SQL stored procedure. It is performed as a management stored procedure to demonstrate the steps necessary to create and configure a managed stored procedure.

Step 4: Configure managed Stored Procedure

After the code is complete, we are ready to configure it to the Northwind database. " The concrete steps for the implementation of Deploy "We will be clear in the 13th step." Go to Solution Explorer, right-click on the Manageddatabaseconstructs project name, and select the "Deploy" item, however, you may receive the following error message: "Incorrect syntax near ' EXTERNAL '." You could need to set the compatibility level of the ' current ' to ' a higher value to ' enable this feature. The stored procedure sp_dbcmptlevel.

This error message occurs when an attempt is made to register a compiled file with the Northwind database. To register a compiled file with a SQL Server 2005 database, the compatibility level of the database must be set to 90. By default, a new SQL Server The compatibility level of the 2005 database is 90. And the default compatibility for databases used by Microsoft SQL Server 2000 Level is 80. Because the Northwind database used is originally a Microsoft SQL Server 2000 database, its compatibility level is set to 80. Therefore, it needs to be set to 90 for registration. To update the compatibility level of the database, open a new query window in Management Studio, and enter: Exec sp_dbcmptlevel ' Northwind ', 90 Click the Execute icon on the toolbar to run the query above.

Figure 9: Updating the compatibility level of the Northwind database

After you update the SQL Server Project, you should not make a mistake this time. Return to SQL Server Management Studio, right-click on the Northwind database in Object Explorer, and select Refresh. Next, Locate the Programmability folder, and then expand the Assemblies folder. As shown in Figure 10, the Northwind database now contains a compiled file generated by the Manageddatabaseconstructs project.

Figure 10: The manageddatabaseconstructs compilation file is now registered with the Northwind database

Also to open the stored Procedures folder. You will see a stored procedure called Getdiscontinuedproducts. The stored procedure was created at the time of deployment, It points to the Getdiscontinuedproducts method in the Manageddatabaseconstructs compilation file. When the getdiscontinuedproducts stored procedure is executed, it performs getdiscontinuedproducts Method. Because it is a management stored procedure (managed stored procedure), it cannot be edited through Management Studio (therefore, there is a lock icon next to the name of the stored procedure)

Figure 11:getdiscontinuedproducts Stored procedure display in Stored Procedures folder

There is another hurdle to overcome: The database is configured to prevent managed code from executing. Let's do an experiment. Open a new query window and execute the getdiscontinuedproducts stored procedure. You will receive the following error message: "Execution of user code in the". NET Framework is disabled. Enable ' CLR enabled ' configuration option.

Let's check the configuration information for the Northwind database and type and run the command "exec sp_configure" in the query window. The display of "CLR enabled" is currently set to 0.

figure: "CLR Enabled" is currently set to 0.

We notice that each configuration (Figure 12) has 4 values: "Minimum", "maximum", "config", "run" value. To update the "config" value of the CLR enabled configuration, perform the following command: exec sp_configure ' CLR enabled ', 1

If you run "exec sp_configure" again, you will see that the declaration sets the "config" value of the CLR enabled configuration to 1, while the "run" value is still 0. Therefore, we need to execute the RECONFIGURE command, which will put the "run" Value to the current "config" value. Enter "Reconfigure" in the query window, and then click the Execute icon on the toolbar. If you run "exec sp_configure," Now you can see "config" and "run" for "CLR enabled" configuration. "Value is 1.

After the CLR enabled configuration is complete, we are ready to run the getdiscontinuedproducts stored procedure. Type and run the command "exec getdiscontinuedproducts" in the query window. Invoking the stored procedure will result in the execution of the corresponding managed code in the Getdiscontinuedproducts method. The code emits a select query and returns all products in the discontinued condition and returns the data to the calling program----in this case Is that SQL Server Management studio.management Studio will receive the data displayed in the results window.

figure 13:getdiscontinuedproducts Stored procedure returns all products in discontinued state

Step Fifth: Create a managed Stored procedures that receives input parameters

Many of the queries and stored procedures that we create in this tutorial use parameters. For example, in the 67th chapter, we created a stored procedure called Getproductsbycategoryid, which receives a name @ CategoryID input parameters. The stored procedure returns products whose CategoryID value matches the @categoryid.

To create a managed stored procedure that receives input parameters, You can only specify these parameters when you define the method. Let's do a demo and add another managed named Getproductswithpricelessthan to the manageddatabaseconstructs project stored procedure. The managed stored procedure receives a specified price parameter and returns all products whose UnitPrice column is below the parameter value.

Let's add it. Right-click on the Manageddatabaseconstructs project name and select Add New stored procedure. Name the file GetProductsWithPriceLessThan.cs. As we saw in Figure 3, this creates a new C # class class file.

Update the Getproductswithpricelessthan method so that it receives an input parameter of the SqlMoney type named price. The code is as follows:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void Getproductswithpricelessthan (SqlMoney price)
{
 //Create the command
 SqlCommand mycommand = new SqlCommand ();
 myCommand.CommandText =
   @ "Select ProductID, ProductName, SupplierID, CategoryID,
     QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
     ReorderLevel, discontinued from the products
   WHERE UnitPrice < @MaxPrice ";

 MyCommand.Parameters.AddWithValue ("@MaxPrice", price);

 Execute the command and send back the results
 SqlContext.Pipe.ExecuteAndSend (mycommand);
}

The life code of the Getproductswithpricelessthan method is very similar to the declaration code of the Getdiscontinuedproducts method that we created in step three. The only difference is that the Getproductswithpricelessthan method receives a loss into the parameter (price), and the SqlCommand query also contains a parameter (@MaxPrice).

After you complete the code addition, redeploy SQL Server Project. Next, go back to SQL Server Management Studio and refresh the stored Procedures folder. You will see a new query Getproductswithpricelessthan. From the Query window, type and execute the command "exec Getproductswithpricelessthan 25", which displays all products with a price below 25, as shown in Figure 14.

Figure 14: Products with prices below 25 are displayed

Step Sixth: Call managed Stored from the data access layer Procedure

At this point, we have added getdiscontinuedproducts and Getproductswithpricelessthan to the Manageddatabaseconstructs project, these 2 managed stored Procedures and register them with the Northwind SQL Server database. We also call them from SQL Server Management (see figures 13 and 14). To enable our ASP.net applications to invoke these managed stored procedures, So we need to add them to the system's data access layer and business logic layer. In this step, we add 2 new methods to the ProductsTableAdapter of the typed dataset Northwindwithsprocs. In step seventh, we'll add the appropriate approach to the business logic layer .

Open the typed DataSet Northwindwithsprocs in Visual Studio and add a method named Getdiscontinuedproducts to ProductsTableAdapter.

　　Note: Since we have moved the Northwind database out of the App_Data folder, we should update the connection string in the Web.config file accordingly. in the second step we explored the value of updating the northwndconnectionstring in the Web.config file. If you forget to update, you will see an error message when you try to add a new method to TableAdapter: " Failed to add query. Unable to find connection ' northwndconnectionstring ' for object ' Web.config '. In order to overcome this problem, You need to update the northwndconnectionstring value of the Web.config file. As in the second step, add new methods to TableAdapter, and this time there will be no mistake.

When adding new methods, the TableAdapter Query Configuration Wizard first asks us how to access the database. Because we have created and registered the getdiscontinuedproducts stored procedure, select the "Use existing stored procedure" item and click Next.

Figure 15: Select the "Use existing stored procedure" Item

Next we want to select the stored procedure to invoke. Select stored procedure getdiscontinuedproducts on the left Drop-down list.

Figure 16: Selecting the getdiscontinuedproducts stored procedure

Since Getdiscontinuedproducts will return a series of record rows, we select the first item ("Tabular Data") and click Next.

Figure 17: Selecting the "Tabular Data" Item

Finally, the wizard wants us to name the method, select the 2 items and name them fillbydiscontinued and getdiscontinuedproducts respectively. Finish the wizard.

Figure 18: Naming the method fillbydiscontinued and Getdiscontinuedproducts

Using the same method, managed stored procedure named Getproductswithpricelessthan added ProductsTableAdapter and Getproductswithpricelessthan these 2 methods.

Figure 19 shows the 2 managed for getdiscontinuedproducts and Getproductswithpricelessthan stored Procedures the interface after adding various methods to the ProductsTableAdapter.

Figure 19:productstableadapter contains the newly added method

Step Seventh: Add the appropriate method to the business logic layer

Now that we have updated the data access layer, we also need to add the appropriate methods to the business logic layer. Add the following 2 methods to the Productsbllwithsprocs class class:

[System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
Public northwindwithsprocs.productsdatatable getdiscontinuedproducts ()
{
 return Adapter.getdiscontinuedproducts ();
}

[System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
Public northwindwithsprocs.productsdatatable
 Getproductswithpricelessthan (decimal pricelessthan)
{ Return
 Adapter.getproductswithpricelessthan (Pricelessthan);
}

These 2 methods simply invoke the corresponding method of the data access layer and return the Productsdatatable instance. and the 2 ways the Dataobjectmethodattribute tag added above is to include the method in the The Drop-down list item in the Select tab of the ObjectDataSource control's Configuration Data Source Wizard. (End of last part)

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.