none
how fetch values from SQL Stored procedure using a data table RRS feed

  • Question

  • Hello All,

    I am connecting to a stored procedure using my C# code and returning 2 values from it. But as a function can return only 1 value so am calling the same SP twice to get the 2 values.

    Now I want to fetch both the records in a data table so that the SP is called only once.

    wanted to know the way how to do this.?

    Monday, October 7, 2013 11:29 AM

Answers

  • Hello,

    Thanks for posting your question to this forum.

    Since I do not know what type values are returned from the store procedure, I assume that it returns two different table query records like below:

    CREATE PROCEDURE [dbo].[FetchMoreRecordFromProcedure]
    
    AS
    
           select * from Course;
    
    
           select * from [Order];
    
    
    RETURN 0
    

    In C#, for getting the result, we usually write codes like below:

    internal void Execute()
    
            {
    
                string connectionString = "server=(localdb)\\V11.0;Integrated Security=SSPI;database=TestDataBase";
    
    
                SqlConnection connection = new SqlConnection(connectionString);
    
    
                connection.Open();
    
    
                string sql = "FetchMoreRecordFromProcedure";
    
    
                SqlCommand cmd = new SqlCommand(sql, connection);
    
    
                SqlDataAdapter da = new SqlDataAdapter(cmd);
    
    
                DataSet ds = new DataSet();
    
    
                da.Fill(ds);
    
    
                connection.Close();
    
    
                connection.Dispose();
    
            }
    

    It will fill the returned records into the DataSet and then the DataSet will have two table query records Course and Order.

    If this does not work for you, could you please share the store procedure so that we could help you better?

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 8, 2013 6:21 AM
    Moderator

All replies

  • Hello,

    Thanks for posting your question to this forum.

    Since I do not know what type values are returned from the store procedure, I assume that it returns two different table query records like below:

    CREATE PROCEDURE [dbo].[FetchMoreRecordFromProcedure]
    
    AS
    
           select * from Course;
    
    
           select * from [Order];
    
    
    RETURN 0
    

    In C#, for getting the result, we usually write codes like below:

    internal void Execute()
    
            {
    
                string connectionString = "server=(localdb)\\V11.0;Integrated Security=SSPI;database=TestDataBase";
    
    
                SqlConnection connection = new SqlConnection(connectionString);
    
    
                connection.Open();
    
    
                string sql = "FetchMoreRecordFromProcedure";
    
    
                SqlCommand cmd = new SqlCommand(sql, connection);
    
    
                SqlDataAdapter da = new SqlDataAdapter(cmd);
    
    
                DataSet ds = new DataSet();
    
    
                da.Fill(ds);
    
    
                connection.Close();
    
    
                connection.Dispose();
    
            }
    

    It will fill the returned records into the DataSet and then the DataSet will have two table query records Course and Order.

    If this does not work for you, could you please share the store procedure so that we could help you better?

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 8, 2013 6:21 AM
    Moderator
  • The SP is returning 2 values after applying an inner join on two tables.
    Tuesday, October 8, 2013 7:41 AM
  • Hi abh_inventive_genius,

    Is it that my answer solve your question because I notice that you have marked my reply to be answer?

    Or if the issue still exists, could you please share the SP so that we can unserstand what is the meaning about 'The SP is returning 2 values after applying an inner join on two tables.'

    Thanks &Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, October 8, 2013 8:25 AM
    Moderator