Shulou(Shulou.com)05/31 Report--

How to carry out SQL Server 2005.NET CLR programming, in view of this problem, this article introduces the corresponding analysis and answer in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

How to create a .NET-based CLR subroutine in SQL Server 2005 in the simplest way possible. It is mainly for .NET developers who use SQL Server 2005 on the server side.

one。 Create SQL Server 2005

This section focuses on creating a database and table that will be used in this article. See the following steps for details:

Execute "Start- > Programs- > Microsoft SQL Server 2005-> SQL Server Management Studio" and connect to your SQL Server 2005 instance using the necessary certificates.

Once the connection is established, open the SQL Server 2005 instance using "Object Explorer", then right-click "databases" and select "New Database" (figure 1).

Then, a New Database dialog box appears. Enter the database name "Sample" and click "OK" to create a new database within the same instance.

Once you create the database, you are sure to see the same result in "Object explorer".

Open the "Sample" database using "Object explorer", right-click "tables" and select "new table" (figure 2).

Create the table structure shown in figure 3 below and save the table with "emp".

Once you have created the table "emp", open it (figure 4) and fill it with the following data (figure 5).

two。 Create a SQL Server 2005 database. NET CLR stored procedure

Once you have created the database and tables, let's create a .NET CLR stored procedure in the SQL Server 2005 database using the following steps:

Go to "Start-> Programs-> Microsoft Visual Studio 2005-> Microsoft Visual Studio 2005".

In the "New Project" dialog box, select "database" under "Project Type" in the "Visual Basic" tree and select "SQL Server Project" as the template, and enter the name "SampleAllInOne". At this point, the dialog box should look like figure 6 below.

Once you click "OK", a "New Database Reference" dialog box appears. Enter your own instance name, select the database "Sample" (created in the previous section), and test the connection (figure 7).

Once the test is successful, click "OK" to create the project.

Then a dialog box appears that asks you if you want to start CLR debugging. Click "Yes" (figure 8).

Using "Solution explorer", right-click the "SampleAllInOne" project, and click "Add- > Stored Procedure" (to create a new stored procedure) (figure 9).

After that, the Add New Item dialog box appears. In the dialog box, select "Stored Procedure" as the template and enter the name "IncSalaries", and then click "Add" (figure 10).

three。 .net CLR stored procedure coding

Once you have created "IncSalaries.vb", modify your code in the following ways, as follows:

The above is a simple CLR-based stored procedure written in Visual Basic.NET 2005, which you can publish to SQL Server 2005. The stored procedure is called "IncSalaries" and receives only one parameter-"IncVal". The above stored procedure simply uses the values provided in the parameters to increase the payroll value of each employee in the table "emp".

four。 .net CLR stored procedure testing

To test the above stored procedure, follow these steps:

Use "Solution Explorer" to open "test.sql" in "test scripts" (figure 11).

Go to the last line and modify the code as follows:

Go to "Start- > Programs- > Microsoft SQL Server 2005-> Configuration Tools- > SQL Server Surface Area Configuration".

In the window, click "Surface Area Configuration for Features" (figure 12).

Start the CLR integration as shown below (figure 13).

Press F5 to execute the whole plan. If there are any errors in its execution, everything is displayed in the output window.

If the stored procedure is executed successfully, you should be able to see a 50% increase in all salaries.

five。 . NET CLR user-defined functions in SQL Server 2005 database

Now, we are going to add a storage function to our existing scheme.

Using "Solution explorer", right-click the "SampleAllInOne" project and go to "Add- > User-defined Function" (to create a new storage function) (figure 14).

After that, the Add New Item dialog box appears. In the dialog box, select "User defined function" as the template, enter the name "getNetSalary", and finally click "Add" (figure 15).

Once you have created "getNetSalary.vb", then modify your code in the following ways:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Imports Microsoft.SqlServer.Server

PartialPublic Class UserDefinedFunctions

_

Public Shared Function getNetSalary (ByVal empno As SqlString)

As SqlDouble

Add your code here

Dim sal As Double

Using cn As New SqlConnection ("context connection=true")

Dim cmd As New SqlCommand ("select sal from"

Sample.d on how to do SQL Server 2005.NET CLR programming questions to share here, I hope the above content can be of some help to you, if you still have a lot of doubts to solve, you can follow the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.