Thursday, July 15, 2010

Getting started with SQL Server Compact 4.0 and ASP.NET 4.0 (no WebMatrix)

While professional developers are waiting for the Visual Studio Tools and Designers for SQL Server Compact 4.0, I will show how impatient develoers can include SQL Server Compact with ASP.NET applications, and use it from ASP.NET pages.

Previously, you had to circumvent the SQL Compact ASP.NET blocker by adding a line of code to global.asax, as I describe here. This is no longer required, and SQL Compact 4.0 can now reliably handle web load.

Including SQL Server Compact 4.0 with your ASP.NET 4.0 app

1: Download http://tiny.cc/cfjia and install the 4.0 CTP runtime.

2: Copy the contents of the folder C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private (show below) to the bin folder in your web app (you may have to use Show All Files in VS to see this folder).

image

3: Place your SQL Compact sdf file in your App_Data folder, so your solution looks like this (with Show All Files on):

image

You can include the database file and the SQL Compact as content (Do not copy), if desired (so they become part of your project file).

image

4: Add a connection string to web.config, notice the |DataDirectory| macro, which will expand to the App_Data folder.

  <connectionStrings>
<
add name ="NorthWind"
connectionString="data source=|DataDirectory|\Nw40.sdf" />
</
connectionStrings>


5: Write code to connect. For now, you must use vanilla ADO.NET.Later you will be able to use Entity Framework and other OR/Ms to provide and model of your database. (Not LINQ to SQL, however). – UPDATE: EF CTP with SQL Compact support just released: Microsoft announced a new Entity Framework CTP today.



using System;
using System.Configuration;
using System.Data.SqlServerCe;

namespace Ce40ASPNET
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
using (SqlCeConnection conn = new SqlCeConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
conn.Open();
using (SqlCeCommand cmd = new SqlCeCommand("SELECT TOP (1) [Category Name] FROM Categories", conn))
{
string valueFromDb = (string)cmd.ExecuteScalar();
Response.Write(string.Format("{0} Time {1}", valueFromDb, DateTime.Now.ToLongTimeString()));
}
}
}
}
}



You can now upload you project files to any web hosting site running ASP.NET 4.0, and your database runtime will be included in the upload. No SQL Server subscription will be required.



Hope this will get you started with SQL Compact 4.0  and ASP.NET.



Tuesday, July 13, 2010

How to check the Visual Studio 2010 edition from code

In my SqlCeToolBox add-in, I only expose some features if the user has Visual Studio Premium or higher, as I depend on some of the Data Dude tools.

The code to do this looks like this, notice the RegistryView option on the OpenBaseKey method. This ensures that this code works on both x64 and x86 based registries.

public static bool IsPremiumOrUltimate()
{
// From http://blogs.msdn.com/b/heaths/archive/2010/05/04/detection-keys-for-net-framework-4-0-and-visual-studio-2010.aspx
//Key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DevDiv\VS\Servicing\10.0\$(var.ProductEdition)\$(var.LCID)
//The values for $(var.ProductEdition) include the following table.
//Visual Studio 2010 Ultimate VSTSCore
//Visual Studio 2010 Premium VSTDCore
//Visual Studio 2010 Professional PROCore
//Visual Studio 2010 Shell (Integrated) IntShell
using (var key = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry32))
{

var ultimateKey = key.OpenSubKey(@"SOFTWARE\Microsoft\DevDiv\VS\Servicing\10.0\VSTSCore");
if (ultimateKey != null)
return true;

var premiumKey = key.OpenSubKey(@"SOFTWARE\Microsoft\DevDiv\VS\Servicing\10.0\VSTDCore");
if (premiumKey != null)
return true;
}
return false;


}



Wednesday, July 7, 2010

SQL Server Compact 4.0 CTP1 released

The SQL Server Compact team has just released of CTP of version 4.0 of SQL Server Compact Edition.

Ambrish Mishra has a detailed blog about the new features. Here is a short summary:

ASP.NET support

The main feature of version 4.0 is support for using SQL Compact as a file based database engine with ASP.NET websites.

The work done to enable ASP.NET includes:

Higher Reliability – can handle load of starter websites

Setup Enhancements – easier private deployment and a single x64 installer.

Support for ASP.NET – removal of the ASP.NET blocker

Virtual Memory Reduction – allows up to 256 concurrent connections (max) without virtual memory errors.

Medium Trust – enables web hosting with .NET 4.0

It is a part of the recently announced WebMatrix tool for starter websites as a starter database engine, which also includes a tool to design the database used on the website:

image

T-SQL changes

Support for paging queries:

SELECT * FROM Customers ORDER BY [Customer ID] OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

ADO.NET Provider enhancements

Seamless Integration with ADO.NET Entity Framework 4.0 (.NET FX 4) – server generated keys and code first support coming soon.

System.Data.SqlServerCe.SqlCeConnection.GetSchema() – allows you to get schema information for use in scripting tools etc.

System.Data.SqlServerCe.SqlCeConnectionStringBuilder() –allows you to build a strongly typed connection string.

Migration

The WebMatrix tools includes a tool to migrate a SQL Compact database to SQL Server 2006/2088/Azure. The tool does not generate a script, but simply creates a new database on the server selected.

image

For migration from SQL Server to SQL Compact, (and script based migration from SQL Compact to SQL Server), you can continue to use my ExportSqlCe tools. I have described the procedure to migrate from SQL Server to SQL Server Compact here. In addition, I have just released SQL Server Compact 4.0 versions of ExportSqlCe and SqlCeCmd.

Visual Studio

Later, a Visual Studio update will enable you to use SQL Server Compact from the Visual Studio Data/Server Explorer, including from Visual Web Developer Express. I assume a similar update for SQL Server Management Studio (SSMS) is not in the cards.

Monday, July 5, 2010

SQL Compact Toolbox – manage Merge Replication subscriptions

In the new release 1.1 of my SQL Compact Toolbox  CodePlex project, it is now possible to manage/test Merge Replication subscriptions in your SQL Compact database file.

image

There is a new “Subscriptions” node. From the nodes context menu, select “New Subscription…”

image

Enter the required parameters, and press Sync. You will then get the tables from the publication in your SQL Compact file.

image

Manage Subscription will load all current parameters (except password), and you can Sync again, or create a C# code sample.

In addition, you can drop the subscription. This will remove any subscription metadata from your SQL Compact database file.

Hope you find this useful, any comments please post here.

Sunday, July 4, 2010

Content overview

My CodePlex SQL Server Compact tools

SQL Server Compact & SQLite Toolbox 4.4 & 4.5 – Visual Guide of new features
SQL Server Compact & SQLite Toolbox 4.3 – Visual Guide of new features
SQL Server Compact & SQLite Toolbox 4.2 – Visual Guide of new features

“SQL Server Compact & SQLite Toolbox” related news


SQLite & SQL Server Compact Toolbox 4.1 – Visual Guide of new features


SQLite Toolbox 4.0 – Visual Guide of Features


SQL Server Compact Toolbox 3.7.3 – Visual Guide of new features


SQL Server Compact Toolbox 3.7.2–Visual Guide of new features


SQL Server Compact Toolbox 3.7.1–Visual Guide of new features


SQL Server Compact Toolbox 3.7–Visual Guide of new features


Document your SQL Server database with free tools: SQL Server Compact Toolbox and DB>Doc
SQL Server Compact Toolbox 3.6–Visual Guide of new features
SQL Server Compact Toolbox 3.4–Visual Guide of new features
SQL Server Compact Toolbox 3.3–Visual Guide of new features
Some SQL Server Compact Toolbox usage graphs
SQL Server Compact Toolbox 3.2–Visual Guide of new features
SQL Server Compact Merge Replication Library alpha released
SQL Server Compact Toolbox 3.1.1 with support for Windows Phone 8 and VS 2012 released
SQL Server Compact Toolbox 3.1–Visual Guide of new features
SQL Server Compact Toolbox 3.0–Visual Guide of new features
SQL Server Compact Toolbox hits 100.000 downloads–new release planned
Migrating databases between SQL Server and SQL Server Compact
SQL Server Compact Toolbox 2.6–Visual Guide of new features
SQL Server Compact Toolbox 2.5–Visual Guide of new features
SQL Server Compact Toolbox available for Visual Studio 11

SQL Server Compact Toolbox 2.4–Visual Guide of new features

SqlCeBulkCopy, a library for fast SQL Server Compact INSERTS released
SQL Server Compact Toolbox 2.3–Visual Guide of new features
SQL Server Compact Toolbox 2.2–Visual Guide of new features
SQL Server Compact ASP.NET Membership, Role and Profile Provider version 2.1 now available
SQL Server Compact Toolbox standalone - including all managed DLLs in a single .exe
New release of Scripting Library and command line utilities–with Schema Diff and DGML from command line
SQL Server Compact Toolbox 2.1–Visual Guide of new features
SQL Server Compact Toolbox 2.0–Visual Guide of new features
New release of ExportSqlCe SSMS add-in and command line utilities–Script to SQL Azure

SQL Server Compact Toolbox add-in now supports version 4.0 databases

SQL Server Compact 4.0 ASP.NET Membership provider

SQL Server Compact version detector

How to Migrate/Downsize a SQL Server database to SQL Server Compact 4.0 (and 3.5)

SQL Server Compact 3.5 Toolbox updated

“SQL Compact Toolbox” – manage merge replication subscriptions

 “SQL Compact Toolbox” Visual Studio 2010 add-in be...

Version 3 of ExportSqlCE now available

SQL Server Compact Bulk Insert Library

Script SQL Server Compact tables in Management Stu...

SqlCeCmd tutorial part two – Creating database obj...

SqlCeCmd tutorial part one – Managing database fil...

SqlCeCmd tutorial part three – query options

Using ExportSQLCE from Visual Studio

Entity Framework Core

Getting started with the SQL Server Compact and other providers for Entity Framework 7 beta 6

Getting and building the Entity Framework 7 alpha bits – step by step

Entity Framework ”reboot” – EF7 – Get a sneak peek via TechEd US live stream

Entity Framework 6

A breaking change in Entity Framework 6.1.2 when using EDMX and SQL Server 2008/2005

Entity Framework 6 and SQL Server Compact 4.0 (10) – “Proper” private desktop deployment
Entity Framework 6 and SQL Server Compact (9) –SqlCeFunctions and DbFunctions
Entity Framework 6 & SQL Server Compact (8) –Logging SQL statements
Entity Framework 6 & SQL Server Compact (7) –New features and fixes in version 6.1
Entity Framework 6 & SQL Server Compact (6)–Entity Framework Reverse POCO Code First Generator
Entity Framework 6 (& SQL Server Compact) (5)–Entity Framework 6 extensions
Entity Framework 6 & SQL Server Compact 4.0 (4) - Restoring full EF Tools support in Visual Studio 2013

Entity Framework 6 & SQL Server Compact (3)–Getting started with the SQL Server Compact 3.5 provider (and Merge Replication)

Entity Framework 6 & SQL Server Compact 4 (2)–Easy Private Desktop Deployment

Entity Framework 6 & SQL Server Compact 4 (1)–Workflows and tooling

INSERTing many rows with Entity Framework 6 beta 1 and SQL Server Compact

Fix for Entity Framework poor INSERT performance with SQL Server Compact and server generated keys

Fixing the Entity Framework designer “Generate Database from Model” T4 template

Entity Framework 4.x

Viewing SQL statements created by Entity Framework with SQL Server Compact

Saving images to SQL Server Compact with Entity Framework 4.1 Code First

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

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

Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

Using Entity Framework with SQL Server Compact 4.0 CTP and ASP.NET – tips & tricks (part one)

Solutions to: “Server-generated keys and server-ge...

SQL Compact and Entity Framework woes

Deployment

The trouble with Any CPU–Prefer 32 bit–BadImageFormatException

SQL Server Compact 4 desktop app with simple Private Deployment and LINQ to SQL

Private deployment of SQL Server Compact 3.5 SP2

SQL Server Compact Private Deployment tweaks

SQL Server Compact “Private Deployment” on desktop–an overview

How to detect if the x64 SQL Compact 3.5 SP2 deskt...

Requirements for SQL Compact installations on x64 ...

Running SQL Compact from CD-ROM (read only media) ...

x64 and SQL Compact

Sort order issue when moving sdf files between pla...

SQL Compact internals and T-SQL

SQL Server Compact ADO.NET data access performance – part 1: Overview

SQL Server Compact ADO.NET data access performance – part 2: INSERTs

SQL Server Compact ADO.NET data access performance – part 3: SELECTs

SQL Server Compact 4.0 SP1 hotfix available

SQL Server Compact breaks when upgrading from Windows 8 to Windows 8.1

FAQ: Why is opening my SQL Server Compact database slow?

Scripting image column data for INSERT statements

Useful new topics in SQL Server Compact 4.0 Books Online

Comparison of SQL Server Compact 4, 3.5 SP2 and SQL Server Express 2008 R2

SQL Server Compact 3.5 SP2 downloadable update list

SQL Compact Table Copy/Export samples published

What’s new in SQL Server Compact 3.5 SP2 beta

Dealing with very large SQL Compact database files...

SQL Server 2005 Compact Edition downloadable hotfixes...

SQL Compact 3.5 SP1 downloadable hotfix list

Scripting SQL datetime fields and avoiding localiz...

SQL Compact 3.5 knowledge base articles

Working with Case Sensitive SQL Compact databases

Testing an sdf file for version info

TOP clause now supported

Hidden gem: Rename table

Code snippet of the week

SQL Server Compact Code Snippet of the Week #1 : locate the ROWGUIDCOL column in a table

SQL Server Compact Code Snippet of the Week #2 : locate the IDENTITY column in a table

SQL Server Compact Code Snippet of the Week #3 : locate the rowversion column in a table

SQL Server Compact Code Snippet of the Week #4 : select rows 50 to 60 from a table

SQL Server Compact Code Snippet of the Week #5 : rename a table

SQL Server Compact Code Snippet of the Week #6 : list all user tables in a database

SQL Server Compact Code Snippet of the Week #7 : get the full path to a database file

SQL Server Compact Code Snippet of the Week #8 : script a database to SQLite

SQL Server Compact Code Snippet of the Week #9 : migrate a SQL Compact database to SQL Server

SQL Server Compact Code Snippet of the Week #10 : generate a CREATE TABLE script

SQL Server Compact Code Snippet of the Week #11 : detect if SQL Server Compact is available

SQL Server Compact Code Snippet of the Week #12 : get the SQL Server Compact runtime version

SQL Server Compact Code Snippet of the Week #13 : reseed (reset) an IDENTITY column

SQL Server Compact Code Snippet of the Week #14 : script all data in a table

SQL Server Compact Code Snippet of the Week #15 : flush data to disk immediately 

SQL Server Compact Code Snippet of the Week #16 : detect Entity Framework database type

SQL Server Compact Code Snippet of the Week #17 : using wildcards with a parameterized query

SQL Server Compact Code Snippet of the Week #18 : handling SqlCeExceptions

SQL Server Compact Code Snippet #19 : migrate a SQL Server database to SQL Compact

SQL Server Compact Code Snippet #20 : change database password

Using SQL Server Compact

Visual Studio

Visual Studio 11 beta - Tooling for SQL Server Compact

Analyzing SQL Server Compact queries using Visual Studio 2010 Premium/Ultimate

SQL Server Compact Toolbox standalone - including all managed DLLs in a single .exe

Visual Studio 2010 Service Pack 1 with support for SQL Server Compact 4.0 released

Migrate a SQL Server Compact database to SQL Server using Web Deploy (MSdeploy)

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

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

Visual Studio Tools for SQL Server Compact 4 now available

Using Entity Framework with SQL Server Compact 4.0 CTP and ASP.NET – tips & tricks (part one)

SQL Compact 4.0 now available as a .zip file

HOW TO: Detect SQL Server Compact version information

HOW TO: Upgrade a version 3.x database file to SQL Server Compact 4.0

How to check the Visual Studio 2010 edition from code

FAQ: Why does my changes not get saved, SQL CE doe...

Diagram database table relationships using DGML, S...

Using the ADO.NET Data Connection dialog in your o...

How to save and retrieve Images using LINQ to SQL ...

Using SQL Compact with Log4net

Two great tutorials

Crystal Reports with SQL Compact

Accessing SQL Compact from SQL Server "Linked Serv...

x64 and SQL Compact

SQL Compact 3.5 - Manage relationships

Windows Phone

Useful Windows Phone advice from Nick Randolph

Windows Phone / SQL Server Compact resources

Populating a Windows Phone “Mango” SQL Server Compact database on desktop

SQL Server Compact Toolbox 2.2–Visual Guide of new features

Windows Phone Local Database tip: Viewing the SQL generated by LINQ to SQL

Windows Phone Local Database tip: Batch INSERT performance

Windows Phone Local Database tip: Exploring INSERT performance–5 power tweaks

Generating a LINQ to SQL DataContext for VS Express for Windows Phone

Windows Phone Local Database tip: Initializing the database

SQL Server Compact Toolbox 2.6.1–Visual Guide of new features

Windows Phone Local Database tip: Exploring multiple UPDATEs and rowversion impact

Windows Phone Local Database tip: Working with encrypted database files

Windows Phone Local Database tip: Exploring DELETE performance and a “Bug Alert”

Generate a Windows Phone 8 Local Database DataContext from an existing database

Windows Mobile

SQL Compact 3.5 OLEDB C++ sample now available

SQL Compact versions on Windows Mobile

New SQL Server Compact training video

ADO.NET Sync Services for Devices sample

SQL Compact version on Windows Mobile 6.5

Mobile Application Pocket Guide v1.1

Intro to Mobile Sync

Windows Mobile platform support

Mobile sample using SQL Compact

SQL Compact Device Deployment

ASP.NET

SQL Server Compact 4.0 under ASP.NET Hosting– common issues

Using Entity Framework with SQL Server Compact 4.0 CTP and ASP.NET – tips & tricks (part one)

Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

Getting started with SQL Server Compact 4.0 (no WebMatrix)

ASP Classic

Access SQL Server Compact 4 with ASP Classic and VbScript

VBA (Office)

Tips and tricks for using SQL Server Compact with VB/VBA/VBScript/ASP

Inserting images from VBA

Import SQL Compact data to Excel

OData (WCF Data Services)

WCF Data Services with Windows Phone - bandwidth requirements measured

Entity Framework with SQL Server Compact 4.0 and ASP.NET – Dynamic Data, OData, deployment (part two)

Walkthrough: Expose SQL Compact data to the world ...

Silverlight

Access a local SQL Compact database from Silverlig...

WPF

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

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

Binding SQL Compact data to WPF in less than 100 l...

LINQ to SQL

Using LINQ to SQL with SQL Server Compact 4.0 (yes, you can!)

How to save and retrieve Images using LINQ to SQL ...

SQL Compact and LINQ to SQL performance advise

LINQ to SQL

 

SQL Server Management Studio

How to clear the list of recently opened SQL Serve...

SQL Server 2008 Management Studio Express

Accessing SQL Compact from SQL Server "Linked Serv...

SQL Compact 3.5 - Manage relationships

PowerShell

Using PowerShell to manage SQL Server Compact database files

F#

HOW TO: Connect to SQL Server Compact from F#

CodeSmith

Getting started with a CodeSmith nettiers data acc...

Merge Replication

Shop Talk” with Eric Sink, Zumero for SQL Server founderShop Talk” with Eric Sink, Zumero for SQL Server founder
An alternative to Merge Replication with SQL Server and SQL Server Compact – Zumero for SQL Server
Merge Replication with SQL Server Compact 3.5 SP2 and SQL Server 2014 and 2012

SQL Server Compact 3.5 SP2 now supports Merge Replication with SQL Server 2012

Snapshot Synchronization with SQL Server Compact 4.0

Walkthrough: Configuring the Merge Replication Age...

Recent Synchronization and Replication advice from...

IIS 7.5 (Windows Server 2008 R2) and SQL Compact i...

Is RDA (Remote Data Access) still relevant

SQL Compact Merge Replication “bible” updated

Merge replication performance tips

Merge Replication and ISA Server

Intro to Mobile Sync

SQL Compact 3.5 client agent logging

Versions...versions

Sync Framework

Sync Framework news roundup

Recent Synchronization and Replication advice from...

2 new Sync Services hotfixes for SQL Compact

Sync SQL Compact with SQL Azure and Oracle

Microsoft Sync Framework 2.0 released to web!

ADO.NET Sync Services for Devices sample

New features in Sync Framework

Recent Sync Framework articles

SQLite

“Database First” with SQLite in a Universal App using the SQLite Toolbox and sqlite-net

Getting started with SQLite in Windows Store / WinRT apps

Exporting SQL Server Compact to SQLite

Other news stories

Using Exceptionless.com for error logging and feature usage tracking with a Visual Studio add-inUsing Exceptionless.com for error logging and feature usage tracking with a Visual Studio add-in

SQL Server Data Tools for Visual Studio – versions, downloads and compatibility

SQL Server - using newsequentialid() as a function, similar to newid()

Primeworks, supplier of tools for SQL Server Compact, is closing, making all products free, open source

Integrating Red Gate SmartAssembly in the SQL Server Compact Toolbox

SQL Server Compact 4.0 SP1 Released To Web

The state and (near) future of SQL Server Compact

SQL Server Compact 4.0 SP1 CTP1 available

SQL Server Compact will be available for developers in upcoming Windows Phone “Mango” update

SQL Server Compact 4.0 released!

SQL Server Compact 4.0 CTP2 released

SQL Server Compact 4.0 news roundup

SQL Compact 4.0 CTP1 released

SQL Server Compact 3.5 SP2 released!

What’s new in SQL Server Compact 3.5 SP2 beta

No SQL Compact on Windows Phone 7?

New KB article about how to remove SQL Compact 3.5...

Interview with SQL Server Compact Program Manager ...

Dynamics Mobile – SQL Compact used in MS Dynamics ...

News from blog land

3rd party tools

SQL Compact 3rd party tools

Books

New book: .NET Compact Framework 3.5 Data Driven Applications

SQL Server Compact Edition books

SQL Compact Merge Replication “bible” updated

Mobile Application Pocket Guide v1.1

Architecture Journal 14