I spent most of the week prototyping some things in Silverlight. I figured during the holiday week most people out there goofed off on their jobs a lot this week, so rather than work on my roadmap priorities, I decided to have some fun and play with Silverlight.

Some of you who have followed my blog for a while may remember some posts I made in the past about my plans for Site Office as a second plug in model for mojoPortal more geared to line of business apps that need a consistent look and feel rather than the web site kind of look, they need to look like applications. I originally protyped the UI using Dojo and then later re-did it with ExtJs. You can see the ExtJs version if you login to this site (or http://demo.mojoportal.com using admin@admin.com and password admin), then click the Site Office link at the top. You'll see the drag resizable panes that give the idea of where I wanted to go with Site Office as a UI for LOB applications. This prototype has just been sitting there without much attention because of other priorities and also partly because my enthusiasm for ExtJs disappeared when they changed the license from LGPL to GPL. Anyway, even back then I implemented a google gears query tool. Its really the only functioning app in the old Site Office prototype, you can find it by clicking the My Stuff in the left accordian menu in Site Office and then click SQL. For those who don't know, google gears is a client side SQL database built on SQLite and having this database available opens a lot of possibilities in web development for very rich and responsive applications.

Well, now my plan is to scrap the old ExtJs based Site Office prototype and build a better one with Silverlight. I've already got the Google Gears Query Tool re-implemented in Silverlight as shown below:

I wrote a nice managed code wrapper around the javscript calls for gears. The only problem is, it doesn't work well in IE 7 for some reason, it works great in Firefox. I've sent an email off to Scott Guthrie at Microsoft in hopes of some help looking into the problem, but for now you can try it out online at http://demo.mojoportal.com/Index.aspx, you can see that I've got the basic layout of Site Office again implemented in Silverlight with the drag re-sizable panes. I plan to build a plug in model that allows you to plugin your own Silverlight applets and let the framework provide stuff thats common across applications. If I can get google gears working well across browsers with Silverlight its really going to be sweet. The code for this is in my svn sandbox and will probably land in trunk sometime next week.

Now using the managed gears wrapper can be seen in this client side business/data class, it looks very much like a server side class but its a client side object populated from a client side database in a very similar fashion to what it would look like in server side code. Notice the parametrized queries to prevent sql injection attacks. This class represents a saved query but it could represent anything.

using System;


namespace mojoPortal.Silverlight.Helpers.Gears
{
   
    public class SavedQuery
    {
        public SavedQuery()
        {}

        private int id = -1;
        private string name = string.Empty;
        private string query = string.Empty;

        public int Id
        {
            get { return id; }
        }

        public string Name
        {
            get { return name; }
            set { name = value; }
        }

        public string Query
        {
            get { return query; }
            set { query = value; }
        }

        public void Save(GearsDb gearsDb)
        {
            if (id == -1) { Create(gearsDb); return; }

            Update(gearsDb);
        }

        private void Create(GearsDb gearsDb)
        {
            if (gearsDb == null) { return; }

            string sqlCommand = "insert into savedqueries (name, query) values (?, ?)";
            object[] parameters = new object[2];
            parameters.SetValue(name, 0);
            parameters.SetValue(query, 1);
            gearsDb.Execute(sqlCommand, parameters);
            id = gearsDb.LastInsertRowId();

        }

        private bool Update(GearsDb gearsDb)
        {
            if (gearsDb == null) { return false; }

            string sqlCommand = "update savedqueries set name = ?, query = ? where id = ?";
            object[] parameters = new object[3];
            parameters.SetValue(name, 0);
            parameters.SetValue(query, 1);
            parameters.SetValue(id, 2);
            gearsDb.Execute(sqlCommand, parameters);
            int rowsAffected = gearsDb.RowsAffected();
            return (rowsAffected > 0);
        }

        public static SavedQuery GetQuery(GearsDb gearsDb, int id)
        {
            if (gearsDb == null) { return null; }
            string sqlCommand = "select * from savedqueries where id = ?";
            object[] parameters = new object[1];
            parameters.SetValue(id, 0);
            GearsResultSet rs = new GearsResultSet(gearsDb.Execute(sqlCommand, parameters));

            SavedQuery query = null;
            if (rs.IsValidRow())
            {
                query = new SavedQuery();
                query.id = Convert.ToInt32(rs.GetFieldValue("id"));
                query.name = rs.GetFieldValue("name").ToString();
                query.query = rs.GetFieldValue("query").ToString();
            }
            rs.Close();

            return query;
        }

        public static SavedQuery GetQuery(GearsDb gearsDb, string name)
        {
            if (gearsDb == null) { return null; }

            string sqlCommand = "select * from savedqueries where name = ?";
            object[] parameters = new object[1];
            parameters.SetValue(name, 0);
            GearsResultSet rs = new GearsResultSet(gearsDb.Execute(sqlCommand, parameters));

            SavedQuery query = null;
            if (rs.IsValidRow())
            {
                query = new SavedQuery();
                query.id = Convert.ToInt32(rs.GetFieldValue("id"));
                query.name = rs.GetFieldValue("name").ToString();
                query.query = rs.GetFieldValue("query").ToString();
            }
            rs.Close();

            return query;
        }

        public static bool Delete(GearsDb gearsDb, int id)
        {
            if (gearsDb == null) { return false; }
            string sqlCommand = "delete from savedqueries where id = ?";
            object[] parameters = new object[1];
            parameters.SetValue(id, 0);
            gearsDb.Execute(sqlCommand, parameters);
            int rowsAffected = gearsDb.RowsAffected();
            return (rowsAffected > 0);

        }

    }
}

Update 2008-12-23

I have narrowed down the problem with IE and use of Google Gears in Silverlight. All the Gears functionality works except for 2 methods. The 2 methods broken in IE are GearsResultSet.GetFieldName(int fieldIndex) and GearsResultSet.GetFieldValue(int fieldIndex). I can get the field value if I know the field name ahead of time using GearsResultSet.GetFieldValue(string fieldName), so for most applications I should still be able to use Gears even in IE because my field names will be known ahead of time. Unfortunately for the query tool we have no way of knowing what fields will be in the result of ad hoc queries. So the query tool will only be useful in Firefox, but for other features I should be able to use gears without any trouble and this is very good news. I also have got a few web services talking to Silverlight, so I'm able to authenticate and get user roles. These services are actually built into the framework so I didn't have to implement them. I am working on some RESTful web services using the WCF REST Starter Kit.