/// gets the dataset from the database.
public void Load_Data()
SqlCommand myCommand = new SqlCommand("[dbo].[ExtractWebData]", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(myCommand);
ds = new DataSet();
ds.Tables.TableName = "Customers";
ds.Tables.TableName = "Purchase_Orders";
ds.Tables.TableName = "Purchase_Order_Details";
ds.Tables.TableName = "Inventory";
Now if there was just some way to use that in a report. It would be great to use a stored procedure to twist all the data into shape before handing it off to a reporting tool! So you create an xsd. You add a table adapter.
Except that you can't do that. You see, the .rdlc report requires that you have the data available at design time in order to design the report. And there is no way to import multiple datasets at once into the .xsd at design time.
From this document: http://msdn.microsoft.com/en-us/library/dd239331.aspx
I have no idea what the text-based query designer is, but as we saw in SQL Server Management Studio...If multiple result sets are retrieved through a single query, only the first result set is processed, and all other result sets are ignored. For example, when you run the following query in the text-based query designer, only the result set for Production.Product appears in the result pane:SELECT ProductID FROM Production.Product GO SELECT ContactID FROM Person.Contact
In my opinion, this is an EPIC DESIGN FAIL on the part of Microsoft.
We know from the earlier code snippet that Visual Studio can access the data, it just - for some stupid reason - is designed in such a way as to disable this feature in certain cases. This is completely unacceptable. But until Microsoft fixes this glaring, stupid, boneheaded omission, we're stuck with it.
Now, I know this blog has no regular readers. You didn't find this page because you're a fan of Visual Studio Journey. You found it because you were googling for this problem, and there were no answers anywhere else. I wish I had better news, but I don't.
Here is the only way I know to work around this issue. Unravel your stored procedure and execute the whole thing in your client. Convert each piece into individual queries, and add those to your .xsd.
Alternately, you could split your stored procedure into multiple pieces, like Proc1, Proc2, Proc3... but then you've kind of lost the convenience of the one-stop shopping the stored procedure offers.
Just to be clear: I think that the ability to return multiple result sets from a stored procedure is awesome! Kudos to the SQL Server development team. If only the Visual Studio guys would catch some of that brilliance, things would be great.
Just to make myself clear, I think that this feature has awesome potential and multi-table stored procedures are still incredibly useful in Visual Studio. They just are not usable for rdlc reports (the one thing they would be most ideally suited for in an ideal world).
Bryan Valencia is a contributing editor and founder of Visual Studio Journey. He owns and operates Software Services, a web design and hosting company in Manteca, California.