A few months ago I did a presentation to the Birmingham .Net Users Group (BUG.NET). The subject was SQL Server Compact Edition, in it I discuss both the 'traditional' way of accessing a SSCE file as well as using LinqToSQL.

This is a recording of a live presentation, so the audio quality may not be as good as some of the previous presentations I've done, it was my first experiment in recording a session live. Let me know how it works for you, I did make every attempt to clean up where I could and ensure the audience was understandable as well.

The wmv is about 76 meg, you can download the wmv directly or watch streaming below.

In my recent presentation I talked about an important but subtle difference with connection strings when using SQL Server Compact Edition. It was so important I thought I'd make a special blog post out of it.

There are two methods for programmatically accessing data in SQL Server Compact Edition (SSCE). The first method is using the System.Data.SqlServerCe library. When you create an instance of the SqlCeEngine, you need to pass a connection string formatted like so:

DataSource="mydatabasename.sdf"; Password='mypassword'

This method is valid, by the way, for version 3.1 or 3.5 of SSCE. The second method, available with Visual Studio 2008 and the 3.5 version of SSCE is to use LINQ to SQL. When creating the DataContext object, you also need a connection string formatted like so:

Data Source=mydatabasename.sdf; Password='mypassword'

Note very carefully the two differences. First, the name of the sdf file lacks the double quote marks in the LINQ to SQL version. Second, note the Data Source phrase has a space between the words in the LINQ version, where the SqlCeEngine version lacks the space.

It's a small distinction, but it'll drive you nuts if you don't catch it. I drove myself nuts for quite a while because I didn't notice the extra space in Data Source when I began experimenting with LINQ to SQL! Hopefully my pain will save others some hair pulling.

Tonight I will be presenting "Getting Started with SQL Server Compact Edition 3.5" at the Bug.Net users group. If you go to my Arcane Lessons page and scroll down just a little, you will find a section called "Getting Started with SQL Server 2005 Compact Edition". The subjects referenced there are still valid under Visual Studio 2008 / SQL Server Compact Edition 3.5.

However, there are some new features that make it worth our while to give SSCE 3.5 a second look. For example, did you know you can access Compact Edition using LINQ to SQL? My updated presentation and code demo will show you how.

Speaking of which, you can find my PowerPoint slides and Code Demos at the new Microsoft Code Gallery site I've setup:

http://code.msdn.microsoft.com/ssce

Over the coming days I will also be adding blog posts to talk about the new features in version 3.5, and how to access SSCE from LINQ to SQL.

Just wanted to let everyone know I'll be doing a presentation this coming Tuesday night, August the 12th for the Birmingham .Net Users Group (BUG.NET). My topic, as you may have guessed from the title, will be using SQL Server Compact Edition.

While I will be using Visual Studio 2008, I will point out which pieces are 2005 compatible. I will also cover the use of both traditional coding techniques as well as how to use LinqToSQL to talk to the Compact Edition.

The meeting takes place at 6:30 pm at New Horizons Training Center in Homewood.

I also plan a new series of blog posts to start later this week on the subject, and will be creating a new Code Gallery site to hold my examples.

Also, don't forget the regular BSDA meeting this coming Thursday night, the 14th. Also starting at 6:30 pm at New Horizons, Shannon Brooks-Hamilton, a software usability expert, will be there to talk about user interface design. Lots of good thought material on how we can make better UIs for our users.

I'm pleased to announce I'll be speaking at Tallahassee Code Camp III. The event takes place in Tallahassee Florida on Saturday, September 22nd. I'll be speaking on SQL Server Compact Edition.

For more info, please visit their site at:

http://codecamp.tlhdotnet.net/

Hope to see you there!

On Thursday July 12th I'll be speaking at the Birmingham Software Developers Association (BSDA). You can get directions from the club's website, http://www.bsda.info/ .

I'll be speaking on the subject of SQL Server Compact Edition. It's been a while since I blogged about this, so I thought I'd provide a few links for quick reference.

As promised, here is a link to the Power Point presentation (in PDF format) I used during the presentation:

SSCE presentation for BSDA user group

My very first post was back in January 2007:

http://shrinkster.com/nsk

My next series of posts began on April 10th, and described how to create databases using the various tools available to you.

http://shrinkster.com/qtl

The complete C# and VB.Net code samples were posted April 13th, 2007:

http://shrinkster.com/qtm

And finally, the series of posts I mentioned on system Views started with this post on April 16th, 2007:

http://shrinkster.com/qtn

If you want to see all of my SSCE posts, simply click the SQL Server Compact Edition tag over in the categories area, or use this link:

http://arcanecode.wordpress.com/tag/sql-server-compact-edition/

Please note each of these links is a starting point, be sure to read the blog for the next few days after each link in order to get the full story.

And now a question, I'm working up material for a new presentation. Debating between SQL Server 2005 Full Text Searching and SQL Server Integration Services. Any opinions?

Eventually I'll do both, but would like to do the first one based on feedback. Even if you can't attend please post a comment and let me know where your interests lie.

I just wanted to thank everyone who took the effort to come to the presentation I did tonight on SQL Server Compact Edition at the Birmingham Dot Net Users Group ( http://www.bugdotnet.com ). It was a small crowd but very engaged, all in all a very enjoyable evening for everyone.

As promised, here is a link to the Power Point presentation (in PDF format) I used during the presentation:

SSCE presentation for BUG.Net group

The complete C# and VB.Net code samples were posted April 13th, 2007:

http://arcanecode.wordpress.com/2007/04/13/sql-server-compact-edition-with-c-and-vbnet/

And finally, the series of posts I mentioned on system Views started with this post on April 16th, 2007:

http://arcanecode.wordpress.com/2007/04/16/system-views-in-sql-server-compact-edition-tables/

If you want to see all of my SSCE posts, simply click the SQL Server Compact Edition tag over in the categories area, or use this link:

http://arcanecode.wordpress.com/tag/sql-server-compact-edition/
Thanks again to  everyone, I had a great time and hope came away with a better understanding of SQL Server Compact Edition.

The new beta of Orcas (the next version of Visual Studio) is now out. You can download it either as an installer or as a Virtual PC image. I opted to download the VPC image (I would have put it in a VPC anyway, so why not save some work?).

The main info page for Orcas is at http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx or http://shrinkster.com/oqp. From there you can pick the download type you want. As I mentioned before, there's an installer version and the VPC version. If you have a spare machine lying around, you can use the installer, otherwise I highly recommend the Virtual PC version. (I grabbed the Visual Studio Team Suite Only – VPC).

It's very very VERY important you completely read the instructions. Did I mention it was important to read the instructions? Well in case I didn't, be sure to read the instructions.

There's actual two VPC images you have to download. The first is named Orcas, the second though is called BASE (it's in an exe). When you run the Orcas VPC the first time, it will ask you to point to the base VPC. In addition, you will need to know the user id and password to login, both available on the download page and buried in the instructions. (See, I told you it was important to read the instructions!)

Like most people playing with Orcas, the first thing I did was tested what I already knew. In this case, I loaded in some of the SQL Server Compact Edition samples I've published here in the past few weeks. Most notably the code in my post on April 13th (http://shrinkster.com/oqq).

In some ways I wish I had a lot of new technical content to share with you. Harrowing tales of how I was able to fight the bugs and to conquer the evil things lurking in Orcas. But I can't. It just worked. And, I'm happy to say worked without any flaws. I didn't have to install any special add-ins (Orcas comes with SSCE assemblies preinstalled) or make special references.

I'll keep playing with it, but for now I'm quite happy to report that while I don't see any radical changes with SSCE in Orcas, I don't see any issues so far either.

I was relaxing Monday evening, watching some of Channel 9 videos I'd downloaded (yeah, I know, but I really do find it relaxing). In this video on Windows Mobile 6: http://channel9.msdn.com/Showpost.aspx?postid=303900 Rory is interviewing Mel Sampat who showed off some cool stuff with Windows Mobile 6.0.

One of the coolest things is that WM6 includes both SQL Server Compact Edition and .Net Compact Framework 2.0. What this means for you as a developer is that you don't have to worry about deploying all the SSCE plumbing. How sweet will that be!

They also demoed some cool voice capabilities that you have to see. Very very awesome.

Now if they'd just hurry up and release Windows Mobile 6!!

In my post on Getting Started with SQL Server ( http://shrinkster.com/nsk ), I mentioned 3 things you need. SQL Server CE, Visual Studio 2005 SP 1, and the SQL Server CE Books on Line. As it turns out there's an important fourth item.

The missing component is the "Microsoft SQL Server Compact Edition Tools for Visual Studio 2005 Service Pack 1", available at http://www.microsoft.com/downloads/details.aspx?familyid=877C0ADC-0347-4A47-B842-58FB71D159AC&displaylang=en or http://shrinkster.com/oam .

This service pack fixes a few things using SSCE with VS, one of them I consider critical. When you do a Project, References from VS, in the .Net list you will now be able to find a selection for System.Data.SqlServerCe.

Second, all of the Create Database dialogs now correctly read "SQL Server Compact Edition" instead of "SQL Server Mobile". It also updates device CAB files correctly and includes new features such as Click Once support.

This should be installed fourth in your list, so if you've already done the other components, you are good to go for installing this. To recap:

  1. Install SQL Server Compact Edition components. (http://shrinkster.com/l9f).
  2. Install Visual Studio Service Pack 1. (http://shrinkster.com/lel )
  3. Install SQL Server CE Books On-line. ( http://shrinkster.com/lem )
  4. Install SSCE for VS SP1, as I've described here. ( http://shrinkster.com/oam).

A special thanks to Doug Tunure, our regional MS Developer Evangelist who helped me communicate with the SSCE team to figure out what was missing, and thanks to the SSCE team for a great tool.

The last view we'll discuss is the INFORMATION_SCHEMA.PROVIDER_TYPES. Strictly speaking, this does not provide a view into your particular database, but into SQL Server Compact Edition. As such, it probably won't be very useful in day to day queries into the database.

What it may be useful for is in automating database code generation. This table lists all of the valid database types that SQL Server CE supports. Presumably if Microsoft chooses to add new data types to SSCE they will show up here.

Let's take a look at the query that'll return some useful info, and what each field means.

select type_name, data_type, column_size,

       literal_prefix, literal_suffix,

       is_nullable, case_sensitive,

       unsigned_attribute, fixed_prec_scale,

       minimum_scale, maximum_scale, is_fixedlength

from information_schema.provider_types

type_name contains the list of valid datatypes, such as int, real, nvarchar, etc.

data_type contains a numeric representation of the type of data stored in the field. Note this is not unique, for example nchar and nvarchar, and ntext all have the same data_type of 130.

Column_size indicates the max size for a data_type.

Literal_prefix / literal_suffix indicates what you should use to wrap a literal value. This is probably one of the more useful items in this table. Let's say you have a static text value you want to insert into a SQL statement, perhaps a system name such as the word arcanecode.com. Looking up the literal_prefix for a ntext item I see that it is N' (the letter N followed by a single quote). The literal_suffix is just a single quote mark, so to include in a SQL statement I'd need to put N'arcanecode.com' .

Is_nullable is obvious, a 1 indicates the field is allowed to hold null values, a 0 means it can't. As of this writing, all the field types in SSCE are nullable.

Case_sensitive is similar, 1 shows the field is case sensitive, 0 not. Again, as of this writing none of the fields in SSCE are case sensitive.

Unsigned_attribute is null for non-numeric type, but for numbers a 1indicates the data_type is unsigned only (i.e. no negative values allowed). A 0 indicates negative values are valid.

Fixed_prec_scale is another Boolean field. A 1 indicates the precision (the number of positions after the decimal point) is fixed. A 0 indicates you can change the precision.

Minimum_scale, maximum_scale is tied to the fixed_prec_scale. For items like int's where the precision is always the same, this is null. However on numerics the user (i.e. you) can set the scale, and these fields indicate the valid range.

Is_fixedlength is a Boolean that flags whether the data_types length is fixed. For numerics this is going to be 1 (true), and for most text types this is 0 (false).

If nothing else this table makes a great quick reference. Since SSCE only supports a subset of the SQL Server data types, it's handy to have a place to see which types are supported.

In your application, you could use this to build code generators or build SQL statements to create new databases. For example you could use it to look up the prefix / suffix types for a data_type.

And there you go, this completes a week long look at the various views built into SQL Server 2005 Compact Edition, and how you can use them within your applications.

Constraints are actually found in two views, INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE. Let's look at table constraints first.

select constraint_name, table_name, constraint_type

from information_schema.table_constraints;

This returns a simple list of the names of the constraints for each table with a constraint in your system. If the table does not have a constraint, it won't be found here. Finally, the constraint type will either read "PRIMARY KEY" or "FOREIGN KEY".

To gather more info about constraints, we have to shift to the key_column_usage view. This view shows the fields associated with a constraint.

select constraint_name, table_name, column_name, ordinal_position

from information_schema.key_column_usage;

By now most of these fields should be familiar. You'll note though the one drawback, within this view there's no way to tell whether the constraint is a primary key or foreign key. However, a little SQL magic to combine these two views will solve this dilemma.

select kcu.constraint_name, tc.constraint_type, kcu.table_name,

       kcu.column_name, kcu.ordinal_position

from information_schema.key_column_usage kcu,

       information_schema.table_constraints tc

where kcu.constraint_name = tc.constraint_name;

You should know there is another table that lists constraints, the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS. This view only shows referential constraints, not primary keys. In this case we're interested in documenting all constraints, so there's nothing here we cannot gather from the other two tables.

And there you go, a simple way to determine the constraints attached to the tables in your SQL Server 2005 Compact Edition tables.

Knowing what indexes are available can help you speed access to your database. While the indexes view has a lot of fields, there's only a handful that are really useful to us.

select table_name, index_name, primary_key,

       [unique], ordinal_position, column_name

from information_schema.indexes;

Table_name and column_name are obvious, as is the index_name field. The primary_key will be 1 if this is a primary key index. The same logic applies to unique, 1 for unique fields. Also note that since unique is a reserved word, we had to enclose it in brackets in order to use it in our query.

That just leaves the ordinal_position, which has the same use here as it did in the columns view in yesterday's post. It indicates the order the fields occur in the index.

Indexes are pretty simple, but it's handy to be able to determine what you have available.

Today we're going to explore a view that is quite a bit more useful. First though, I spent a little time rewriting the create table scripts so we could fully explore a lot of the features these tables and SSCE has to offer.

Drop the two tables you created yesterday, or just create a new database. Then you can run these statements:

create table employee (

empid uniqueidentifier not null,

last   nvarchar(100),

first nvarchar(100),

birth datetime,

constraint pk_empid primary key (empid)

);

create table parts (

partid bigint IDENTITY(100,1) not null,

partname nvarchar(100) not null,

qtyonhand int default 0,

constraint pk_parts primary key (partid)

);

create table inventory (

partid bigint not null,

binid bigint not null,

location nvarchar(50),

constraint pk_inventory primary key (partid, binid),

constraint fk_part foreign key (partid) references parts(partid)

);

As you can see, the new version of parts uses a bigint for the primary key, and I've set it up as an identity column. The 100, 1 after identity tells SSCE to start the first number at 100, and increment by 1.

For use in a later example I've also redone the inventory table and added a part table. I've made the index for the inventory table two parts, and added a foreign key back to the parts table. Now let's take a look at the Columns view.

Like many of the views, a simple select * is going to return a lot of empty fields. Thus I have created the following statement which returns the most useful fields to us.

select table_name, column_name, ordinal_position,

       column_hasdefault, column_flags, is_nullable, data_type,

       character_maximum_length, numeric_precision, numeric_scale,

       datetime_precision, autoinc_min, autoinc_max, autoinc_next,

       autoinc_seed, autoinc_increment

from information_schema.columns

I won't go over every single field, but let me point out a few of the more useful ones. The table_name, column_name, and data_type fields are pretty obvious and are probably the ones you'll use the most.

Column_hasdefault is a Boolean, 1 indicates there's a default value, 0 indicates there's no default. Character_maximum_length is just as it describes, for character types it indicates the max length, for non characters it will be null.

The autoinc_* fields are only valid for ints or bigints being used as identity columns, such as the partid field in the parts table. Min and max are obvious, describe the bounds of the field. Seed simply shows what the starting value is. This can be handy if you want to know what value to start reading from. Increment shows the value to add when creating the next primary key.

The most handy though is Next. Next will show you what the next primary key will be. This can be useful if the user is creating a new record, and you'd like to go ahead and show them what the new primary key will be without wanting to create the record.

Finally is ordinal_position. This field indicates the order the fields occur in the table. Use this for sorting, if you want to display the fields in the same order as the database: order by ordinal_position.

First off, let me say a few thank you's. Thanks to the Lower Alabama .Net User Group for putting on a great code camp. And thanks to all of you who attended my presentation, I felt honored, especially considering some of the others who were speaking at the same time. Now, on to the views.

While SQL Server Compact Edition does not support the creation of views, it does come with several views built in. For those of you familiar with full blown SQL Server, these will be familiar as they are all part of the INFORMATION_SCHEMA.

There are seven, Columns, Indexes, Key_Column_Usage, Tables, Table_Constraints, Provider_Types, and Referential_Constraints. When SSCE was created, these views were setup to mimic many of the views of "big" SQL Server. But because SSCE only supports a limited set of features, many of the fields wind up being NULL. Like in the INFORMATION_SCHEMA.TABLES view.

For this example, I'm creating a couple of very simple tables. Start by creating a brand new SSCE database. Next, here's the SQL needed to create my example tables, you should be able to use either Visual Studio Database Explorer or SQL Server Management Studio.

create table employee (

empid uniqueidentifier not null,

last   nvarchar(100),

first nvarchar(100),

birth datetime,

constraint pk_empid primary key (empid)

);

go

create table inventory (

partid uniqueidentifier not null,

partname nvarchar(100) not null,

qtyonhand int,

location nvarchar(50),

constraint pk_partid primary key (partid)

)

OK, now that you have a few tables created, let's issue the following command:

select * from information_schema.tables;

What gets brought back is:

[SSCE Table Output 1]

You'll note all the NULLs, as I said most of this was jettisoned in order to put the Compact in Compact Edition. As such, we have a lot of unnecessary fields in the output. Let's refine the query a little:

select table_name, table_type from information_schema.tables;

Produces this output:

[SSCE Table Output 2]

At first glance you may think the TABLE_TYPE of TABLE is redundant. And with the state of SSCE today you'd be right. But for the time being if you want to write queries that will survive into the future, I'd suggest writing your SQL as:

select table_name from information_schema.tables where table_type = 'TABLE';

This will give you this output:

[SSCE Table Output 3]

Now you have a nice, safe query that will return all table names in your database, and you can rest assured that it will survive into the future should Microsoft decide to add other object types to the Tables view.

So what could you do with this? Well I setup a little test harness similar to the one I did in the last series. I created a simple windows form with one button named Tables and a label we'll call lblResults. I also made a few other minor changes from my previous code base. Here's the code, so you can see:

// Class level to hold connection

SqlCeConnection _cn = new SqlCeConnection(ConnectString());

public Form1()

    {

      InitializeComponent();

    }

    #region ConnectString

// This became static so it could be called

// during the constructor, so we could set the

// class level variable.

private static string ConnectString()

    {

string connectionString;

string fileName = "SSCE_View_Test.sdf";

      connectionString = string.Format(

"DataSource=\"{0}\" ", fileName) ;

return connectionString;

    }