Shulou(Shulou.com)06/02 Report--

Today I'll show you how to create CLR stored procedures. The content of the article is good. Now I would like to share it with you. Friends who feel in need can understand it. I hope it will be helpful to you. Let's read it along with the editor's ideas.

The following describes the creation of CLR stored procedures, from theory to practice, the author made a clear explanation of the code, to ensure that you can understand, I hope this article can help you.

First, open Visual Studio and click "create"-> "Visual C#"-> "Database"-> "SQL Server Project". After the selection is completed, a database connection window will automatically pop up. Choose according to your actual situation. There is no difference here, so I won't say much about it. Follow this step to create a new SQL Server project.

Second, right-click the project name, click "add"-> "CLR stored procedure", and there will be an extra .cs file in the project. Edit it.

Using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class Test {[Microsoft.SqlServer.Server.SqlProcedure] public static void StoredProcedures () {/ / place the code SqlPipe sp = SqlContext.Pipe; string sql = "Select * from [Table_1]" here Using (SqlConnection conn = new SqlConnection ("context connection=true")) {conn.Open (); SqlCommand cmd = new SqlCommand (); cmd.CommandType = CommandType.Text; cmd.Connection = conn; cmd.CommandText = sql; SqlDataReader rdr = cmd.ExecuteReader (); sp.Send (rdr) Conn.Close ();}

Line 16: using (SqlConnection conn = new SqlConnection ("context connection=true")) means that a "contextual connection" allows you to use the user currently logged in to the database as authentication information for your login database.

After editing, we can compile it, if the compilation passes. We can deploy it to SQL Server. Right-click the project name and select "deploy" to OK. Of course, directly "run", the system will automatically deploy it.

At this point, if we execute our CLR stored procedure in SQL Server, I'm afraid it will prompt an error. "prohibit the execution of user code in .NET Framework. Enable the" clr enabled "configuration option.

"at this point, we need to execute the following script

-- execute this code in Sql Server to open CLR exec sp_configure 'show advanced options',' 1; go reconfigure; go exec sp_configure 'clr enabled',' 1'go reconfigure; exec sp_configure 'show advanced options',' 1; go

It's all right now. Execute the CLR stored procedure "Exec StoredProcedures" again. It's done.

Transact-SQL stored procedures are basically no different from CLR stored procedures, but CLR stored procedures cannot be "modified" in SQL Server, and the interior of stored procedures can not be seen by selecting "Create", "Alter" and so on. The real content can only be seen in the SQL Server Project.

In terms of performance, I have tried 100000 pieces of data before, and the same "Select * From [Table]" Transact-SQL stored procedure took about 8 seconds, while the CLR stored procedure took about 9 to 10 seconds, so it is certain that the Transact-SQL stored procedure will be slightly faster than the CLR stored procedure.

This is all about creating CLR stored procedures. For more information about how to create CLR stored procedures, you can search the previous articles or browse the following articles to learn! I believe the editor will add more knowledge to you. I hope you can support it!

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.