Category Archives: SQL Server Compact Edition

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!

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

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

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

[Picture of SSCE and VS 1]

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

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

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

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

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

[Picture of SSCE and VS 2]

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

[Picture of SSCE and VS 3]

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

[Picture of SSCE and VS 4]

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

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

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

[Picture of SSCE and VS 5]

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

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

[Picture of SSCE and VS 6]

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

[Picture of SSCE and VS 7]

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

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

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

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

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

[Picture of SSCE and VS 8]

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

[Picture of SSCE and VS 9]

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

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

[Picture of SSCE and VS 10]

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

Getting Ready for Code Camp

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

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

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

http://shrinkster.com/nsk

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

http://shrinkster.com/nsl

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

http://shrinkster.com/nsm

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

http://shrinkster.com/nsn

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

http://shrinkster.com/nso

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

Arcane Tidbits

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

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

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

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

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

Arcane Searches

I thought it was time for another round of Arcane Searches. In case you missed the last round, these are some of the search terms users entered into their search engine and managed to run across my blog. I’ll try to pick out a few that seem to occur frequently and answer them. Let’s get started.

ssis package not reading environment variables

There is a policy setting on the server that dictates whether or not the SQL Server job engine can access environment variables.

virtual pc exit fullscreen

To exit full screen mode, use the Right ALT+ENTER combination. Note that the left ALT key won’t work, you must use the right ALT key, with the Enter key.

connectstring SQL compact, connectionstring sql compact edition

These two show up frequently, the connection string for SQL Server Compact Edition (SSCE) is:

Datasource=”C:\MyData\myFile.sdf”; Password=’mypassword’

Note a few things, first the password part is optional. Second, the password must be enclosed in single quotes. Finally, the path to your sdf data file must be in double quotes.

wrap a string in double quotes c#

I’m guessing the search here is how to embed a double quote in a string, that’s easy. Just use a backslash in front of the double quote, and C# will interpret it as a literal character and not a double quote.

string myvar = “Right before he became unconscious, he said \”Yes dear, those pants to make you look fat.\”. “

virtual pc 2007 different ip addresses

Each Virtual PC, be it 2004 or 2007, appears as it’s own PC to the network. Your network hub can’t tell the difference between a virtual machine and a real one, and assigns each machine it’s IP address.

why would I use an interface C#

Why not? OK, sorry, couldn’t resist. Short answer, an interface gives you a way to treat many kinds of objects as if they were all the same kind of object. For example, if you had manager, field worker, office worker, and executive objects, you could have all of the implement the employee interface, then you could treat them all like generic employees to do things like pay, give vacation, etc.

For a more detailed instruction, see my series of posts which begin at https://arcanecode.wordpress.com/2007/02/13/more-oop-interfaces-in-c-part-1/ or http://shrinkster.com/mjk.

There you go, a quick selection of the most common searches I found, that were not already answered in my previous blog entries.

Loading a SQL Server Compact Edition Table From a DataTable in C#

Of all the situations you can be in using SSCE, I think one of the most common will be to pull data from a larger database provider and populate a table in your local SSCE database. While SSCE does have some nifty replication support, it only works with SQL Server. Like many of you I have to support data coming from many different places, such as Oracle, Access, and XML to name but a few.

The easiest way to pull the data is to get it into an ADO.Net DataTable, but what then? I sure didn’t want to have to create the insert statements by hand each time, plus I wanted to be able to reuse my routines. Being a lazy individual, I wanted a routine I could pass any DataTable into and push the data into my SSCE table.

I started with a blank class, and created a few class level variables. One was out of a SqlCeConnection which I named _connection. I also had two strings to hold the database name and password. Next, I created a “ConnectionString” method, identical to what I described a few days ago.

    private string ConnectionString()

    {

      return string.Format(

        “DataSource=\”{0}\”; Password='{1}'”

        , this._CacheDatabase

        , this._Password);

    }

 

I then created an Open method, which is pretty simple:

 

    public void Open()

    {

      if (_connection == null)

        _connection = new SqlCeConnection(this.ConnectionString());

 

      if (_connection.State == ConnectionState.Closed)

        _connection.Open();

    }

 

OK, now for the grand finale.

 

 

    public void Populate(DataTable myDataTable

      , string tableName)

    {

      // If the datatable has no rows, we’re wasting

      // our time, get outta dodge.

      if (myDataTable.Rows.Count == 0)

      {

        return;

      }

 

      // Make sure database is open for business

      this.Open();

 

      // Use a string builder to hold our insert clause

      StringBuilder sql = new StringBuilder();

      sql.Append(“insert into “ + tableName + ” (“);

 

      // Two more, one for the list of field names,

      // the other for the list of parameters

      StringBuilder fields = new StringBuilder();

      StringBuilder parameters = new StringBuilder();

 

      // This cycles thru each column in the datatable,

      // and gets it’s name. It then uses the column name

      // for the list of fields, and the column name in

      // all lower case for the parameters

      foreach (DataColumn col in myDataTable.Columns)

      {

        fields.Append(col.ColumnName);

        parameters.Append(“@” + col.ColumnName.ToLower());

 

        if (col.ColumnName !=

          myDataTable.Columns[myDataTable.Columns.Count

                 – 1].ColumnName)

        {

          fields.Append(“, “);

          parameters.Append(“, “);

        }

      }

      sql.Append(fields.ToString() + “) “);

      sql.Append(“values (“);

      sql.Append(parameters.ToString() + “) “);

 

      // We now have our Insert statement generated.

      // At this point we are ready to go through

      // each row and add it to our SSCE table.

      int rowCnt = 0;

      string totalRows = myDataTable.Rows.Count.ToString();

 

      foreach (DataRow row in myDataTable.Rows)

      {

        SqlCeCommand cmd

          = new SqlCeCommand(sql.ToString(), _connection);

 

        foreach (DataColumn col in myDataTable.Columns)

        {

          cmd.Parameters.AddWithValue(“@”

            + col.ColumnName.ToLower()

            , row[col.ColumnName]);

        }

        // Optional: I created a delegate called message delegate,

        // and assign it to the class level variable Cachemessage

        // It’s a simple method that takes one string and displays

        // the results in a status bar. If you want to simplify

        // things, just remove this entire section (down to

        // the try).

        rowCnt++;

        if (_CacheMessage != null)

        {

          if ((rowCnt % 100) == 0)

            _CacheMessage(“Loading “ + tableName

              + ” Row “ + rowCnt.ToString()

              + ” of “ + totalRows);

        }

 

        try

        {

          // Here’s where all the action is

          // sports racers! This is what sends

          // our insert statement to our local table.

          cmd.ExecuteNonQuery();

        }

        catch (Exception ex)

        {

          // You’ll probably want to be a bit more

          // elegant here, but for an example it’ll do.

          throw ex;

        } 

      } 

    }

 

My code comments elaborate pretty well on what’s going on, a quick read should be all you need. Only two things to really point out. First, I create a delegate to send a progress message. I’ll go into more on delegates another day, if need be, there’s a ton of info on them out on the web. Let me show you the declarations I used for my delegates so you can repeat them in your class:

 

    private CacheMessageHandler _CacheMessage;

 

    public void Messenger(CacheMessageHandler messageRoutine)

    {

      _CacheMessage = messageRoutine;

    }

 

I created a method in the calling program called ShowStatus. Very simple, takes one string as a parameter and displays it somewhere to the user. (I chose a status bar, you might use a label). All I had to do was call the Messenger method like so: 

    myMethod.Messenger(

         new MyClass.CacheMessageHandler(ShowStatus))

 

In retrospect I could also have created a CacheMessage property, I just didn’t think of it at the time. If you paste in the declarations you should be able to use the method even though you never use the delegate (note I check to see if _CacheMessage is null) but if you have issues, just delete that small section, it’s not that important to the process of loading the table.

The other major thing, and a gold star to you if you already noticed this: in order for this method to work, the column names in your DataTable must match exactly with the column names in your SSCE table!

Personally this seems like a small price to pay, and frankly if you are replicating data it will make your debugging and programming life much easier as you work through bugs. I do this as a standard, which is why this kind of routine fits well into my environment.

This wraps up (for now) my exploration of SQL Server Compact Edition. If you have decided to leverage SSCE in your own apps, please leave a comment, let us all know what your experiences have been, problems, benefits, and the like.

Inserting Rows Into A SQL Server Compact Edition Table in C#

Now that we have some tables, you naturally want to put some data into them. As you might have guessed from my last post, you perform data manipulation (insert, update, delete) just like you do when creating the table and use the SqlCeCommand object. Only this time there’s a twist.

First, because I wanted to load several rows I created a method to load a single row and pass in the parameters to my “CoolPeople” table. Here’s the small bit of code that handles it:  

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

 

I then wrote a routine that would take the passed in data and insert it into the database. As with my create table example yesterday, I’m using the command object. This time though, I am adding parameters to the command.

If you look in the SQL, you see I have three parameters, noted by the @ sign. @first, @last, and @url. When SSCE creates the insert statement for the database, it will then look for three parameters and replace these three @ placeholders with the values you put into the parameters.

Sure, you could concatenate it all together in a string, but then you have to worry about things like the “O’Malley Issue” and SQL Injection attacks. (See Bill Vaughn’s book “Hitchhiker’s Guide to Visual Studio and SQL Server, 7th edition for a complete discussion on these topics, or browse the web. There’s lots of info so I won’t take up further space now.)

Here’s the entire LoadARow routine. Note that my choosing to name the method parameters the same as the SSCE command parameters is entirely a coincidence, it simply makes it self documenting and is not a requirement.

 

    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”, first);

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

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

      }

    }

 

After creating the SqlCeCommand object by passing in the sql string and the connection object, I can then add the parameters with a single line for each. By using the Parameters object of the command object, I can call the AddWithValue method, and simply pass in the parameter name as a string and the value for that parameter. Once you add all the parameters, simply call the ExecuteNonQuery method and the data is inserted!

This method can be a basis for all your future work with SSCE. Everything you need to do revolves around the command object and sending SQL commands to the database. Need to delete a record? Just change the SQL from an insert to a delete, pass the correct parameters and you are in business. Update? Same thing.

Using the code I’ve shown in this series you can create your own SSCE applications, and store data locally. Lest you think this is the wrap up, I have one more cool method to show you, but that’ll be for tomorrow!

Create a Table in SQL Server Compact Edition with C#

Before we create a table in SSCE, you need to understand that SSCE only supports a subset of the data types provided in full SQL Server. The specific list is: bigint, integer, smallint, tinyint, bit, numeric (p, s), money, float, real, datetime, national character(n) (Synonym:nchar(n)), national character varying(n) (Synonym:nvarchar(n)), ntext, nchar, binary(n), varbinary(n), image, uniqueidentifier, ROWGUIDCOL , and IDENTITY [(s, i)].

The last two, strictly speaking are not data types but properties of the data column. SSCE was designed to be small and lightweight (hence the Compact name). To keep it small, many of the datatypes were eliminated.

A full list, with detailed explanations can be found at on the MSDN site: http://msdn2.microsoft.com/en-us/library/ms172424.aspx or http://shrinkster.com/lij.

Now that you know what’s available, let’s talk about how to create a table. In keeping with it’s theme of compactness, SSCE doesn’t include classes for manipulating the structures inside a SSCE database directly. Instead, you have to do everything through SQL DDL (Data Definition Language) statements. You know, Create Table, Drop Table, etc.

The code below shows the fairly simple steps involved. First you need to open a connection to the database. You do this by creating a SqlCeConnection object and passing in the connection string. The connection string has the same format I described in my previous post on creating the database.

Next, we create a string to hold some SQL (sql), and create a SqlCeCommand object (cmd). Finally we call the ExecuteNonQuery method of the command object to kick off the SQL. Here’s the code, with some try / catch logic thrown in to trap for errors.

 

      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 Fudge.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);

      }

      catch (Exception ex)

      {

        MessageBox.Show(ex.Message, “Fooey.”, MessageBoxButtons.OK,

          MessageBoxIcon.Error);

      }

      finally

      {

        cn.Close();

      }

 

There’s a few things I’d like to point out. First, the connection. In this simple example, I opened and closed the connection in the same routine. For talking to large databases, especially when doing so from a web app, this methodology makes a lot of sense. You keep the connection open for a brief time and reduce the load on your server.

With SSCE however that need does not exist. The database is local with one connection, so you save nothing by the brief open / close connection, and instead slow yourself down. With SSCE I would recommend you open the connection when your application launches, and close it when it exists. Pass it to the classes that need it. You will gain a lot of speed doing so, and minimal cost in terms of memory.

The other thing to note, within the line that instantiates the new SqlCeConnection I have a method called ConnectString(). To make life easy for my demo I encapsulated the connection string in a method that returns a string. You could place your connection in a string, method, or whatever is convenient for you. I already documented the code to create a connection string in yesterday’s post on creating a database, so I won’t bore you with it again.

Would you like to see the database? Cool. Open up SQL Server Management Studio (I’m guessing you have SQL Express or SQL Server Developer Edition installed). When it opens, pick SQL Server Mobile as the database type. Under Database File, click the Browse for more… option and navigate to the sdf file you created, and click on OK. Enter in the password, and click OK again to open the database.

Now you can do many of the normal things you’d do with a SQL Server database. Click on the Tables in the tree and you should see it expand and show our “CoolPeople” table. You can drill down further to see all the fields.

OK, we’ve created our table, the next step will be to load it. But that’s for the next post!

Create a SQL Server Compact Edition Database with C#

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

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

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

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

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

using System.Data.SqlServerCe;

using System.IO;

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

 

      string connectionString;

      string fileName = “ArcaneCode.sdf”;

      string password = “arcanecode”;

 

      if (File.Exists(fileName))

      {

        File.Delete(fileName);

      }

 

      connectionString = string.Format(

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

 

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

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

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

      SqlCeEngine en = new SqlCeEngine(connectionString);

      en.CreateDatabase();

 

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

 

[Picture of ArcaneCode.sdf]

 

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

Getting Started with SQL Server Compact Edition

What is SQL Server Compact Edition?

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

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

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

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

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

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

Haven’t I seen this before?

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

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

Places to use SQL Server Compact Edition

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

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

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

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

What do I need?

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

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

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

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

How do I learn this stuff?

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

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

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

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

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

 

SQL Server Compact Edition Released

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

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

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

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

or

http://shrinkster.com/l9f