How to Export data from multiple join table to excel in asp.net C#

See more:

ASP.NET

SQL

stored-procedure

Hi

I want to export some data from data base to simple format excel file
I write the bellow code for this all code work right,

I have selected data from multiple tables by a store procedure that the table are join to gather, the main table is School table every school has some child tables like studentTB, BookTB,TeachersTB,ProvinceTB,DistrictTB........
now I want every school ID export from data base to one row of simple format excel file with full of child tables

my C# code

protected DataTable GetDatafromDatabase()
    {
        DataTable dt = new DataTable();
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("ExportDatatoExcel", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            con.Close();
        }
        return dt;
    }
    protected void BtnExportData_Click(object sender, EventArgs e)
    {

        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "School.xls"));
        Response.ContentType = "application/ms-excel";
        Response.ContentEncoding = System.Text.Encoding.Unicode;
        Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

        DataTable dt = GetDatafromDatabase();
        string str = string.Empty;
        foreach (DataColumn dtcol in dt.Columns)
        {
            Response.Write(str + dtcol.ColumnName);
            str = "\t";
        }
        Response.Write("\n");
        foreach (DataRow dr in dt.Rows)
        {
            str = "";
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                Response.Write(str + Convert.ToString(dr[j]));
                str = "\t";
            }
            Response.Write("\n");
        }
        Response.End();

    }//

SQl Code

USE [School]
GO
/****** Object:  StoredProcedure [dbo].[ExportDatatoExcel]    Script Date: 6/16/2015 12:00:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[ExportDatatoExcel]
as
begin

select PName_en,DName_en,SName,Code,Type,Grade,School.Gender, 

Enrolled_Male,Enrolled_Female,Passed_Male,Passed_Female,
Failed_Male,Failed_Female,PermanentAbsent_Male,PermanentAbsent_Female,
DropOut_Male,DropOut_Female,

HeadMaster_Teachers,Teachers_TB.Gender,Phd,Master,Bachelors,TED15,TED14,TED12,
Vocational14,Vocational12,RelegiousMadrasa14,RelegiousMadrasa12,Sport14,Sport12,
General12,LowerSecondary,Primaryt,Private,
TeachersTashkeel,Male,Female

from School
join Province on School.ProvinceID=Province.P_ID 
join District on School.DistrictID=District.D_ID 
INNER JOIN Students ON School.S_ID = Students.Student_ID
join Teachers_TB on Teachers_TB.Teacher_ID=School.S_ID
join Tashkeel on Tashkeel.TashkeelID=School.S_ID
Order by P_ID,D_ID

end