Create a SQL Server Compact Edition Database Using SQL Server 2005 Management Studio

Yesterday I showed you how to create a SQL Server Compact Edition database, using Visual Studio. Today I’ll show you how to create the same database using SQL Server 2005 Management Studio.

When you launch SQL Server Management Studio, you are shown the normal “Connect to Server” dialog. First, you need to change the Server Type to “SQL Server Compact Edition”. Now in the Database File area, click on the drop down and pick “<New Database…>”.

[Picture of SSMS 01]

After picking the new database option, you should see a familiar looking dialog. Enter the name for your database, or browse for it. Like I said yesterday, I always encrypt and use a password, if you want to as well check Encrypt on and enter a password. Remember if you do choose to encrypt, you must enter a password.  

[Picture of SSMS 02]

Like yesterday, if you use a weak password you are warned. Either click no and enter a stronger password or click Yes to continue.

[Picture of SSMS 03]

Now your database has been created, and you are brought back to the original “Connect to Server” dialog you saw. SQL Server Management Studio has helpfully filled in the Database File name for you as well as the password. I clicked “Remember Password” on then clicked the Connect button.

[Picture of SSMS 04]

You are now brought to the standard SQL Server Management Studio interface.

[Picture of SSMS 05]

From here on, you’ll find things work almost identically to Visual Studio. If you right click on the Tables, you’ll see the same menu. If you pick “New Table” you will see the same dialog as yesterday. Rather than repeating myself, I’ll suggest you take a look at yesterday’s blog entry for more details on creating tables and adding rows.

I can hear the questions now: Why would you use SQL Server Management Studio over Visual Studio to create and manage your SSCE database?

It really comes down to a matter of your viewpoint. If you are a long time developer, you will be more comfortable with Visual Studio and thus want to use it to handle your SSCE database. On the other hand, if you are a DBA and have been assigned the task of managing the SSCE databases, you will be more familiar with management studio and thus want to use it as your tool of choice.

So there you go, yet another way to create a SQL Server Compact Edition database. Which tool you use is up to you!

Creating a SQL Server Compact Edition Database Using Visual Studio Server Explorer

In the past I showed how to create a new SQL Server Compact Edition (SSCE) database using code. While it’s great for your app to be able to be able to create its own database(s), it’s not always necessary. For example, if you will be deploying the database with your app, it’s not really necessary to write the code to create it.

Instead, you can create the SSCE database right from within Visual Studio 2005. First, if Server Explorer is not visible select View, Server Explorer from the menu. Now right Click on Data Connections, and pick “Add Connection…”.

[Picture of SSCE and VS 1]

Note do NOT pick the Create New SQL Server Database… option, this works with full blown SQL Server such as SQL Server Express, not SSCE.

On the next screen, make sure the Data source is set to the “Microsoft SQL Server 2005 Compact Edition (.NET Framework Data Provider for SQL Server CE)”, if not click the Change button to launch a dialog where you can select it.

For Data Source make sure My Computer is picked, then enter the directory and path where you want your database stored. Note SSCE databases need to end with a .SDF extension (case is unimportant, .sdf works just as well).

Next is the password. I always create my databases with a password. It makes them more secure, and adding a password will also allow you to encrypt the database to keep it safe from prying eyes. However, you can select not to do so if you want.

Now you’ve got everything filled out, just click the “Create” button.

[Picture of SSCE and VS 2]

A new dialog will appear. Note this is your chance to encrypt the database, as I mentioned I always click this ON. Now click OK.

[Picture of SSCE and VS 3]

Note if you use a very simple password, as I did for this example (I picked arcanecode as the password) you may get an error dialog.

[Picture of SSCE and VS 4]

What it’s simply letting you know is that your password is not very secure. If this were a production system I would absolutely click No, and go enter a much more complex password. I entered a weak password for this demo so I could show you the dialog, so to move things along I’ll click Yes to continue with the password I entered.

You’ll note you’ve been returned to the Add Connection dialog. Let’s see if the create was successful. Click the Test Connection button. If all went well you will see the “Test connection succeeded” message. Click OK to continue.

Returning to the Server Explorer, your new database (which I named ArcaneCode2) should be listed in your Data Connections tree. Click the + button to expand, and you can see the Tables, System Views (not to be confused with a traditional database view), and a Replication folder.

[Picture of SSCE and VS 5]

Note that if you wanted to open an existing SSCE database, you’d simply skip the steps associated with the Create button. Instead you’d simply pick “Add Connection…”, key in (or browse for) the database name, enter the password and click Open.

Let’s create a new table. Right click on the Tables and select Create Table.

[Picture of SSCE and VS 6]

In the dialog that appears, key in some standard info for your table.

[Picture of SSCE and VS 7]

In this example, I named the table CodeCampAttendees, with the column ccaID being the unique ID. One thing I generally do is to use a system generated ID as a behind the scenes unique ID for a row in my databases. In some rare circumstances I’m comfortable using some intelligent key, but in most cases I prefer the computer generate me a key, and I’ll keep up with it behind the scenes (never, and I mean never show these to the user, they’ll abuse the heck out of it).

That said, what are your choices when it comes to unique ID’s? You have two, first is the uniqueidentifier data type, which is a GUID. I like GUIDs, but the one drawback they have is that SSCE won’t automatically generate one for you, you have to take care of that yourself.

The second choice is to use an Int or BigInt. Then in the properties area under the column editor, for the ccaID column change the Identity property to True. This will result in an new ID being generated for you everytime you insert a row. You can also fine tune by setting the value with which the IDs will be incremented on each insert (IdentityIncrement) and what you want the first value to be (IdentitySeed). Often rather than starting with “1”, people will prefer to start with a larger number such as 10000 so the numbers will look more uniform as they increment.

For this example, I’ve picked a BigInt, set the Identity property to true, and Primary Key true for the ccaID column, then entered my remaining columns as nvarchars.

Once you have entered all the fields you want, click OK. Drilling down into our new table, you can see all the columns that were entered. In addition, when I created the table I picked ccaID as my Primary Key, and you can see an Index was automatically created for us.

[Picture of SSCE and VS 8]

Finally we can even add a few rows, simply right click on the table name and pick “Open”. You can view, alter, delete or add new records. Here you can see I added a few new rows, note I did not enter the ccaID. SQL Server Compact Edition took care of that for me.

[Picture of SSCE and VS 9]

You can also do other standard table maintenance via Visual Studio, such as altering the table schema or dropping the table all together. One of the coolest things is the query creator.

Right click on the table name, and pick New Query, then pick the table name(s) from the next dialog. Now you’ll see a dialog that looks disturbingly like the old Access dialog for creating queries. I won’t go into details on how to use it here, but thought you’d like quick screen shot:

[Picture of SSCE and VS 10]

There’s not much more I can say here, this pretty much covers the major features surrounding table creation in SQL Server Compact Edition. Note many of the features will work with any database. Once you make a connection, you can use all the features I’ve shown here to maintain your database whether it’s in SQL Server, SSCE, or even Oracle (assuming you have permissions of course!).

Getting Ready for Code Camp

This week I’ll be working toward the Alabama Code Camp to be held in Mobile this Saturday, April 14th. (http://alabamacodecamp.com) I will be giving a presentation on SQL Server Compact Edition, and invite you all to come.

Since my previous SSCE posts were a bit back and a little scattered, I thought I’d start with a quick recap of my SSCE to date.

My first post described the background of SSCE, and where to get it from. You can find it at:

http://shrinkster.com/nsk

My second post described how to create a new SSCE database using code. You can find this post at:

http://shrinkster.com/nsl

The third post showed how to create a new table in your database, and can be located at:

http://shrinkster.com/nsm

My next post showed you how to insert rows into the newly created table:

http://shrinkster.com/nsn

And finally is my piece on loading a SSCE table from a standard DataTable object:

http://shrinkster.com/nso

That ought to get you up to speed, stay tuned this week as I build up to the weekend presentation.

Arcane Mash-ups

A buddy of mine (thanks Eric) found one of the more unique mash-ups I’ve found on the web:

http://video.google.com/videoplay?docid=-2757699799528285056

For those who don’t feel like following the link right now, it’s a roller coaster but it’s path is dictated by housing prices (adjusted for inflation) by year from 1890 to today.

Another interesting mash-up is Twitter Vision (http://twittervision.com/). It uses Google maps to display a person’s location when they post something to Twitter, along with their twitter message.

Makes for some interesting thought processes. When someone like Google exposes an API to use their maps, it leads to some interesting results. What sorts of things can you do with a few open APIs?

Side note, still working on getting all my stuff re-installed on the laptop after my Vista install. So far I really like the Vista experience. Media player seems especially nice, I’ve been catching up with my DL.TV episodes while copying files back onto the drive, and it hasn’t had any stutters or hic-cups while I’ve been churning my slow disks.

Arcane Vistas

Well, I finally did it. Or more accurately am doing it. I’ve spent the last two evenings wiping the C drive of my HP Laptop and installing Vista Ultimate. So far I’ve gotten Vista installed, along with my freshly arrived copy of Office 2007. I’m working on getting my development environment (VS, SQL Server etc) setup now.

The VPN software my office uses is not Vista compliant yet, so in order to be able to still work remotely I have setup the machine to dual boot, with an 80 gig Vista partition and a 20 gig XP Media Center partition. (The laptop came with Media Center, in case you are wondering.)

I found some really good instructions on getting the dual boot working at:

http://apcmag.com/5485/dualbooting_vista_and_xp

Step by step on how to install Vista, then XP and get it all to work, plus there’s even instructions for when you can finally kill off XP and reclaim the space.

I’ve heard a lot of good things about doing development under Vista, so I’ll let you know as I make progress!

Arcane Tidbits

The blog now has it’s own domain, just tell your friends to go to http://arcanecode.com for this blog.

I’m speaking at the upcoming code camp, Alabama Code Camp (http://alabamacodecamp.com). I’ll be speaking on SQL Server Compact Edition, but even if you are not interested in SSCE come on out there’s plenty of sessions no matter what your interest. April 14th in Mobile, make your plans now!

As I mentioned yesterday, I spoke to a group today at a Microsoft sponsored event. It was a lunch and learn session at one of Birmingham’s exclusive business clubs.

It was an interesting experience, I really enjoyed it. Of course I’m one of those people who really enjoy public speaking, so perhaps that shades my experience somewhat, but I think it’s something everyone should do given the opportunity.

If you are a little uncomfortable speaking, I highly recommend Toastmasters (http://www.toastmasters.org/). They give you training in a supportive environment to get comfortable speaking in front of crowds.

Arcane Solutions: A Data Warehouse

Tomorrow I’m doing a presentation for Microsoft, in conjunction with the vendor that we outsourced the project with, CTS (http://askcts.com). Our solution for the data warehouse was pretty cool, and I thought I’d briefly mention the technologies involved.

A SQL Server Integration Services solution reads data from our Oracle transaction system, and places the data into a SQL Server 2005 warehouse. This SSIS job runs every fifteen minutes, in what we call a “right time” scenario.

Next, the users first go to a SharePoint portal site. We have a master site for the company, the sub sites for each plant, then within those for each department. Our SharePoint site holds some report viewer SharePoint web parts, which hold SQL Server Reporting Services reports that act as our KPI (Key Performance Indicator) reports.

Also on the page is a special SharePoint web part we custom wrote in C# using the .Net 1.1 framework. This part reads a list of valid reports from a SQL Server 2005 table, then launches an ASP.Net 2.0 site.

The ASP.Net page, written in C# on the 2.0 framework, primarily houses a Microsoft report viewer control. It also has a drop down combo which holds a list of saved queries. These allow users to quickly pick combinations of stored report filters to apply to the reports.

We’ve also given users the ability to create ad-hoc reports by linking in the Report Builder control that’s part of the SQL Server 2005 suite. This lets the users create their own reports, without the necessity of providing ODBC connection info as we would with say Access.

I certainly don’t think Microsoft is perfect, as I’ve written before they certainly have room in some areas for improvement. But when it comes to their business applications, they are outstanding. I don’t know of any other company that puts out a suite of tools that integrate so nicely together, and provide the users such a complete, seamless solution.