How to bind multiple Table data in gridview

Question

---

1. am using Grid view for displaying the data according to user selection.If in drop down user select all it has to display all the data from the two or more tables.

here is my store procedure:-

USE [TenderCrawling] GO /****** Object: 
 StoredProcedure [dbo].[Usp_Temp]   
 Script Date: 5/29/2017 11:51:33 AM ******/
 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER 
ON GO -- ============================================= --
 Author: <Author,,Name> -- Create date: <Create Date,,> -- 
Description: <Description,,> -- ============================================= 
ALTER PROCEDURE [dbo].[Usp_Temp] @SiteId int AS BEGIN SET NOCOUNT ON; 
Declare @abc int set @abc=(select  count(1) from sitemaster) 
Declare @TemplateName varchar(50) Declare @Isindian bit     
Declare @Id int Declare @output nvarchar(max) Declare @temp bit   
  
Select @TemplateName=Template,@Isindian=IsIndianSite from SiteMaster where SiteId=@SiteId     SET @TemplateName= (select substring(@TemplateName, 1, (len(@TemplateName) - 4)))    SET @TemplateName=@TemplateName+'_ErrorLog'      
IF EXISTS (SELECT 1 FROM Information_SCHEMA.Tables
 WHERE TABLE_NAME = @TemplateName)     
 BEGIN         
 set @temp=1    
  END     
 else Begin set @temp=0 End if(@temp=1)           
  begin                
 SET @output='select log,OrganizationName,TenderNumber,RequirementWorkBrief,TenderDetailWorkDescription,NameOfWebSite' 
if(@Isindian=0)
 Begin                               
 SET @output+=',City,State,Country from'+' '+@TemplateName+' '+'e                                  left join sitemaster s on s.SiteId=e.SiteId Where e.IsDuplicate = 0'                             end                 else Begin                      SET @output+= '  from'+' '+@TemplateName+' '+'e                         left join sitemaster s on s.SiteId=e.SiteId Where e.IsDuplicate = 0 '             end      end      print @output    exec sp_executesql  @output    END

end

else

Begin

SET @output+= ' from'+' '+@TemplateName+' '+'e left join sitemaster s on s.SiteId=e.SiteId Where e.IsDuplicate = 0 '

end

end

print @output exec sp_executesql @output

END

 

 --here @TemplatName is the name of the table present in Information_SCHEMA. but the porblem i am facing is the fields .If it is indian then @templatName_ErrorLog table dose not contain field like city,country and state.where as if its is not indian it contain fields like city ,country and state. 

 

 

--when code runs in asp.net application its throws error that invalid column name city ,country ,state because i am trying to bind all the indian and global table data at same time in the grid view by selecting option All from dropdown. 

 

 here is my .net code:-

 

 List<Errorlog> lst = new List<Errorlog>();
List<int> id = new List<int>();
SqlCommand cmd = new SqlCommand("Select SiteId from SiteMaster", con);

con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
id.Add(Convert.ToInt16(dr["SiteId"]));

}
dr.Close();
foreach (int k in id)
{
DynamicParameters param = new DynamicParameters();
param.Add("SiteId", k, DbType.Int16);
List<Errorlog> temp = con.Query<Errorlog>("Usp_Temp", param, null, true, 200, CommandType.StoredProcedure).ToList();

lst.AddRange(temp);

}

if (lst.Count != 0)
{

grd.DataSource = lst;
grd.DataBind();
}