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.

  <connectionStrings>
    <add name="netTiersConnectionString" connectionString="Data Source=C:\NetTiers\Northwind.sdf;" />
  </connectionStrings>

  <netTiersService defaultProvider="SqlCeNetTiersProvider">
    <providers>
        <!--
        *** SqlCeClient Provider ***  this is the Sql Compact .netTiers provider
        connectionStringName: the connection string to the db
        useStoredProcedure: always false, so we use parametrized queries that are embedded.
    -->
      <add
        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"
        />
    </providers>

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!