Explore SQL Server 2005.NET CLR programming

Source: Internet
Author: User
Tags sql net return tostring management studio sql server management sql server management studio visual studio

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.



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.