how to get stored procedure's batches query in different dataset

Question

0.00/5 (No votes)

See more:

I'm trying to fetch all the data from one stored procedure for different different Dataset in a single page.

SQL

stored procedure CREATE PROCEDURE [dbo].[usp_Details](@status int, @Id int) AS begin

Select u.Id,u.FName,u.ImageName,u.ImagePath,u.Sex FROM [User] as u where u.Id IN (SELECT MyId as Id FROM Friends WHERE FriendId=@Id AND FriendStatus=0)

Select Points,FName from [User] where Id=@Id

SELECT ImagePath from [User] where Id=@Id end GO

Now, how I can bind datatable/dataset for individual query. Example: query1 for dataset1, query2 for dataset2, query3 for dataset3

If this is not possible then which is the best way to avoid connecting database each time for fetching different tables.

Posted 16-Feb-12 7:11am

Ashwini Verma

Add a Solution

4 solutions

Solution 1

DataSet MyDS = [run your SP];

Now you can use it as

MyDS.Tables[0];
MyDS.Tables[1];
MyDS.Tables[2];

Hope this helps if yes then accept and vote the answer .
I would be very happy to provide solution to queries you have further.
--Rahul D.

Posted 16-Feb-12 8:41am

RDBurmon

Comments

Ashwini Verma 17-Feb-12 0:27am

Thanks for your reply.
Sorry, but I didn't get you. How to filter queries then? could you be a more specific?

Solution 2

Hi, fill dataset, it will give result in sequence in each table. dataset can have multiple tables at a time. when you run this then ds has three tables. first result will be avail in ds.Tables[0]. second will be in ds.Tables[1] and so on.

after that, you can bind grid with each datasource.
i.e. grid1.DataSource = ds.Tables[0] and so on.

Posted 16-Feb-12 18:35pm

Sarvesh Kumar Gupta

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ashwini Verma · Accepted Answer · 2012-02-17T00:08:00

I got the answer but still need a suggestion whether this is worth using.

C#

private void GetMultiSelect()
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnect1"].ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "usp_AllDetail";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sqlParam = cmd.Parameters.Add("@Id", SqlDbType.Int, 4);
                sqlParam.Value = 1;
                //dataset object to get all select statement results
                DataSet ds = new DataSet();

                //sql dataadoptor to fill dataset
                using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                {
                    //here all select statements are fill in dataset object
                    adp.Fill(ds);

                    //now u can fetch each and every select statement by providing table index in dataset

                    foreach (DataTable dt in ds.Tables)
                    {
                        //select statement result in dt..
                    }

                    //or instead of loop u can specify the index
                   GridView1.DataSource= ds.Tables[1]; // first select statement result
                   GridView1.DataBind();
                   GridView2.DataSource = ds.Tables[0]; // second select statement result
                   GridView2.DataBind();
                }
            }
        }
    }

touseef4pk · Accepted Answer · 2012-02-17T00:10:00

@Ashwini Verma

I am providing here some code for your reference so that you make understand this comprehensively.

SqlConnection mySqlConnection =new SqlConnection("server=(local);database=MyDatabase;Integrated Security=SSPI;");

SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText = "IDCategory";
mySqlCommand.CommandType = CommandType.StoredProcedure;
mySqlCommand.Parameters.Add("@IDCategory", SqlDbType.Int).Value = 5;

SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
mySqlDataAdapter.Fill(myDataSet);

and now you can use this dataset in your code as

myDataSet.Tables[0];
myDataSet.Tables[1];
myDataSet.Tables[2];

hope it would help

how to get stored procedure's batches query in different dataset

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0