CODEWITHSUNDEEP
c#asp.net
Andy
Andy

Reputation: 55

Execute Stored Procedure and get and save xml c#

I have been trying to execute a stored procedure, returning xml, and saving that xml to a file with asp.net c#. I have tried many things and have used bits and pieces from other posts here on stack overflow. My procedure works fine when I run it in sql server management studio; however, I cannot seem to get it to right correctly. The saved file always comes back blank or with one line. Is there anything I am doing wrong that somebody can see? I am very amature at doing this and would apreciate any help I can get. See code below...

SqlConnection conn = new SqlConnection(@"Server=server\ERP;DataBase=dbase;User ID=id;Password=pw");
    conn.Open();

    SqlCommand cmd = new SqlCommand("Labels_printer", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@job_control", jobControl));

    cmd.Parameters.Add(new SqlParameter("@from", from));

    cmd.Parameters.Add(new SqlParameter("@to", to));

    cmd.Parameters.Add(new SqlParameter("@factory", 88));

    cmd.Parameters.Add(new SqlParameter("@printer", printer));
    cmd.CommandTimeout = 1000;
    //System.Xml.XmlReader myXmlReader = cmd.ExecuteXmlReader();
    using (XmlReader reader = cmd.ExecuteXmlReader()) //e.command.commandtimeout = 500
    {

        while (reader.Read())
        {
          //  string s = reader.ReadOuterXml();


            XmlDocument dom = new XmlDocument();
            dom.Load(reader);

            var settings = new XmlWriterSettings();
            settings.Indent = true;
            settings.OmitXmlDeclaration = true;

                using (var writer = XmlTextWriter.Create(@"\\file\.xml", settings))
                {
                    dom.WriteContentTo(writer);



            }
        }
        reader.Close();
    }

Upvotes: 1

Views: 3072

Answers (3)

Vinnie Amir
Vinnie Amir

Reputation: 621

I usually get XML from the Stored procedure either as a field (Rec.GetString(Rec.GetOrdinal("XMLFieldName")) then load this string into an XMLDocument using dom.LoadXML(strXML). What I dont understand is why you would want to do that? Just save the XML string to a text file. Its the same thing. You only use XMLDocument if you want to parse it, add/remove nodes, etc..

Upvotes: 0

Adam Bilinski
Adam Bilinski

Reputation: 1198

This should work

using (XmlReader reader = cmd.ExecuteXmlReader()) //e.command.commandtimeout = 500
    {
        var settings = new XmlWriterSettings();
        settings.Indent = true;
        settings.OmitXmlDeclaration = true;

        using (var writer = XmlTextWriter.Create(@"\\file\.xml", settings))
        {
           while (reader.Read())
           {
            //  string s = reader.ReadOuterXml();


                XmlDocument dom = new XmlDocument();
                dom.Load(reader);
                dom.WriteContentTo(writer);
           }
        }
    reader.Close();
}

Upvotes: 1

Dexion
Dexion

Reputation: 1101

Move this : using (var writer = XmlTextWriter.Create(@"\file.xml", settings)) outside of the while.

Upvotes: 0

Related Questions