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:

 image

(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:inetpubwwwrootAspClassicApp_Datanw40.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>

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 FilesMicrosoft SQL Server Compact Editionv4.0Private.
image
Copy all files and folders here to the project folder (on my system C:projectsChinookChinook.WPF).
Select “Show all files” in Solution Explorer in Visual Studio. Your project should now look similar to this:
image
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):
image
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

image

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

image

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:

image

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

image

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):

image

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

image

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:

image

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

image

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:

image

Happy deployment!

You can download the completed solution from here:

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

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:

image

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

image

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:

image

<?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:

image

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:

Ok, I learned a cool feature of the GO command in SQL Server client tools (osql, sqlcmd and SQL Server Management Studio). That is that you can add a number [Count] after the GO keyword. The client tool will then execute the batch that precedes the GO the number of times specified by the [Count] value.

It looks like this was added back in SQL 2005, and I just never noticed it. So, it’s new to me!

For Example, the following batch submitted in a SQL Server Management Studio query window

SET NOCOUNT ON –just to cut down on the output…
SELECT ‘Hey, that’’s cool!’ AS Neat
GO 3

Produced the following output:

Beginning execution loop
Neat
—————–
Hey, That’s Cool!

Neat
—————–
Hey, That’s Cool!

Neat
—————–
Hey, That’s Cool!

Batch execution completed 3 times.

The following is a handy script that will give you a list of all objects which were created, altered and deleted in the past 24 hours.

SELECT  TE.name ,
v.subclass_name ,
DB_NAME(t.DatabaseId) AS DBName ,
T.NTDomainName ,
t.NTUserName ,
t.HostName ,
t.ApplicationName ,
t.LoginName ,
t.Duration ,
t.StartTime ,
t.ObjectName ,
CASE t.ObjectType
WHEN 8259 THEN 'Check Constraint'
WHEN 8260 THEN 'Default (constraint or standalone)'
WHEN 8262 THEN 'Foreign-key Constraint'
WHEN 8272 THEN 'Stored Procedure'
WHEN 8274 THEN 'Rule'
WHEN 8275 THEN 'System Table'
WHEN 8276 THEN 'Trigger on Server'
WHEN 8277 THEN '(User-defined) Table'
WHEN 8278 THEN 'View'
WHEN 8280 THEN 'Extended Stored Procedure'
WHEN 16724 THEN 'CLR Trigger'
WHEN 16964 THEN 'Database'
WHEN 16975 THEN 'Object'
WHEN 17222 THEN 'FullText Catalog'
WHEN 17232 THEN 'CLR Stored Procedure'
WHEN 17235 THEN 'Schema'
WHEN 17475 THEN 'Credential'
WHEN 17491 THEN 'DDL Event'
WHEN 17741 THEN 'Management Event'
WHEN 17747 THEN 'Security Event'
WHEN 17749 THEN 'User Event'
WHEN 17985 THEN 'CLR Aggregate Function'
WHEN 17993 THEN 'Inline Table-valued SQL Function'
WHEN 18000 THEN 'Partition Function'
WHEN 18002 THEN 'Replication Filter Procedure'
WHEN 18004 THEN 'Table-valued SQL Function'
WHEN 18259 THEN 'Server Role'
WHEN 18263 THEN 'Microsoft Windows Group'
WHEN 19265 THEN 'Asymmetric Key'
WHEN 19277 THEN 'Master Key'
WHEN 19280 THEN 'Primary Key'
WHEN 19283 THEN 'ObfusKey'
WHEN 19521 THEN 'Asymmetric Key Login'
WHEN 19523 THEN 'Certificate Login'
WHEN 19538 THEN 'Role'
WHEN 19539 THEN 'SQL Login'
WHEN 19543 THEN 'Windows Login'
WHEN 20034 THEN 'Remote Service Binding'
WHEN 20036 THEN 'Event Notification on Database'
WHEN 20037 THEN 'Event Notification'
WHEN 20038 THEN 'Scalar SQL Function'
WHEN 20047 THEN 'Event Notification on Object'
WHEN 20051 THEN 'Synonym'
WHEN 20549 THEN 'End Point'
WHEN 20801 THEN 'Adhoc Queries which may be cached'
WHEN 20816 THEN 'Prepared Queries which may be cached'
WHEN 20819 THEN 'Service Broker Service Queue'
WHEN 20821 THEN 'Unique Constraint'
WHEN 21057 THEN 'Application Role'
WHEN 21059 THEN 'Certificate'
WHEN 21075 THEN 'Server'
WHEN 21076 THEN 'Transact-SQL Trigger'
WHEN 21313 THEN 'Assembly'
WHEN 21318 THEN 'CLR Scalar Function'
WHEN 21321 THEN 'Inline scalar SQL Function'
WHEN 21328 THEN 'Partition Scheme'
WHEN 21333 THEN 'User'
WHEN 21571 THEN 'Service Broker Service Contract'
WHEN 21572 THEN 'Trigger on Database'
WHEN 21574 THEN 'CLR Table-valued Function'
WHEN 21577
THEN 'Internal Table (For example, XML Node Table, Queue Table.)'
WHEN 21581 THEN 'Service Broker Message Type'
WHEN 21586 THEN 'Service Broker Route'
WHEN 21587 THEN 'Statistics'
WHEN 21825 THEN 'User'
WHEN 21827 THEN 'User'
WHEN 21831 THEN 'User'
WHEN 21843 THEN 'User'
WHEN 21847 THEN 'User'
WHEN 22099 THEN 'Service Broker Service'
WHEN 22601 THEN 'Index'
WHEN 22604 THEN 'Certificate Login'
WHEN 22611 THEN 'XMLSchema'
WHEN 22868 THEN 'Type'
ELSE 'Hmmm???'
END AS ObjectType
FROM [fn_trace_gettable](CONVERT(VARCHAR(150), ( SELECT TOP 1
value
FROM [fn_trace_getinfo](NULL)
WHERE [property] = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
JOIN sys.trace_subclass_values v ON v.trace_event_id = TE.trace_event_id
AND v.subclass_value = t.EventSubClass
WHERE EventClass IN ( 46, 47, 164 )
-- filter statistics created by SQL server
AND t.ObjectType NOT IN ( 21587 )
-- filter tempdb objects
AND DatabaseID &lt;&gt; 2
-- get only events in the past 24 hours
AND StartTime &gt; DATEADD(HH, -24, GETDATE())
ORDER BY t.StartTime DESC ;