CRUD operations in Wijmo Grid

Wijmo Grid is the most extensively used widget among all other Wijmo widgets. In most of the cases, it is bound to the database and end users are allowed to perform the basic CRUD i.e. Create Read Update Delete operations. This is the most common requirement for all the developers while creating any application. This blog demonstrates how you can easily perform CRUD operations in WijGrid widget bound to a webservice.

Create Operation

You can create a new row object with the required fields and push it in the data option of WijGrid. After that, you can pass this object in the WebMethod defined in the Webservice. In this method, you can write an Insert query to add this record in the database. Here is the code : JS Code:

var data = $("#demo").wijgrid("data");  
var newProduct = new Object();  
newProduct.ProductID = 111;  
newProduct.ProductName = "ABC";  
newProduct.UnitPrice = 23;  

data.push(newProduct); // add a new item  

$.ajax({  
  type: "POST",  
  url: "GetDetails.asmx/SaveNewProduct",  
  contentType: "application/json; charset=utf-8",  
  dataType: "json",  
  data: "{'newProduct':" + JSON.stringify(newProduct) + "}",  
  success: function (data) {  
     $("#demo").wijgrid("ensureControl", true); // refresh wijgrid  
  },  
  error: function (e) { }  
});

WebService Method:

[ScriptMethod(ResponseFormat = ResponseFormat.Json)]  
public void SaveNewProduct(Product newProduct)  
{  
  OleDbConnection myCon = new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString);  
  string queryProduct = "Insert into [Products](ProductID,ProductName,UnitPrice) Values (" + newProduct.ProductID + ",'" + newProduct.ProductName + "'," +                          newProduct.UnitPrice + ")";  
  OleDbCommand da = new OleDbCommand(queryProduct, myCon);  
  myCon.Open();  
  da.ExecuteNonQuery();  
  myCon.Close();  
}

Read Operation

You can define a WebMethod that access the database and creates a collection containing the records from desired datatable. And in the script, you can make a AJAX call to access this List collection and set it to the data option of WijGrid. You may achieve the same using the below code: WebMethod

public object GetProducts()  
{  
  //retrieve the data  
  DataTable ds = new DataTable();  
  string queryProduct = "SELECT * FROM [Products] ";  
  string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;  
  OleDbDataAdapter da = new OleDbDataAdapter(queryProduct, strConn);  
  DataTable dt = new DataTable();  
  da.Fill(dt);  

  //create list of CityNames  
  List<Product> products = new List<Product>();  
  for (int i = 0; i < dt.Rows.Count; i++)  
  {  
    //set the values  
    Product cn = new Product();  
    cn.ProductID = (int)dt.Rows[i]["ProductID"];  
    cn.ProductName = dt.Rows[i]["ProductName"].ToString();  
    cn.UnitPrice = (decimal)dt.Rows[i]["UnitPrice"];  
    products.Add(cn);  
  }  
 return products;  
}

JS Code

//ajax call to get the data  
$.ajax({  
  type: "POST",  
  url: "GetDetails.asmx/GetProducts",  
  contentType: "application/json; charset=utf-8",  
  dataType: "json",  
  success: function (data) {  
  //push the data in an array  
    $.each(data.d, function (i, elem) {  
      arr.push({  
         ProductID: elem.ProductID,  
         ProductName: elem.ProductName,  
         UnitPrice: elem.UnitPrice  
      });  
   });  
  SetData(arr)  
  },  
  error: function (result, status) {  
    if (status = "error")  
       alert(status);  
 }  
});

Update Operation

This is one of the most important operation required by all the users i.e. to save the edited data back to the database. You can easily get the edited row using 'selection' method of WijGrid and then, you can pass this row object to the Webmethod. In the WebMethod, you can define an Update query to post the changes back to the database. The code will look like: JS Code

var $grid = $("#demo"),  
row = $grid.wijgrid("selection").selectedCells().length() > 0  
        ? $grid.wijgrid("selection").selectedCells().item(0).row()  
        : null;  

$.ajax({  
  type: "POST",  
  url: "GetDetails.asmx/SaveProduct",  
  contentType: "application/json; charset=utf-8",  
  dataType: "json",  
  data: "{'product':" + JSON.stringify(row.data) + "}",  
  success: function (data) {  
     $("#demo").wijgrid("ensureControl", true); // refresh wijgrid  
  },  
  error: function (e) { }  
});

WebMethod

public void SaveProduct(Product product)  
{  
  OleDbConnection myCon = new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString);  
  string queryProduct = "Update [Products] Set ProductName='" + product.ProductName + "', UnitPrice=" + product.UnitPrice + " where ProductID=" + product.                         ProductID + "";  
  OleDbCommand da = new OleDbCommand(queryProduct, myCon);  
  myCon.Open();  
  da.ExecuteNonQuery();  
  myCon.Close();  
}

Delete Operation

Lastly, the 'Delete' operation allows the end users to delete the undesired rows from the WijGrid but you need to make sure that the same are deleted from the database as well to avoid inconsistency. You can use the following code for same: JS Code

var $grid = $("#demo"),  
row = $grid.wijgrid("selection").selectedCells().length() > 0  
      ? $grid.wijgrid("selection").selectedCells().item(0).row()  
      : null;  

if (row && (row.type & $.wijmo.wijgrid.rowType.data)) {  
  $grid.wijgrid("data").splice(row.dataItemIndex, 1);  

  $.ajax({  
    type: "POST",  
    url: "GetDetails.asmx/DeleteProduct",  
    contentType: "application/json; charset=utf-8",  
    dataType: "json",  
    data: "{'product':" + JSON.stringify(row.data) + "}",  
    success: function (data) {  
        $("#demo").wijgrid("ensureControl", true); // refresh wijgrid  
    },  
    error: function (e) { }  
  });  
}

WebMethod

public void DeleteProduct(Product product)  
{  
  OleDbConnection myCon = new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString);  
  string queryProduct = "Delete from [Products] where ProductID=" + product.ProductID + "";  
  OleDbCommand da = new OleDbCommand(queryProduct, myCon);  
  myCon.Open();  
  da.ExecuteNonQuery();  
  myCon.Close();  
}

You may also check the attached sample for complete implementation.