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:

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:

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:

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:
 
 
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:

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…>”.

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.

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

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.

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

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…”.

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.

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.

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.

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.

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.

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

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.

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.

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:

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 14^th. (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 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 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.

Encoding Strings to Base64 in C#

I recently had the need to convert simple strings back and forth from Base64 encoding. It turned out to be relatively simple in .Net, once you figured out which class libraries you needed to combine.

It turns out the System.Convert has two handy methods for dealing with Base64, ToBase64String and FromBase64String. On the ToBase64String, we have a bit of a challenge, since it expects a byte array and not a string to be passed in.

It does make a certain amount of sense, typically you aren’t encoding a simple string but instead a binary object such as a file, which is usually represented as an array of bytes. For us, this means we have to take our string and convert it to a byte array.

You’d think the string class would have a nice static method to handle this, but alas it does not. Instead we have to turn to System.Text. I imagine most of you are working with ASCII encoding, so here we’ll call on the ASCIIEncoding.ASCIII class, and use it’s GetBytes to convert a string to bytes.

The small method below combines the two methods I’ve described to create a Base64 encoded string from a normal string.

static public string EncodeTo64(string toEncode)

{

byte[] toEncodeAsBytes

= System.Text.ASCIIEncoding.ASCII.GetBytes(toEncode);

string returnValue

= System.Convert.ToBase64String(toEncodeAsBytes);

return returnValue;

}

Note two things, first I am using ASCII encoding, which should cover most folks. Just in case though, System.Text has encodings for the various flavors of UTF as well as Unicode. Just choose the appropriate encoding method for your need.

Second, I made the class static because I was using a console app for my test harness. While it could be static in your class, there’s no reason it has to be. Your choice.

OK, we’ve got the string encoded, at some point we’re going to want to decode it. We essentially do the reverse of encoding, we call the FromBase64String and pass in our encoded string, which returns a byte array. We then call the AsciiEncoding GetString to convert our byte array to a string. Here’s a small method to Decode your Base64 strings.

static public string DecodeFrom64(string encodedData)

{

byte[] encodedDataAsBytes

= System.Convert.FromBase64String(encodedData);

string returnValue =

System.Text.ASCIIEncoding.ASCII.GetString(encodedDataAsBytes);

return returnValue;

}

Finally, here’s a simple test harness, done in a console app, to show you calls to the two methods.

string myData = “Here is a string to encode.”;

string myDataEncoded = EncodeTo64(myData);

Console.WriteLine(myDataEncoded);

string myDataUnencoded = DecodeFrom64(myDataEncoded);

Console.WriteLine(myDataUnencoded);

Console.ReadLine();

Be aware, I’ve done no error checking here. My little methods assume that the data you are decoding will properly convert to and from a string. If that’s not the case you could wind up with a serious case of gibberish, if not cause a run time exception. If you are not positive that all you are dealing with is simple strings, then make effort to include some try / catch logic in your implementation.

Arcane Education: Alabama Code Camp IV

Yes, it’s spring time, when the flowers are in bloom, and a young man’s fancy turns to… .NET!

Alabama Code Camp IV is coming up quick, April 14th is just a few short weeks away. For those in the south-east, this one will be held in Mobile AL. See all the details at:

http://www.alabamacodecamp.com/

If this is anything like the previous ones it’ll be great, tons of great speakers and lots of good swag. If you have never been to Mobile before, there’s a lot to see and do. My family is coming along and we’re going to make a weekend of it.

Top on my list (after code camp of course) is seeing the USS Alabama (http://www.ussalabama.com/), a WW II Battleship. At the same location they have the USS Drum, a submarine, a B52, an A-12 Blackbird, and tons of other exhibits.

If battleships aren’t your thing, Mobile is loaded with opportunities, check out Yahoo’s top 16 list at http://travel.yahoo.com/p-travelguide-2833565-mobile_things_to_do-i, or the Mobile city guide on AL.com (http://www.al.com/mobile/cityguide/index.ssf?attractions.html).

And for the beach goers, Orange Beach is right next door to Mobile (literally). Check out the fun in the Gulf of Mexico at http://www.gulfshores.com/things-to-do/attractions/.

So there you go, a whole day of geeky fun for you, and plenty of ways for the family to be out spending your money, oops I mean having fun while you are getting an education. Well, at least Code Camp is free!

So quit sitting on your duff and start making plans today!

Arcane Tricks: Cut / Copy / Paste To and From Virtual PC

As you might guess from my various posts, I like using Virtual PC. It has one annoyance, you can’t cut / copy / paste between the Virtual PC and another machine, like the host operating system.

This is actually a good thing, as it’s a security feature. It allows you to test suspicious software safely, without fear of it damaging your host. There are times though, when you know it is safe to allow the cut / copy / paste connection between your Virtual PC and your host. It would be nice to have a switch or option that turns this on and off. Sadly, it’s not there.

There is a work around though. Back in December, I talked about the combination of Virtual PC and Remote Desktop. (https://arcanecode.wordpress.com/2006/12/06/arcane-combinations-virtual-pc-and-remote-desktop/ or http://shrinkster.com/mvq) . While VPC lacks the ability to cut / copy / paste, Remote Desktop does not. Using Remote Desktop you can cut, copy, and paste data from your host (or any other remotely controlled PC) to the Virtual PC you are controlling via Remote Desktop.

All you have to do is follow the instructions in the post mentioned above, and “remote” into the virtual machine. Yes, you will be remoting to a machine running on the same box, but all the remote desktop software sees is an IP address. It can’t tell if that IP is on the same host computer or one across the planet.

This little trick has saved me several times, and was one of those “doh!” moments when it first occurred to me. Even if you have been following along and using Remote Desktop with VPC, it may not have occurred to you to use it on the same machine.

Resources

Using Remote Desktop: http://shrinkster.com/mvr
VPC & Remote Desktop: http://shrinkster.com/mvq
Allowing Remote Desktop in XP: http://shrinkster.com/mvs