Wednesday, August 12, 2009

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.

clip_image002

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

Next, set the required configuration properties:

clip_image004

Click the button on the ChooseSourceDatabase grid line.

clip_image006

Click the button next to the Data Source drop down.

clip_image008

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

clip_image010

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.

clip_image012

clip_image014

Click OK and Close

clip_image016

Select the Northwind data source and click Select

clip_image018

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.

clip_image020

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.

clip_image022

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

clip_image024

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:

clip_image026

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:

clip_image028

Add references to the following projects from the Winforms project:

clip_image030

Your Solution Explorer should look like this now:

clip_image032

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

clip_image034

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

clip_image036

Your Form1 now looks like this:

clip_image038

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();

clip_image040

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:

clip_image042

Press F5 to build and run the application:

clip_image044

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!

13 comments:

L said...

Nice! I am only using CodeSmith and NetTiers with ASP.NET, but this is very useful!

gezgin said...

Can you post here the full app.config file? I couldn't be sure to put the net-tiers parts in the app.config file. You can put any sample (net-tiers including) VS projects app.config file.

Thanks.

ErikEJ said...

Use the app.config file from the Unit Test project

awni said...

Hi,

I just downloaded CS 5.3.4 and for Ent. Lib. Version, it only has two options:
3.1
5.0

There is no 4.1 option. I tried using both 3.1 and 5. I get a lot of compiler errors and some generated code that is not logical.

Is there a work-around for this case?

thanks,
awni

ErikEJ said...

Awni: Weird, I have installed 5.3.4 and only seen entlib 3.1 and 4.1 !?

awni said...

Erik, have you tried to run the generated code on a mobile device?

ErikEJ said...

Awni: Mobile device won't work, have a look at orm.codplex.com

awni said...

Thanks.
I tried sqlcecodegen from codeplex, it's pretty good but not open like your suggestions.

regards,
awni

Sue said...

This article is excellent. However, I am unable to generate the Northwind.Services project. I'm using VS2010, .NET FW4.

ErikEJ said...

Sue: Could you test with VS 2008, this is an old article (and old product). For new projects, I prefer EF4.

cesar.qüeb said...

Hello,

Somebody knows how to retrieve all foreing keys in an Nettier's entity?.

Unknown said...

Hello everybody, actually I'm trying to use .neTiers in order to make an ASP.net application, I have generated the code from the database (adding ,updating,searching...) but the problem is that I can't exploit it for a specific treatment, for example ( DrowpDownList will be filled out from the database and it will fill out a textbox when I choose an item). I am so grateful.

ErikEJ said...

Alaeddine: Suggest you ask in the CodeSmith support forum