Wijmo GridView : Updating Database From Client Side

Many a times you wish to show data in a GridView and allow users to edit the data and save it. It used to be a cumbersome and time-consuming task where you had to define template columns and then manually get the updated values. Now, with C1GridView for Wijmo, all of that is history. C1GridView has the feature where users can edit cells on the client side itself. This can be achieved by setting the property AllowClientEditing to true without the need to define Template Columns. You just have to double click on a cell to make it editable, edit the values and then change selection to some other row to save the updated values. This blog explains how you can easily update the database with the modified values without any postback.

Defining the connection and binding C1GridView

You can either use an Oledb datasource or a Sql datasource for binding C1GridView. For this blog, we will use an Oledb datasource. Define the DataKeyNames and the columns in C1GridView as below. We also need to set the CallbackSettings for editing so that there is no postback when the grid is edited.

<wijmo:C1GridView ID="C1GridView1" runat="server" OnRowUpdating="C1GridView1_RowUpdating"  
 AutogenerateColumns="false" DataKeyNames="CustomerID" ClientSelectionMode="SingleRow"  
 AllowClientEditing="true" ShowFilter="true" OnFiltering="C1GridView1_Filtering"  
     <CallbackSettings Action="Editing, Filtering" />  
         <wijmo:C1BoundField DataField="CustomerID" HeaderText="CustomerID" SortExpression="CustomerID">  
         <wijmo:C1BoundField DataField="CompanyName" HeaderText="Company Name" SortExpression="CompanyName">  
         <wijmo:C1BoundField DataField="ContactName" HeaderText="Contact Name" SortExpression="ContactName">  
         <wijmo:C1BoundField DataField="City" HeaderText="City" SortExpression="City">  
         <wijmo:C1BoundField DataField="Country" HeaderText="Country" SortExpression="Country">  

Now, define the Oledb connection since we want to update the database as well; hence, we also have to set the Update command and the required parameters and call the Update method.

public DataTable GetDataTable()  
    DataTable dt = Page.Session["Customers"] as DataTable;  
    OleDbConnection con = new OleDbConnection("provider=Microsoft.Jet.Oledb.4.0; Data Source=" + Server.MapPath("~/App_Data/C1NWind.mdb"));  
    OleDbDataAdapter da = new OleDbDataAdapter();  
    da.SelectCommand = new OleDbCommand("SELECT * FROM [Customers] Order By [CustomerID]", con);  

    da.UpdateCommand = new OleDbCommand("Update [Customers] set [CompanyName]=?, [ContactName]=?, [City]=?, [Country]=? where CustomerID = ?", con);  
    da.UpdateCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 50, "CompanyName");  
    da.UpdateCommand.Parameters.Add("@ContactName", OleDbType.VarChar, 50, "ContactName");  
    da.UpdateCommand.Parameters.Add("@City", OleDbType.VarChar, 50, "City");  
    da.UpdateCommand.Parameters.Add("@Country", OleDbType.VarChar, 50, "Country");  
    da.UpdateCommand.Parameters.Add("@CustomerID", OleDbType.VarChar, 50, "CustomerID");  

    if (dt == null)  
        dt = new DataTable();  
        dt.PrimaryKey = new DataColumn[] { dt.Columns["CustomerID"] };  
        Page.Session["Customers"] = dt;  

    return dt;  
C1GridView1.DataSource = GetDataTable();  

Updating Data

To update the data we just need to handle the RowUpdating and EndRowUpdated events of C1GridView and update the row whose data has been modified.

protected void C1GridView1_RowUpdating(object sender, C1.Web.Wijmo.Controls.C1GridView.C1GridViewUpdateEventArgs e)</pre>  
    DataTable customers = GetDataTable();  
    DataRow row = customers.Rows.Find(C1GridView1.DataKeys[e.RowIndex].Value);  

    if (row != null)  
        foreach (DictionaryEntry entry in e.NewValues)  
            row[(string)entry.Key] = entry.Value;  

        throw new RowNotInTableException();  

    Page.Session["Customers"] = customers;  

Rebind the grid in EndRowUpdated event.

protected void C1GridView1_EndRowUpdated(object sender, C1.Web.Wijmo.Controls.C1GridView.C1GridViewEndRowUpdatedEventArgs e)  
    C1GridView1.DataSource = GetDataTable();  

Now, suppose there is only one row in C1GridView (eg. after filtering) and now the user wants to edit the grid. It's not possible because there's no other row which can be clicked to change the selection and save changes. Not to worry! For such a scenario, you just have to call the client side update() method of C1GridView (on a button click) and it'll work.

<asp:Button ID="btn1" runat="server" Text="Update C1GridView"  
OnClientClick="btn_ClientClick(); return false;" />

Use the following jQuery function to call update() method:

function btn_ClientClick(sender, args) {  
    var grid = $("#C1GridView1");  

Refer to the attached sample. Download Sample


GrapeCity Developer Tools
comments powered by Disqus