A first look at SlickGrid with read, insert and update (in WebMatrix)

Standard

A few days ago I had a quick look at Flexigrid and I planned to continue with it to enable updates. However when I searched a bit further I got recommendations to use SlickGrid instead and so I did. As with my Flexigrid post this post is based on what I learned in a short time – if you are looking for a in depth / best practices sample you should indeed look at the library authors samples instead.

My first impression with SlickGrid is that it is a very good looking grid and it was easy to get a read and update solution up and running with very little code.

Another strength of SlickGrid is that it is possible to use a DataView model to handle the data. However that’s not something I have experimented with yet, and this sample is using a straight serverdata – grid path.

Edit (2011-12-12) to make it use the latest SlickGrid I updated my code pretty much + now also allows insert (so full CRUD, yay…)

Here’s how I did it:

The data comes from a simple Sql CE 4 db-table with the columns id (pk+identity), name (nvarchar 100), description (nvarchar 100) and somevalue (int). To create the SlickGrid I initialize it with a columns array, an options object, a data.cshtml that returns a Json object from a database read and takes post data for database insert or update.

Here’s how the html & javascript code looks like for my page (default.cshtml):

@{
    Layout = "/_layout.cshtml";
}        
        <script type="text/javascript">
    $(document).ready(function () {
        var grid;
        var columns = [
		
            { id: "name", name: "Name", field: "name", editor:TextCellEditor, resizable:false, width:100, rerenderOnResize:true},
            { id: "description", name: "Description", field: "description", editor:TextCellEditor, resizable:false, width:100, rerenderOnResize:true},
            { id: "somevalue", name: "Some value", field: "somevalue", editor:IntegerCellEditor, resizable:false, width:100, rerenderOnResize:true},
            { id:"id", name:"Delete", field:"id",
              formatter: function (r,c,id,def,datactx) {
                  return "<a href='#' onclick='removeClick(" + id + "," + r + ")'>x</a>";
            }}
	    ];

        function removeClick(databaseId, gridRow) {

            $.post("data/delete/",{id:databaseId});
            var data = grid.getData(); 
            data.splice(gridRow, 1);
            grid.setData(data);
            grid.render();

        }


        var options = {
            editable: true,
			enableAddRow: true,
			enableCellNavigation: true,
			asyncEditorLoading: false,
            enableColumnReorder: false,                        
            autoHeight: true
        };


        // Handle updates

        $.getJSON("/mygrid/data", success = function (data) {
            grid = new Slick.Grid("#myGrid", data, columns, options);            
            grid.onCellChange.subscribe(function (e, args) {
                if (typeof(args.item.id)=='undefined')
                    $.post("/mygrid/data/insert", args.item);
                else
                    $.post("/mygrid/data/update", args.item);
            });
            
            // Handle new row
            
            grid.onAddNewRow.subscribe(function(e, args) {
                var item = args.item;
                var column = args.column;
                grid.invalidateRow(data.length);
                data.push(item);
                grid.updateRowCount();
                grid.render();
            });
            
            $("#myGrid").show();
        });
        
    });
</script>

<div id="myGrid" style="width:400px;display:none;"></div>

The page is using jQuery and SlickGrid with editors plugin (included in the library samples). The css is both from jQuery UI and from SlickGrid.

Here are all the js- and css- files that’s included in the header section of the page (_layout.cshtml):

<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title></title>
        <link rel="stylesheet" href="/js/slickgrid/slick.grid.css" type="text/css" media="screen" charset="utf-8" />
        <link rel="stylesheet" href="/css/smoothness/jquery-ui-1.8.16.custom.css" type="text/css" media="screen" charset="utf-8" />
        <link rel="stylesheet" href="/css/examples.css" type="text/css" media="screen" charset="utf-8" />
        
		<script src="/js/jquery-1.7.min.js"></script>
		<script src="/js/jquery.event.drag-2.0.min.js"></script>

        <script src="/js/slickgrid/slick.core.js"></script>
        <script src="/js/slickgrid/slick.editors.js"></script>
        <script src="/js/slickgrid/slick.grid.js"></script>

        
    </head>
    <body>
        @RenderBody()
    </body>
</html>

The data.cshtml:

@{
    
    // Get Action from Url f ex /mygrid/data/insert /mygrid/data/update
    var action = UrlData[0];
    var db = Database.Open("data");
    
    if (IsPost)
    {        
        
        // Get Post data (convert null's to string or integer)
        
        var id = Request["id"];
        var name = Request["name"]??"";
        var description = Request["description"]??"";
        var somevalue = Request["somevalue"].AsInt();
        
        if (action=="insert")
        {                        
            var sql = @"INSERT INTO item (name, description, somevalue) VALUES (@0,@1,@2)";
            db.Execute(sql,name,description,somevalue);
            var newId = db.GetLastInsertId();
            Response.Write(Json.Encode(newId));
        }
        else if (action=="update")
        {
            var sql = @"UPDATE item set name=@1, description=@2, somevalue=@3 WHERE id=@0";
            var data = db.Execute(sql,id,name,description,somevalue);
        }                
        else if (action=="delete")
        {
            var sql = @"DELETE FROM item WHERE id=@0";
            var data = db.Execute(sql,id);
        }                
    }
    else
    {        
        var sql = "SELECT id, name, description, somevalue FROM item";
        var data = db.Query(sql).ToList();        
        Response.Write(Json.Encode(data));
    }       

}
About these ads

5 thoughts on “A first look at SlickGrid with read, insert and update (in WebMatrix)

  1. Snp

    Hey Joe(I think this is ur first name,if not forgive me), Very Good article..liked it.
    I am going to use this SlickGrid, can you please guide me or Share the source ?

  2. Thank you!

    It took me three days (part-time) to finally get a working example going..in the end it was your example that got the out of band calls working for me.

    Regards from Southern California
    Stephen

  3. sonam Shah

    hi.
    i want to display data in slick grid.
    i can get the data from the webservice.
    i have written the following code for display the data into slickgrid but not working.

    grid = new Slick.Grid(“#mygrid”, data, columns, options);

    please help me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s