Ext.ux.LiveGrid

[magunes117]

Hi all,

Thorsten, great component, thx.

I am using .net/mssql instead of php/mysql, so I modified your example for this purpose, and here is how to make it work:

Server Side: test.ashx

[JsonRpcMethod("liveGrid", Idempotent = true)]
        [JsonRpcHelp("LiveGrid Test")]
        public Dictionary<string, object> LiveGrid(int start, int limit, string sort, string dir)
        {
            using (OleDbConnection connection = new OleDbConnection("Provider=SQLOLEDB;Data Source=datasource;Initial Catalog=initcatalog;User Id=user;Password=password;"))
            {
                connection.Open();
                string sql = "SELECT * FROM ORDERS ORDER BY " + sort + " " + dir;
                OleDbCommand command = new OleDbCommand(sql, connection);
                OleDbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                DataTable resultSet = new DataTable();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    resultSet.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
                }

                DataRow[] dr = new DataRow[300];

                for (int i = 0; i < start && reader.Read(); i++);

                for (int i = 0; i < limit && reader.Read(); i++)
                {
                    dr[i] = resultSet.NewRow();

                    for (int j = 0; j < resultSet.Columns.Count; j++)
                    {
                        dr[i][j] = reader.GetValue(j);
                    }
                    resultSet.Rows.Add(dr[i]);
                }

                command.Cancel();			
                reader.Close();
                connection.Close();

                connection.Open();
                string csql = "SELECT COUNT(*) FROM ORDERS";
                OleDbCommand ccommand = new OleDbCommand(csql, connection);
                OleDbDataReader creader = ccommand.ExecuteReader(CommandBehavior.CloseConnection);
                creader.Read();
                int total = (int)creader.GetValue(0);

                creader.Close();
                connection.Close();

                Dictionary<string, object> ret = new Dictionary<string, object>();
                ret["total"] = total;
                ret["version"] = 1;
                ret["data"] = resultSet.DefaultView;
                return ret;
            }
        }

Client Side: index.html

...

<script type="text/javascript" src="test.ashx?proxy&v=2"></script>

...

Ext.onReady(function(){

	var bufferedReader = new Ext.ux.data.BufferedJsonReader({
        root            : 'result.data',
        versionProperty : 'result.version',
        totalProperty   : 'result.total',
        id              : 'id'
      }, [ {
         name : 'OrderID', sortType : 'int'
      },{
         name : 'CustomerID', sortType : 'string'
      },{
         name : 'EmployeeID',   sortType : 'int'
      },{
         name : 'OrderDate', sortType : 'int'
      },{
         name : 'RequiredDate',   sortType : 'int'
      },{
         name : 'ShippedDate', sortType : 'int'
      },{
         name : 'ShipVia',   sortType : 'int'
      },{
         name : 'Freight', sortType : 'string'
      },{
         name : 'ShipName',   sortType : 'string'
      },{
         name : 'ShipAddress', sortType : 'string'
      },{
         name : 'ShipCity',   sortType : 'string'
      },{
         name : 'ShipRegion', sortType : 'string'
      },{
         name : 'ShipPostalCode',   sortType : 'string'
      },{
         name : 'ShipCountry', sortType : 'string'
    }]);

Ext.JsonRpcProxy = function(jsonRpcProxy, listMethod) 
{
    var _jsonRpcProxy = null ; 
    var _listMethod = null;
 
    Ext.JsonRpcProxy.superclass.constructor.call(this);        
 
    this.load = function(params, reader, callback, scope, arg) {
        _jsonRpcProxy[listMethod] (params.start, params.limit, params.sort, params.dir, this.loadResponse.createDelegate(this, [reader, callback, scope, arg], true ) 
        ).call(Ext.JsonRpcProxy.ext_channel1); 
    }
 
    this.loadResponse = function(response, reader, callback, scope, arg) {
        var result;
        result = reader.read(response) ;
        callback.call(scope||this, result, arg, true) ;
    }
 
    this.setRpcProxy = function(jsonRpcProxy, listMethod)  {
        if (jsonRpcProxy)
            _jsonRpcProxy = jsonRpcProxy.rpc || jsonRpcProxy ;
        _listMethod = listMethod || "list"  ; 
    }    
    this.setRpcProxy(jsonRpcProxy, listMethod) ;    
} ;
 
Ext.JsonRpcProxy.ext_channel1 = function(call)
{
    if (typeof(call.callback) !== 'function')
        throw new Error("The EXT channel does not support synchronous methods.");        
    call.yuiconn = Ext.Ajax.request (
        {
            url: call.url , 
            params: {"JSON-RPC": Ext.util.JSON.encode(call.request)} , 
            success: function(response, options) {
                    call.callback(response) ;
                },                            
            failure: function(response, options) {
                    call.callback({xhr:response}); 
                }   
        }
    );
    return call ; 
} ;
  
Ext.extend(Ext.JsonRpcProxy, Ext.data.DataProxy, {
}) ;

    var bufferedDataStore = new Ext.ux.grid.BufferedStore({
        autoLoad   : true,
        bufferSize : 300,
		proxy	   : new Ext.JsonRpcProxy(Test.rpc, "liveGrid"),
		sortInfo   : {field: 'OrderID', direction: 'ASC'},
        reader     : bufferedReader
    });
 
    var bufferedView = new Ext.ux.grid.BufferedGridView({
            nearLimit      : 100,
            loadMask : {
                msg : 'Please wait...'
    }});          
        
    var bufferedGridToolbar = new Ext.ux.BufferedGridToolbar({
        view        : bufferedView,
        displayInfo : true
    });

    var bufferedSelectionModel = new Ext.ux.grid.BufferedRowSelectionModel();        
    
    var colModel = new Ext.grid.ColumnModel([
        {header: "OrderID", align : 'left',   width: 160, sortable: true, dataIndex: 'OrderID'},
        {header: "CustomerID", align : 'left',   width: 160, sortable: true, dataIndex: 'CustomerID'},
        {header: "EmployeeID", align : 'left',   width: 160, sortable: true, dataIndex: 'EmployeeID'},
        {header: "OrderDate", align : 'left',   width: 160, sortable: true, dataIndex: 'OrderDate'},
        {header: "RequiredDate", align : 'left',   width: 160, sortable: true, dataIndex: 'RequiredDate'},
        {header: "ShippedDate", align : 'left',   width: 160, sortable: true, dataIndex: 'ShippedDate'},
        {header: "ShipVia", align : 'left',   width: 160, sortable: true, dataIndex: 'ShipVia'},
        {header: "Freight", align : 'left',   width: 160, sortable: true, dataIndex: 'Freight'},
        {header: "ShipName", align : 'left',   width: 160, sortable: true, dataIndex: 'ShipName'},
        {header: "ShipAddress", align : 'left',   width: 160, sortable: true, dataIndex: 'ShipAddress'},
        {header: "ShipCity", align : 'left',   width: 160, sortable: true, dataIndex: 'ShipCity'},
        {header: "ShipRegion", align : 'left',   width: 160, sortable: true, dataIndex: 'ShipRegion'},
        {header: "ShipPostalCode", align : 'left',   width: 160, sortable: true, dataIndex: 'ShipPostalCode'},
        {header: "ShipCountry", align : 'left',   width: 160, sortable: true, dataIndex: 'ShipCountry'}
    ]);

    var grid = new Ext.grid.GridPanel({
        ds             : bufferedDataStore,
        enableDragDrop : false,
        cm             : colModel,
        sm             : bufferedSelectionModel,
        loadMask       : {
            msg : 'Loading...'
        },
        view           : bufferedView,
        title          : 'Test',
        bbar           : bufferedGridToolbar
    });
    
    var w = new Ext.Window({
        title       : 'LiveGrid',
        maximizable : true,
        resizable   : true,
        layout      : 'fit',
        items       : [grid],
        height      : 480,
        width       : 600
    });
    
    w.show();
        
});

Table Structure: orders

CREATE TABLE ORDERS (
	"OrderID" "int" IDENTITY (1, 1) NOT NULL ,
	"CustomerID" nchar (5) NULL ,
	"EmployeeID" "int" NULL ,
	"OrderDate" "datetime" NULL ,
	"RequiredDate" "datetime" NULL ,
	"ShippedDate" "datetime" NULL ,
	"ShipVia" "int" NULL ,
	"Freight" "money" NULL DEFAULT (0),
	"ShipName" nvarchar (40) NULL ,
	"ShipAddress" nvarchar (60) NULL ,
	"ShipCity" nvarchar (15) NULL ,
	"ShipRegion" nvarchar (15) NULL ,
	"ShipPostalCode" nvarchar (10) NULL ,
	"ShipCountry" nvarchar (15) NULL
)

Since there isn't a replacement for mysql's limit in mssql, I used a different method here. Anyway, it works fine with 500.000 rows. Sorting the columns take a little long, because the 'order by' clause sorts the whole table and if there are millions of rows ordering may become a pain.

I want to thank FritFrut, for the "jayrock tutorial" and for helping me write this code.

Regards.

[WoLpH]

Since there isn't a replacement for mysql's limit in mssql, I used a different method here. Anyway, it works fine with 500.000 rows. Sorting the columns take a little long, because the 'order by' clause sorts the whole table and if there are millions of rows ordering may become a pain.

Not a direct replacement, but using TOP works pretty well or this method if you're using SQL 2005: http://select-sql.com/mssql/how-to-m...ssql-2005.html

[magunes117]

Not a direct replacement, but using TOP works pretty well or this method if you're using SQL 2005: http://select-sql.com/mssql/how-to-m...ssql-2005.html

Hi WoLpH,

I know about 'TOP' but you can only use it as top X rows, not like the rows between X and Y. So it is not much useful. And I am using sql2005 as well as sql2000, oracle, etc. But if you have a better solution, I will be happy to use it.

Regards.

[WoLpH]

Yeah, it's not a perfect solution, here are a few others but I still think MS should have gone with the PostgreSQL/MySQL way of doing it: http://databases.aspfaq.com/database...recordset.html

Although Oracle's implementation isn't that bad either, but less transparent imho.

[franck34]

Hi,

Just starting using extJs since 3 days. Seem's very cool, just need to learn. Big thanks for sharing.

Regarding liveGrid, i've just have a little strange thing (both ie and ff). Not sure it's liveGrid because i'm using it into a Desktop app.

Go here: http://innovacode.com/extjs/admin/
Click the Example icon.
On my side, I only have one item in the grid, but you will see that php file sent 2 items. If you resize/reduce/maximize the window, 2 items appear, as expected.

Using Firebug, i can confirm when i see only one item, there is only one div (x-gridX-row), expected 2. Have an idea ?

Thanks in advance.
Best Regards,
Franck

[WoLpH]

It is most likely caused by the animating effect, when animating it sees the height of the grid as 0 when rendering, so it only renders 1 item. Perhaps a proper solution for this would be always rendering atleast x items.

I have detected a little bug of my own by the way, when inserting rows it is only possible to insert rows at index 0 when there are not enough rows to scroll yet. After that inserting at 0 gives an error and only 1 and up is working.

[franck34]

It is most likely caused by the animating effect, when animating it sees the height of the grid as 0 when rendering, so it only renders 1 item. Perhaps a proper solution for this would be always rendering atleast x items.

Not sure about that because FireBug show only one div, expected 2. I can be wrong cause i'm starting with extJs.

[franck34]

Fixed:
this.grid.syncSize();

( just after win.show(); in my desktop app)

[meteorbites]

impressive plugin.

i've seen this kind of implementation before and it's great to have this as a plugin for EXT.

Good job.

[fred]

Hello,
I'm a new user with Ext and discover yesterday this amazing widget.
I try to get value on a rowclick event with no success; hereafter my code :

Ext.onReady(function(){

    var bufferedReader = new Ext.ux.data.BufferedJsonReader({
        root            : 'response.value.items',
        versionProperty : 'response.value.version',
        totalProperty   : 'response.value.total_count',
        id              : 'id'
      }, [ {
         name : 'number_field', sortType : 'int'
      },{
         name : 'string_field', sortType : 'string'
      },{
         name : 'date_field',   sortType : 'int'
    }]);
    
    var bufferedDataStore = new Ext.ux.grid.BufferedStore({
        autoLoad   : true,
        bufferSize : 300,
        reader     : bufferedReader,
        sortInfo   : {field: 'number_field', direction: 'ASC'},
        url        : '../services/data-proxy.php'
    });

    var bufferedView = new Ext.ux.grid.BufferedGridView({
            nearLimit      : 100,
            loadMask : {
                msg : 'Chargement...'
    }});          
        
    var bufferedGridToolbar = new Ext.ux.BufferedGridToolbar({
        view        : bufferedView,
        displayInfo : true
    });

    var bufferedSelectionModel = new Ext.ux.grid.BufferedRowSelectionModel();        
   
 var colModel = new Ext.grid.ColumnModel([
        {header: "Number", align : 'left',   width: 160, sortable: true, dataIndex:'number_field'},
        {header: "String", align : 'left',   width: 160, sortable: true, dataIndex: 'string_field'},
        {header: "Date",   align : 'right',  width: 160, sortable: true, dataIndex: 'date_field'}
    ]);


    var grid = new Ext.grid.GridPanel({
        ds             : bufferedDataStore,
        enableDragDrop : false,
        cm             : colModel,
        sm             : bufferedSelectionModel,
        loadMask       : {msg : 'Chargement...'},
        view           : bufferedView,
        title          : 'Fonction2',
        bbar           : bufferedGridToolbar
    });
	
    grid.on("rowclick", function(grid, rowIndex, e) {
               rec = ds.data.items[rowIndex].id; // doesn't work !!!
		alert(rec);
		str = rec.get('Number');
		alert(str);
    },this);
    
    var viewport = new Ext.Viewport({
	maximizable : true,
        resizable   : true,
        layout      : 'fit',
        items       : [grid],
	});
        
});

How get 'Number' value of the selected row ?
Thanks for your help.
Frederic