ExtJs的.NET控件----YuiGrid(分页/在线编辑)【附示例代码下载】

本文将介绍ExtJs的.NET控件YuiGrid分页机制和在线编辑功能.

一、分页【示例代码下载】

YuiGrid的分页机制不是很强大,内置有两种分页样式,下图便是其中的一种.

本示例代码是借鉴于YuiGrid源代码中的测试案例的代码,使用MSSQL2005数据库,下面为分页查询的代码

public static DataTable GetPagedMovies(string sort,int offset,int page_size,string dir )
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    con.Open();


    offset = offset / page_size;

    offset += 1;
    //query that gets only the records needed to the page
    //using the new ROW_NUMBER function in sql2005
    string sql = "WITH MOVIES AS ( " +
                " SELECT ROW_NUMBER() OVER " +
                "(ORDER BY " + sort + " " + dir + ")AS Row," +
                " ID_MOVIE,TITLE, RATING,VOTES,YEAR,GENRE " +
                " FROM TB_MOVIE )" +
                " SELECT ID_MOVIE,TITLE, GENRE,RATING,VOTES,YEAR" +
                " FROM MOVIES " +
                " WHERE Row between (@PageIndex-1)* @PageSize+1  and @PageIndex*@PageSize";

    SqlCommand cmd = new SqlCommand(sql, con);
    //add the parameters to the query to grab the correct page
    cmd.Parameters.AddWithValue("@PageIndex", offset);
    cmd.Parameters.AddWithValue("@PageSize", page_size);
    SqlDataAdapter adapt = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    adapt.Fill(ds);
    //closes the objects and disposes

    //GetMovieCount(ds);
    con.Close();
    cmd.Dispose();
    con.Dispose();
    return ds.Tables[0];
}

代码实现上很简单,有了上面的分页查询,下面只需要在Page_load里将数据进行绑定就OK了.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        int page_size = 10;//default page size
        int offset = 1;//default page
        string sortCol, sortDir;
        if (string.IsNullOrEmpty(Request["sort"]))
        {
            sortCol = "ID_MOVIE";
            sortDir = "ASC";
        }
        else
        {
            sortCol = Request["sort"];
            sortDir = Request["dir"];
        }
        if (!string.IsNullOrEmpty(Request["limit"]))
        {
            page_size = int.Parse(Request["limit"]);
            offset = int.Parse(Request["start"]);
        }

        YuiGrid1.TotalRecords = Movie.GetMovieCount();
        YuiGrid1.DataSource = Movie.GetPagedMovies(sortCol,offset,page_size,sortDir);
        YuiGrid1.DataBind();
    }
}

二、在线编辑

在实现YuiGrid的在线编辑，首先需将其EnableEdit属性设为True,然后通过CellEdited事件来做数据库更新操作。其实更新数据库无非就是将当前行的数据取出来，然后执行一条相应的SQL就OK，这里的重点应该是取数据，在标准GridView里通常都是通过命令参数来搞定的，YuiGrid不同的是通过他自己的事件传递的对象属性来获取，如下：

protected void YuiGrid1_CellEdited(object sender, ExtExtenders.GridCellEditedArgs e)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;//ConnectionString
    con.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    string sql = "UPDATE TB_MOVIE SET " + e.Field + "=@" + e.Field +
                 " WHERE ID_MOVIE=@ID_MOVIE";

    cmd.CommandText = sql;
    cmd.Parameters.AddWithValue("@" + e.Field, e.Value);
    cmd.Parameters.AddWithValue("@ID_MOVIE", e.Record["ID_MOVIE"]);
    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine(ex.Message);
    }
}

大致如下图：

YuiGrid里可以非常方便的嵌入ListBox或DropDownList等控件，只需要在外部任意定义一个DropDownList控件，然后将YuiGrid对应列的EditControlId属性设置为该控件ID就OK了。如下代码遍上上图的完整定义：

<cc1:YuiGrid  ID="YuiGrid1"  runat="server" EnableRowSorting="true" Width="550px"
    EnablePaging="true" PagingStyle="NavBar" EnableEdit="true" OnCellEdited="YuiGrid1_CellEdited" SelectMultiple="true">
    <Columns>
        <cc1:ColModel header="ID_MOVIE" dataIndex="ID_MOVIE" hidden="true" />
        <cc1:ColModel header="Movie Title" dataIndex="TITLE" sortable="true" width="230" allowBlank="false"  maxLength="40"/>
        <cc1:ColModel header="Votes" dataIndex="VOTES" sortable="true" width="90" DataType="Numeric" />
        <cc1:ColModel header="Rating" dataIndex="RATING" sortable="true" width="60" DataType="Numeric" />
        <cc1:ColModel header="Year" dataIndex="YEAR" sortable="true" width="60" EditControlId="cboYear"/>
        <cc1:ColModel header="Genre " dataIndex="GENRE" sortable="true" width="120" EditControlId="cboGenre" />

    </Columns>
</cc1:YuiGrid>

<asp:DropDownList ID="cboGenre"  runat="server" CssClass="x-hid">
    <asp:ListItem Text="Action" Value="Action"></asp:ListItem>
    <asp:ListItem Text="Adventure" Value="Adventure"></asp:ListItem>
    <asp:ListItem Text="Animation" Value="Animation"></asp:ListItem>
    <asp:ListItem Text="Comedy" Value="Comedy"></asp:ListItem>
    <asp:ListItem Text="Crime" Value="Crime"></asp:ListItem>
    <asp:ListItem Text="Documentary" Value="Documentary"></asp:ListItem>
    <asp:ListItem Text="Drama" Value="Drama"></asp:ListItem>
    <asp:ListItem Text="Family" Value="Family"></asp:ListItem>
    <asp:ListItem Text="Fantasy" Value="Fantasy"></asp:ListItem>
    <asp:ListItem Text="Horror" Value="Horror"></asp:ListItem>
    <asp:ListItem Text="Independent" Value="Independent"></asp:ListItem>
    <asp:ListItem Text="Musical" Value="Musical"></asp:ListItem>
    <asp:ListItem Text="Mystery" Value="Mystery"></asp:ListItem>
    <asp:ListItem Text="Noir" Value="Noir"></asp:ListItem>
    <asp:ListItem Text="Romance" Value="Romance"></asp:ListItem>
    <asp:ListItem Text="Sci-Fi" Value="Sci-Fi"></asp:ListItem>
    <asp:ListItem Text="Thriller" Value="Thriller"></asp:ListItem>
    <asp:ListItem Text="War" Value="War"></asp:ListItem>
    <asp:ListItem Text="Western" Value="Western"></asp:ListItem>
</asp:DropDownList>