从 sql server 存储过程返回多个数据集 [英] Return multiple datasets from sql server stored procedure

问题描述

我需要基于调用运行 5 个不同查询的存储过程通过 Web Api 返回 Base64 XML 输出.

I need to return through Web Api a Base64 XML output based upon calling a stored procedures which runs 5 different queries.

未编写存储过程(我需要编写它)但是有 5 个查询中的数据是完全不同的表和列等......所以我想知道这是否可能?

Stored procedure is not written ( I need to write it ) but there are 5 queries in which the data is completely different tables and columns etc... so I am wondering if this is even possible?

我知道在 Oracle 中您可以返回多个游标，但是使用 SQL Server，我可以返回到 asp.net 4.5 (mvc c#/Ado.net) 多个数据集或集合吗?有没有这样的例子?

I know in Oracle you can return multiple cursors, but with SQL Server , can I return into asp.net 4.5 ( mvc c# / Ado.net) multiple datasets or collections? Any examples of this?

仅一个查询的示例

   -- Content Tab
SELECT -- vTC.[TemplateId]  
  t.Name as "Client Name and Document"  ,vTC.[SectionName] ,vTC.[ContentId] ,vTC.[ContentName]
  ,vTC.[ContentDescription],vTC.[ContentValue] ,CAL.ContentValue as "Spanish Content" , iif(S.IsClientSection = 1, 'Global Section','Template Section') as "Global or Template Section"
  ,DT.Title as DataType ,iif(vTC.IsRequired = 1, 'Yes', 'No') as "Required" ,vTC.[DisplayType] 
FROM [dbo].[vwTemplateContent] vTC
 left join dbo.Template t on vTC.TemplateId = t.TemplateId
  left join dbo.DataType DT on vTC.DataTypeId = dt.datatypeid
   left join dbo.Section S on S.SectionID = vTC.SectionID
   left join [dbo].[ContentAlternateLanguage] CAL on vTC.ContentId =    CAL.ContentID
  where vTC.templateid in (1) 
  order by DisplayOrder

推荐答案

如果您要获得多个表，那么您必须将多个 select 语句写入您的存储过程，如下所示:

If you are going to get multiple tables then you have to write multiple select statements into your stored procedure like below:

CREATE PROCEDURE SPName
(
/*Declare your parameters*/
@parm1 dataType
)
AS
BEGIN
/*Write your select statements below*/
-- SELECT * FROM tblName
-- SELECT * FROM tblName2

END

您必须将这些记录填充到您的数据集中，DataSet 支持多表到 ADO.net.

You have to fill these records into your DataSet, DataSet supports multiple table into ADO.net.

请参考以下代码来填充您的数据集:

Please refer below code to fill your DataSet:

SqlConnection con=new SqlConnection("YourConnection String");
SqlCommand cmd=new SqlCommand();
SqlDataAdapter da=new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("SPName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@parm1", id);//if you have parameters.
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();

在此之后，您可以使用

ds.Tables[0]
ds.Tables[1]
..

希望能帮到你

谢谢

这篇关于从 sql server 存储过程返回多个数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！