Category Archives: SQL Server

Create a SQL Server Compact Edition Database with C#

Before I start on the coding route, I found one other component you might want to download for SSCE. It’s not absolutely needed, but makes life in VS much easier. It’s the SSCE Developer SDK. It has the Northwind sample database, CAB files for installing SSCE on mobile devices, and MSI for installing the help files, and more. Grab it at the URL below and install all the goodies.

http://www.microsoft.com/downloads/details.aspx?FamilyId=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en or http://shrinkster.com/lho

OK, let’s have a little fun. First thing I did was create a test app using a Windows Forms application. Next thing you need to do is create a reference to the SSCE. Click on Project, Add Reference. According to all directions I’ve read, all you have to do is be on the .Net tab, scroll down to System.Data.SqlServerCe, click on it and click OK. Found it?

Nah, I couldn’t find it either. So here’s the part the instructions don’t tell you. Go click on the Browse tab. Now navigate to C:\Program Files\Microsoft SQL Server Compact Edition\v3.1. Now pick the System.Dadta.SqlServerCe.dll, click on it, then click on OK to pull in the reference. (That little nugget will save you a lot of hair pulling, and if you did find it then good for you, just click on it and keep going.)

Now let’s write some code. First, go to the head of the class, whoops I mean header of your class and let’s create a using reference.  

using System.Data.SqlServerCe;

using System.IO;

While I was at it I also set a reference to the System.IO namespace since we’ll want to check for the databases existence. Now in a method somewhere, let’s setup a few variables. Then we’ll check to see if the database already exists. Since this is just a sample, I’m going to delete it. This would be a valid action if the database was just a temporary cache for reports. In your situation you might want to return an error, or just exit gracefully without causing any problems.

 

      string connectionString;

      string fileName = “ArcaneCode.sdf”;

      string password = “arcanecode”;

 

      if (File.Exists(fileName))

      {

        File.Delete(fileName);

      }

 

      connectionString = string.Format(

        “DataSource=\”{0}\”; Password='{1}'”, fileName, password);

 

You can see the last thing I did was establish the connection string. Some very important things you need to note. First, the DataSource must be surrounded with double quotes. The password, on the other hand, must be surrounded with single quotes.

In this example I’m just giving the database an SDF name, and letting it drop in the current directory. You could hard code a full path, or put it in a variable.

OK, we’ve got everything setup. Turns out it’s quite easy to create a new database. First, we create a new instance of a SqlCeEngine, and initialize it by passing the connection string into the constructor. Then, it’s as simple as calling the CreateDatabase method of the engine.  

      SqlCeEngine en = new SqlCeEngine(connectionString);

      en.CreateDatabase();

 

And that’s all there is to it. Looking in our bin debug folder we see the new database:

 

[Picture of ArcaneCode.sdf]

 

Of course an empty database doesn’t do us a lot of good, so in the next installment we’ll start creating some tables, and maybe even put a little data in them.

SSIS Package Not Reading Environment Variables

My work today on SQL Server Compact Edition (see yesterday) has gotten interrupted by some issues with a SQL Server Integration Services (SSIS) package. We keep our connection strings inside environment variables, and had this one package that just would not read those environment variables correctly. To further compound our fun, our development platform is using a 32 bit version of SSIS but our test server is running 64 bit SSIS.

We finally corrected the issue by opening the package in BIDS (Business Intelligence Developer Studio, basically Visual Studio with some SQL Server components loaded into it). Once open, we deleted the connections, recreated them, and then redeployed the package.

And ta-da! It suddenly started working correctly. I put this out here for two reasons: first, to pass along our knowledge in case you are having the same issue. Second, if you’ve had this issue I’m curious to know about it, please leave a comment and let me know your experience.

Getting Started with SQL Server Compact Edition

What is SQL Server Compact Edition?

As I blogged about last week (https://arcanecode.wordpress.com/2007/01/15/sql-server-compact-edition-released/), Microsoft has released SQL Server Compact Edition. SSCE is not new, although it carries a new name and new, expanded functionality. It’s roots extend back to the SQL Server for CE and SQL Server Mobile framework.

Microsoft recognized the need for a robust, secure database that can bridge the gap between the mobile world and the server world, and at the same time meet a need for the desktop environment. SSCE is, at its heart, a database, and nothing else.

Unlike its bigger relatives, from SQL Server Express on up, it does not run as a service. Instead, it’s a set of DLLs that you call from within your programming environment. It’s designed to have a very small footprint, the DLLs take less than 2 megabytes of space.

With SSCE you can create a database that resides in a single file, with a default extension of sdf. You can create tables, indexes and that’s about it. No stored procedures, only one user can be in the database at a time.

I know it doesn’t sound like much, but it fits an important need. First off, the database can be password protected and encrypted, to make it secure. Second, it is transportable. The database can run on both mobile devices like smart phones or PDAs, and on the desktop. You can easily move the file around, back it up, even e-mail it. After all it’s a single file, and since SSCE doesn’t run as a service it’s not locked when your app isn’t running.

SSCE also supports replication, you can replicate between it and a full blown SQL Server install. SQL Server Integration Services also works with it, making it easy to move data between your SSCE database and larger systems.

Haven’t I seen this before?

For a little while SQL Server Compact Edition was known as SQL Server Everywhere. Frankly I liked the Everywhere name better, but that’s just my opinion. I mention this because there have been some articles and books (Programming SQL Server 2005 by Andrew Brust and Stephen Forte, for example) that talk about the Everywhere edition.

The important thing to note is SQL Server Everywhere and SQL Server Compact Edition are the same thing.

Places to use SQL Server Compact Edition

So when do you use SSCE? Well, if you want to develop a database app that will run on a mobile device, this is your only choice. (Well, outside of developing a web app, but we’re talking an app that will run totally independently of external influences).

If you are developing a Windows based application, for a single user, and you need a local database just to hold some data, SSCE is a good choice. For example, a salesman could have an application with his companies product table and sales tables loaded. He could take his laptop to the field, place orders, then have the app resync when he returns to the office.

In my own case, I have an app that generates graphs from a specific dataset. This data is gathered from multiple databases. To create one chart takes about 1 to 2 minutes depending on how many users, amount of data, etc. That doesn’t sound like much, but they look at 12 to 18 reports every morning, that’s a lot of time for a Vice President, Plant Manager, and six other top guys to sit around waiting. Instead I’ll be using SSCE to cache the data locally. It takes about 3 minutes to pull down the data, but after that each chart generates in about one third of a second. Quite an improvement!

If you need multiple users, to store your data on the network, or have stored procedures, you will need to move up the food chain to something like SQL Server Express. If you need more info, there is an excellent article at http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx that contrasts the differences between SSCE and SQL Server Express.

What do I need?

OK, you’ve decided to take the plunge and look into SSCE. Well first, you need to get SSCE and install on your development system. I provided a link in last week’s post, but in case you missed it you can download SSCE from http://shrinkster.com/l9f.

Next, you need to install Visual Studio Service Pack 1. VS SP1 will correct add some intellisense and correct some naming issues (for example the reference shows as SQL Server Mobile prior to installing SP1). In addition it’s probably a good idea to install SP1 anyway, to correct various issues. You can get your copy from http://msdn.microsoft.com/vstudio/support/vs2005sp1/default.aspx or http://shrinkster.com/lel.

Next, you will want the SQL Server Compact Edition Books On Line (that’s a mouthful, we’ll call it SSCE BOL from now on). You can get these at http://www.microsoft.com/downloads/details.aspx?FamilyId=E6BC81E8-175B-46EA-86A0-C9DACAA84C85&displaylang=en or http://shrinkster.com/lem. These have essential documentation for understanding SSCE.

Update (April 24, 2007): It turns out there is a fourth component you will want, the SQL Server Compact Edition Tools for Visual Studio 2005, Service Pack 1. See my post on April 24th (http://shrinkster.com/ob4) for details and where to download it from. If you want you can go ahead and finish this post first, as you will install this component as the final step.

How do I learn this stuff?

First off, the SSCE BOL is a good resource, it has lots of links and information on how to use SSCE. I admit though it’s laid out like a help file, for learning it isn’t laid out in a logical chapter by chapter manner.

Microsoft has some “How To” tutorials at http://msdn2.microsoft.com/en-us/sql/bb219480.aspx or http://shrinkster.com/len . They come in both C# and VB.Net flavors, and step you through various topics.

There are two spots on Microsoft that are launching points for SSCE. The main site off the SQL Server area is http://www.microsoft.com/sql/editions/compact/default.mspx (or http://shrinkster.com/leo). The MSDN site is http://msdn2.microsoft.com/en-us/sql/bb204609.aspx (or http://shrinkster.com/lep).

SQL Server Guru Bill Vaughn has written an excellent E-Book on SSCE. Of all the resources I’ve mentioned this is probably the best, although you do have to purchase it (dirt cheap). Bill’s site for his Hitchhiker’s Guides is http://www.hitchhikerguides.net/, the E-Book I’m speaking of is at http://www.hitchhikerguides.net/EBooks/5582.aspx. At 20 bucks it’s well worth the investment. (Standard disclaimer, I don’t make any money off sales of the book, Bill’s site, or anything else associated with the Hitchhiker’s guides. )

Finally, I’ll spend a few days talking about what I’ve learned from all these sources, showing you what I’ve done with this new technology. Hopefully I’ll help you shortcut some of the learning pains I’ve gone through.

 

SQL Server Compact Edition Released

I learned over the weekend Microsoft has now released SQL Server Compact Edition, or SSCE. SSCE is for use both in mobile applications and the desktop. It’s for situations where you need a robust database, but not much else.

Just in time too, I’m looking at a C# Windows app that needs to cache a large amount of data locally, and be able to quickly sort and retrieve parts of the data. An XML file would have been slow and unsecure, and I really did not want to use Access. This is the perfect solution.

Look for some blog entries from me in the next few weeks, I’ll let you know how my experiences have been. Meanwhile you can grab your copy from Microsoft at:

http://www.microsoft.com/downloads/details.aspx?familyid=85E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&mg_id=10096&displaylang=en

or

http://shrinkster.com/l9f