Arcane Thoughts: Tips for Daily Blogging

Last Thursday I wrote a post on The Benefits of Blogging, which has turned out to be quite popular. One of the comments mentioned how easily you can become overwhelmed trying to create posts daily. I thought I might share a few of the tips that I’ve picked up to make life easier.

Use tools

I use a variety of tools to create my blog. My first pass is done in Microsoft Word. By using an editor I can keep copies of my blog posts locally, should something happen with my blog. (Backups are always a good thing.)

I then use a posting tool to help with the pushing of my entry to the site. Currently I’m using Microsoft Live Writer Beta (http://get.live.com/betas/writer_betas). I cut and paste what I’ve done in Word over to it, and add in embellishments such as graphics or additional HTML. I can swap back and forth between presentation view and html easily.

To do my graphics I use two tools, WinSnap and Paint.Net. I’ve already blogged about WinSnap (http://shrinkster.com/oif), and you can get Paint.Net at http://www.getpaint.net/index2.html. Both of these are free tools.

Finally, if you plan to paste Visual Studio code samples into your blog, an indispensible tool is the “Copy As Html” add-in. I’ve blogged about this as well at http://shrinkster.com/oig.

Write in advance

Using the tools above also allows me to write my articles in advance. Often I’ll pick one or two evenings a week or a quiet Sunday afternoon, and write several articles at once, then post them as each day arrives.

This allows me to take a little bit of the daily stress off, as I don’t have to worry about a post every day. Second, it allows me to write a series of posts much more coherently. You can break large subjects up over multiple days, which means you avoid posts that are too big and won’t overwhelm either your readers or yourself.

Obviously there are times or blogs who are not suited to this. Daily tech news sites, for example. But for most of us, having a week or so worth of posts ready to go is a reasonable balance to achieve.

Write Now. And Later!

Another benefit to writing in advance is that it gives you time to review what you’ve done. You can write the post, then walk away for a bit, several hours at least if not days. Once you’ve had that break, you can come back and re-read what you’ve created. Make sure it still makes sense.

There have been many times I’ve gone back and revised entries I’d written several days earlier, prior to posting. I think it only makes your writing stronger when you can have a break so you can look at your creation with fresh eyes.

Use a Reviewer

When I’ve written something I’m not sure about, or have an especially long piece I want checked for accuracy and consistency, I will ask one of my co-workers or on-line friends to act as a reviewer or editor. I’ll e-mail them the post, and let them make comments. I’ll then incorporate their feedback into the post.

It’s important though to select someone who you have a good relationship with, someone who will give you honest feedback. One way to get good feedback is to use a technique I picked up in Toastmasters: Ask the person what three areas could use improvement, and what three areas did you get right. People aren’t so reluctant to criticize when they know they will also be able to say something positive.

It’s also important to pick someone who is knowledgeable enough about the subject to be able to provide worthwhile feedback. I recently did a series on SQL Server Compact Edition in C#. A coworker, who is good with C# and SQL Server but new to SQL Server Compact Edition, was a great choice to act as a reviewer. In contrast my lovely wife, who is good with computers but doesn’t know a thing about C# or SQL Server, would not have been able to provide the quality of feedback I needed.

Use Illustrations

While not every post is a good fit for a graphic of some sort, many can benefit by having a simple picture or code snippet included. These are pleasing to the eye of the reader. Be careful not to make them so big they dominate the page though, unless you actually need them to (such as in a ‘step by step’ set of instructions).

Use Categories (Tags)

Most blogging tools I know of allow you to add tags or categories to your posts. Use them! And don’t be afraid to use a lot of them, you never know how your readers will need to find the information you are providing.

Additionally, it makes it easier to help others who are interested in a particular topic. Remember the point I made about mentoring in my benefits article? I’ve been surprised at how many times I’ve said “just got to my blog and look at my posts under the XYZ category.”

Be Flexible

Sometimes you need to be able to change what you had originally planned to post. Perhaps some company makes a new announcement you feel the need to respond to. Or perhaps a comment on your blog inspires thought, such as this post. I originally had another post ready for today, but when I read a comment made last night inspiration struck, and this post came to life.

Set Reasonable Goals

My final piece of advice is to set a reasonable posting goal. For me, for various reasons one post per weekday seemed a reasonable goal. You will notice I don’t usually post on the weekends or holidays, to give myself some time off.

Your life though is not mine. You may find three times a week to be enough, or even just once a week. It’s important though to set a goal and stick with it, make it a commitment, just as if it were a job. Just be careful not to set your goal too high, or else you’ll wind up burning out and growing to hate or resent your own blog. Consistency though is the key, again select a goal and stick with it.

Well there you go, a few of the techniques I use that allow me to keep up the pace of daily blogging. Please feel free to leave a comment below with your own tips and tricks.

Arcane Events: Upcoming Tech Events in the Birmingham AL Area

Today we’re having a big staff meeting at work, and the organizers are graciously allowing me to mention some of the upcoming tech events happening in the Birmingham, Alabama area. I’m also providing this info here, so everyone can benefit.

Tech Birmingham’s (http://www.techbirmingham.com/) Tech Mixer (http://www.techmixer.org) is the huge gathering for anyone in the local tech industry. Vendors, user groups, geeks, and nerds of every type will descend upon the McWane Center in downtown Birmingham on May 1st. Entry and parking is free, go to the third floor entrance on the parking deck. If you pre-register you will be eligible for a special door prize, so jump online now it’s quick and easy.

https://www.itsbits.com/TechMixer/Default.aspx

The Birmingham Software Developers Association (BSDA) meets the second Thursday of every month at 6:30 pm. The BSDA meets in the computer center of Virginia Tech college, which is located on the backside of the Palisades shopping center in Homewood. While most of the club presentations are Microsoft centric, we do stray into other areas from time to time. For more info go to the club website at http://www.bsda.info/

The Birmingham Dot Net User Group (Bug.Net) meets the second Tuesday of every month, also at 6:30. With a heavy emphasis on Microsoft dot Net development, the club meets in the CTS offices in Riverchase. Details and directions can be found at http://www.bugdotnet.com/

So you say night meetings are hard for you to attend? Well for the lunch time crowd there’s the Internet Professional Society of Alabama, or IPSA. Meeting at St Vincent’s Bruno Conference Center, IPSA meets once a month to discuss internet development, and they’re even sponsored by Alabama Power! See the club site at http://ipsaonline.org/ for meeting times and directions.

These are just a few of the groups in the Birmingham Area. There are user groups for Linux, Oracle, DB2, Perl, Java, Ruby, and much, much more. See a complete list at http://shrinkster.com/oe1.

Whatever your interest is, there’s a tech group for you. Just do a google on “user group” and the name of your town and I bet you’ll find a match.

Arcane Thoughts: Benefits of Blogging

This week marks six months of serious blogging for me. Nearly every weekday I’ve tried to provide some new piece of information. A couple of important milestones have been achieved, as well. Last week this blog rolled over 50,000 hits, and also got its first day with over 1000 hits in a single day.

To celebrate I decided to give the blog a little face lift, and picked a new look. I thought all the blue looked somewhat Microsoftish. I also liked the 3 column look.

Over the past months I’ve been surprised, pleasantly, by some unexpected benefits to keeping up this blog. I thought I’d share some of them with you, should you decide to take the plunge.

Meet and Greet

Through the blog I’ve met some interesting new people, and it’s made a good tool for introducing myself to others.

Self Documenting

I’m sure you’ve heard the notion that well written source code is self documenting. Well this blog has let me document a lot of the things I’ve done in the past, so I can go back and reference it later. Not unlike a personal wiki.

Self Promotion

Like most folks I have an exaggerated sense of self importance. Blogging just reinforces my narcissistic streak and inflates my ego even further. Seriously, if you have an interest in writing, speaking, etc., a blog serves as an important tool in promoting your skills and desires to others.

Mentoring

I guess the biggest surprise has been the ability to use the blog as a mentoring tool. When someone asks a question, it’s been great to be able to say “oh yeah, I wrote a post about that on such and such date, go take a look”. A real time saver too, and it means I don’t overlook telling someone a critical piece of information.

Education

OK, I admit it, some days I’m just blocked. Not a clue what I want to write about. On those days I pull a book off the shelf, open to a random page, and read something. Try some code, and next thing you know I have a post, and I’ve managed to learn something new in the process.

Giving Back

Finally, but I think most importantly, blogging gives me a chance to give back. I’ve learned so much by reading the contributions of others, I feel an obligation to “pay it forward” (so to speak).

Well, what can I say? It’s been a lot of work, but a lot of fun as well, and quite an education to boot.. Thanks for the nice comments and the visits, I look forward to see what the future has in store.

Ubuntu 7.04 and Virtual PC 2007 – Mouse Issue Workaround (sort of)

Update: October 18, 2007 – Ubuntu 7.10 was released, the install for it is a bit more straight forward. If you haven’t installed 7.04 yet, I’d suggest giving 7.10 a try. Complete instructions here:
http://arcanecode.wordpress.com/2007/10/18/installing-ubuntu-710-under-virtual-pc-2007/


Update: A reader named John posted a link to a fix, see my post on May 17 (http://shrinkster.com/p2w) for full details. But keep reading this post, as you’ll need the info here to implement the fix. OK, thanks to a suggestion I saw on the Ubuntu forums, I found a workaround for the no mouse issue, of sorts. This won’t give you the mouse back, but it will let you use Ubuntu 7.04 using keyboard control.

First, let me take a second to explain what the issue is. There was a bug in the kernel code that affected many different distros of Linux. Apparently the kernel was not finding PS/2 style mice. Some work has been done and now most PS/2 style mice are now being found.

Except, sadly for the ones being emulated. Both VMWare and VirtualPC emulate a PS/2 style mouse, and are not getting found by the kernel. Remember, it doesn’t matter what type of mouse you have hooked up to the host box (I have two mice, a Logitech MediaPlay and Microsoft Travel mouse, both USB). It only matters what the virtual machine is telling the guest OS (Ubuntu), which is PS/2 style mouse.

OK, that explained let’s play with Ubuntu some. Fire up Virtual PC, then use the CD menu to either capture an ISO image or capture the CD Drive you have the Ubuntu Desktop disk in.

When the menu appears, select “Start Ubuntu in Safe Graphics Mode” by hitting the down arrow once and pressing Enter. If you fail to do this, you’ll get garbled graphics.

Once Ubuntu gets fully loaded, press the left ALT key plus F1 (Left ALT+F1, remember VPC takes over the right ALT). This should highlight the Applications menu. Press the right arrow twice to System, then down once to Preferences. Now press the right key once to get to Accessibility, then right again. Finally go down to Keyboard Accessibility and press ENTER.

[Picture of Ubuntu 7 menus]

When the Keyboard Accessibility Preferences window appears, you should already be on the “Enable keyboard accessibility features”. (You can tell you’re on it because of the little ‘dancing ants’ rectangle around it.) Just press the SPACE BAR to check this on, as you see below.

[Picture of Keyboard Accessibility window]

Now press the TAB key to get on the Basic tab. Press the RIGHT ARROW key twice to get the Mouse Keys highlighted.

[Picture of Keyboard Accessibility window]

Press TAB again to get to the “Enable Mouse Keys” and press the SPACE BAR to check it on. Now tab on down to the Close button and press ENTER.

What you just did was turn the numeric keypad into your mouse. When you press left (number 4 ), the mouse moves left, press the up arrow (number 8 ) mouse goes up, and so on. The angles work, pressing 7 (the home key on my keypad) moves up and to the left, for exampe. Finally, the number 5 key works as the mouse click.

One thing to be aware of, these only work with the numeric keys. It will not work with the standard arrow keys. Laptop owners with compact keyboards are in for a painful experience, you’ll have to hit NUMLOCK to activate the mouse, then turn Numlock off to be able to type letters again.

Well there you go, a way you can use Ubuntu 7.04 in Virtual PC 2007. Not the greatest solution, but might work for you until the kernel issue gets fixed.

I’d like to give credit where it’s due, in reading through the Ubuntu forums I saw someone suggest using keyboard accessibility to work around the no mouse issue. After playing with it some, I decided it was worth documenting and hence this post. I’d like to give credit but haven’t found the original post since then, so if someone happens to notice it in the forums please let me know so I can give credit for the idea to the original poster.

SQL Server 2005 Compact Edition – Important Component

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.

Ubuntu 7.04 and Virtual PC 2007 – No Mouse Issue

Update: October 18, 2007 – The new version of Ubuntu, 7.10 is now out. If you haven’t yet installed Ubuntu, I’d suggest using 7.10. I have step by step instructions at http://arcanecode.wordpress.com/2007/10/18/installing-ubuntu-710-under-virtual-pc-2007/


Last week the folks at Ubuntu released Fiesty Fawn, better known as version 7.04 of the Ubuntu OS. I had planned a new version of the “step by step” to detail how to install under VirtualPC 2007.

Sadly there seems to be a severe error that prevents 7.04 from running in Virtual PC. This error lies somewhere deep in the kernel, and affects the mouse. As you have discovered, Ubuntu won’t recognize the mouse when running inside VPC.

This was actually a much more serious error at first, Ubuntu and several other Linux distros had errors recognizing most PS/2 style mice, as you can see from this bug: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=223606

They have opened a new bug specifically for Virtual PC, which you can monitor here: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=234348. (Credit due alert: Thanks to Michael Wexler for first pointing out these links!).

I’ve spent quite a while trying various combinations, but thus far have found no easy solution or workaround to the problem. So for now, my advice is simple.

If you have an existing Ubuntu install under VPC, I recommend you NOT upgrade. You will lose your mouse for sure, and probably keyboard as well. If you want to experiment, start with a fresh virtual machine and go from there.

In the meantime, keep an eye on the second bug I listed. I’m hopeful there will be an update in the near future. At that point I’ll work up some step by step instructions, which should be pretty similar to the 6.06 instructions.

Update: (April 25th, 2007) – I found a work around (of sorts) that will let you use your numeric keypad as a mouse. Not the most pleasant experience, but it does work. See my post on the 25th ( http://shrinkster.com/ocx ) for full details.

System Views in SQL Server Compact Edition: Provider Types

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.

System Views in SQL Server Compact Edition: Constraints

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.

System Views in SQL Server Compact Edition: Indexes

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.

System Views in SQL Server Compact Edition: Columns

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.

System Views in SQL Server Compact Edition: Tables

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;

    }

    #endregion

 

    #region OpenConnection

    private void OpenConnection()

    {

      if (_cn.State==ConnectionState.Closed)

      {

        _cn.Open();

      }

 

    }

    #endregion

 

    #region btnTables_Click

    private void btnTables_Click(object sender, EventArgs e)

    {

      // In case it’s not already open

      // Calling it too much won’t hurt,

      // as it checks first before trying

      OpenConnection();

 

      SqlCeCommand cmd;

      string sql = “select table_name “

      + “from information_schema.tables “

      + “where table_type = ‘TABLE'”;

 

      try

      {

        cmd = new SqlCeCommand(sql, _cn);

        cmd.CommandType = CommandType.Text;

        SqlCeResultSet rs =

          cmd.ExecuteResultSet(ResultSetOptions.Scrollable);

 

        if (rs.HasRows)

        {

          int ordTable = rs.GetOrdinal(“table_name”);

          StringBuilder output = new StringBuilder();

          rs.ReadFirst();

          output.AppendLine(rs.GetString(ordTable));

          while (rs.Read())

          {

            output.AppendLine(rs.GetString(ordTable));

          }

          lblResults.Text = output.ToString();

        }

        else

          lblResults.Text = “No tables found.”;

      }

      catch (SqlCeException sqlexception)

      {

        MessageBox.Show(sqlexception.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      catch (Exception ex)

      {

        MessageBox.Show(ex.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

 

    }

    #endregion

  }

 

Here’s the output:

[SSCE Table Output 4]

 

You could use this to build your own SQL Queries, reports, or to do code generation.

The rest of the examples in this series will fall into the same test harness, so be sure to get a good look and understanding. The rest of the week we’ll spend exploring some of the more useful SSCE Views.

Alabama Code Camp Slides

Here’s a copy of the Slides I’ll be using (or used depending on when you read this) at Alabama Code Camp 4 in Mobile AL. They are in PDF format.

Getting Started with SQL Server Compact Edition – Code Camp Slides

Thanks to all who came to my presentation!

SQL Server Compact Edition with C# and VB.Net

As a wrap up before this weekend’s code camp (http://alabamacodecamp.com), I wanted to upload the entire project so you could have it in one spot. In addition, I’ve also created a VB.Net version. I won’t go back over and comment on everything, you can look over the posts from my last few days for detailed explanations this is simply so you can have the code all in one spot.

I did the project as a simple windows project, here is an example of what my final form looked like:

[SSCE Windows Form Sample]

The C# version of the form looked identical except in the title bar, which reads SSCE C# Version.

Here is the complete C# version of the code behind the form. Note there is one difference from the code I put in my previous blog posts, in the LoadARow method I had the parameters in the order of last name, then first name, but in the tnLoadTable_Click when I called LoadARow I had put the cool people’s names in the order of first name, last name. I fixed that in the version below.

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlServerCe;

using System.IO;

 

namespace SSCE1

{

public partial class Form1 : Form

{

#region Form1

public Form1()

{

InitializeComponent();

}

#endregion

 

#region btnCreateDatabase_Click

private void btnCreateDatabase_Click(object sender, EventArgs e)

{

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

 

SqlCeEngine en = new SqlCeEngine(connectionString);

en.CreateDatabase();

 

lblResults.Text = “Database Created.”;

 

}

#endregion

 

#region btnCreateTable_Click

private void btnCreateTable_Click(object sender, EventArgs e)

{

SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

if (cn.State==ConnectionState.Closed)

{

cn.Open();

}

 

SqlCeCommand cmd;

 

string sql = “create table CoolPeople (“

+ “LastName nvarchar (40) not null, “

+ “FirstName nvarchar (40), “

+ “URL nvarchar (256) )”;

 

cmd = new SqlCeCommand(sql, cn);

 

try

{

cmd.ExecuteNonQuery();

lblResults.Text = “Table Created.”;

}

catch (SqlCeException sqlexception)

{

MessageBox.Show(sqlexception.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

cn.Close();

}

 

}

#endregion

 

#region ConnectString

private string ConnectString()

{

string connectionString;

string fileName = “ArcaneCode.sdf”;

string password = “arcanecode”;

 

connectionString = string.Format(

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

 

return connectionString;

}

#endregion

 

#region btnLoadTable_Click

private void btnLoadTable_Click(object sender, EventArgs e)

{

try

{

LoadARow(“Carl”, “Franklin”, @”http:\\www.dnrtv.com”);

LoadARow(“Richard”, “Campbell”, @”http:\\www.dotnetrocks.com”);

LoadARow(“Leo”, “Laporte”, @”http:\\www.twit.tv”);

LoadARow(“Steve”, “Gibson”, @”http:\\www.grc.com”);

LoadARow(“Arcane”, “Code”, @”http:\\arcanecode.wordpress.com”);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

}

#endregion

 

#region LoadARow

private void LoadARow(string first, string last, string url)

{

SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

if (cn.State == ConnectionState.Closed)

{

cn.Open();

}

 

SqlCeCommand cmd;

 

string sql = “insert into CoolPeople “

+ “(LastName, FirstName, URL) “

+ “values (@lastname, @firstname, @url)”;

 

try

{

cmd = new SqlCeCommand(sql, cn);

cmd.Parameters.AddWithValue(“@lastname”, last);

cmd.Parameters.AddWithValue(“@firstname”, first);

cmd.Parameters.AddWithValue(“@url”, url);

cmd.ExecuteNonQuery();

lblResults.Text = “Row Added.”;

}

catch (SqlCeException sqlexception)

{

MessageBox.Show(sqlexception.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”, MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

cn.Close();

}

 

}

#endregion

 

#region cmdLoadDataGrid_Click

private void cmdLoadDataGrid_Click(object sender, EventArgs e)

{

SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

if (cn.State==ConnectionState.Closed)

{

cn.Open();

}

 

try

{

// Set the command to use the table, not a query

SqlCeCommand cmd = new SqlCeCommand(“CoolPeople”, cn);

cmd.CommandType = CommandType.TableDirect;

 

// Get the table

SqlCeResultSet rs = cmd.ExecuteResultSet(

ResultSetOptions.Scrollable);

 

// load the result set into the datasource

dgvCoolPeople.DataSource = rs;

}

catch (SqlCeException sqlexception)

{

MessageBox.Show(sqlexception.Message, “Oh Crap.”,

MessageBoxButtons.OK, MessageBoxIcon.Error);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”,

MessageBoxButtons.OK, MessageBoxIcon.Error);

}

 

// Note, do not close the connection,

// if you do the grid won’t be able to display.

// For production code you probably want to make

// your result set (rs) a class level variable

 

}

#endregion

 

#region btnReadRecords_Click

private void btnReadRecords_Click(object sender, EventArgs e)

{

SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

if (cn.State == ConnectionState.Closed)

{

cn.Open();

}

 

// Build the sql query. If this was real life,

// I’d use a parameter for the where bit

// to avoid SQL Injection attacks.

string sql = “select LastName, FirstName from CoolPeople “;

if (txtName.Text.Length > 0)

{

sql += “where LastName like ‘” + txtName.Text + “%’ “;

}

 

try

{

SqlCeCommand cmd = new SqlCeCommand(sql, cn);

cmd.CommandType = CommandType.Text;

 

// if you don’t set the result set to

// scrollable HasRows does not work

SqlCeResultSet rs = cmd.ExecuteResultSet(

ResultSetOptions.Scrollable);

 

// If you need to be able to update the result set, instead use:

// SqlCeResultSet rs = cmd.ExecuteResultSet(

// ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

 

if (rs.HasRows)

{

// Use the get ordinal function so you don’t

// have to worry about remembering what

// order your SQL put the field names in.

int ordLastName = rs.GetOrdinal(“LastName”);

int ordFirstname = rs.GetOrdinal(“FirstName”);

 

// Hold the output

StringBuilder output = new StringBuilder();

 

// Read the first record and get it’s data

rs.ReadFirst();

output.AppendLine(rs.GetString(ordFirstname)

+ ” “ + rs.GetString(ordLastName));

 

// Now read thru the rest of the records.

// When there’s no more data, .Read returns false.

while (rs.Read())

{

output.AppendLine(rs.GetString(ordFirstname)

+ ” “ + rs.GetString(ordLastName));

}

 

// Set the output in the label

lblResults.Text = output.ToString();

}

else

{

lblResults.Text = “No Rows Found.”;

}

 

}

catch (SqlCeException sqlexception)

{

MessageBox.Show(sqlexception.Message, “Oh Crap.”,

MessageBoxButtons.OK, MessageBoxIcon.Error);

}

catch (Exception ex)

{

MessageBox.Show(ex.Message, “Oh Crap.”,

MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

// Don’t need it anymore so we’ll be good and close it.

// in a ‘real life’ situation

// cn would likely be class level

cn.Close();

}

 

}

#endregion

 

}

}

 

Here is the VB.Net version of the code. I tried to make all of the method names, variable names and comments match the C# version as much as possible.

 

Imports System

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Data

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data.SqlServerCe

Imports System.IO

 

Public Class Form1

 

#Region “btnCreateDatabase_Click”

  ‘ Create an empty SSCE Database with a password.

  ‘ Note that when creating a db with code, adding a

  ‘ password automatically encrypts the database

  Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, _

  ByVal e As System.EventArgs) Handles btnCreateDatabase.Click

 

    Dim connectString As String = “”

    Dim fileName As String = “ArcaneCode.sdf”

    Dim password As String = “arcanecode”

 

    If File.Exists(fileName) Then

      File.Delete(fileName)

    End If

 

    connectString = String.Format( _

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

 

    Dim eng As SqlCeEngine = _

    New SqlCeEngine(connectString)

    eng.CreateDatabase()

 

    lblResults.Text = “Database Created”

 

  End Sub

#End Region

 

#Region “btnCreateTable_Click”

  ‘ Issue a SQL command to create a table

  ‘ Note this only creates the table, it

  ‘ does not put any rows in it.

  Private Sub btnCreateTable_Click(ByVal sender As System.Object, _

  ByVal e As System.EventArgs) Handles btnCreateTable.Click

 

    Dim cn As New SqlCeConnection(ConnectString())

 

    If cn.State = ConnectionState.Closed Then

      cn.Open()

    End If

 

    Dim cmd As SqlCeCommand

 

    Dim sql As String = “create table CoolPeople (“ _

        + “LastName nvarchar (40) not null, “ _

        + “FirstName nvarchar (40), “ _

        + “URL nvarchar (256) )”

 

    cmd = New SqlCeCommand(sql, cn)

 

    Try

      cmd.ExecuteNonQuery()

      lblResults.Text = “Table created.”

    Catch sqlexception As SqlCeException

      MessageBox.Show(sqlexception.Message, “Oh Crap.” _

      , MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.” _

      , MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally

      cn.Close()

    End Try

 

  End Sub

#End Region

 

#Region “btnLoadTable_Click”

  ‘ This routine calls a subroutine that

  ‘ does the real work of inserting rows

  ‘ into the database.

  Private Sub btnLoadTable_Click(ByVal sender As System.Object, _

  ByVal e As System.EventArgs) Handles btnLoadTable.Click

 

    Try

      LoadARow(“Scott”, “Hanselman”, “http:\\www.hanselminutes.com”)

      LoadARow(“Wally”, “McClure”, “http:\\aspnetpodcast.com/CS11/Default.aspx”)

      LoadARow(“John”, “Dvorak”, “http:\\www.crankygeeks.com”)

      LoadARow(“Arcane”, “Code”, “http:\\arcanecode.wordpress.com”)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Try

 

  End Sub

#End Region

 

#Region “ConnectString”

  ‘ A central place to serve up the connection string

  Private Function ConnectString() As String

 

    Dim connectionString As String

    Dim fileName As String = “ArcaneCode.sdf”

    Dim password As String = “arcanecode”

 

    connectionString = String.Format( _

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

 

    Return connectionString

 

  End Function

#End Region

 

#Region “LoadARow”

  ‘ Generates the SQL and issues the command to

  ‘ insert a single row into the database

  Private Sub LoadARow(ByVal first As String, _

  ByVal last As String, ByVal url As String)

 

    Dim cn As New SqlCeConnection(ConnectString())

 

    If cn.State = ConnectionState.Closed Then

      cn.Open()

    End If

 

    Dim cmd As SqlCeCommand

 

    Dim sql As String = “insert into CoolPeople “ _

        + “(LastName, FirstName, URL) “ _

        + “values (@lastname, @firstname, @url)”

 

    Try

      cmd = New SqlCeCommand(sql, cn)

      cmd.Parameters.AddWithValue(“@lastname”, last)

      cmd.Parameters.AddWithValue(“@firstname”, first)

      cmd.Parameters.AddWithValue(“@url”, url)

      cmd.ExecuteNonQuery()

      lblResults.Text = “Row Added.”

    Catch sqlexception As SqlCeException

      MessageBox.Show(sqlexception.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally

      cn.Close()

    End Try

 

  End Sub

#End Region

 

#Region “btnLoadGrid_Click”

  Private Sub btnLoadGrid_Click(ByVal sender As System.Object _

  , ByVal e As System.EventArgs) Handles btnLoadGrid.Click

 

    Dim cn As New SqlCeConnection(ConnectString())

 

    If cn.State = ConnectionState.Closed Then

      cn.Open()

    End If

 

    Try

      ‘ Set the command to use the table, not a query

      Dim cmd As SqlCeCommand = New SqlCeCommand(“CoolPeople”, cn)

      cmd.CommandType = CommandType.TableDirect

 

      ‘ Get the Table

      Dim rs As SqlCeResultSet = cmd.ExecuteResultSet( _

        ResultSetOptions.Scrollable)

 

      ‘ Load the result set into the database

      dgvCoolPeople.DataSource = rs

 

    Catch sqlexception As SqlCeException

      MessageBox.Show(sqlexception.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Try

    ‘ Note, do not close the connection,

    ‘ if you do the grid won’t be able to display.

    ‘ For production code you probably want to make

    ‘ your result set (rs) a class level variable

 

  End Sub

#End Region

 

#Region “btnReadRecords_Click”

  Private Sub btnReadRecords_Click(ByVal sender As System.Object _

  , ByVal e As System.EventArgs) Handles btnReadRecords.Click

 

    Dim cn As New SqlCeConnection(ConnectString())

 

    If cn.State = ConnectionState.Closed Then

      cn.Open()

    End If

 

    ‘ Build the sql query. If this was real life,

    ‘ I’d use a parameter for the where bit

    ‘ to avoid SQL Injection attacks.

    Dim sql As String = “select LastName, FirstName from CoolPeople “

    If txtName.Text.Length > 0 Then

      sql += “where LastName like ‘” + txtName.Text + “%’ “

    End If

 

    Try

 

      Dim cmd As SqlCeCommand = New SqlCeCommand(sql, cn)

      cmd.CommandType = CommandType.Text

 

      ‘ if you don’t set the result set to

      ‘ scrollable HasRows does not work     

      Dim rs As SqlCeResultSet = cmd.ExecuteResultSet( _

        ResultSetOptions.Scrollable)

 

      If rs.HasRows Then

 

        ‘ Use the get ordinal function so you don’t

        ‘ have to worry about remembering what

        ‘ order your SQL put the field names in.

        Dim ordLastName As Integer = rs.GetOrdinal(“LastName”)

        Dim ordFirstName As Integer = rs.GetOrdinal(“FirstName”)

 

        ‘ Hold the output

        Dim output As StringBuilder = New StringBuilder()

 

        ‘ Read the first record and get it’s data

        rs.ReadFirst()

        output.AppendLine(rs.GetString(ordFirstName) _

            + ” “ + rs.GetString(ordLastName))

 

        ‘ Now read thru the rest of the records.

        ‘ When there’s no more data, .Read returns false.

        Do While rs.Read()

          output.AppendLine(rs.GetString(ordFirstName) _

          + ” “ + rs.GetString(ordLastName))

        Loop

 

        ‘ Set the output in the label

        lblResults.Text = output.ToString()

      Else

        lblResults.Text = “No Rows Found.”

      End If

 

    Catch sqlexception As SqlCeException

      MessageBox.Show(sqlexception.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception

      MessageBox.Show(ex.Message, “Oh Crap.”, _

        MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally

      ‘ Don’t need it anymore so we’ll be good and close it.

      ‘ in a ‘real life’ situation

      ‘ cn would likely be class level

      cn.Close()

    End Try

 

  End Sub

#End Region

End Class

There you go, the complete sample project for working with SQL Server Compact Edition. If you found this useful, please post a comment and let us know what sorts of applications you build using SSCE.

Hope to see you at code camp!

Reading From A SQL Server Compact Edition Database With C#

In our discussions so far, I’ve shown how to create SSCE databases and load them with data. By now I’m sure you are wondering how to pull that data back out. Today I will show you two methods, first a way to bind the data to a control, then how to read through a tables rows programmatically.

First, create a form and put a button on, call it btnLoadGrid. Next add a data grid viewer control, I named mine dgvCoolPeople after the table we’ll be reading.

In the click event for the button, here’s the code you’ll need:  

private void cmdLoadDataGrid_Click(object sender, EventArgs e)

    {

      SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

      if (cn.State==ConnectionState.Closed)

      {

        cn.Open();

      }

 

      try

      {

        // Set the command to use the table, not a query

        SqlCeCommand cmd = new SqlCeCommand(“CoolPeople”, cn);

        cmd.CommandType = CommandType.TableDirect;

 

        // Get the table

        SqlCeResultSet rs = cmd.ExecuteResultSet(

          ResultSetOptions.Scrollable);

 

        // load the result set into the datasource

        dgvCoolPeople.DataSource = rs;

      }

      catch (SqlCeException sqlexception)

      {

        MessageBox.Show(sqlexception.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      catch (Exception ex)

      {

        MessageBox.Show(ex.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

 

      // Note, do not close the connection,

      // if you do the grid won’t be able to display.

      // For production code you probably want to make

      // your result set (rs) a class level variable

 

    }

 

First we open the database, as you have seen before. Next we set the command to a new SqlCeCommand, and pass in the name of the table, CoolPeople. Then we tell the command it’s type is TableDirect. Using this method we can directly access the table, which is very fast if we are doing a quick read through all rows.

Next we execute the command and return a SqlCeResultSet, which is then loaded into the data source for the dgvCoolPeople grid viewer control.

The one important thing to note is NOT to close the connection, otherwise it will also close your grid. Normally I would keep my connection at the class level instead of in a method.

And that’s all there is to it, I should mention if you want your grid updateable make sure to use ResultSetOptions.Scrollable|ResultSetOptions.Updatable in the ExecuteResultSet method.

Now that you’ve seen how to bind your control, let’s look at what it takes to step through the rows programmatically. Go back to your form and add another button, call it btnReadRecords. Also add a textbox named txtName. Finally if you are using your code from before, you already have a label named lblResult, if not go ahead and add it as well.  

    private void btnReadRecords_Click(object sender, EventArgs e)

    {

      SqlCeConnection cn = new SqlCeConnection(ConnectString());

 

      if (cn.State == ConnectionState.Closed)

      {

        cn.Open();

      }

 

      // Build the sql query. If this was real life,

      // I’d use a parameter for the where bit

      // to avoid SQL Injection attacks.

      string sql = “select LastName, FirstName from CoolPeople “;

      if (txtName.Text.Length > 0)

      {

        sql += “where LastName like ‘” + txtName.Text + “%’ “;

      }

 

      try

      {

        SqlCeCommand cmd = new SqlCeCommand(sql, cn);

        cmd.CommandType = CommandType.Text;

 

        // if you don’t set the result set to

        // scrollable HasRows does not work

        SqlCeResultSet rs = cmd.ExecuteResultSet(

          ResultSetOptions.Scrollable);

 

        // If you need to be able to update the result set, instead use:

        // SqlCeResultSet rs = cmd.ExecuteResultSet(

        //  ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

 

        if (rs.HasRows)

        {

          // Use the get ordinal function so you don’t

          // have to worry about remembering what

          // order your SQL put the field names in.

          int ordLastName = rs.GetOrdinal(“LastName”);

          int ordFirstname = rs.GetOrdinal(“FirstName”);

 

          // Hold the output

          StringBuilder output = new StringBuilder();

 

          // Read the first record and get it’s data

          rs.ReadFirst();

          output.AppendLine(rs.GetString(ordFirstname)

            + ” “ + rs.GetString(ordLastName));

 

          // Now read thru the rest of the records.

          // When there’s no more data, .Read returns false.

          while (rs.Read())

          {

            output.AppendLine(rs.GetString(ordFirstname)

              + ” “ + rs.GetString(ordLastName));

          }

 

          // Set the output in the label

          lblResults.Text = output.ToString();

        }

        else

        {

          lblResults.Text = “No Rows Found.”;

        }

 

      }

      catch (SqlCeException sqlexception)

      {

        MessageBox.Show(sqlexception.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      catch (Exception ex)

      {

        MessageBox.Show(ex.Message, “Oh Crap.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      finally

      {

        // Don’t need it anymore so we’ll be good and close it.

        // in a ‘real life’ situation

        // cn would likely be class level

        cn.Close();

      }

 

    }

 

Again we open the connection, then setup our SQL statement. This time I’ve constructed a simple select. If the user enters a letter for a name, I’ve added code for an optional where clause to limit the number of rows returned.

Like with the grid, we need to create a command that we can execute. This time we’ll pass in the SQL statement, and indicate that it is a SQL Statement by setting the command type to text.

Let me take a short side trip, if you typed in the code, when you hit the period after “CommandType”, you should have noticed 3 options.. The first two we have discussed, TableDirect and Text. You’ll also notice there’s a choice for “StoredProcedure”.

If you read the previous articles, you’ll probably be scratching your head as I’ve already said SSCE does not support stored procedures. So why is this option in the list?

Ya got me. My guess is they are sharing the intellisense with another library, and didn’t or couldn’t remove it. Either way, you should ignore it. If you try to use it all you’ll do is generate a run time error.

Back to the code, you see the next thing that is done is a check for HasRows. I need to emphasize something very important: HasRows only works when the Scrollable option is set in the ExecuteResultSet method! I can’t tell you why, I can just tell you to make sure to use a scrollable option or else you’ll have no end of headaches.

So if we have rows, we obviously want to process them. To retrieve column data from a SSCE row, the SqlCeResultSet object has a variety of GetType methods, where Type is such things as String or Int. In order to make it work, you pass the GetType methods what they call an ordinal value, which is nothing more than an integer that indicates the column number you want to retrieve.

I, for one don’t want to have to keep up with which column is which number, further I want the flexibility to change my column order or add new columns without worrying about a lot of code refactoring. That’s where the GetOrdinal method comes into play.

Simply call GetOrdinal and pass in a string with the name of the column, and SSCE will tell you what column number it’s in. Because I use these several times I took these and stored them in int variables. Now I’m free to go change my SQL all I want and don’t have to worry about breaking the rest of my code. It’s a technique I highly advise you to follow.

OK, so we know which column goes where, from here it’s pretty simple. Use the ReadFirst method to move to the first row, then let’s grab it’s data using the GetString methods.

Next we enter a while loop, the Read method will move us to the next record, and return false when there are no more records (thus exiting the loop).

And finally we copy the output we’ve been building into the label control. In this case I have no more need for the connection, so unlike in the grid example I can go ahead and close this connection variable.

These two techniques can be interchanged, for example I could have used a table direct to load my label, or a text type command to load the grid view. But these two basic techniques should give you all the functionality to complete your toolset for working with SSCE databases.

 

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!

Follow

Get every new post delivered to your Inbox.

Join 100 other followers