XMLni saqlangan protseduradan qanday qaytarishim mumkin?

Men XML ni qaytaradigan Saqlangan protsedura yaratdim va men yaratgan usulda ushbu XML ni ham qaytarishni xohlayman.

Menda ikkita muammo bor. Birinchidan, biroz qidiruvdan so'ng, .ExecuteScalar(); dan foydalanish tavsiya etilmaydi, chunki u 2033 belgidan ortiq satrlarni qisqartiradi.

Shunday qilib, men ExecuteXMlReader() deb nomlangan funktsiyani topdim, lekin .NET 4.0 (C#) da ishlaydigan Visual Web Developer 2010 Express da "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" xatosini keltirib chiqarmoqda.

Mana mening saqlangan protseduram:

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

set nocount on;

RETURN

Mana mening usulim:

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

Ikkinchidan, ExecuteXMLReader() muammosiga qo'shimcha ravishda, satrni qaytarish birinchi navbatda XMLni qaytarishning to'g'ri yo'li ekanligini bilmayman... Men uni aylantirishim kerak bo'lgan boshqa ob'ekt turi bormi? Yoki boshqa funktsiyadan foydalanishim kerakmi?

Oldindan rahmat!!


xml .net c# stored-procedures
person AngeloS    schedule 24.03.2011    source manba

Javoblar (3)


arrow_upward
27
arrow_downward

Birinchidan, SqlCommand siz yozgan ExecuteXMlReader emas, balki ExecuteXmlReader usuliga ega (bu noto'g'ri imlo). Ikkinchidan, SqlCommand.ExecuteXmlReader usuli XmlReader turidagi qiymatni qaytaradi, ammo bu emas. a DataReader sizning misolingizda bo'lgani kabi. Shunday qilib, kodingizni quyidagicha o'zgartiring:

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

masalani hal qilishi kerak.

person Alex    schedule 24.03.2011
comment
bu juda yaxshi ishladi, lekin tez savol .. XMLni satr emas, XML sifatida qaytarishning bir usuli bormi? - person AngeloS; 24.03.2011
comment
XmlReader har qanday XMLni o'qishning asosiy usuli hisoblanadi. Masalan, XDocument.Load(reader) yordamida XDocument yoki XmlDocumentni yuklash uchun undan foydalanasiz. - person John Saunders; 24.03.2011
comment
@Anjelo, siz XmlDocument document = new XmlDocument(); document.Load(reader); ga o'xshash narsani ishlatishingiz yoki @John aytganidek XDocument.Load(reader) dan foydalanishingiz mumkin. Rahmat, @John! - person Alex; 24.03.2011
comment
XmlReader reader = cmd.ExecuteXmlReader(); XmlDocument document = new XmlDocument(); document.Load(reader); dan foydalanganda men quyidagi xatoni olaman: Bu hujjatda allaqachon "DocumentElement" tugun mavjud. .. har qanday fikr? - person AngeloS; 24.03.2011

arrow_upward
6
arrow_downward

Men @Alexdan oddiy yondashuv bilan muammoga duch keldim va ushbu yondashuv:

// Execute a SqlCommand that you've created earlier.
// (Don't forget your 'using' statements around SqlConnection, SqlCommand and XmlReader!)
// This is where our XML will end up 
var xmlDocument = new XmlDocument();

using (XmlReader xmlReader = cmd.ExecuteXmlReader())
{
    // Now xmlReader has the XML but no root element so we can't
    // load it straight into XmlDocument :( But we can use XPathDocument
    // to add a node for us first.
    var xp = new XPathDocument(xmlReader);
    var xn = xp.CreateNavigator();
    XmlNode root = xmlDocument.CreateElement("YourFavouriteRootElementName");
    root.InnerXml = xn.OuterXml;
    xmlDocument.AppendChild(root);
}

// Now xmlDocument has all the XML you have dreamed of

reader.Read() ... var s = reader.ReadOuterXml() dan foydalanish uzoqroq murakkabroq XML-dagi ba'zi elementlarni o'tkazib yubordi. Buning sababini o'rganish bilan bezovta qilmadim, lekin XPathDocument ga o'tish men uchun ishladi.

person Rory    schedule 23.11.2016

arrow_upward
0
arrow_downward

XmlDocumentni qaytarish uchun siz quyidagi koddan foydalanishingiz mumkin:

using(XmlReader reader = command.ExecuteXmlReader())
{
    XmlDocument xmlDocument = new XmlDocument();

    while (reader.Read())
    {
        xmlDocument.Load(reader);
    }

    return xmlDocument;
}
person mbadeveloper    schedule 17.09.2020