Multiple Tables returned from a Stored Procedure

Question

Hi,

 

 I have a dataset with 2 tables in it which I use for reporting in Crystal. Now I have created an SP in Mssql to fill these tables in one go. But everytime I call the SP in my dataset I get 2 new tables named Table & table1 and my original tables remain empty. Is there a way that from my SP I fill my 2 tables and not having 2 new ones created so that my report works fine. Hope this is clear because I am not sure

Answer 1

Hi Ivan,

Since this forum is mainly discuss questions about database/server project, I'm going to move your post to the appropriate SQL Server Data Access forum for more efficient response.

Thanks for your understanding.

Best Regards,

---

Andrew Wu [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Ivan,

Could you please show us your stored procedure and a code snippet of your application? If you want to populate two existing DataTable in a DataSet from a SqlDataAdapter, please see:

T-SQL:

CREATE PROCEDURE TwoResults
AS
BEGIN
	SELECT object_id FROM sys.tables
	SELECT object_id FROM sys.procedures
END

 

C#: 

using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Integrated Security=SSPI"))
{
 SqlCommand cmd = new SqlCommand("TwoResults", conn);
 cmd.CommandType = CommandType.StoredProcedure;

 SqlDataAdapter da = new SqlDataAdapter(cmd);

 DataSet ds = new DataSet();

 ds.Tables.Add("TheFirstResult");
 ds.Tables.Add("TheSecondResult");

 da.TableMappings.Add("Table","TheFirstResult");
 da.TableMappings.Add("Table1", "TheSecondResult");  

 da.Fill(ds); 

}

 

Reference: DataAdapter DataTable and DataColumn Mappings (ADO.NET)

 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 3

And, if you don't care what the tables are called (IOW, you're fine with leaving them called Table and Table1), you can skip some of the steps above, and simply do this:

using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=TestDB;Integrated Security=SSPI"))
{
 SqlCommand cmd = new SqlCommand("TwoResults", conn);
 cmd.CommandType = CommandType.StoredProcedure;

 SqlDataAdapter da = new SqlDataAdapter(cmd);

 DataSet ds = new DataSet();

 da.Fill(ds); 
}

 

---

~~Bonnie Berent [C# MVP]

geek-goddess-bonnie.blogspot.com