Hybrid View

  1. #1
    Ext User
    Join Date
    Mar 2007
    Posts
    161
    Vote Rating
    0
    Choleriker is on a distinguished road

      0  

    Default Yui, Client-Side SQL Query and AjaxPro for .NET

    Yui, Client-Side SQL Query and AjaxPro for .NET


    Im using server-controls, but not they Postbacks for it. In this way i can use Theming and that stuff, which is a great extension. For getting HTML-Controls appearing, im using a UpdateManager which loads a aspx page with a parameter wich specifies the content, which has to load. That page dynamically loads a control in a directory which is named like the design, which is stored plain text in the user-profile. Heres the ASP.net code of that loader:

    Code:
    <Page>
    <script>
        protected void Page_Load(object sender, EventArgs e)
        {
            this.PlaceHolder1.Controls.Add(LoadControl(Profile.Design + "/" + Request["c"] + ".ascx"));
        }
    </script>
    <asp></asp>
    for that above: the <asp></asp> means only an asp placeholder with the id Placeholder1 running at server!

    As you can see, the parameter 'c' takes the name of the content.

    The next (a little more complex) thing was to extend the AjaxPro framework. First i have wrote a custom YUI wrapper classes that extends the dataset, datatable etc. on client-side. If you get closer with that code, you will find a class TrimQuery. That great piece of code give you the possibilities to trigger SQL-Statements over Javascript objects. With that you dont need to have complex loops over the tables of the dataset. You can find that lib at http://trimpath.com/project/wiki/TrimQuery I like it

    Here are my code:

    Code:
    /**
    *   @author Michael Baarz, mb@tecbehind.de
    *   @copyright www.tecbehind.de
    **/
    
    function isUndefined(o) { return typeof o=='undefined' || o==null; }
    function isNull(o) { return (o === null); }
    function isFunction(o) { return typeof o=='function'; }
    function isArray(o) { return typeof o=='array'; }
    function isString(o) { return typeof o=='string'; }
    function isObject(o) { return typeof o=='object'; }
    function isFunction(e) { return typeof o=='function'; }
    function isBool(e) { return typeof o=='boolean'; }
    
    if(isUndefined(YAHOO.tecbehind)) YAHOO.namespace('tecbehind');
    
    /*****************************************************************************************************************************************************************
    *   Implementation einer Spalten-Definition in einem DataSet
    *
    *   @class YAHOO.tecbehind.DataColumn
    *   @extends YAHOO.ext.util.Observable
    *   @constructor
    *****************************************************************************************************************************************************************/
    YAHOO.tecbehind.DataColumn = function(cfg) {
       this.id = cfg.id;
       this.type = cfg.type;
    };
    YAHOO.extendX(YAHOO.tecbehind.DataColumn, YAHOO.ext.util.Observable, {
    });
    /*****************************************************************************************************************************************************************
    *   Implementation einer Zeile in einem DataSet
    *
    *   @class YAHOO.tecbehind.DataRow
    *   @extends YAHOO.ext.util.Observable
    *   @constructor
    *****************************************************************************************************************************************************************/
    YAHOO.tecbehind.DataRow = function(cells) {
       for(var i in cells) this[i] = cells[i];
    };
    YAHOO.extendX(YAHOO.tecbehind.DataRow, YAHOO.ext.util.Observable, {
    });
    /*****************************************************************************************************************************************************************
    *   Implementation einer Tabelle in einem DataSet.
    *
    *   @class YAHOO.tecbehind.DataTable
    *   @extends YAHOO.ext.util.Observable
    *   @constructor
    *****************************************************************************************************************************************************************/
    YAHOO.tecbehind.DataTable = function(cols, rows, tableName) {
       this.tableName = tableName;   
       this.columns = new YAHOO.ext.util.MixedCollection();
       this.rows = new Array();
       
       this.ds = null;  // Das DataSet, welches dieser Table zugeordnet ist
       
       // Spalten aufnehmen + Parsen
       var _l = cols.length;
       for(var i=0; i<_l; i++) this.addColumn(cols[i]);
       
       // Zeilen aufnehmen und Parsen
       var _l = rows.length;
       for(var i=0; i<_l; i++) this.addRow(rows[i]);
    };
    YAHOO.extendX(YAHOO.tecbehind.DataTable, YAHOO.ext.util.Observable, {
        /**
        *   Gibt das SQL Model für diese Tabelle zurück, welche mit TrimQuery verwendet werden kann.
        **/
        getSqlModel: function() {
            var _def = {};
            this.columns.eachKey(function(key, item) {
                _def[key] = {
                    type: item.type
                 };
            });
            return _def;
        },
        addColumn: function(col) {
            this.columns.add(col.id, col);
        },
        addRow: function(row) {
            this.rows.push(row);
        },
        assignDataSet: function(ds) {
            this.ds = ds;
        }
    });
    /*****************************************************************************************************************************************************************
    *   Implementation des DataSets
    *
    *   @class YAHOO.tecbehind.DataSet
    *   @extends YAHOO.ext.util.Observable
    *   @constructor
    *****************************************************************************************************************************************************************/
    YAHOO.tecbehind.DataSet = function(tables) {
        this.tables = {};
        var _l;
        if(!isUndefined(tables)) {
            _l = tables.length;
            for(var i=0; i<_l; i++) this.addTable(tables[i]);
        }
    };
    YAHOO.extendX(YAHOO.tecbehind.DataSet, YAHOO.ext.util.Observable, {
        /**
        *   Ermöglicht das Client-Seitige Ausführen von SQL Statements
        **/
        select: function(strSelect) {
            debug('SELECT-Statement auf DataSet ausführen: '+strSelect);
            // Query-Language mit Definitionen vorcompilieren
            var queryLang = TrimPath.makeQueryLang(this.getSqlModel());
            // Select-Statement ausführen
            var statement = queryLang.parseSQL(strSelect);
            // Query ausführen
            var results = statement.filter(this._getQueryableData());
            debug('Result-count: '+results.length);
            return results;        
        },
        /**
        *   Bereitet die Daten für Client-Seitige Selects auf
        **/
        _getQueryableData: function() { 
            var sh = {};
            for(var i in this.tables) {
                sh[this.tables[i].tableName]=this.tables[i].rows;
            }
            return sh;
        },
        /**
        *   Erstellt das SQL-Model, welches mit TrimPath zum verwenden von SQL-Statements benötigt wird.
        **/
        getSqlModel: function() {
            var _def = {};
            for(var i in this.tables) _def[this.tables[i].tableName] = this.tables[i].getSqlModel();
            return _def;
        },
        
        addTable: function(tab) {
            // In Table-Collection aufnehmen
            this.tables[tab.tableName] = tab;
            /** DataSet-Referenz erstellen **/
            tab.assignDataSet(this);
        }
    });
    After that i have to change the JSON Converters at the AjaxPro Frameworks. You can add new classes and define which JSON-Converter has to use. I will never use other ways to handle dataset, so i have only changed the source by itself. Here is my code:

    JSON\Converts\DataSetConverter.js
    Code:
    using System;
    using System.Text;
    using System.Data;
    
    namespace AjaxPro
    {
    	/// <summary>
    	/// Provides methods to serialize and deserialize a DataSet object.
    	/// </summary>
    	public class DataSetConverter : IJavaScriptConverter
    	{
    		private string clientType = "YAHOO.tecbehind.DataSet";
    
    		public DataSetConverter() : base()
    		{
    			m_AllowInheritance = true;
    
    			m_serializableTypes = new Type[] { typeof(DataSet) };
    			m_deserializableTypes = new Type[] { typeof(DataSet) };
    		}
    
    		public override string GetClientScript()
    		{
                return ""; 
    		}
    
    		public override object Deserialize(IJavaScriptObject o, Type t)
    		{
    			JavaScriptObject ht = o as JavaScriptObject;
    
    			if(ht == null)
    				throw new NotSupportedException();
    
    			if(!ht.Contains("Tables") || !(ht["Tables"] is JavaScriptArray))
    				throw new NotSupportedException();
    
    			JavaScriptArray tables = (JavaScriptArray)ht["Tables"];
    			
    			DataSet ds = new DataSet();
    			DataTable dt = null;
    
    			foreach(IJavaScriptObject table in tables)
    			{
    				dt = (DataTable)JavaScriptDeserializer.Deserialize(table, typeof(DataTable));
    
    				if(dt != null)
    					ds.Tables.Add(dt);
    			}
    
    			return ds;			
    		}
    
    		public override string Serialize(object o)
    		{
    			StringBuilder sb = new StringBuilder();
    			Serialize(o, sb);
    			return sb.ToString();
    		}
    
    		public override void Serialize(object o, StringBuilder sb)
    		{
    			DataSet ds = o as DataSet;
    
    			if(ds == null)
    				throw new NotSupportedException();
    			
    			bool b = true, b1 = true;
    
    			sb.Append("new ");
    			sb.Append(clientType);
    			sb.Append("([");
    				
    			foreach(DataTable dt in ds.Tables)
    			{
    				if(b){ b = false; }
    				else{ sb.Append(","); }
    
    				JavaScriptSerializer.Serialize(dt, sb);
                }
                sb.Append("])");
    		}
    	}
    }
    JSON\Converts\DataTableConverter.js
    Code:
    using System;
    using System.Text;
    using System.Data;
    
    namespace AjaxPro
    {
    	/// <summary>
    	/// Provides methods to serialize and deserialize a DataTable object.
    	/// </summary>
    	public class DataTableConverter : IJavaScriptConverter
    	{
    		private string clientType = "YAHOO.tecbehind.DataTable";
    
    		public DataTableConverter() : base()
    		{
    			m_AllowInheritance = true;
    
    			m_serializableTypes = new Type[] { typeof(DataTable) };
    			m_deserializableTypes = new Type[] { typeof(DataTable) };
    		}
    
    		public override string GetClientScript()
    		{
                return "";
    		}
    
    		public override object Deserialize(IJavaScriptObject o, Type t)
    		{
    			JavaScriptObject ht = o as JavaScriptObject;
    
    			if(ht == null)
    				throw new NotSupportedException();
    
    			if(!ht.Contains("Columns") || !(ht["Columns"] is JavaScriptArray) ||
    				!ht.Contains("Rows") || !(ht["Rows"] is JavaScriptArray))
    			{
    				throw new NotSupportedException();
    			}
    
    			JavaScriptArray columns = (JavaScriptArray)ht["Columns"];
    			JavaScriptArray rows = (JavaScriptArray)ht["Rows"];
    
    			DataTable dt = new DataTable();
    			DataRow row = null;
    			Type colType;
    			JavaScriptArray column;
    
    			if(ht.Contains("TableName") && ht["TableName"] is JavaScriptString)
    				dt.TableName = ht["TableName"].ToString();
    
    			for(int i=0; i<columns.Count; i++)
    			{
    				column = (JavaScriptArray)columns[i];
    				colType = Type.GetType(column[1].ToString(), true);
    				dt.Columns.Add(column[0].ToString(), colType);
    			}
    		
    			JavaScriptArray cols = null;
    			object obj;
    
    			for (int y = 0; y < rows.Count; y++)
    			{
    //				if(!(r is JavaScriptArray))
    //					continue;
    				
    				cols = (JavaScriptArray)rows[y];
    				row = dt.NewRow();
    
    				for (int i = 0; i < cols.Count; i++)
    				{
    					//row[i] = JavaScriptDeserializer.Deserialize((IJavaScriptObject)cols[i], dt.Columns[i].DataType);
    
    					obj = JavaScriptDeserializer.Deserialize((IJavaScriptObject)cols[i],dt.Columns[i].DataType);
    					row[i] = (obj == null) ? DBNull.Value : obj;
    				}
    
    				dt.Rows.Add(row);
    			}
    	
    			return dt;
    		}
    
    		public override string Serialize(object o)
    		{
    			StringBuilder sb = new StringBuilder();
    			Serialize(o, sb);
    			return sb.ToString();
    		}
    
            public string GetSqlType(string typeName)
            {
                switch (typeName)
                {
                    case "String": return "String";
                    case "Int32":
                    case "Int16":
                    case "Int64":
                    case "Decimal":
                    case "Double":
                        return "Number";
                    case "DateTime":
                        return "Date";
                    default:
                        throw new Exception("Typ " + typeName + " kann nicht aufgelöst werden");
                }
            }
    
    		public override void Serialize(object o, StringBuilder sb)
    		{
    			DataTable dt = o as DataTable;
    
    			if(dt == null)
    				throw new NotSupportedException();
    			
    			DataColumnCollection cols = dt.Columns;
    			DataRowCollection rows = dt.Rows;
    
    			bool b = true;
    
    			sb.Append("new ");
    			sb.Append(clientType);
    			sb.Append("([");
    				
    			foreach(DataColumn col in cols)
    			{
    				if(b){ b = false; }
    				else{ sb.Append(","); }
    
    				sb.Append("new YAHOO.tecbehind.DataColumn({id:");
    				JavaScriptUtil.QuoteString(col.ColumnName, sb);
    				sb.Append(",type:");
                    JavaScriptUtil.QuoteString(GetSqlType(col.DataType.Name), sb);
    				sb.Append("})");
    			}
    
    			sb.Append("],[");
    
    			b = true;
    
    			foreach(DataRow row in rows)
    			{
    				if(!b) sb.Append(",");
                    sb.Append("new YAHOO.tecbehind.DataRow(");
    				JavaScriptSerializer.Serialize(row, sb);
                    sb.Append(")");
    				b = false;
    			}
    
    			sb.Append("], '");
                sb.Append(dt.TableName);
                sb.Append("')");
    		}
    	}
    }
    last but not least,

    JSON\Converts\DataRowConverter.js
    Code:
    using System;
    using System.Text;
    using System.Data;
    
    namespace AjaxPro
    {
    	/// <summary>
    	/// Provides methods to serialize and deserialize a DataRow object.
    	/// </summary>
    	public class DataRowConverter : IJavaScriptConverter
    	{
    		public DataRowConverter() : base()
    		{
    			m_AllowInheritance = true;
    
    			m_serializableTypes = new Type[] { typeof(DataRow) };
    		}
    
    		public override string Serialize(object o)
    		{
    			StringBuilder sb = new StringBuilder();
    			Serialize(o, sb);
    			return sb.ToString();
    		}
    
    		public override void Serialize(object o, StringBuilder sb)
    		{
    			DataRow row = o as DataRow;
    
    			if(row == null)
    				throw new NotSupportedException();
    			
    			DataColumnCollection cols = row.Table.Columns;
    			int colcount = cols.Count;
    
    			bool b = true;
    
    			sb.Append("{");
    
    			for(int i=0; i<colcount; i++)
    			{
    				if(b){ b = false; }
    				else{ sb.Append(","); }
                    sb.Append(cols[i].ColumnName);
                    sb.Append(":");
    				JavaScriptSerializer.Serialize(row[cols[i].ColumnName], sb);
    			}
    
    			sb.Append("}");
    		}
    	}
    }
    With that you can use AjaxPro, get a DataSet, and simply trigger

    Code:
    var dataSet = AjaxProNamespace.InterfaceObject.GetRoutings();
    var result = dataSet.select(
       "SELECT Routings.id, Routings.srn, Routings.caller, Routings.destination, Routings.length "+
       "FROM Routings "+
       "ORDER BY Routings.srn" );
    hock:

    and bind that query-result to a grid using JSON or do anything else with it.

    Its my current development state!

    Now, im interested in you code-implementations using .NET, YUI and YUI-EXT.

    Any suggestions to my code?

    Greetings, Michael

  2. #2
    Ext User
    Join Date
    Mar 2007
    Posts
    31
    Vote Rating
    0
    indulgence is on a distinguished road

      0  

    Default


    I wouldn't construct queries client-side that execute server-side. Biggest security risk EVER

  3. #3
    Sencha - Ext JS Dev Team Animal's Avatar
    Join Date
    Mar 2007
    Location
    Notts/Redwood City
    Posts
    30,496
    Vote Rating
    44
    Animal has a spectacular aura about Animal has a spectacular aura about Animal has a spectacular aura about

      0  

    Default


    I don't really see the difference, if you have written the back end.

    I mean conceptually, what's the difference between submitting a load of form filter fields, like

    Code:
    <input name="dateFrom">
    <input name="dateTo">
    And building up a string which just happens to look like "SELECT blah...." and using Connect.asyncRequest to send that?

    It's how you've written the back end that matters.

    Personally, I use the first option. I process raw input field name/value pairs server-side where I have access to all the metadata, but if you want to process a "SELECT blah..." string server-side, then why not?

  4. #4
    Ext User
    Join Date
    Mar 2007
    Posts
    161
    Vote Rating
    0
    Choleriker is on a distinguished road

      0  

    Default Server Side SQL?

    Server Side SQL?


    I would never trigger SQL from client to server!! A Dataset consist more than one table! The SQL there prepares the data for output, which is read from server before. So you can read all necessary data (which is only that data the user can view!) and show it in any ways on client without re-read it from server.

    THE SQL QUERY THERE IS A CLIENT SIDE SQL WHICH RUNS OVER CLIENT-SIDE OBJECTS! Give SQL Queries from Client to the server is stupid!

    Look TrimQuery website for more informations!

    The possibilities are to load data from server and show it in several ways. The data i use is very huge and goes over more than 10 tables and my customer needs many many views which joins over that data. All i need to do is to read out the data one times > one of the goals from Ajax is to minimize client-server traffic, right?

  5. #5
    Sencha - Ext JS Dev Team Animal's Avatar
    Join Date
    Mar 2007
    Location
    Notts/Redwood City
    Posts
    30,496
    Vote Rating
    44
    Animal has a spectacular aura about Animal has a spectacular aura about Animal has a spectacular aura about

      0  

    Default Re: Server Side SQL?

    Re: Server Side SQL?


    Quote Originally Posted by Choleriker
    I would never trigger SQL from client to server!!
    Then your web app will never be able to display any information!

    Any form submission where you return data back to the browser is going to trigger some SQL activity! Your server-side code has to be smart enough to only return to the browser information that the logged-on user is allowed to see.

  6. #6
    Ext User
    Join Date
    Mar 2007
    Posts
    161
    Vote Rating
    0
    Choleriker is on a distinguished road

      0  

    Default Hey Animal,

    Hey Animal,


    Thatz correct, only logged on users can see this data.

    Animal: my english is bad! What i mean was is: i will never trigger SQL Data directly by building the SQL-select-string on Client-side!

    Can no1 see the advantages of fast SQL Querying on client side over JS-Objects??? Im sure, ASP.NET users they are know the advantages of a DataSet will find the advantages.

    The advantage is that you dont have to know the structure everytime. Please try to use TrimPath on the one side, and using common loops. You will see, its very fast!

    This at this time is only for showing data to the client. This is not really usable every time, because the selected data on server side is completely transfered to the client (how i said, only the data for the user).

    At least for the other users they are reading this thread: it is mostly very successful to prepare the data server-side and only show it on client side. But after common tests i found that using Template's from DomHelper are very very fast. If you try to prepare data for output on client side not using yui-ext will give you huge speed issues! Yui-Ext rocks!


  7. #7
    Sencha - Ext JS Dev Team Animal's Avatar
    Join Date
    Mar 2007
    Location
    Notts/Redwood City
    Posts
    30,496
    Vote Rating
    44
    Animal has a spectacular aura about Animal has a spectacular aura about Animal has a spectacular aura about

      0  

    Default Re: Hey Animal,

    Re: Hey Animal,


    Quote Originally Posted by Choleriker
    Thatz correct, only logged on users can see this data.

    Animal: my english is bad! What i mean was is: i will never trigger SQL Data directly by building the SQL-select-string on Client-side!

    Can no1 see the advantages of fast SQL Querying on client side over JS-Objects??? Im sure, ASP.NET users they are know the advantages of a DataSet will find the advantages.
    I can't see advantages. Sending a whole dataset and its metadata down the wire, and then selecting the required subset of data on the client side seems like a waste of a database, and a waste of bandwidth.

  8. #8
    Ext User
    Join Date
    Mar 2007
    Posts
    161
    Vote Rating
    0
    Choleriker is on a distinguished road

      0  

    Default OK

    OK


    In my project it is believe me. Im developing high-traffic systems since 10 years now, after that ive learned where speed eaters to find :roll:

    I have routings on a phone server. At most 100 per times. That routings have to show and many many ways which mostly are grouped by service-number, the account who owns this number, by modules etc. If a user clicks on such grouped header data i have to show the routes in detail for that data. The user has the possibility to select more than one row to show the details.

    The best and fastest way is to read that data normalized from server and process it on client side. Try to re-read the client-data for each selected row, or re-read the data after switching the view. The client-server traffic will be enurmous.

    If you cant find the advantage maybe you havnt had a scenario like that before. All i wanted here is to share my code that other developers maybe can take advantage of it. If you dont like my code, delete that post :oops:

    kidding ^^

  9. #9
    Sencha - Ext JS Dev Team Animal's Avatar
    Join Date
    Mar 2007
    Location
    Notts/Redwood City
    Posts
    30,496
    Vote Rating
    44
    Animal has a spectacular aura about Animal has a spectacular aura about Animal has a spectacular aura about

      0  

    Default


    OK, If it's a fairly small amount of data, and is not volatile, but the user needs to change his view of it frequently, then I can see some advantage to processing it client-side.

  10. #10
    Ext User
    Join Date
    Mar 2007
    Posts
    161
    Vote Rating
    0
    Choleriker is on a distinguished road

      0  

    Default Thank you

    Thank you


    Yes me too. I have tried to get the selection fast like TrimQuery with common loops, but nothing was such fast. TrimQuery is a very nice tool, that can be used for common config parsing etc. too, for sure it makes only sence if the additional javascript-file of TrimQuery will reduce complexity of data-parsing. If you see a scenario for you, try it, its very nice. Its the second nice lib after yui-ext!

Similar Threads

  1. Client-side grid filtering (Newbie)
    By protech in forum Ext 1.x: Help & Discussion
    Replies: 6
    Last Post: 25 Apr 2007, 8:36 AM
  2. AjaxPro and YUI-ext
    By EdgarAVJ in forum Ext 1.x: Help & Discussion
    Replies: 11
    Last Post: 30 Mar 2007, 9:37 AM
  3. Exploring Client-Side Cross Browser XML Data-Binding
    By genius551v in forum Ext 1.x: Help & Discussion
    Replies: 10
    Last Post: 5 Jan 2007, 8:41 AM
  4. Server-side vs. Client-side...
    By zquirm in forum Community Discussion
    Replies: 2
    Last Post: 22 Dec 2006, 8:15 PM
  5. 2-tier or N-tier on client side?
    By qiuyl in forum Community Discussion
    Replies: 1
    Last Post: 19 Dec 2006, 1:26 PM

Thread Participants: 2