Explore SQL Server 2005.NET CLR programming

This article explains how to create a based on the simplest possible method in SQL Server 2005. NET's CLR subroutine. This article focuses on using SQL Server 2005 on the server side. NET Developer.

First, create a SQL Server 2005 database

This section focuses on creating a database and tables that will be used in this article. Refer to the following steps:

· Perform "Start->programs->microsoft SQL Server 2005->sql server Management Studio" and connect to your SQL Server 2005 instance with the necessary certificates.

· Once the connection is established, use Object Explorer to open the SQL Server 2005 instance, then right-click Databases and select New Database (Figure 1).

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 will be able to see the same results in Object Explorer.

· Use Object Explorer to open the Sample database, right-click tables and select New Table (Figure 2).

Figure 2

· Create the table structure as shown in Figure 3 below and use EMP to save the table.

Figure 3

· Once you have created the form "EMP", open it (Figure 4) and populate it with the following data (Figure 5).

Figure 4

Figure 5

Ii. Creating a SQL Server 2005 database. NET CLR Stored procedures

Once you've created the database and the table, let's use the following steps to create a. NET CLR stored procedure in the SQL Server 2005 database:

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

· In the New Project dialog box, select Database under Project Type in the Visual Basic tree and choose SQL Server Project as the template and enter the name "Sampleallinone". At this point, the dialog box should look like Figure 6 below.

Figure 6

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

Figure 7

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

· The system then appears with a dialog box asking if you want to start the CLR debugging feature. Please click "Yes" (Figure 8).

Figure 8

· Using "Solution Explorer", right-click the "Sampleallinone" Project and click on "Add->stored Procedure" (to create a new stored procedure) (Figure 9).

Figure 9

· Then, 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).

Figure 10

Iii.. NET CLR Stored procedure encoding

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

The following are the referenced contents: Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures <microsoft.sqlserver.server.sqlprocedure () > _ Public Shared Sub incsalaries (ByVal incval as SqlDouble) ' Add your code here Using cn as New SqlConnection ("Context Connection=true") Dim cmd as New SqlCommand ("Update sample.dbo.emp set Sal = Sal + "& Incval.tostring, CN" Cmd. Connection.Open () Cmd. ExecuteNonQuery () End Using End Sub End Class

Above is a simple CLR-based stored procedure written using Visual Basic.NET 2005, which you can publish to SQL Server 2005. The stored procedure is named "Incsalaries", which 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".

Iv.. NET CLR Stored procedure tests

To test the above stored procedure, follow these steps:

· Use "Solution Explorer" to open "Test.sql" in "Test Scripts" (Figure 11).

Figure 11

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

--To run your project, please edit your project's Test.sql file.

--This file is located under the "Test Scripts" folder of Solution Explorer

EXEC incsalaries 50

· Go to the Start->programs->microsoft SQL Server 2005->configuration tools->sql server Surface area Configuration.

· In the window, click "Surface area Configuration for Features" (Figure 12).

Figure 12

· Start the CLR integration as follows (Figure 13).

Figure 13

· Press F5 to execute the entire program. If any errors occur in its execution, everything will be displayed in the Output window.

· If the stored procedure executes successfully, you should be able to see all the payroll increases by 50.

V. NET CLR user-defined functions in a SQL Server 2005 database

Now, we're going to add a storage function to our existing scenario.

· Using "Solution Explorer", right-click the "Sampleallinone" project and go to "add->user-defined function" (to create a new storage function) (Figure 14).

Figure 14

· Then, the ADD New Item dialog box appears. In the dialog box, select "User defined function" as the template, enter the name "Getnetsalary", and then click "Add" (Figure 15).

Figure 15

Once you create "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 < Microsoft.SqlServer.Server.SqlFunction (Dataaccess:=dataaccesskind.read) > _ Public Shared Function Getnetsalary (ByVal empno as SqlString) as SqlDouble ' Add your code here Dim sal as Double Using cn as New Sqlconnecti On ("Context connection=true") Dim cmd as New SqlCommand ("Select Sal from sample.dbo.emp where empno= '" & empno . ToString & "'", cn) CMD. Connection.Open () Sal = CType (cmd. ExecuteScalar, Double) cmd. Dispose () End Using Dim hra As Double = sal * 10/100 Dim ta As Double = Dim gross As Double = Sal + HRA + TA Dim epf as Double = sal * 5/100 Dim net as Double = GROSS-EPF Return net End Function End C Lass

Then, use the following steps to test the stored procedure above:

· Use "Solution Explorer" to open "Test.sql" in "Test Scripts" (see Figure 11 above).

· Go to and modify the following code:

--To run your project, please edit your project's Test.sql file.

--This file is located under the "Test Scripts" folder of Solution Explorer

--exec Incsalaries 50

Select Dbo.getnetsalary (empno) from Sample.dbo.emp

Press F5 to execute the function, and you should be able to see the corresponding result.

Vi. defining. NET CLR user-defined aggregators

Now we start by adding an aggregate function on our existing scenario, based on the previous section.

· Using the Solution Explorer, right-click the "Sampleallinone" project and Go to "add->aggregate" (to create a new aggregate function) (Figure 16).

Figure 16

· Then, the ADD New Item dialog box appears. In the dialog box, select "Aggregate" as the template, enter the name "Netsal" and click "Add" (Figure 17).

Figure 17

Once you create "Netsal.vb", you will 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 <serializable () > _ < Microsoft.SqlServer.Server.SqlUserDefinedAggregate (format.native) > _ publicstructure netsal Public Sumnetsal as SqlDouble Public Sub Init () ' here to add your code Sumnetsal = 0 End Sub Public Sub Accumulate (ByVal　Value as SqlDouble) Add your code here Dim sal as Double = CType (value, Double) Dim hra As Double = sal * 10/100 Dim ta As Double = Dim gross As Double = sal + HRA + TA Dim epf as Double = sal * 5/100 Dim NET as Doub Le = GROSS-EPF Sumnetsal = Net End Sub Public Sub Merge (ByVal obj as netsal) ' Add your code here Sumnetsal = O bj. Sumnetsal End Sub Public Function Terminate () as SqlDouble ' Add your code here return sumnetsal End Function Endstructure

To test the above stored procedure, follow these steps:

· Use Solution Explorer, and then open "Test.sql" in "Test Scripts".

· Go to the last line and modify the following code:

--To run your project, please edit your project's Test.sql file.

--This file is located under the "Test Scripts" folder of Solution Explorer

--exec Incsalaries 50

--select dbo.getnetsalary (empno) from Sample.dbo.emp

Select Dbo.netsal (SAL) from Sample.dbo.emp

Press F5 to execute the function, and you should be able to observe the corresponding result.