从 SQL CLR 程序集返回记录集？

发布于 2024-08-05 01:25:50 字数 807 浏览 4 评论 0原文

伙计们租赁帮助我，我想我已经接近了，但我错过了一些东西。

背景

我正在重写 SQL CLR 程序集（存储过程），我的程序集联系另一个资源并返回 XML。我想将此 XML 作为记录集而不是标量值返回。

据我所知，这是返回记录集的方法：

SqlContext.Pipe.Send(mySqlDataReader)

Send方法接受3个可能的参数：

public void Send(string message);
public void Send(SqlDataRecord record);
public void Send(SqlDataReader reader);

SqlDataReader类没有构造函数，SqlCommand如何.ExecuteReader() 返回一个？

我认为我需要做什么

创建我自己的类继承自 IDataReader。
使此类使用 XML，并将其公开为记录（如 DataReader）。
重写 SqlDataReader 并将其传递给 SqlContext.Pipe.Send(mySqlDataReader) ???

这就是它变得模糊的地方。

如何将自定义记录集返回到 SQL Server？

原文

Guys lease help me out I think I'm close, but I'm missing something.

Background

I'm rewriting a SQL CLR assembly (stored procedure), my assembly contacts another resource and gets back XML. I want to return this XML as a recordset, NOT a scalar value.

From what I've seen, this is how to return a recordset:

SqlContext.Pipe.Send(mySqlDataReader)

The Send method takes in 3 possible parameters:

public void Send(string message);
public void Send(SqlDataRecord record);
public void Send(SqlDataReader reader);

The SqlDataReader class does not have a constructor, how does SqlCommand.ExecuteReader() return one?

What I think I need to do

Create my own class inheriting from
IDataReader.
Make this class consume the XML, and expose it as
records (like a DataReader).
Override SqlDataReader and pass this to SqlContext.Pipe.Send(mySqlDataReader)
???

This is where it gets fuzzy.

How do I return my custom recordset back to SQL Server?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

面如桃花 2024-08-12 01:25:50

我想你最好使用SqlDataRecord。您可以在此处查看说明

。看起来如下：

 SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Column1", SqlDbType.NVarChar)});

 // Set the record fields.
 record.SetString(0, youVariableWithXmlData);

 // Send the data
 SqlContext.Pipe.Send(record);

I guess you'd better use SqlDataRecord. You can see a description here

It should look like follows:

 SqlDataRecord record = new SqlDataRecord(new SqlMetaData[] { new SqlMetaData("Column1", SqlDbType.NVarChar)});

 // Set the record fields.
 record.SetString(0, youVariableWithXmlData);

 // Send the data
 SqlContext.Pipe.Send(record);

回复收藏 0 原文

鸠书 2024-08-12 01:25:50

第一：程序集不返回任何内容，它们仅包含一个或多个可能返回结果集、标量值、返回值的方法。

如果您已经有一个SqlDataReader（从问题的措辞中不清楚），那么您非常接近返回结果集。

SqlDataReader 类没有构造函数，SqlCommand.ExecuteReader() 如何返回构造函数？

ExecuteReader 方法如何创建 SqlDataReader 并不重要。重要的是它确实返回一个SqlDataReader。因此，您只需要执行以下操作：

SqlDataReader _Reader = SqlCommand.ExecuteReader();
SqlContext.Pipe.Send(_Reader);
// be sure to call .Dispose() on the SqlDataReader, SqlCommand, and SqlConnection objects,
// if they are not each in their own using() blocks

该方法非常有效，但不允许您在将 SqlDataReader 的行作为结果集行传回之前拦截它们。因此，如果您需要在将值发送回调用方之前对其进行操作，请使用 SqlContext.Pipe 的以下方法：

SendResultsStart(SqlDataRecord)
SendResultsRow(SqlDataRecord)
SendResultsEnd()

First: Assemblies do not return anything, they simply contain one or more methods that might return result sets, scalar values, return values.

If you already have a SqlDataReader (it is unclear from the wording of the Question), then you were very close with having the result set returned.

The SqlDataReader class does not have a constructor, how does SqlCommand.ExecuteReader() return one?

How the ExecuteReader method creates the SqlDataReader isn't important. What is important is that it does return a SqlDataReader. Hence, you just need to do the following:

SqlDataReader _Reader = SqlCommand.ExecuteReader();
SqlContext.Pipe.Send(_Reader);
// be sure to call .Dispose() on the SqlDataReader, SqlCommand, and SqlConnection objects,
// if they are not each in their own using() blocks

That method is pretty efficient, but doesn't allow you to intercept the rows of the SqlDataReader before passing them back as result set rows. So if you need to manipulate the values before sending them back to the caller, use the following methods of SqlContext.Pipe: