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);