search, filter, paging, sorting in gridview via ADO.NET(code)

噫~~這是我的code, 大家會覺得我寫得很傻, 特別是在bind() ,本來想做到, 不用criteria 時, sql statement 會不同。但當我將sqlstr 化為variable, 它就說sqlstr不存在。由於我想不到更好的方法, 只可以這樣, 如有更好的方法, 請提點。

還有一個問題就是, 行這個是, 每當一run 完這個export, 之後的code 基本上都不會行。如果我打句response.redirect, 那個也不會run。結果我想export 完會refresh 個gridview, 就只好加多一個button 來手動…汗…不知怎改。(這個好東西是在此處下載)          

  //  pass the grid that for exporting …
            GridViewExportUtil.Export(“Unsubscribe.xls”, this.GridView1);

code

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
public partial class admin_Testing : System.Web.UI.Page
{
    SqlConnection sqlcon;
    string strCon = “Connection String”;

    Hashtable filter;
    protected void Page_Load(object sender, EventArgs e)
    {
        filter = ViewState["FilterArgs"] == null ? new Hashtable() : (Hashtable)ViewState["FilterArgs"];
       
        if (!IsPostBack)
        {
            ViewState["SortOrder"] = “EMAIL”;
            ViewState["OrderDire"] = “ASC”;
            bind();
        }

    }

    //++++++++++Drop Down List+++++++++++++++++
    protected void ddtype_IndexChanged(object sender, EventArgs e)
    {
        txtkey.Text = ddtype.SelectedValue.ToString() + ddtype.SelectedIndex.ToString();
        bind();
    }
    protected void GridView1_OnSorting(object sender, GridViewSortEventArgs e)
    {
        string sPage = e.SortExpression;
        if (ViewState["SortOrder"].ToString() == sPage)
        {
            if (ViewState["OrderDire"].ToString() == “Desc”)
                ViewState["OrderDire"] = “ASC”;
            else
                ViewState["OrderDire"] = “Desc”;
        }
        else
        {
            ViewState["SortOrder"] = e.SortExpression;
        }
        bind();
    }
    public void GridView1_OnPageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        ViewState["PageIndex"] = e.NewPageIndex;
        bind();
    }

    public void bind()
    {

        if (ddtype.SelectedIndex != 0)
        {
            txtkey.Text = ddtype.SelectedValue.ToString();
            string sqlstr3 = “SELECT * FROM [Table] WHERE [ACTIVE]=0 and [TYPE]=’” + txtkey.Text + “‘”;
            sqlcon = new SqlConnection(strCon);
            SqlDataAdapter myda3 = new SqlDataAdapter(sqlstr3, sqlcon);
            DataSet myds3 = new DataSet();
            sqlcon.Open();
            myda3.Fill(myds3, “Table”);

            DataView view3 = myds3.Tables["Table"].DefaultView;
            string sort3 = (string)ViewState["SortOrder"] + ” ” + (string)ViewState["OrderDire"];
            view3.Sort = sort3;

            GridView1.DataKeyNames = new string[] { “UID” };
            GridView1.DataSource = view3;
            GridView1.DataBind();
            sqlcon.Close();
        }
        else
        {
            if (txtSearch.Text != null)
            {
                string sqlstr4 = “SELECT * FROM [Table] WHERE [ACTIVE]=0 and [EMAIL] like ‘%” + txtSearch.Text + “%’”;
                sqlcon = new SqlConnection(strCon);
                SqlDataAdapter myda = new SqlDataAdapter(sqlstr4, sqlcon);
                DataSet myds4 = new DataSet();
                sqlcon.Open();
                myda.Fill(myds4, “Table”);

                DataView view4 = myds4.Tables["Table"].DefaultView;
                string sort4 = (string)ViewState["SortOrder"] + ” ” + (string)ViewState["OrderDire"];
                view4.Sort = sort4;

                GridView1.DataKeyNames = new string[] { “UID” };
                GridView1.DataSource = view4;
                GridView1.DataBind();
                sqlcon.Close();
            }
            else
            {

                string sqlstr = “SELECT * FROM [Table] WHERE [ACTIVE]=0″;
                sqlcon = new SqlConnection(strCon);
                SqlDataAdapter myda = new SqlDataAdapter(sqlstr, sqlcon);
                DataSet myds = new DataSet();
                sqlcon.Open();
                myda.Fill(myds, “Table”);

                DataView view = myds.Tables["Table"].DefaultView;
                string sort = (string)ViewState["SortOrder"] + ” ” + (string)ViewState["OrderDire"];
                view.Sort = sort;
                GridView1.DataKeyNames = new string[] { “UID” };
                GridView1.DataSource = view;
                GridView1.DataBind();

                sqlcon.Close();
            }
        }
    }

     protected void Export_Click(object sender, EventArgs e)
    {
        this.GridView1.GridLines = GridLines.Both;
        if (this.CheckBox1.Checked == true)
        {
            this.GridView1.AllowPaging = false;
            bind();
       }
        sqlcon = new SqlConnection(strCon);
        SqlCommand sqlcom;

        for (int i = 0; i <= GridView1.Rows.Count – 1; i++)
        {
            sqlcon.Open();
            string sqlstr = “UPDATE [Table] SET [ACTIVE]=1 where [UID]=’” + GridView1.DataKeys[i].Value + “‘”;
          sqlcom = new SqlCommand(sqlstr, sqlcon);

            sqlcom.ExecuteNonQuery();
            sqlcon.Close();
        }
            //  pass the grid that for exporting …
            GridViewExportUtil.Export(“Table.xls”, this.GridView1);
           
        bind();

       }

     protected void Button4_Click(object sender, EventArgs e)
    {
        sqlcon = new SqlConnection(strCon);
        SqlCommand sqlcom;

               sqlcon.Open();
                string sqlstr = “UPDATE [Table] SET [ACTIVE]=0″;
                sqlcom = new SqlCommand(sqlstr, sqlcon);

                sqlcom.ExecuteNonQuery();
                sqlcon.Close();
        bind();
         Response.Redirect(“Testing.aspx”);
    }
      protected void Button5_Click(object sender, EventArgs e)
    {
                sqlcon = new SqlConnection(strCon);
                SqlCommand sqlcom;
                sqlcon.Open();
                string sqlstr = “UPDATE [Table] SET [ACTIVE]=2 WHERE [ACTIVE]=1″;
                sqlcom = new SqlCommand(sqlstr, sqlcon);

                sqlcom.ExecuteNonQuery();
                sqlcon.Close();
                Response.Redirect(“Testing.aspx”);

    }
    protected void Button6_Click(object sender, EventArgs e)
    {

        this.GridView1.GridLines = GridLines.Both;
        if (this.CheckBox1.Checked == true)
        {
            this.GridView1.AllowPaging = false;
            bind();
        }
        Response.Redirect(“Testing.aspx”);
    }

    protected void txtSearch_TextChanged(object sender, EventArgs e)
    {
        bind();
    }
}