How to binding to gridview using SqlDatasource

Question

User729207731 posted

I have one store procedure  

and one gridview (i'm use radgrid same with  asp gridview)

and in sql Sp  i have three select command

create procedure xxxyyyZZZ
BEGIN
        xxxxxxxxxxxxx
         xxxxxxxxxxxxxx
        commit
	  end
	close cs_ge
	deallocate cs_gr

	select * from #g1
	select * from #g2
	select * from #g3
	select * from #temp
END

but when i bingding to gridview only row  #g1 select.

i want to display line two (#g2), how to do this, thanks in advance.

Answer 1

User-654786183 posted

If all #g1, #g2, #g3 and #temp return the same columns in same order then try changing your Stored Procedure to have an union of all the select statements.  Your stored procedure currently return the result set from the first select statement

select * from #g1
UNION
select * from #g2
UNION
select * from #g3
UNION
select * from #temp

Answer 2

User1724605321 posted

Hi hoitm,

Do you want to bind the second select query results to gridview ? When multi select statements in one single procedure ,you will get  all values into one dataset and you could  read the individual datatable ,then bind to gridview .  Code below is for your reference :

T-SQL

CREATE PROCEDURE testSP
AS
BEGIN
	SELECT CategoryID,CategoryName,Description FROM Categories 
	SELECT SupplierID,CompanyName,ContactName FROM Suppliers
	SELECT ProductID,ProductName,CategoryID,SupplierID FROM Products
END
GO


C#

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindNewConnectionString"].ToString()))
{
 SqlDataAdapter da = new SqlDataAdapter("testSP",conn);
 da.SelectCommand.CommandType = CommandType.StoredProcedure;

 DataSet ds = new DataSet();

 da.Fill(ds);

 DataTable dtCategories = ds.Tables[0];
 DataTable dtSuppliers = ds.Tables[1];
 DataTable dtProducts = ds.Tables[2];
}

After that bind the specific datatable to gridview .

Best Regards,

Nan Yu

Answer 3

User729207731 posted

thanks so much. :D