I have an XML stored in the database table that I am trying to retrieve (variable strXML) below. Any reason why this is coming as blank? Please help. When I execute the procedure directly in database, it's working fine.
我有一个XML存储在我试图检索的数据库表中(变量strXML)。有什么理由说这是空白的?请帮忙。当我直接在数据库中执行该过程时,它工作正常。
try
{
// Open the database connection
using (SqlConnection myConn = my.openConnection("~",PConnectionString"))
{
// Initialize the SQL command object.
using (SqlCommand myCOmmand = new SqlCommand("getCycleXML", myConn))
{
myCOmmand.CommandType = CommandType.StoredProcedure;
// Add the parameters
SqlParameter parameter2 = new SqlParameter("@emailID", SqlDbType.NVarChar, 128);
parameter2.Value = strEmailID;
parameter2.Direction = ParameterDirection.Input;
myCOmmand.Parameters.Add(parameter2);
SqlParameter parameter1 = new SqlParameter("@pCode", SqlDbType.VarChar, 50);
parameter1.Value = strPCode;
parameter1.Direction = ParameterDirection.Input;
myCOmmand.Parameters.Add(parameter1);
SqlParameter parameter7 = new SqlParameter("@cID", SqlDbType.Int);
parameter7.Direction = ParameterDirection.Input;
parameter7.Value = icID;
myCOmmand.Parameters.Add(parameter7);
SqlParameter parameter9 = new SqlParameter("@cycleXML", SqlDbType.Xml);
parameter9.Direction = ParameterDirection.Output;
myCOmmand.Parameters.Add(parameter9);
SqlParameter parameter10 = new SqlParameter("@msg", SqlDbType.VarChar, 50);
parameter10.Direction = ParameterDirection.Output;
myCOmmand.Parameters.Add(parameter10);
SqlParameter parameter11 = new SqlParameter("@Success", SqlDbType.Int);
parameter11.Direction = ParameterDirection.Output;
myCOmmand.Parameters.Add(parameter11);
//Execute the command
myCOmmand.ExecuteNonQuery();
// Read the values of the output parameters
strXML = myCOmmand.Parameters["@cycleXML"].Value.ToString();
=================================================
Stored procedure definition is as follows:
存储过程定义如下:
USE [DMac]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[getCycleXML]
@emailID Nvarchar(128),
@cID varchar(50),
@pCode varchar(50),
@cycleXML xml output,
@Success int output,
@msg varchar(50) output
AS
BEGIN
select XMLDefectCycle from dbo.CycleXML where EmailID=@emailID and PCode=@pCode and CID=@cID
END
==============================
==============================
Why the code is coming back as a blank XML. I checked that for the given parameters in the database I was expecting an XML from the database.
为什么代码将作为空白XML返回。我检查了数据库中的给定参数,我期待数据库中的XML。
1 个解决方案
#1
1
You're not setting the output parameter in your stored procedure
您没有在存储过程中设置输出参数
It should be
它应该是
select @cycleXML = XMLDefectCycle
from dbo.CycleXML where EmailID=@emailID and PCode=@pCode and CID=@cID
or call your stored procedure with ExecuteScalar()
instead
或者使用ExecuteScalar()调用存储过程
#1
1
You're not setting the output parameter in your stored procedure
您没有在存储过程中设置输出参数
It should be
它应该是
select @cycleXML = XMLDefectCycle
from dbo.CycleXML where EmailID=@emailID and PCode=@pCode and CID=@cID
or call your stored procedure with ExecuteScalar()
instead
或者使用ExecuteScalar()调用存储过程