SQL Server CLR存储过程返回数据到客户端详解

上一篇文章中我们了解了CLR存储过程的基本用法。

不知道大家有没有注意，在StoredProcedureTest()方法中我们使用的SqlContext.Pipe.ExecuteAndSend(cmd)语句将查询的结果集返回到客户端。SqlPipe就是本文要讲的话题。

一开始，我以为像平常使用ADO.NET那样只要执行了Select语句就可以把结果集返回到客户端。后来才发现没有我想象的那么简单，有个专门的类来把想要的数据发送到客户端，那就是SqlPipe。为了避免后来的人和我犯同样的错误，觉得有必要把SqlPipe的方法详细讲一下。

通过SqlPipe我们有5中方式传数据到客户端，具体如下：

1. ExecuteAndSend(SqlCommand)方法，执行指定的SqlCommand并将得到的结果集返回给客户端。上一篇文章中我们就使用的是这种方法：

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedureTest(string strTableName)
    {
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cmd = new SqlCommand("", conn);
 
        try
        {
            cmd.CommandText = "select * from " + strTableName;
            
            conn.Open();
 
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }
}

2. Send(String)方法，直接返回字符串到客户端。比如我们可以对Product表中的数据做一个检查，检查成功之后给一个提示信息：

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedureTest(string strTableName)
    {
         // 检查Product表的过程省略
 
         SqlContext.Pipe.Send("Product表中的数据没有问题，不需要调整。");

    }
}

3. Send(SqlDataReader)方法，将多行记录集返回到客户端。和ExecuteAndSend(SqlCommand)方法有点类似：

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedureTest(string strTableName)
    {
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cmd = new SqlCommand("", conn);

        try
        {
            cmd.CommandText = "select * from " + strTableName;

            conn.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            SqlContext.Pipe.Send(reader);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }

4. Send(SqlDataRecord)方法，将一条记录返回到客户端。注意在New SqlMetaData时如果类型为NVarChar，一定要指定长度，否则执行存储过程时会报错。

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedureTest()
    {
        SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
                new SqlMetaData("Name", SqlDbType.NVarChar,255),
                new SqlMetaData("Price", SqlDbType.Decimal)});

        record.SetString(0, "iPhone");
        record.SetDecimal(1, 5000);

        SqlContext.Pipe.Send(record);
    }

5. SendResultsRow(SqlDataRecord)方法，该方法需要配合SendResultsStart和SendResultsEnd使用，可将多条记录集返回到客户端。例如我们可以返回10种手机的价格列表到客户端：

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedureTest()
    {
        SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] {
                new SqlMetaData("Name", SqlDbType.NVarChar,255),
                new SqlMetaData("Price", SqlDbType.Decimal)});

        SqlContext.Pipe.SendResultsStart(record);

        for (int intCounter = 1; intCounter <= 10; intCounter++)
        {
            record.SetString(0, "Phone" + intCounter.ToString());
            record.SetDecimal(1,intCounter * 1000);

            SqlContext.Pipe.SendResultsRow(record);
        }

        SqlContext.Pipe.SendResultsEnd();
    }

总结：

Send(String)和Send(SqlDataRecord)用户返回简单的数据和单条记录，ExecuteAndSend(SqlCommand)、Send(SqlDataReader)、SendResultsRow(SqlDataRecord)用于返回多行记录。后3中方法使用起来相当灵活，到底用哪一个视具体情况而定即可。

本文章由创风网发布，转载请注明出处：http://www.windite.com/article/details/t2alt96