Wednesday, June 23, 2010

Walkthrough: Expose SQL Compact data to the world as an OData feed, and access SQL Compact data from Excel 2010 and Silverlight

The Open Data Protocol (OData) is a Web protocol for querying and updating data.

Currently, SQL Compact data is only available to ADO.NET and OLEDB based Windows client.

Exposing your SQL Compact data as an OData feed allows your data to be consumed by a variety of different client platforms at any location, including:

OData Explorer (Silverlight Application)

PowerPivot for Excel 2010 is a plugin to Excel 2010 that has OData support built-in.

LINQPad is a tool for building OData queries interactively.

Client libraries: Javascript, PHP , Java, Windows Phone 7 Series, iPhone (Objective C), .NET (Including Silverlight)

I will now show you the steps required to expose a SQL Compact database file a an OData feed using Visual Studio 2010, and how to consume this feed from Silverlight and Excel PowerPivot.

Please note that exposing SQL Compact data like this will not scale to 100s of concurrent users.

Step 1: Create the Web Application

In Visual Studio, select File, New, Project…

image

Select Web, ASP.NET Empty Web Application, and click OK.

Step 2: Add the Entity Framework Model

From the Project menu, select Add New Item…

image

Name the model NorthwindModel and click add.

image

Select Generate from Database and click Next.

image

Select an existing Data Connection or create a new one if required, and click Next.

image

“Just say No”

image

Select Tables and click “Pluralize…” – then click Finish.

Step 3: Add the WCF Data Service (OData Service)

Select Project, Add New Item…

image

Select Web, WCF Data Service, and name it “NWService”, then click Add.

Modify the service code (noted in italic):

    public class NwDataService : DataService<NorthwindEntities>
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(DataServiceConfiguration config)
{
// Give readonly access to all of the entities
config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);


config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
}
}



Step 4: Modify global.asax to allow SQL Compact access from ASP.NET



SQL Server Compact 3.5 is not currently optimized to serve as a database for Web sites. SQL Server Compact 3.5 can be used with ASP.NET in application scenarios where ASP.NET is used to create SQL Server Compact 3.5 databases for synchronization scenarios.



To change the default behavior, despite the warning above, open global.asax, and modify as follows (in italic):



void Application_Start(object sender, EventArgs e)
{
// Code that runs on application startup
AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);
}

Step 5: Run your service!




image 




Right click the NwDataService.svc, and select View in Browser:



image



Your OData service is now ready for consumption by any of the clients mentioned above.



Step 6 – test access from various clients



Access the service from Silverlight (installed elevated):



image



Access the service from Excel 2010 PowerPivot:



image

Thursday, June 17, 2010

Walkthrough: Configuring the Merge Replication Agent (Web Synchronization) sqlcesa35.dll (SP2) on Windows 7 x64 and x86 with IIS 7.5

With the recent release of SQL Server Compact 3.5 SP2, installing the Web Synchronization Agent on Windows 7 and Server 2008 R2 has become a lot easier, as I demonstrate below.

Download the latest Server Tools

First, download the latest version of the SQL Compact Server tools for 3.5 SP2.

For x64 systems, the x64 version of the tools is:  SSCEServerTools_x64-ENU.msi

Notice the following on the download page:

  • Prior to installing SQL Server Compact 3.5 SP2 Server Tools, uninstall all earlier versions of SQL Server Compact Server Tools, including, SQL Server Compact 3.5 SP2 Server Tools Beta, SQL Server Compact 3.5 SP1 Server Tools, SQL Server Compact 3.5 Server Tools, SQL Server 2005 Compact Edition Server Tools or SQL Server 2005 Mobile Edition Server Tools by using the Add/Remove Programs in the Control Panel.
  • On Windows Vista SP1, Windows Server 2008, Windows 7 or Windows Server 2008 R2 with IIS 7.0, the IIS 6.0 Compatibility Components need to be installed prior to installing SQL Server Compact 3.5 SP2 Server Tools. For more information about installing the IIS 6.0 Compatibility Components, see IIS 6 Compatibility Components.

    Configure IIS 7.5

    Enable IIS on Windows 7:

    Click Start, click Control Panel, click Programs and Features, and then click Turn Windows features on or off.

    Select the Internet Information Services check box.

    Open Internet Information Services.

    Open World Wide Web Services.

    Open Application Development Features

    Select the ISAPI Extensions check box.

    Open Web Management Tools.

    Open IIS 6.0 Management Compatibility.

    Select the check boxes for IIS 6 Metabase and IIS 6 configuration compatibility and IIS 6 Management Console

    Click OK.

    image

     

    Install the Server Tools

    Install SSCEServerTools_x64-ENU.msi or SSCEServerTools_x86-ENU.msi

    Important steps:

    image

    The checkmarks in this steps reflects the version of the SQL Server Replication components installed on the system, I my case only SQL Server 2008 replication components are installed.

    Otherwise, just click Next, Next…

    Create the publication and configure for SQL Compact subscribers

    Go ahead and do this. See http://msdn.microsoft.com/en-us/library/ms171780(v=SQL.100).aspx

    Configure the agent - run Configure Web Syncronization Wizard

    Run "C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\sync\SQL\ConnWiz.exe" (you can use the shortcut in Start, All Programs, Microsoft SQL Server Compact 3.5).

    Complete the Wizard.

    Test the agent

    Run: http://mypc/ssce35/sscesa35.dll in your browser.

    You may encounter this error:

    image

    If this happens, try to remove the web.config file from your root web folder (c:\inetpub\wwwroot). This worked for me.

    If the test succeeds, you are good to go:

    image

    You can now use SQL Server Management Studio to test your publication, before testing on other machines and devices.

    Create a new SQL Server Compact Database, and run the New Subscription Wizard to test it.

  • Monday, June 7, 2010

    “SQL Compact Toolbox” Visual Studio 2010 add-in beta available

    I have just published the beta of a new Visual Studio (Professional or higher) add-in, that provides a number of useful tools for SQL Compact developers, directly from the Visual Studio environment:

    Scripting of schema and data, ad-hoc query editing and execution, database relationship diagrams, migration/import/export and more features to come in subsequent releases.

    After investigating the difficulty of creating a Visual Studio add-in, I stumbled upon the SQL Azure Explorer add-in on CodePlex. This gave me hope that creating a Visual Studio add-in could actually be done within a finite and limited amount of effort and time. This project showcased how to create a Toolwindow, and hook this up with a treeview complete with context menus and even a SQL free-form editor. The add-in uses the new VSIX package format, which greatly simplifies end-user management of extensions to the Visual Studio IDE

    To start creating your own add-ins, download the Visual Studio 2010 SDK, and go through the walkthrough on MSDN.

    The add-in gets it’s data repository and script generation code on the “API” available in my ExportSqlCe CodePlex project. In the latest source for this project, the “scripting framework” is available a a single DLL file, SqlCeScripting.dll.

    The add-in bases it’s SQL Server Compact database connections on the Data Connections that you have defined in the Server Explorer. Below is the code to return a list of SQL Server Compact Data Connection names and related connection strings, based on the connections in Server Explorer:

    using Microsoft.VisualStudio.Data;
    using Microsoft.VisualStudio.Data.AdoDotNet;
    using Microsoft.VisualStudio.Data.Interop;



    internal static Dictionary<string, string> GetDataConnections(SqlCe35ToolboxPackage package)
    {
    // http://www.mztools.com/articles/2007/MZ2007018.aspx
    Dictionary<string, string> databaseList = new Dictionary<string, string>();

    var objIVsDataConnectionsService = package.GetServiceHelper(typeof(IVsDataConnectionsService)) as IVsDataConnectionsService;
    var objIVsDataConnectionManager = package.GetServiceHelper(typeof(IVsDataConnectionManager)) as IVsDataConnectionManager;
    if (objIVsDataConnectionsService != null)
    {
    for (int i = 0; i < objIVsDataConnectionsService.Count; i++)
    {
    var objProviderGuid = objIVsDataConnectionsService.GetProvider(i);
    if (objProviderGuid == package.SqlCompact35Provider)
    {
    var sConnectionName = objIVsDataConnectionsService.GetConnectionName(i);
    var sConnectionString = objIVsDataConnectionsService.GetConnectionString(i);
    var objIVsDataConnection = objIVsDataConnectionManager.GetDataConnection(objProviderGuid, sConnectionString, true);
    var conn = (DataConnection)objIVsDataConnection;
    var adoConn = (AdoDotNetConnectionSupport)conn.ConnectionSupport;
    //TODO test for Mobile Connection (using Microsoft.SqlServerCe.Client)
    //Hack to get the underlying connection string including password
    var ceconn = (System.Data.Common.DbConnection)adoConn.ProviderObject;
    databaseList.Add(ceconn.ConnectionString, sConnectionName);
    }
    }
    }
    return databaseList;
    }

    //In the package class:


    public object GetServiceHelper(Type type)
    {
    return GetService(type);
    }



    In addition to the script related features, the add-in allows you to create DGML documents, that visualize the table relationships of your database (see sample in my earlier blog post).



    The following code shows how to open the DGML file from add-in code:



    var dte = package.GetServiceHelper(typeof(EnvDTE.DTE)) as EnvDTE.DTE;


    using (IRepository repository = new DBRepository(databaseInfo.Connectionstring))
    {
    Generator generator = new Generator(repository, fileName);
    generator.GenerateSchemaGraph();
    dte.ItemOperations.OpenFile(fileName);
    dte.ActiveDocument.Activate();
    }


    Here are a few screenshots:



     



    toolbox2



    The table context menu, and the simple query editor.



     



    toolbox1



    The database context menu.



     



    TOOLBOX3



    The root context menu, with SQL Server related menu items.



    Please try out the beta, and provide feedback of any kind.

    Wednesday, June 2, 2010

    SQL Compact Table Copy/Export samples published

    João Paulo Figueira has published a couple of useful sample apps (this time in managed code!) on his new “SmilingSmith” blog:

    SQL Compact table copy

    How do you make a copy of a SQL Compact table? Version 3.5 does not support the SELECT INTO command that allows you to easily do just that. Instead, it provides the INSERT INTO SELECT FROM command that allows you to copy existing table data into another existing table. So we are only missing one part: how to create a replica of the existing table structure.

    SQL Compact table export

    This is a very simple variation on my last blog post: instead of copying the table to the same database, you can now copy it to another database (although with the same table name, but you can easily change that).

    Enjoy!