How many tables a dataset can return using stored procedure

I am using a stored procedure wich contains 6 select queries and I am executing this stored procedure from C开发者_开发技巧#. I am getting the tables in dataset of only first 3 queries. i.e. the data is retrieved from first 3 queries I used. The dataset contains only 3 tables.

I am unable to understand what's wrong or whether executing the stored procedure from C# can retrieve a maximum of only 3 tables

UPDATE:

Create Procedure myTest
    @paraID as bigint
AS

Here I have written 6 select queries using the parameter @paraid in where clause

In C#:

SqlConnection con = new SQLConnection();
SqlCommand cmd = new SQLCommand();
SqlDAtaadpter adpt = new SqlDataadapter();

DataSet ds = new DataSet();

con.ConnectionString = "MyConnectionString";

con.Open();
cmd.CommandType= CommandType.StoredProcedure;
cmd.CommandText ="mytest";
cmd.Parameters.Add("@paraID", SqlDbType.Bigint).value = 1;

cmd.Connection = con;

adpt.SelectCommant = cmd;
adpt.Fill(ds);

con.Close();

This is my c# code. Now tell me what has to be done

FRom DbDataAdapter.Fill Method (DataSet)

If a command does not return any rows, no tables are added to the DataSet, and no exception is raised.

When the SELECT statement used to populate the DataSet returns multiple results, such as batch SQL statements, if one of the results contains an error, all subsequent results are skipped and are not added to the DataSet.

When the query specified returns multiple results, the result set for each row returning query is placed in a separate table. Additional result sets are named by appending integral values to the specified table name (for example, "Table", "Table1", "Table2", and so on). Because no table is created for a query that does not return rows, if you process an insert query followed by a select query, the table created for the select query is named "Table" because it is the first table created. Applications using column and table names should ensure that conflicts with these naming patterns does not occur.

Are you sure there are no errors and that all 6 SELECT statements are returning data?

There is no as such data table limitation on dataset, so in your case you can store and return data table from SP. I think there must be some issue in your sp execution. If you are sure there is no error on sp and no exception then for testing you can use microsoft dll which tell you what exactly is going on.

Microsoft provide a good dll (Data Access Application Block) for accessing data base, so you can try that. In this dll there are some function in SqlHelper class like ExecuteDataSet, ExecuteNonQuery etc..

So in your case you can use

object[] objParams = { par1,par2}; DataSet ds = SqlHelper.ExecuteDataset(ConnectionString, "SP_Name", objParams);

and SP_Name should be like this..

crerate Procedure SP_Name varchar @par1 as

// Select * from tab1 // select * from tab2 // select * from tab3 // select * from tab4 end

so on SqlHelper's ExecuteDataSet, you will get 4 table... So try ApplicationDataBlock.dll

I realise this question is old, but when I came upon the same problem, it was the only question I found by googling that described the problem. The accepted answers did not help, so I share my solution.
To recap the problem. Using the DataAdapter to fill tables from a stored procedure that returns multiple result sets, you get less tables than you expected. In my case it was 4 from 8. Calling the stored procedure directly in Management Studio returns the correct number. The original OP said nothing about environment. I experienced the problem using mono on a Linux distribution to access SQL Server over the network. When running the same c# binary locally on the machine hosting SQL Server, there was no problem. When I checked, every single stored procedure that I have, which returns multiple resultsets, had the same problem when called from Linux.
In order to be thorough, I set up SQL Profiler to catch the SP call. Although you cannot see the actual results returned, it did confirm that the correct number of rows was being fetched. I was therefore forced to conclude that there is a bug somewhere in the mono implementation that makes the DataAdapter fail to fill in all the tables. Fortunately there is a nice way to get around the problem, by not using a DataAdapter at all, but by using a DataReader. I already had a helper function, which returns a DataTable from a DataReader (not hard to write for yourself, but if anyone needs help, please let me know), so I was able to code thus:

SqlDataReader rdr = cmd.ExecuteReader();
List<DataTable> tables = new List<DataTable>();
while (rdr.HasRows)
{
    DataTable t = GetTableFromReader(rdr);
    tables.Add(t);
    rdr.NextResult();
}

Then all I needed to do was to add the tables to a DataSet and I had achieved exactly what the DataAdapter's Fill method did. This technique works for both the native Windows and Mono implementations of .Net. Incidentally, in my experience the quoted extract from the documentation is in fact wrong. It states that if any part of the multiple resultsets contains no rows, then the DataAdapter's Fill method does not add the table to the DataSet. This is not correct. In fact you do get a table added, with columns correctly set, just with no rows. This is particularly important (and very useful), if you are binding to the DataSet. My above workaround, however, does not do this. It behaves as the documentation says that the DataAdapter should! So if you are binding to the resultant DataSet, you will have to add your own empty tables to replace the missing ones. The fact that my workaround works and retrieves the correct number of tables is further evidence for believing that the problem experienced is a bug in the mono implementation.