locked
Multiple DataSource Controls from 1 Stored Procedure RRS feed

  • Question

  • User1120190316 posted

    I have a stored procedure that returns two data sets.  Is it somehow possible, using VB.Net and/or ASP.Net code, to attach each of these two data sets to a separate SqlDataSource control?

    Tuesday, September 22, 2020 2:56 AM

All replies

  • User-939850651 posted

    Hi Sheldon Penner,

    According to your description, do you mean that multiple DataTables will be returned in a stored procedure? If this is the case, you could use DataSet to receive the result set.

    Just like this:

    use DataBaseName
    Go
    create or alter PROCEDURE  test_proc
    @param varchar(255)	--Possible parameters
    AS
    BEGIN
    SELECT * FROM TableName1
    SELECT * FROM TableName2
    END
    
     SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ToString());
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand("test_proc", con);
                cmd.CommandType = CommandType.StoredProcedure;
                //if you have parameters.
                cmd.Parameters.AddWithValue("@param", "test_param");
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
                con.Close();
    
                GV1.DataSource = ds.Tables[0];
                GV1.DataBind();
                GV2.DataSource = ds.Tables[1];
                GV2.DataBind();

    Result:

    If I misunderstood something, please let me know.

    Best regards,

    Xudong Peng

    Thursday, September 24, 2020 10:28 AM