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

This article will explain in detail how to create CLR stored procedures in SQL Server. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

Prior to 2005, the creation of stored procedures was implemented in Transact-SQL in the database, but now SQL Server 2005 supports writing CLR stored procedures in other object-oriented languages. There are many official explanations for the benefits of doing so. Here we will directly explain the implementation method. Suppose there is a test database in the server, and the database has a schema user and a table test1, and then there is a sql login user named test_user, who sets this user as the login user connected to the database in VS2005. Create a project in VS2005 with the category SQL Server database project, and then add a stored procedure to the project. Then write the following stored procedure code in this file: copy the code as follows: using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;public partial class StoredProcedures {[Microsoft.SqlServer.Server.SqlProcedure] public static void SelectAll () {using (SqlConnection connection= new SqlConnection ("context connection=true")) {connection.Open (); SqlCommand command = new SqlCommand ("SELECT * FROM [user] .test1", connection)

SqlContext.Pipe.ExecuteAndSend (command);}

Save the above code and name it the testProcedure.cs file. Then there are 2 ways to add this stored procedure to the database.

1. Open the command line tool for .NET, then compile testProcedure.cs into dll assembly testProcedure.dllcsc / t:library / out:testProcedure.dll testProcedure.cs, then open the database test, click the "add new assembly" item in the assembly right-click menu in the database, and then browse the newly compiled testProcedure.dll assembly. This adds the assembly, then creates a new sql query and creates a stored procedure in it as follows: CREATE PROCEDURE [user]. SelectAllAS EXTERNAL NAME testProcedure.StoredProcedures.SelectAllGO successfully added a CLR stored procedure to the database after executing the above query. Note that the test stored procedure above has no parameters, and if any, the stored procedure prototype in the database must be the same as the prototype in the CLR language. It is recommended that you use this method to create CLRC stored procedures.

two。 Use VS to automatically create stored procedures for sql. Write a debug script to add to the project, and the script can simply execute the programmed stored procedure. Then compile and build the above project and then deploy it, and you can also start debugging to complete it. VS automatically adds the appropriate stored procedures to the database. But the first method is more flexible and reliable. In addition, using the second method, if an error occurs: the user fails to execute the stored procedure sp_enable_sql_debug, this may be due to:

There is a connection problem. A stable connection to the server is required.

The necessary permissions are missing on the server. To debug on SQL Server 2005, both the account running Visual Studio and the account used to connect to SQL Server must be members of the sysadmin role. The account used to connect to SQL Server is either a Windows user account (if you are using Windows authentication) or an account with a user ID and password (if you are using SQL authentication).

In other words, the test_user must be a sysadmin character.

On how to create CLR stored procedures in SQL Server to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.