0

I'm passing an id to a stored procedure, and that procedure returns XML to me (well, supposed to). If I don't add parameters from my C# but instead hard code them into my procedure, I get all the XML I ever wanted. On the flip side, I get nothing as far as XML content goes. There is some kind of disconnect here, and would appreciate any advice!

Here is some of my C#:

public string BuildXml(string po)
{
    StringBuilder sb = new StringBuilder();

    using (var dbConn = new SqlConnection(ConnectString))
    {
        using (var command = new SqlCommand(
            "BuildXML", dbConn
        ))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(
                "@PONUM", SqlDbType.VarChar
            ).Value = po;

            command.Parameters.Add(
                "@EVERYTHING", SqlDbType.Xml
            ).Direction = ParameterDirection.Output;

            dbConn.Open();

            XmlReader xmlr = command.ExecuteXmlReader();

            xmlr.Read();

            while (xmlr.ReadState != ReadState.EndOfFile)
            {
                sb.AppendLine(xmlr.ReadOuterXml());
            }
        }
    }
}

The stored procedure is quite long, so here is just what I'm passing:

@PONUM varchar(18),
@EVERYTHING XML OUTPUT

If I comment-out the "command.Parameters.Add" code blocks in the C#, comment-out the variables above, and add the following to the body of my procedure, everything works as it should:

DECLARE @EVERYTHING XML
DECLARE @PONUM varchar(18)
SET @PONUM = 100203130

Is C# not passing the parameters or is passing them incorrect? Is the procedure not receiving them or is receiving them incorrect?

Thanks for your time!


UPDATE 1: As per user suggestions, explicitly providing output parameter and input parameter size didn't change anything. Here is the revised block:

command.Parameters.Add(
    "@PONUM", SqlDbType.VarChar, 18
).Value = po;

//command.Parameters.Add(
//    "@EVERYTHING", SqlDbType.Xml
//).Direction = ParameterDirection.Output;

var output = new SqlParameter
{
    SqlDbType = SqlDbType.Xml,
    Direction = ParameterDirection.Output,
    ParameterName = "@EVERYTHING"
};

command.Parameters.Add(output);
midoriha_senpai
  • 177
  • 1
  • 16

0 Answers0