如何从一个存储过程返回的XML？ [英] How do I return XML from a Stored Procedure?

问题描述

我创建了一个存储过程返回XML，我也想返回的XML在我创建了一个方法。

我有两个问题。首先，做一些搜索后，不建议使用 .ExecuteScalar（）; ，因为它超过2033个字符截断字符串<​​/ P>

所以，我发现的ExecuteXmlReader（），但在Visual Web Developer中叫做功能上的.NET 4.0（C＃），它是扔运行2010例preSS错误System.Data.SqlClient.SqlCommand'不包含一个定义的ExecuteXmlReader'，没有扩展方法的ExecuteXmlReader接受第一类型的参数'System.Data.SqlClient.SqlCommand可能是发现

下面是我的存储过程：

  CREATE PROCEDURE dbo.GETReport
    （@ReportDate日）
如
SELECT * FROM ReportTbl
WHERE ReportDate = @ReportDate
对于XML自动，元素

SET NOCOUNT上;

返回
 

下面是我的方法：

 使用System.Data这;
使用System.Data.SqlClient的;

...

        //连接
        SqlConnection的康恩=新的SqlConnection（数据源=本地主机;用户ID = foo的;密码= foo的;初始目录=数据库1）;
        conn.Open（）;

        //创建命令
        的SqlCommand CMD =新的SqlCommand（dbo.GETReport，康涅狄格州）;
        cmd.Parameters.AddWithValue（@ ReportDate，3/24/2011）;
        cmd.CommandType = CommandType.StoredProcedure;

        DataReader的RD = cmd.ExecuteXMlReader（）; //这是错误的地方正在发生
        //同时，它抛出一个错误的DataReader也说没有
        //命名空间的使用该名称类型
        rd.Read（）;

        字符串s = rd.ReadOuterXml（）; //同样不知道这是否是我应该如何返回XML
 

其次，除了的ExecuteXmlReader（）的问题，我不知道是否返回一个字符串，是摆在首位返回XML的正确方法？有另一种对象类型，我应该把它转换为？或者另一个函数，我应该使用？

感谢你在前进！

解决方案

首先，的SqlCommand 有一个的ExecuteXmlReader 方法，没有的ExecuteXmlReader 为你写的（这是拼写错误）。其次，<一个href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executexmlreader.aspx"><$c$c>SqlCommand.ExecuteXmlReader方法返回类型的值的XmlReader ，不是的DataReader 就像在你的榜样。因此，改变你的code到：

 使用（XmlReader的读者= cmd.ExecuteXmlReader（））
{
    而（reader.Read（））
    {
        字符串s = reader.ReadOuterXml（）;
        //做一些与S
    }
}
 

应该可以解决这个问题。

I created a Stored Procedure that returns XML and I would like to also return that XML in a method I created.

I'm having two issues. First, after doing some searching, it is not advised to use .ExecuteScalar(); because it truncates strings over 2033 characters.

So, I found a function called ExecuteXMlReader(), but in Visual Web Developer 2010 Express that runs on .NET 4.0 (C#) it is throwing the error "System.Data.SqlClient.SqlCommand' does not contain a definition for 'ExecuteXMlReader' and no extension method 'ExecuteXMlReader' accepting a first argument of type 'System.Data.SqlClient.SqlCommand' could be found"

Here is my stored procedure:

CREATE PROCEDURE dbo.GETReport
    (@ReportDate date)
AS
SELECT * FROM ReportTbl
WHERE ReportDate = @ReportDate
for xml auto, elements

set nocount on;

RETURN

Here is my method:

using System.Data;
using System.Data.SqlClient;

...

        //connect        
        SqlConnection conn = new SqlConnection("Data Source=localhost; User Id=foo; Password=foo; Initial Catalog=Database1");
        conn.Open();

        //create command
        SqlCommand cmd = new SqlCommand("dbo.GETReport", conn);
        cmd.Parameters.AddWithValue("@ReportDate", "3/24/2011"); 
        cmd.CommandType = CommandType.StoredProcedure;

        DataReader rd = cmd.ExecuteXMlReader(); //this is where error is occuring
        //also, it is throwing an error for DataReader as well saying there is no
        //type of namespace with that name
        rd.Read();

        string s = rd.ReadOuterXml(); //also dont know if this is how i should return the XML

Second, in addition to the ExecuteXMLReader() issue, I don't know if returning a string is the proper way of returning XML in the first place... Is there another object type I should convert it to?? Or another function I should use??

Thank you in advance!!

解决方案

First, SqlCommand has a ExecuteXmlReader method, not ExecuteXMlReader as you wrote (this is misspelling). Second, SqlCommand.ExecuteXmlReader method returns a value of type XmlReader, not a DataReader as is in your example. So changing your code to:

using (XmlReader reader = cmd.ExecuteXmlReader())
{
    while(reader.Read())
    {
        string s = reader.ReadOuterXml();
        // do something with s
    }
}

should solve the issue.

这篇关于如何从一个存储过程返回的XML？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！