The EntitySpaces team decided to take a slight detour from its upcoming 1.6.0 release and add support for the Compact Framework to its popular architecture, which is a persistence layer and business object system for Microsoft .NET 2.0. Support for the Compact Framework paves the way for VistaDB 3.0 and the Microsoft SQL Compact Edition well in advance of their official releases. Both VistaDB 3.0 and the Microsoft SQL Compact Edition are still in CTP mode. EntitySpaces classes are generated from your database schema using download.com's #1 download in the .NET development category, MyGeneration, using the EntitySpaces template suite.

EntitySpaces LLC has created a sample application using a very powerful combination of its EntitySpaces architecture in conjunction with VistaDB 3.0, and running both under the Windows Mobile 5.0 Smartphone emulator. Setting up the demo solution was very simple. First, references to the EntitySpaces.Core.Ce and EntitySpaces.Interfaces.Ce assemblies were added. Next, a reference to the EntitySpaces.VistaDB.Ce data provider was added. Finally, the VistaDB Northwind.vdb3 database was added to the project and its "Build Action" was set to "Content" to ensure that the database is copied along with the other assemblies to the proper folder when the simulator is launched. To see the solution in Visual Studio click HERE.

The next step was to write the code to connect to the VistaDB Northwind database. To accomplish this, the new configless execution feature provided by the recent EntitySpaces 1.5.2 release was used. The trick was actually figuring out how to get the correct path to the database while running in the simulator. You can see how this was accomplished in the first line of the following code snippet.

    string cnString = ("Data Source =" + (System.IO.Path.GetDirectoryName
     (System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) +
     "\\Northwind.vdb3; Open Mode = ExclusiveReadWrite"));

    // What the heck let's register a connection
    esConnectionElement conn = new esConnectionElement();
    conn.ConnectionString = cnString;
    conn.Name = "VistaDb";
    conn.Provider = "EntitySpaces.VistaDBProvider.Ce";
    conn.ProviderClass = "DataProvider";
    conn.SqlAccessType = esSqlAccessType.DynamicSQL;
    conn.ProviderMetadataKey = "esDefault";
    esConfigSettings.ConnectionInfo.Connections.Add(conn);

    // Assign the Default Connection
    esConfigSettings.ConnectionInfo.Default = "VistaDb";

The only option on the opening Menu brings the user to a form which contains a DataGrid bound directly to an EntitySpaces EmployeesCollection object loaded with all of the employees in the database. Note that you can use the EntitySpaces collections in design time mode directly on the form itself, this can be seen HERE. The EntitySpaces dynamic query mechanism was used to limit the columns brought back from the database to only those needed to operate the DataGrid. These included the EmployeeID, FirstName, and LastName columns. Finally, the DataGrid TableStyles collection was used to set the EmployeeID column's width to zero so that it would not be visible. The only thing left to do is fetch the data and assign the EntitySpaces collection directly to the DataGrid.DataSource property.

The developer will never need to use any ADO.NET code, or any specific VistaDB code to accomplish this task. In fact, the developer could easily swap out the EntitySpaces VistaDB provider and exchange it for the EntitySpaces Microsoft SQL CE provider, and run the same exact binary code. The EntitySpaces architecture itself is tested using NUnit against all five supported databases using the same exact binary code. No recompilation necessary. The code used to fetch the data and bind to the DataGrid is shown below and taken from the FormGrid class in the demo.

 

     private void PopulateGrid()
    {
        EmployeesCollection empColl = new EmployeesCollection();
        empColl.Query.Select
            (
                empColl.Query.EmployeeID,
                empColl.Query.FirstName,
                empColl.Query.LastName
            );
        empColl.Query.Load();

        this.theGrid.DataSource = empColl;
    }

It really is that simple. Next, the "Edit Record" menu item needs to be handled in order to navigate to the editing form. To accomplish this the hidden EmployeeID needs to be grabbed, and passed to the "FormEdit" page based on the selected row at the time the "Edit Record" menu item is clicked.

    private void menuItem_EditRecord_Click(object sender, EventArgs e)
    {
        int index = this.theGrid.CurrentRowIndex;
        int id = (int)this.theGrid[index, 0];  // index is the row
                                               // zero = the first column
        FormEdit form = new FormEdit(id);
        form.Show();
    }

It is on the editing page where users can modify, delete, and add new records. Remember from above that the EmployeeID is passed to the FormEdit page. The constructor for the FormEdit page is very easy to implement using EntitySpaces architecture. Here, instead of the EmployeesCollection, the Employees class is used to load the correct record. The Employees class is used to hold a single record, whereas, the EmployeesCollection holds a set of Employees classes. The record is loaded using the LoadByPimaryKey method as shown in the code snippet below. Finally, the labels and textbox controls are populated with the data from the EntitySpaces Employees object. 

 

 

 

    public FormEdit(int employeeID)
    {
        InitializeComponent();

        Employees emp = new Employees();
        if (emp.LoadByPrimaryKey(employeeID))
        {
            this.lblID.Text = emp.str.EmployeeID;

            this.txtFirstName.Text = emp.FirstName;
            this.txtLastName.Text  = emp.LastName;
        }
        else
        {
            this.lblError.Text = "Employee not found";
        }
    }

On the editing screen shown on the left Margaret's first name has been changed to "Mary" and then saved using Save on the menu. The ability to add and delete records is in the EntitySpaces Mobile demo too but not shown here. Pressing close will return the user to the DataGrid form reflecting any edits that may have occurred. The image on the right reveals that not only was Margaret's name changed to Mary, but "Mike Griffin" was added to the database as well. The grid is refreshed in the Activated event of the FormGrid class. 

It should be clearly seen how easy EntitySpaces can be used to work with the Compact Framework. While creating the demo we were very impressed with VistaDB 3.0. VistaDB has some features that we found make it very attractive. First, VistaDB has some nice advantages over the Microsoft SQL Compact Edition. The VistaDB Namespace doesn't change when moving to the Compact Framework. The Microsoft Compact Edition, however, uses SqlCeClient instead of SqlClient, which makes it more difficult for developers to maintain a single codebase. The EntitySpaces architecture conveniently hides these low level issues from the developer, thereby isolating code from such provider quirks. Also, the same VistaDB database (the exact same physical file) will run on all environments with no loss of features or a lesser SQL syntax when running under the Compact Framework.

Having said that, the EntitySpaces Architecture does support the Microsoft SQL Compact Edition fully, and we will publishing the same demo application shown here for it very soon. We are just putting a few finishing touches on the EntitySpaces provider for the Microsoft SQL Compact Edition. The SQL supported under the Microsoft SQL Compact Edition is different than what is allowable under Microsoft SQL 2005. The EntitySpaces user of course will never realize these differences and that is one of the main strengths of EntitySpaces.

The EntitySpaces Mobile demo will be available for download Monday December 4th by 8:00 AM EST time. A link will be posted on this page.

 

 

 

 

 

 

- The EntitySpaces Team