Saturday, February 25, 2012

multiple select statement in a stored-proc?

I am using SQL sever 2k and C#.

There is a stored-procedure that it has multiple select statements as returned result set. How can I use SqlCommand.ExecuteReader to get all result set?

What if the multiple select statements is " FOR XML", how can I set all xml using ExecuteXmlReader?

I tried to use ExecuteReader or ExecuteXmlReader, but seems that I can only get back the result set of the first select statement, all others are messed up.

stored procedure example: NorthWind database:


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

Create PROCEDURE dbo.getShippersAndEmployeesXML
AS

select * from Shippers for xml auto, elements
select * from Employees for xml auto, elements

RETURN @.@.ERROR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

C# code example:


//set connect, build sqlcommand etc

XmlTextReader reader = (XmlTextReader)command.ExecuteXmlReader();
StringBuilder sb = new StringBuilder();
while(reader.Read()) sb.Append(reader.ReadOuterXml());

Thanks for your help.

reader.NextResult()
|||it works for SqlDataReader. however XmlTextReader seems don't have this method. anyway I can just use SqlDataReader.
thanks very much.

No comments:

Post a Comment