Everything SQL Server Compact: February 2011

Sunday, February 27, 2011

Access SQL Server Compact 4 with ASP Classic and VbScript

Now that you can use SQL Server Compact 4 with ASP.NET, some developers wonder if you can also use it with ASP Classic. To access with ASP Classic, the SQL Server Compact 4.0 OLEDB provider must be installed on the system, so the 4.0 MSI must be installed by an administrator – no private deployment.

But YES, you can access a SQL Server Compact 4 database file from ASP Classic:

(The url is: http://localhost/aspclassic/default.asp)

Of course the required read/write permissions must be given the the relevant process user to the folder where the database file is located.

Here is the code to do it:

<html>
<head>
<title>Test SQL Compact 4 and ASP Classic + ADO</title>
</head>
<body>

set conn = Server.CreateObject("ADODB.Connection")

strCnxn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.4.0;" & _
"Data Source=C:\inetpub\wwwroot\AspClassic\App_Data\nw40.sdf;"

conn.Open strCnxn

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", conn

do until rs.EOF
    for each x in rs.Fields
       Response.Write(x.name)
       Response.Write(" = ")
       Response.Write(x.value & "<br />")
    next
    Response.Write("<br />")
    rs.MoveNext
loop
'
rs.close
conn.close
%>

</body>
</html>

Monday, February 21, 2011

Using SQL Server Compact 4.0 with Desktop Private Deployment and a Setup project (MSI) (part 2)

I my previous post in this 2 part series, I demonstrated how to use SQL Server Compact 4 with Entity Framework 4 in a desktop application, despite the not excellent tools support.
This time I will show how to implement private deployment, and also how to solve other challenges related to installing via a Windows Installer file (MSI), by adding a Visual Studio Setup project to the solution.
First I will configure the project for Private Deployment, and then add a Setup project.For more information on requirements for Private Deployment, see my blog post SQL Server Compact “Private Deployment” on desktop–an overview.

Enable Private Deployment in a project

This includes copying the required SQL Server Compact 4.0 runtime files, and including these as content in the project, and modifying app.config to refer to the Private managed ADO.NET provider.
Locate the files to be copied in C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private.

Copy all files and folders here to the project folder (on my system C:\projects\Chinook\Chinook.WPF).
Select “Show all files” in Solution Explorer in Visual Studio. Your project should now look similar to this:

Include the amd64 and x86 folder in project (right click), including all content and subfolder as Content, Copy Always. Also include the two managed DLL files in the project root (System.Data.SqlServerCe.dll and System.Data.SqlServerCe.Entity.dll). Make sure to specify “Copy Always”, or the files will not be included in the project output. Verify that all files are included by looking in the debug folder after building the project.
You should now have a project structure like this (same set of files in the x86 folder, of course):

Now modify your app.config to refer to the Private ADO.NET provider, which has assembly version 4.0.0.1, not 4.0.0.0 as the on in the GAC. Using this special assembly version provider will prevent assembly probing from picking up a newer version of the provider in the GAC.

<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0"/>
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>

Notice the version on the type entry is 4.0.0.1.
IMPORTANT: If you reference System.Data.SqlServerCe.dll, make sure to reference the 4.0.0.1 version in your project folder!

Verify that the application still runs, and displays data.

Add and configure a setup project to produce a MSI (Windows Installer) file

(This is a little involved, as I will demonstrate solutions to several deployment issues here).

Start by adding a Visual Studio Installer project to the solution, and call it Chinook.WPF.Setup

Create a setup for a Windows application, and add the Content Files and Primary Output from Chinook.WPF:

For this project, we want to include the Chinook40.sdf file and deploy it with our installer. Other options include creating the sdf file at the first application startup. So we add the Chinook40.sdf file as additional file:

Set the Permanent property on the Chinook40.sdf file to True, to prevent it from being removed during uninstall.

In the file system browser in the Setup project, add the User’s application data folder, this is where we want the sdf file placed,as this is a writable location (Program Files folder is not writable):

Now we can set the folder location of the sdf file to this folder:

So the database file will now be installed in the C:\\Users\\<Username>\\AppData\\Roaming\\ folder. Now we need to modify the connection string. We can do this by manipulating the DataDirectory location, as this is part of the connection string. Add a Startup event handler to App.xaml:

<Application x:Class="Chinook.WPF.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             StartupUri="MainWindow.xaml" Startup="Application_Startup">
    <Application.Resources>
         
    </Application.Resources>
</Application>

In this handler, add the following code, notice the comments (!):

private void Application_Startup(object sender, StartupEventArgs e)
        {
            // This is our connection string: Data Source=|DataDirectory|\Chinook40.sdf
            // Set the data directory to the users %AppData% folder
            // So the Chinook40.sdf file must be placed in:  C:\\Users\\<Username>\\AppData\\Roaming\\
            AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
        }

Finally, add a shortcut to the application in the User’s Programs Menu file system folder:

Move the shortcut to the User’s Programs Menu folder, and rename to Chinook.

Now build the Setup project (right click and select Build), and test the installer on a system without SQL Server Compact 4.0 installed. .NET 4.0 Client Profile must be installed, however. The setup will not install if this is not the case.

You can test on your development system, by uninstalling the desktop runtime:

Happy deployment!

You can download the completed solution from here:

https://1drv.ms/u/s!AitHcOtLnuVHgwewiWcudEGRkpEt

Monday, February 7, 2011

Using SQL Server Compact 4.0 with WPF DataGrid, Entity Framework 4 and Private deployment (part 1)

In this and a following post, I will show, how you can use SQL Server Compact 4.0 with a desktop application, despite full tool support for this. Reasons for wanting to use SQL Server Compact 4 rather than 3.5 SP2 could be: Better support for Entity Framework 4, including support for Server Generated Keys and the Code First CTP, paging support and new ADO.NET APIs such as GetSchema and SqlCeConnectionStringBuilder. For an overview of SQL Server Compact 4, see this.

I will also show how to include the SQL Server Compact DLL files for private deployment with a desktop application, including in a Visual Studio Setup project (in part 2).

I will reuse the Data Access Layer based on Entity Framework 4 and POCO classes that I created in this blog post. Notice that there is no direct tooling to create a Entity Framework Model from database in the SQL Server Compact 4 tools for Visual Studio SP1, and therefore you must use one of the workarounds described. A third workaround is to install the VS 2010 SP1 SQL Compact Tools, create the 4.0 based model in a web project, and move this model to a Class library.

Start by opening the Chinook solution, and add a new WPF Application, Chinook.WPF:

Add references to the 3 project that make up the Data Access Layer (Data, Model and Repository):

Add a WPF DataGrid to the MainWindow.xml file, so it looks like so:

<Window x:Class="Chinook.WPF.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <DataGrid AutoGenerateColumns="True" Margin="5,5,5,5" Name="dataGrid1" />
    </Grid>
</Window>

Notice that for this demo, I have set AutoGenerateColumns=True.

Add an event handler for the Loaded event, and add this code to MainWindow.xaml.cs:

var repo = new TrackRepository();
dataGrid1.ItemsSource = repo.GetAll(null, 50, 0); ;

Also add using Chinook.Repository; to the using statements.

For this demo, let us assume that each user has her own database file. So where can we put the file – the users ApplicationData is a possibility.

Now add an app.config file with the Entity Framework connection string:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="ChinookEntities" connectionString="metadata=res://*/ChinookModel.csdl|res://*/ChinookModel.ssdl|res://*/ChinookModel.msl;provider=System.Data.SqlServerCe.4.0;provider connection string=&quot;Data Source=|DataDirectory|\Chinook40.sdf&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

Notice that the SQL Server Compact connection string is:

Data Source=|DataDirectory|\Chinook40.sdf

So to make the application look for the database in the proper location, we can redefine the DataDirectory macro, by adding an event handler to App.xaml.cs like so:

private void Application_Startup(object sender, StartupEventArgs e)
{
    // This is our connection string: Data Source=|DataDirectory|\Chinook40.sdf
    // Set the data directory to the users %AppData% folder
    // So the Chinook40.sdf file must be placed in:  C:\\Users\\<Username>\\AppData\\Roaming\\
    AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
}

Now place a copy of the Chinnok40.sdf file in the proper location (on my Windows 7 PC, it is C:\\Users\\ErikEJ\\AppData\\Roaming\\, set Chinook.WPF as the startup project and run the application:

To make the code run faster, you can add this line to the TrackRepository.GetAll method:

context.ContextOptions.ProxyCreationEnabled = false;

You now have implemented a WPF application using Entity Framework 4 using POCO and Database First with SQL Server Compact 4.0.

In the next part I will show how to add the SQL Server Compact DLL files for private deployment, and create a MSI that installs these files and the database file, so the application can run on any machine with .NET 4.0 and a supported platform. (And .NET 4.0 being the only required component).

Part 2- Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment: http://erikej.blogspot.dk/2011/01/entity-framework-with-sql-server.html

You can download the solution so far from here:

Everything SQL Server Compact