public abstract class SqlDataGrid : System.Web.UI.WebControls.WebControl,INamingContainer
    {


        #region Enumerations
        //
        // Public Enumarators
        //
        public enum ListTypes
        {
            PAGE,
            SCROLL
        }
        public enum Positions
        {
            TOP,
            BOTTOM,
            HIDE
        }
        public enum SortDirections
        {
            ASCENDING,
            DESCENDING
        }
        #endregion

        #region Variables

        private DataGrid MyDataGrid;

        // The genric Connection Interface variable
        private System.Data.IDbConnection _Connection;

        private Button            btnFilter;
        private TextBox            txtSearch;
        private DropDownList    ddlColumn;

        private string        _SqlStatement    = "";

        private ListTypes    _ListType        = ListTypes.PAGE;

        private bool        _SortHeader;
        private String        _SearchColumn;
        private String        _SearchValue    = "";
        private String        _SortColumn;
        private String        _SortOrder;

        private Positions        _Position;
        private SortDirections    _SortDirection;

        // These events are accessed from the user page 
        public event DataGridCommandEventHandler EditCommand;
        public event DataGridCommandEventHandler DeleteCommand;

        //private bool currentchecked = false;

        #endregion

        #region Constructor
        /// <summary>
        /// Constructor 
        /// </summary>
        public SqlDataGrid()
        {
            //MyDataGrid.CurrentPageIndex = 1;
            EnsureChildControls();
        }
        #endregion

        #region Properties

        [Browsable(false)]
        [Category("Display")]
        [Description("Get/set the String of The SQL Connection.")]        
        public Positions Position
        {
            get
            {
                return _Position; 
            }
            set
            {
                _Position = value; 
            }
        }

        [Browsable(true)]
        [Category("Display")]
        [Description("Get/set the Sort Direction of The SQL Connection.")]        
        public SortDirections SortDirection
        {
            get
            {
                return _SortDirection; 
            }
            set
            {
                _SortDirection = value; 
            }
        }

        [Browsable(true)]
        [Category("Display")]
        [Description("Get/set the String of The SQL Connection.")]        
        public String SqlStatement
        {
            get 
            {
                return _SqlStatement; 
            }
            set 
            { 
                _SqlStatement = value; 
            }
        }

        public System.Data.IDbConnection Connection
        {
            get
            { 
                return _Connection; 
            }
            set
            { 
                _Connection = value; 
            }
        }

        [Category("Display"),Browsable(true),Bindable(true)]
        public ListTypes ListType
        {
            get 
            { 
                return _ListType; 
            }
            set
            { 
                _ListType = value; 
            }
        }
 
        [Category("Display"),DefaultValue(true),Browsable(true),Bindable(true)]
        public bool SortHeader
        {
            get 
            { 
                return _SortHeader; 
            }
            set 
            { 
                _SortHeader = value; 
            }
        }


        [Category("Display"),Browsable(true),Bindable(true)]
        public String SortOrder
        {
            get 
            {
                return _SortOrder; 
            }
            set 
            { 
                _SortOrder = value; 
            }
        }

        [Category("Display"),Browsable(true),Bindable(true)]
        public String SortColumn
        {
            get 
            { 
                return _SortColumn; 
            }
            set 
            { 
                _SortColumn = value; 
            }
        }


        [Category("Display"),Browsable(true),Bindable(true)]
        public String SearchColumn
        {
            get 
            { 
                return _SearchColumn; 
            }
            set 
            { 
                _SearchColumn = value; 
            }
        }

        [Category("Display"),DefaultValue(""),Browsable(true),Bindable(true)]
        public String SearchValue
        {
            get 
            { 
                return _SearchValue; 
            }
            set 
            { 
                _SearchValue = value; 
            }
        }
        #endregion


        /// <summary>
        /// Must be overridden in the child class
        /// </summary>
        /// <returns>DataSet</returns>
        public virtual DataSet GetDataSet()
        {
            return new DataSet();
        }

        /// <summary>
        /// Overloaded GetDataSet to retrieve data based on sql statement
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual DataSet GetDataSet(string sql)
        {
            return new DataSet();
        }

        /// <summary>
        /// This fuction is used to create the datagrid scrolling 
        /// using the iScroll parameter
        /// </summary>
        /// <param name="isScroll"></param>
        private void CreateDataGridWithScroll(bool isScroll)
        {
            if(isScroll == true)
            {
                this.Controls.Add(new LiteralControl("<div STYLE=\"height: 200px; overflow: auto;\">"));
                this.Controls.Add(MyDataGrid);
                this.Controls.Add(new LiteralControl("</div>"));
            }
            else
            {
                this.Controls.Add(MyDataGrid);
            }
        }

        private void a_Checkme(object sender, EventArgs e)
        {
            //currentchecked = sender.ToString();
        }

        protected override void OnInit(EventArgs e)
        {


            //this.MyDataGrid.PageIndexChanged +=new DataGridPageChangedEventHandler(MyDataGrid_PageIndexChanged);
        }
        /// <summary>
        /// 
        /// Actual Rendering Starts here
        /// 
        /// </summary>
        /// <param name="writer"></param>
        protected override void Render(HtmlTextWriter writer) 
        {  
            Debug.WriteLine(MyDataGrid.CurrentPageIndex.ToString());
            Debug.WriteLine("RENDERED");
            EnsureChildControls();



            // Clear all Controls
            this.Controls.Clear(); 



            // Check to see if the sort is in asc or desc?
            if(this._SortDirection == SortDirections.ASCENDING)
            {
                ViewState["SortDirection"] = "ASC";
            }
            else
            {
                ViewState["SortDirection"] = "DESC";
            }

            // Check whether to show the search bar or not    
            if(this.SortHeader == false)
            {
                MyDataGrid.AllowSorting = false;
            }
            else
            {
                MyDataGrid.AllowSorting = true;
            }

            // Determine the Search Bar Position
            if (this._Position == Positions.TOP)
            {
                this.Controls.Add(txtSearch);
                this.Controls.Add(new LiteralControl(" "));
                this.Controls.Add(ddlColumn);
                this.Controls.Add(new LiteralControl(" "));
                this.Controls.Add(btnFilter); 

                if(this.ListType == ListTypes.SCROLL)
                {
                    CreateDataGridWithScroll(true);
                }
                else
                {
                    CreateDataGridWithScroll(false);
                }
            }
            else if (this._Position == Positions.BOTTOM)
            {
                if(this.ListType == ListTypes.SCROLL)
                {
                    CreateDataGridWithScroll(true);
                }
                else
                {
                    CreateDataGridWithScroll(false);
                }

                this.Controls.Add(txtSearch);
                this.Controls.Add(new LiteralControl(" "));
                this.Controls.Add(ddlColumn);
                this.Controls.Add(new LiteralControl(" "));
                this.Controls.Add(btnFilter); 
            }
            else
            {
                if(this.ListType == ListTypes.SCROLL)
                {
                    CreateDataGridWithScroll(true);
                }
                else
                {
                    CreateDataGridWithScroll(false);
                }
            }
            EnsureChildControls();

            base.RenderBeginTag(writer);  
            base.RenderContents(writer);  
            base.RenderEndTag(writer);  

        }


        private void CreateControls() 
        {  
            MyDataGrid    = new DataGrid();
              txtSearch    = new TextBox();
            ddlColumn    = new DropDownList();
            btnFilter    = new Button();  

            btnFilter.Text = "Filter";  
            btnFilter.CommandName = "Click";
              btnFilter.Click += new System.EventHandler (this.btnFilter_Click);  

            MyDataGrid.AutoGenerateColumns = false;

            // Formatting 
            MyDataGrid.PageSize = 10;
            MyDataGrid.AllowPaging = true;
            MyDataGrid.AllowSorting = true;


            MyDataGrid.ShowFooter = true;
            MyDataGrid.ShowHeader = true;
            MyDataGrid.Font.Name = "Verdana, Helvetica";
            MyDataGrid.Font.Size = FontUnit.XSmall;
            MyDataGrid.Width = 500;

            // Header Setting
            MyDataGrid.HeaderStyle.Height = 30;
            MyDataGrid.HeaderStyle.Font.Bold = true;
            MyDataGrid.HeaderStyle.Font.Size = FontUnit.XXSmall;
            MyDataGrid.HeaderStyle.BackColor = Color.Black;
            MyDataGrid.HeaderStyle.ForeColor = Color.White;
            MyDataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;

            // Pagerstyle settings
            MyDataGrid.PagerStyle.Mode = PagerMode.NumericPages;
            MyDataGrid.PagerStyle.BackColor = Color.Gainsboro;
            MyDataGrid.PagerStyle.PageButtonCount = 10;
            MyDataGrid.PagerStyle.Font.Size = FontUnit.XSmall;
            MyDataGrid.PagerStyle.HorizontalAlign = HorizontalAlign.Center;

            this.Controls.Add(txtSearch);
            this.Controls.Add(ddlColumn);
            this.Controls.Add(btnFilter); 
            this.Controls.Add(MyDataGrid);
          }  

        protected void MyDataGrid_Init(object sender, System.EventArgs e)
        {
            this.MyDataGrid.PageIndexChanged +=new DataGridPageChangedEventHandler(MyDataGrid_PageIndexChanged);
            MyDataGrid.CurrentPageIndex = int.Parse(ViewState["CurrentPage"].ToString());
        }

        /// <summary>
        /// Sorting Event
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void MyDataGrid_SortCommand(Object sender, DataGridSortCommandEventArgs e)
        {
            //
            // The If sorted the page should show the first one
            //
            MyDataGrid.CurrentPageIndex = 0;

            if(ViewState["SortField"].ToString() != e.SortExpression.ToString())
            {
                ViewState["SortField"] = e.SortExpression;
                ViewState["SortDirection"] = "ASC";
            }
            else
            {
                if(ViewState["SortDirection"].ToString() == "ASC")
                {
                    ViewState["SortDirection"] = "DESC";
                }
                else
                {
                    ViewState["SortDirection"] = "ASC";
                }
                ViewState["SortField"] = e.SortExpression;
            }

            Refresh(SqlStatement + " order by " + e.SortExpression + " " +     ViewState["SortDirection"]);
        }

        /// <summary>
        /// Page Index Event
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void MyDataGrid_PageIndexChanged(Object sender, DataGridPageChangedEventArgs e)
        {

            ViewState["CurrentPage"] = e.NewPageIndex;
            MyDataGrid.CurrentPageIndex = e.NewPageIndex;

            Refresh(SqlStatement + " order by " + ViewState["SortField"].ToString() + " " + ViewState["SortDirection"]);
        }


        /// <summary>
        /// This function is to call the events from the main page
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void MyDataGrid_ItemCommand ( Object sender, DataGridCommandEventArgs e )
        {

            if ( e.CommandName == "editme" )
            {
                EditCommand(this,e );
            }
            else if(e.CommandName == "deleteme")
            {
                DeleteCommand(this,e);
            }

        }

        /// <summary>
        /// This function creates the column dynamically based on the 
        /// dataset
        /// </summary>
        /// <param name="dataset"></param>
        private void CreateAllColumns(DataSet dataset)
        {
            // Clear the list box
            ddlColumn.Items.Clear();

            int totalColumn = dataset.Tables[0].Columns.Count;

            // Iterate all the columns
            // and make changes appropriately
            for (int i = 0 ; i < totalColumn ; i++)
            {
                // get the column name
                string columnName = dataset.Tables[0].Columns[i].ColumnName;

                BoundColumn column = new BoundColumn();
                column.DataField = columnName.ToString();
                ddlColumn.Items.Add(columnName.ToString());

                // Initialize the sort field from the user page
                // or change to default ASC
                if(ViewState["SortField"].ToString() == "")
                {
                    ViewState["SortField"] = dataset.Tables[0].Columns[0].ColumnName;
                    if(this._SortDirection == SortDirections.ASCENDING)
                    {
                        ViewState["SortDirection"] = "ASC";
                    }
                    else
                    {
                        ViewState["SortDirection"] = "DESC";
                    }
                }

                if(ViewState["SortField"].ToString() == columnName.ToString())
                {
                    if(ViewState["SortDirection"].ToString() == "ASC")
                    {
                        column.HeaderText = columnName.ToString() + "V";
                        column.HeaderText = columnName.ToString() + " <img src=" + "\"up.bmp\">";

                    }
                    else
                    {
                        column.HeaderText = columnName.ToString() + "^";
                        column.HeaderText = columnName.ToString() + " <img src=" + "\"down.bmp\">";

                    }
                }
                else
                {

                    if(i == 0 && ViewState["SortField"].ToString() == "")
                    {
                        column.HeaderText = columnName.ToString() + " <img src=" + "\"up.bmp\">";
                    }
                    else
                    {
                        column.HeaderText = columnName.ToString();
                    }

                }


                column.SortExpression = columnName.ToString();
                MyDataGrid.Columns.Add(column);
                MyDataGrid.Columns[i].SortExpression = columnName;
            }

            // Template column for the check box control
            TemplateColumn templateColumn = new  TemplateColumn();
            templateColumn.HeaderText = "Check";
            templateColumn.Visible = true;
            CheckItem a = new CheckItem("Check");
            //a.Checkme
            a.Checkme +=new EventHandler(a_Checkme);
            templateColumn.ItemTemplate =  a;
            MyDataGrid.Columns.Add(templateColumn);




            ButtonColumn MyButtonEdit = new ButtonColumn();
            MyButtonEdit.CommandName = "editme";
            MyButtonEdit.ButtonType = ButtonColumnType.LinkButton;
            MyButtonEdit.Text = "Edit";
            MyDataGrid.Columns.Add(MyButtonEdit);

            ButtonColumn MyButtonDelete = new ButtonColumn();
            MyButtonDelete.CommandName = "deleteme";
            MyButtonDelete.ButtonType = ButtonColumnType.LinkButton;
            MyButtonDelete.Text = "Delete";
            MyDataGrid.Columns.Add(MyButtonDelete);

            // set the key field of all the columns
            MyDataGrid.DataKeyField = dataset.Tables[0].Columns[0].ColumnName;

            // Set autogenerate to false since we are generating dynamically.
            MyDataGrid.AutoGenerateColumns = false;

            // Register the events
            this.MyDataGrid.SortCommand +=new DataGridSortCommandEventHandler(MyDataGrid_SortCommand);
            this.MyDataGrid.PageIndexChanged +=new DataGridPageChangedEventHandler(MyDataGrid_PageIndexChanged);
            this.MyDataGrid.ItemCommand +=new DataGridCommandEventHandler(MyDataGrid_ItemCommand);

            // Bind the data and bingo...
            MyDataGrid.DataSource = dataset;
            MyDataGrid.DataBind();
        }


        /// <summary>
        /// This function is used to refresh
        /// everytime the postback is made.
        /// </summary>
        /// <param name="sql"></param>
        public void Refresh(string sql)
        {
            MyDataGrid.Columns.Clear();
            DataSet dataset = new DataSet();
            CreateAllColumns(GetDataSet(sql));

        }

        /// <summary>
        /// This is the default refresh method without the sql statement
        /// </summary>
        public void Refresh()
        {
            MyDataGrid.Columns.Clear();
            DataSet dataset = new DataSet();

            string sql = "";
            if(this._SortColumn == null)
            {
                ViewState["SortField"] = "";
                sql = SqlStatement;
            }
            else
            {
                ViewState["SortField"] = this._SortColumn;
                sql = SqlStatement + " order by " + ViewState["SortField"].ToString() + ViewState["SortDirection"];
            }
            CreateAllColumns(GetDataSet(sql));


        }


        /// <summary>
        /// Create the child controls dynamically
        /// </summary>
        protected override void CreateChildControls() 
        {  
            CreateControls(); 
        }  


        /// <summary>
        /// This event fires when the Filter button is clicked
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnFilter_Click(object sender, System.EventArgs e) 
        {  

            if(txtSearch.Text == "")
            {
                MyDataGrid.CurrentPageIndex = 0;
                Refresh();
            }
            else
            {
                MyDataGrid.CurrentPageIndex = 0;

                SearchColumn = ddlColumn.SelectedItem.Text.ToString();
                SearchValue = txtSearch.Text;

                string newsql = SqlStatement + " where " + ddlColumn.SelectedItem.Text.ToString() + "='" + txtSearch.Text + "'";
                Refresh(newsql);
            }

          }  

        public override ControlCollection Controls 
        {  
              get 
            {  
                  base.EnsureChildControls();  
                  return base.Controls;  
              }  
          }

        public override void DataBind()  
        {  
            base.DataBind();  
            CreateControls(); 
        }  
    }


    // The template class for the CheckBox template item
    public class CheckItem : ITemplate
    {
        private string column;
        public event EventHandler Checkme;
        private CheckBox l;

        public CheckItem(string column)
        {
            this.column = column;
        }
        public void InstantiateIn(Control container)
        {
            l = new CheckBox();

            l.DataBinding += new EventHandler(this.BindData);
            l.CheckedChanged +=new EventHandler(l_CheckedChanged);

            container.Controls.Add(l);

        }


        public void l_CheckedChanged(object sender, EventArgs e)
        {
            //l.Checked = true;
            Checkme(sender,e);

        }

        public void BindData(object sender, EventArgs e)
        {
            CheckBox l = (CheckBox) sender;
            DataGridItem container = (DataGridItem) l.NamingContainer;
            //l.Checked = false; 
        }
    }