using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public class clsCommonn
{
    public clsCommonn()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    /// CREATED BY : Kishori Athavale
    /// CREATED ON : 27 April 2008
    /// To Export Data Table to Excel File
    /// Parameters To Pass : 1) File Name To Be Generated
    ///                      2) Data Table To Be Exported
    ///                      3) HttpResponse Object
    public static void DataGridToExcel(string strFileName, DataTable dtToExport, HttpResponse response)
    {
        System.IO.StringWriter stringWrite = null;
        System.Web.UI.HtmlTextWriter htmlWrite = null;
        DataGrid dg = null;
        try
        {
            response.Clear();
            response.AddHeader("content-disposition", "attachment;filename=" + strFileName + ".xls");
            response.Charset = "";
            response.ContentType = "application/vnd.ms-excel";
            stringWrite = new System.IO.StringWriter();
            htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
            dg = new DataGrid();
            dg.DataSource = dtToExport;
            dg.DataBind();
            dg.GridLines = GridLines.Both;
            dg.HeaderStyle.Font.Bold = true;
            dg.DataBind();
            dg.RenderControl(htmlWrite);
            response.Write(stringWrite);
            response.End();

        }
        catch
        {
            // setAlert("Unexpected error occured while exporting file Excel.");
            throw;
        }
        finally
        {
            // Dispose objects
            if (null != dg) dg.Dispose();
            if (null != stringWrite) stringWrite.Dispose();
            if (null != htmlWrite) htmlWrite.Dispose();
        }
    }


}

public partial class ShoaibMIS_state_wise_member : System.Web.UI.Page
{
    public static DataTable dtTable;
    protected void Page_Load(object sender, EventArgs e)
    {
        //if (Session["u_id"] == null)
        //{
        //    Response.Redirect("login.aspx");
        //}
        //String str = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionKey");
        //SqlConnection sqlcon = new SqlConnection(str);
        //sqlcon.Open();
        //DataSet ds = new DataSet();
        //SqlDataAdapter da = new SqlDataAdapter("SELECT Members.ID, Members.OrganisationName, State.State_Name, Members.Membership_No, Members.Status, Members.App_Date, Members.Address, Members.dist, Members.pin, Members.phone1, Members.phone2, Members.email1, Members.representative, Region.Region_name FROM Members INNER JOIN Region ON Members.Region_ID = Region.Region_id INNER JOIN State ON Members.State_ID = State.State_ID AND (Region.Region_id = State.Region_ID) where State_Name=" + "'" + DropDownList1.SelectedItem + "'", sqlcon);
        //da.Fill(ds, "members");
        //dtTable = ds.Tables["members"];


    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        //String str = ConfigurationSettings.AppSettings["ConnectionKey"];
        //SqlConnection sqlcon = new SqlConnection(str);
        //sqlcon.Open();
        //DataSet ds = new DataSet();
        //SqlDataAdapter da = new SqlDataAdapter("SELECT Members.ID, Members.OrganisationName, State.State_Name, Members.Membership_No, Members.Status, Members.App_Date, Members.Address, Members.dist, Members.pin, Members.phone1, Members.phone2, Members.email1, Members.representative, Region.Region_name FROM Members INNER JOIN Region ON Members.Region_ID = Region.Region_id INNER JOIN State ON Members.State_ID = State.State_ID AND (Region.Region_id = State.Region_ID) where State_Name=" + "'" + DropDownList1.SelectedItem  + "'", sqlcon);
        //da.Fill(ds, "members");
        //DataTable dtTable = ds.Tables["members"];
        //int count = dtTable.Rows.Count;
        //Label2.Text = count.ToString();
        //sqlcon.Close();
    }
    protected void export_Click(object sender, EventArgs e)
    {
        //try
        //{
        //    clsCommonn.DataGridToExcel("File1", dtTable, Response);

        //}
        //catch
        //{
        //    //lblError.Text = "Unexpected error occured while exporting file Excel.";
        //    //lblError.Visible = true;
        //}
    }
}