Is RDA (Remote Data Access) still relevant

Rob Tiffany asks this rhetorical question, and the answer is a resounding YES – there are many sync scenarios, in particular on devices, where RDA is the right technology. Keep in mind, that RDA has not been updated to support the new data types in SQL Server 2008.

READ MORE - Is RDA (Remote Data Access) still relevant

Interview with SQL Server Compact Program Manager Himadri Sarkar

SQL Compact Program Manager Himadri Sarkar was interviewed on the Connected Show in July 2009. He discusses the histroy of SQL Server Compact, from only Windows Mobile/CE support to support for all Windows desktop OSes. He also describes the extensive tooling support both in Visual Studio and SQL Server Management Studio. Listen to the 30 minute interview here. (Starting at about 30 minutes into the show)

READ MORE - Interview with SQL Server Compact Program Manager Himadri Sarkar

Which SQL Compact version is included with Visual Studio 2010 beta 1 and SQL Server 2008 R2 August CTP?

The version of SQL Compact included with these beta/CTP releases is 3.5.5692.0 – that means 3.5 SP1 RTM. But there are indications that this will change to a more recent build/SP in VS 2010 beta 2 (and later).

READ MORE - Which SQL Compact version is included with Visual Studio 2010 beta 1 and SQL Server 2008 R2 August CTP?

Running SQL Compact from CD-ROM (read only media) on all Windows platforms

In order to create a read only application to be run from CD-ROM (or similar) in managed code, there are some issues that must be considered in order to support all current Microsoft Windows platforms (XP (if you can call that “current”) and Vista/Win7), and both x86 and AMD64 (x64) processor architectures.

The user will need to have .NET Framework 2.0 installed, it will already be installed if the OS is Vista or later.

For the SQL Compact runtime engine, simply include the SQL Compact managed and unmanaged DLL files in the application folder. Make sure to set the platform of your .exe to x86, or include both the x86 and AMD64 runtimes. Doing either of these 2 options will allow the SQL Compact engine to run on all Windows x86 and x64 platforms (XP/2003 and later).

For the procedure for including the relevant runtime files in your project, see this blog post: http://blogs.msdn.com/stevelasker/archive/2008/10/22/privately-deploying-sql-server-compact-with-the-ado-net-entity-provider.aspx and this sample from Steve Lasker: Running Compact from Read Only Media (DVD, CD, Locked USB Key).

As reported here by the SQL Compact team (sadly, not before April 1st (!) 2009), the runtime needs to re-create indexes if a SDF file has been moved from Vista/Server 2008 to XP/Server 2003. On read-only media, this cannot happen, and you will get an obscure “Permission denied” error message.

A possible workaround for this would be to open the file on both platforms (using Virtual PC or similar, if need be), and distribute 2 SDF files on the read only media.

In order to open a SDF file form read-only media, you also need to add two additional parameters to the connection string in use: “Mode=Read Only” and “Temp Path=”

The sample code below illustrates how you could implement the 2 paragraphs above when creating your connection string. In addition, the path to the SDF file is dynamically created.

 

using System;
namespace ReadOnly
{
class Program
{
static void Main(string[] args)
{
    string connStr;
    string sdfName;
    // Choose file based on OS version
    if (System.Environment.OSVersion.Version.Major < 6)
    {
        // This SDF was created on XP or Win2003
        sdfName = "Northwind5.sdf";
    }
    else
    {
        // This SDF was created on Vista or Win7
        sdfName = "Northwind.sdf";
    }

    // Notice the "Mode = Read Only" and "Temp Path=%TEMP%" options added to the connection string
    connStr = String.Format(@"Data Source = {0}\{1};Mode = Read Only;Temp Path={2}", 
    System.IO.Path.GetDirectoryName(System.Reflection. Assembly.GetExecutingAssembly().GetName().CodeBase),
    sdfName, 
    System.IO.Path.GetTempPath());                
}
}
}
 

READ MORE - Running SQL Compact from CD-ROM (read only media) on all Windows platforms

Getting started with a CodeSmith nettiers data access layer for your SQL Compact database

The CodeSmith code generation tool has recently been updated to support SQL Server Compact Edition 3.5 (from version 5.1), and the nettiers Object/Relational Mapper (ORM) included with the CodeSmith product has also been enhanced with support for creating a data access layer based on SQL Compact. I have posted a feature request that the excellent PLINQO product (which also comes with CodeSmith) should be updated to support SQL Compact.

This blog post demonstrates how to create a data access layer for a Windows forms application, with all the nettiers features, and highlights the special considerations you have to apply when using SQL Compact. It is not a general overview of the nettiers code generation process.

Please note the flowing limitations when using SQL Compact and SQL Compact with nettiers:

- SQL Compact with ASP.NET applications is not supported (SQL Compact engine limitation).

-SQL Compact does not have stored procedures and views (SQL Compact engine limitation).

- Paging with Get… methods is not supported (all rows are returned)

- You must use Enterprise Library version 4.1 (see “Using SQL Server CE” section in link)

First, download and install the latest version of CodeSmith (5.1.4). It includes everything that you need to get started with nettiers and SQL Compact usage.

Code generation

After installation, locate the nettiers templates in the folder shown below, making sure it contains a “DataAccessLayer.SqlCeClient” folder. If not, you can extract the nettiers templates from the samples.zip in the C:\Program Files\CodeSmith\v5.1\Samples folder.

Double click the NetTiers.cst file, and wait for CodeSmith to compile the templates.

Next, set the required configuration properties:

Click the button on the ChooseSourceDatabase grid line.

Click the button next to the Data Source drop down.

Click add to create a data source pointing to your SQL Compact database file.

Give you data source a name, and select the SqlCompactSchemaProvider in the Provider Type dropdown. You can type the SQL Compact connection string below, or use the (simple) connection string builder by clicking the button next to the Connection String text box. For this sample I am using the the repaired Northwind, which Roger Jennings has created for his ADO.NET 3.5 book.

Click OK and Close

Select the Northwind data source and click Select

Back at the CodeSmith properties, I have now selected the Northwind data source, and changed the OutputDirectory and RootNameSpace properties.

In the following I will only mention non-default properties that are recommended for working with SQL Compact.

You must use Microsoft Enterprise Library version 4.1, older versions are not supported with SQL Compact. You can use any supported DotNetVersion (2, 3, 3.5).

I suggest using the ServiceLayer and also recommend creating a Unit test project. This will help you get the proper app.config and entlib.config files for your Winforms project. Remaining properties are left at their defaults.

Do not generate any ASP.NET projects, so I set GenerateWebLibrary, GenerateWebService , GenerateWebsite and GenerateWebSiteAdmin to False.

Set GenerateWinLibrary to True, to get some strong typed RAD User Controls generated for use in our Winforms project. Click Generate to create the Visual Studio 2008 solution and projects.

Your output folder should look like this:

This folder contains the projects that implements the nettiers data access layer, the Unit Test project and the Windows Forms User Controls.

Visual Studio integration

Open the solution (Northwind.sln) in Visual Studio, and add a new Winforms project to the solution, in order to consume the generated code:

Add references to the following projects from the Winforms project:

Your Solution Explorer should look like this now:

Build the solution, and from the Toolbox, add the CustomersDataGridView user control to the form:

Set the Dock property to Fill on the Property Grid for the customersDataGridView1:

Your Form1 now looks like this:

Open the Form1.cs code. Add the following using statements:

using Northwind.Entities;

using Northwind.Services;

Add a Form1_Load event handler, with the following code:

customersDataGridView1.CustomersList = new CustomersService().GetAll();

Notice the other GetBy… methods that have been generated for you, based on indexes and foreign keys in the database.

Finally, you need to add the proper Enterprise Library configuration setting to your application. This can be done by copying the Northwind.UnitTests.dll.config from the UnitTests project to your UI project, and rename this file to app.config.

The SQL Compact specific app.config section looks like this – notice the special providerInvariantName value.

 
   
 

 
   
       
              name="SqlCeNetTiersProvider"
        type="Northwind.Data.SqlCeClient.SqlCeNetTiersProvider, Northwind.Data.SqlCeClient"
        connectionStringName="netTiersConnectionString"
        useStoredProcedure="false"
        providerInvariantName="System.Data.SqlServerCe.3.5"
        entityFactoryType="Northwind.Entities.EntityFactory"
        useEntityFactory="true"
        enableEntityTracking="false"
        enableMethodAuthorization="false"
        defaultCommandTimeout="30"
        />
   

Also copy the entlib.config file from the UnitTests project to the UI project.

Right click the UI project in Solution Explorer and select “Set as StartUp project”.

Now your solution should look like this:

Press F5 to build and run the application:

In order to save any changes made to the data bound grid, use this single line of code (in an event handler):

new CustomersService().Save(customersDataGridView1.CustomersList);

See the nettiers documentation for more samples on how to use the generated data access layer – and happy coding!

READ MORE - Getting started with a CodeSmith nettiers data access layer for your SQL Compact database

Script SQL Server Compact tables in Management Studio just like SQL Server tables

One of the notable features missing from the SQL Server Management tools for SQL Compact is the ability to script DML (data manipulation language) that is: SELECT, UPDATE, INSERT, DELETE and DDL (data definition language): CREATE, DROP, ALTER when browsing SQL Server Compact tables in SQL Server Management Studio (SSMS) (Figure 1 below). Now, 1 year after the release of SQL Server 2008, you can now get the same features for SQL Compact tables (Figure 2 below) by installing the latest version of the free ExportSqlCE add-in for SSMS, available for download on CodePlex.

As always, let me know if there are any issues and/or feature requests by providing feedback here.

Figure 1: This is available in SSMS when browsing SQL Server tables.

Figure 2: With version 2 of the ExportSqlCE SSMS add-in, this is available when browsing SQL Server Compact tables.

READ MORE - Script SQL Server Compact tables in Management Studio just like SQL Server tables