Vs2005开发CLR存储过程

最新推荐文章于 2020-07-23 07:23:57 发布

ITFLY8

最新推荐文章于 2020-07-23 07:23:57 发布

阅读量938

点赞数

分类专栏： Sql高级查询/存储过程 C#/C#2.0/C#3.5 参考手册 Asp.Net 2.0 文章标签：存储 sql server returning assembly command parameters

Asp.Net 2.0 同时被 3 个专栏收录

77 篇文章 0 订阅

订阅专栏

Sql高级查询/存储过程

16 篇文章 0 订阅

订阅专栏

C#/C#2.0/C#3.5 参考手册

5 篇文章 0 订阅

订阅专栏

使用SQL Server 2005中的CLR集成
出处:http://www.dedecms.com/plus/view.php?aid=38019

这个问题是前几天遇到的，简单描述一下：单一课程和系列课程。一个系列包括多个单一课程。存放数据的表的设计为

表：ES_RefSerailCourse

列：SerialCourseID(int) | CourseID(int)

一个SerialCourseID对应多个CourseID。操作时，创建系列，选择多个课程到一个控件（该控件可以返回用","分隔的选择的课程的编号，点击提交后创建系列，同时把生成的系列编号和课程编号插入到ES_RefSerailCourse。

这种连续插入数据的问题，以前用vs2003+sql200的时候，我是在数据访问层打开一个连接后，循化插入数据，最后关闭连接。这个办法虽然显得很笨，但是当时也没有什么好办法可以在数据库边完成循化插入数据。而现在，时代不同了，我们有了vs2005和sql2005，对于这种t-sql没法完成的任务，可以用sql2005的CLR集成功能实现。

sql2005的CLR集成，说的通俗一些就是用.net语言编写数据库应用，包括存储过程，函数，触发器等等均可。其基本的使用过程为：

1.在vs2005建立一个数据库应用项目。

2.在该项目用.net语言编写数据库应用。

3.将用.net语言编写数据库应用通过vs2005部署到sql2005。

4.实际使用中调用。

下面按照上面的顺序讲一下这个过程。（好久没有写技术文章了，如果有描述不清或错误的地方，欢迎在评论中交流）

1.在vs2005建立一个数据库应用项目。

如图所示，建立一个这样的database项目。建立后会要求你指定一个数据库连接，按照提示一步步进行就ok。建立完成后可以测试一下数据库连接。办法为：在解决方案上点右键，选Deploy（中文应该是部署？），如果状态栏提示部署成功，那就ok了，否则请检查一下数据库连接（以及……人品）。

2.在该项目用.net语言编写数据库应用。

我用C#。这个过程和编写普通的程序基本相同。下面是上面问题的解决程序

1 ///

2 /// 将课程加入到系列

3 ///

4 /// 系列编号

5 /// 课程编号汇总

6 ///

7 [Microsoft.SqlServer.Server.SqlProcedure]

8 public static void up_AddCoursetoSerial(SqlInt32 serialID, SqlString ids)

9 {

10 using (SqlConnection connection = new SqlConnection("context connection=true"))

11 {

12 connection.Open();

13 System.Text.StringBuilder sqlb=new System.Text.StringBuilder();

14 string[] courseIds = ids.Value.Split(new char[] { ',' });

15 foreach (string courseId in courseIds)

16 {

17 if (courseId.Length > 0)

18 {

19 sqlb.AppendFormat("insert into [ES_RefSerailCourse] values({0},{1}) ;", serialID, courseId);

20 }

21 }

22 command = new SqlCommand(sqlb.ToString(), connection);

23 SqlContext.Pipe.ExecuteAndSend(command);

24 }

25 }

a .在方法上加attribute，是什么就加什么。具体可以查SDK文档。这样在编译和部署的时候vs2005就会按照你的意思来做了。

b.暴露在外的数据需要使用System.Data.SqlTypes。这是自然的。

c.构造完command后，使用SqlContext.Pipe.ExecuteAndSend()执行。

（实际上，我们还是拼了sql语句，但最后的表现形式却成了一个存储过程。大部分的项目都不允许直接把sql语句写到程序中，所以，这个办法就有了用武之地。）

3.将用.net语言编写数据库应用通过vs2005部署到sql2005。

完成上面的工作后，对项目进行编译，部署,方法是在解决方案上点右键，选...... 这个的结果是vs2005在sql2005中生成了一个存储过程。接下来就可以到sql2005的存储过程管理部分看一下是否多了一个名字叫“up_AddCoursetoSerial”的，如果存在，那恭喜你，一切都很完美。

4.实际使用中调用。

到这一步，就忘记这是一个用.net语言写的存储过程吧，该怎么用就怎么用。下面是我的实际应用：

1 ALTER PROC [dbo].[ES_SerialRecordCourse_Insert]

2 (

3 @SerialCourseID int out,

4 @SerialCourseName nvarchar(100),

5 @Description nvarchar(200),

6 @IDs nvarchar(500)

7 )

8 AS

9 INSERT INTO [ES_SerialRecordCourse]

10 (

11 [SerialCourseName],

12 [Description]

13 )

14 VALUES

15 (

16 @SerialCourseName,

17 @Description

18 )

19 SET @SerialCourseID=@@IDENTITY

21 --向系列_课程对应表插入数据

22 exec dbo.up_AddCoursetoSerial @SerialCourseID,@IDs

最后一句就是调用了我们刚才写的存储过程，接受两个参数。是不是很方便？

看到这里，相信聪明的你已经对sql2005的CLR集成有了初步的印象。如果想完全掌握这个强大的武器，最好的办法就是立刻开始自己的编写。在之前，看看msdn上对这个的介绍也会很有帮助

http://www.microsoft.com/china/msdn/library/data/sqlserver/sqlclrguidance.mspx

我也是前几天因为项目需要才接触这个，希望可以和大家一起来学这个很酷的新功能：）

好了，我的最新的一篇笔记完成了！和前一篇笔记相隔的时间大概是……一年？？

but,better later than never :)

VS2005下C#+SQL2005创建CLR存储过程
出处：http://blog.csdn.net/yanjiangbo/archive/2007/09/04/1771767.aspx

   Stored procedures are routines that cannot be used in scalar expressions. Unlike scalar functions, they can return tabular results and messages to the client, invoke data definition language (DDL) and data manipulation language (DML) statements, and return output parameters.
Requirements for CLR Stored Procedures
In the common language runtime (CLR), stored procedures are implemented as public static methods on a class in a Microsoft .NET Framework assembly. The static method can either be declared as void, or return an integer value. If it returns an integer value, the integer returned is treated as the return code from the procedure. For example:
EXECUTE @return_status = procedure_name
The @return_status variable will contain the value returned by the method. If the method is declared void, the return code is 0.
If the method takes parameters, the number of parameters in the .NET Framework implementation should be the same as the number of parameters used in the Transact-SQL declaration of the stored procedure.
Parameters passed to a CLR stored procedure can be any of the native SQL Server types that have an equivalent in managed code. For the Transact-SQL syntax to create the procedure, these types should be specified with the most appropriate native SQL Server type equivalent. For more information about type conversions, see SQL Server Data Types and Their .NET Framework Equivalents.
Returning Results from CLR Stored Procedures
Information may be returned from .NET Framework stored procedures in several ways. This includes output parameters, tabular results, and messages.
1.   OUTPUT Parameters and CLR Stored Procedures
As with Transact-SQL stored procedures, information may be returned from .NET Framework stored procedures using OUTPUT parameters. The Transact-SQL DML syntax used for creating .NET Framework stored procedures is the same as that used for creating stored procedures written in Transact-SQL. The corresponding parameter in the implementation code in the .NET Framework class should use a pass-by-reference parameter as the argument.
The following shows a stored procedure returning information through an OUTPUT parameter:
C#
using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void PriceSum(out SqlInt32 value)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            value = 0;
    connection.Open();
            SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);
            SqlDataReader reader = command.ExecuteReader();
            using (reader)
            {
                while (reader.Read())
                {
                    value += reader.GetSqlInt32(0);
                }
            }
        }
    }
}
Built assembly on the server:
        create assembly assm_name from 'C:/****.dll'
Once the assembly containing the above CLR stored procedure has been built and created on the server, the following Transact-SQL is used to create the procedure in the database, and specifies sum as an OUTPUT parameter(assm_name=TestStoredProc).
CREATE PROCEDURE PriceSum (@sum int OUTPUT)AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum
Note that sum is declared as an int SQL Server data type, and that the value parameter defined in the CLR stored procedure is specified as a SqlInt32 CLR data type. When a calling program executes the CLR stored procedure, SQL Server automatically converts the SqlInt32 CLR data type to an int SQL Server data type. For more information about which CLR data types can and cannot be converted, see SQL Server Data Types and Their .NET Framework Equivalents.
2.   Returning Tabular Results and Messages
Returning tabular results and messages to the client is done through the SqlPipe object, which is obtained by using the Pipe property of the SqlContext class. The SqlPipe object has a Send method. By calling the Send method, you can transmit data through the pipe to the calling application.
These are several overloads of the SqlPipe.Send method, including one that sends a SqlDataReader and another that simply sends a text string.
1.   SqlContext.Pipe.Send(SqlDataRecord record)
2.   SqlContext.Pipe.Send(string message)
3.   SqlContext.Pipe.Send(System.Data.SqlClient.SqlDataReader reader)

2.1.              Returning Message
Use SqlPipe.Send(string) to send messages to the client application. The text of the message is limited to 8000 characters. If the message exceeds 8000 characters, it will be truncated.
2.2.              Returning Tabular Results
To send the results of a query directly to the client, use one of the overloads of the Execute method on the SqlPipe object. This is the most efficient way to return results to the client, since the data is transferred to the network buffers without being copied into managed memory. For example:
[C#]
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class StoredProcedures
{   ///<summary>
    /// Execute a command and send the results to the client directly.
    ///</summary>   [Microsoft.SqlServer.Server.SqlProcedure]
     public static void ExecuteToClient()
     {
         using(SqlConnection connection = new SqlConnection("context connection=true"))
         {
             connection.Open();
             SqlCommand command = new SqlCommand("select @@version", connection);
             SqlContext.Pipe.ExecuteAndSend(command);
         }
     }
}
To send the results of a query that was executed previously through the in-process provider (or to pre-process the data using a custom implementation of SqlDataReader), use the overload of the Send method that takes a SqlDataReader. This method is slightly slower than the direct method described previously, but it offers greater flexibility to manipulate the data before it is sent to the client.
C#
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
    ///<summary>
    /// Execute a command and send the resultig reader to the client
    ///</summary>
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SendReaderToClient()
    {
        using(SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand("select @@version", connection);
            SqlDataReader r = command.ExecuteReader();
            SqlContext.Pipe.Send(r);
        }
    }
}
To create a dynamic result set, populate it and send it to the client, you can create records from the current connection and send them using SqlPipe.Send.
C#
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class StoredProcedures
{
    ///<summary>
    /// Create a result set on the fly and send it to the client.
    ///</summary>
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SendTransientResultSet()
    {
        // Create a record object that represents an individual row, including it's metadata.
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));
        // Populate the record.
        record.SetSqlString(0, "Hello World!");
        // Send the record to the client.
        SqlContext.Pipe.Send(record);
    }
}
Here is an example of sending a tabular result and a message through SqlPipe.
C#
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld()
    {
        SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString() + "/n");
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
      SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);
            SqlDataReader reader = command.ExecuteReader();
            SqlContext.Pipe.Send(reader);
        }
    }
}
The first Send sends a message to the client, while the second sends a tabular result using SqlDataReader.
Note that these examples are for illustrative purposes only. CLR functions are more appropriate than simple Transact-SQL statements for computation-intensive applications.

Note:
Messages and result sets are retrieved differently in the client application. For instance, SQL Server Management Studio result sets appear in the Results view, and messages appear in the Messages pane.

If the above Visual C# code is saved in a file MyFirstUdp.cs and compiled with:
csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs

The resulting assembly can be registered, and the entry point invoked, with the following DDL:
CREATE ASSEMBLY MyFirstUdp FROM 'C:/Programming/MyFirstUdp.dll'

CREATE PROCEDURE HelloWorld AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld

EXEC HelloWorld

Some points which need us to pay attention to:
1.    Don’t need namespace
2.    The class must be public
3.    The method must be public static
4.    Return message should use SqlContext.pip.send(),not console.write()