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

This article mainly introduces how to use CLR stored procedures to send back messages, the article is very detailed, has a certain reference value, interested friends must read it!

The CLR stored procedure is stored in the class library project SendYouDataReader, which demonstrates how to use the Send method of the SqlPipe object to pass the query results (that is, the SqlDataReader category object) directly to the client. In particular, although the Send method is slightly less efficient than the ExecuteAndSend method, the Send method provides * flexibility to process the data before it is transferred to the client, in other words, if you want to transfer the results of a query previously executed through the same process (In-Process) provider, or if you want to take advantage of the custom implementation of SqlDataReader to preprocess the data, make this overloaded version of the Send method. The program code after completion is as follows:

Using Microsoft.SqlServer.Server; namespace SendYouDataReader {public class SendYouDataReaderClass {[Microsoft.SqlServer.Server.SqlProcedure ()] public static void SendYouDataReader (decimal LowerSalary, decimal HigherSalary) {SqlCommand cmd; SqlDataReader dr; / / uses content connections to connect. Using (SqlConnection connection= new SqlConnection ("context connection=true")) {connection.Open (); cmd = new SqlCommand ("SELECT employee number, name, current salary FROM dbo. Zhang limin laboratory "+" WHERE current salary BETWEEN @ LowerSalary AND @ HigherSalary ", connection); cmd.Parameters.AddWithValue (" @ LowerSalary ", LowerSalary); cmd.Parameters.AddWithValue (" @ HigherSalary ", HigherSalary); dr = cmd.ExecuteReader (); / / call the Send method of the SqlPipe object to transmit the query result directly to the client SqlContext.Pipe.Send (dr);}}

Execute the SQL instructions shown below, log in to the component SendYouDataReader.dll, establish a stored procedure that references the logged-in component SendYouDataReader.dll, and then execute the CLR stored procedure, as shown in figure 1:

USE North Wind Trade; GO IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'SendYouDataReader') DROP PROCEDURE SendYouDataReader; GO IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] =' SendYouDataReader') DROP ASSEMBLY SendYouDataReader; GO-login component SendYouDataReader.dll CREATE ASSEMBLY SendYouDataReader FROM'C:\ SQL2005Demo\ CH13\ SendYouDataReader\ SendYouDataReader\ bin\ SendYouDataReader.dll' WITH permission_set = Safe GO-- create a stored procedure CREATE PROCEDURE dbo.SendYouDataReader (@ LowerSalary money, @ HigherSalary money) AS EXTERNAL NAME SendYouDataReader.SendYouDataReader.SendYouDataReaderClass] .SendYouDataReader; GO EXEC sp_configure 'clr enabled',' 1readers; GO RECONFIGURE; GO DECLARE @ return_value int-execute the CLR stored procedure EXECUTE @ return_value = dbo.SendYouDataReader @ LowerSalary = 50000, @ HigherSalary = 70000

The above is all the contents of the article "how to use CLR stored procedures to return messages". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.