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!

About these ads

36 Responses to “SQL Server Compact Edition with C# and VB.Net”

  1. James McAlister Says:

    Excellent series of SQL Compact articles. In your example you show how to create a table with ExecuteNonQuery(), Is there support for executing a series of create statements in one command? I have a script that works in Management Studio, but when I pull it in as a string and try to run it in the fashion you have here, I get a token error at my first “GO”.

    Thanks,
    James

    P.S. the application I’m working on is route automation for direct store delivery

  2. You know you have made........ - More Wally - Wallace B. McClure Says:

    [...] someone puts your name into their source file and uses you as an example.http://arcanecode.wordpress.com/2007/04/13/sql-server-compact-edition-with-c-and-vbnet/  Published Aug 17 2007, 03:45 PM by wallym Filed under: Wally [...]

  3. Anthony Says:

    Hi there,
    Thank you for some simply fantastic code.

    The example you provided was exactly what I’ve spent far too long searching for. Your are a credit to your profession.

    All the best,
    Anthony.

  4. James Says:

    Great, clear example, a delight after trudging through the gruesome pathetic Microsoft documentation.

  5. JEmlay Says:

    Wow…simply WOW!!!

    I have been struggling with LINQ to Dataset for two weeks. I finally got fed up and looked into SSCE. This was one of the first pages I came across. It was my last!
    With the help of your sample code I learned everything I needed to know to completely convert my entire project from datasets to SQL tables. After 2 hours of work not only did I get back to where I was with my LINQs version, I completely finished my app.

    Had I come across this page two weeks ago my app would have taken about 3 hours instead of two weeks!!!

    You rock and thank you very much for your sample!!!

  6. The Registrar Says:

    Thanks! I stumbled on your websit and it proved very valuable. I am currently studing ado.net and .net enviroment and believe me, i am not novice in programming.

  7. glenn Says:

    Tremendous service to the development community. I’m about to start a CE project and wanted to get a start some place. Your examples really saved me a lot of time. Thanks very much.

  8. Paul Says:

    First let me say thank you for the help with my first SQL compact. I am just moving from VB6 to VBnet. This is a great example thanks for the time it took to put it together!

    I tried your example and got this error when reading from SQL

    there was an error parsing the query. [ Token line number = 1,Token line offset = 64,Token in error = ']

    This is what you had written
    ‘sql += “WHERE LastName LIKE ‘” & txtName.Text & “%’ ”
    by trial and error I added the parenthesis and this works
    sql += “WHERE (LastName LIKE ‘” & txtName.Text & “%’) ”

    must be the difference between C# and VBnet but I don’t know enough to say for sure.

    Thanks again.

  9. adheputra URL Says:

    thanks for this reference… thanks..!!!

  10. yinon Says:

    here is a vb.net version (2008)

    I didnt managed to find the vb.net version and ended up doing it myself so here it is, in case anyone else needs one too. great sample. thx.

    Public Class Form1

    ‘====================== assumed controls on the form:
    ‘buttons:
    ‘ CreateDataBaseBtn
    ‘ CreateTableBtn
    ‘ LoadGridBtn
    ‘ LoadTableBtn
    ‘ ReadRecordsBtn
    ‘labels:
    ‘ ResultsLbl
    ‘textboxes:
    ‘ NameTxt
    ‘DataGridView:
    ‘ dgvCoolPeople

    ‘====================================== Services

    Private Function ConnectionString() As String
    Dim fileName As String = “ArcaneCode.sdf”
    Dim password As String = “arcanecode”
    Dim connectionStr As String

    connectionStr = “DataSource=\” & fileName & “; Password=” & password

    Return connectionStr

    End Function

    Private Sub LoadRow(ByVal firstName As String, ByVal lastName As String, ByVal url As String)
    Dim connectionStr As String = ConnectionString()
    Dim connection As New System.Data.SqlServerCe.SqlCeConnection(connectionStr)

    If connection.State = ConnectionState.Closed Then
    connection.Open()
    End If

    Dim cmd As System.Data.SqlServerCe.SqlCeCommand

    Dim sqlInsertRowStatement As String = “insert into CoolPeople (LastName, FirstName, URL) values ”
    sqlInsertRowStatement = sqlInsertRowStatement & “(@lastname, @firstname, @url)”

    Try

    cmd = New System.Data.SqlServerCe.SqlCeCommand(sqlInsertRowStatement, connection)
    cmd.Parameters.AddWithValue(“@lastname”, lastName)
    cmd.Parameters.AddWithValue(“@firstname”, firstName)
    cmd.Parameters.AddWithValue(“@url”, url)
    cmd.ExecuteNonQuery()

    ResultsLbl.Text = “Row Added.”

    Catch sqlexception As System.Data.SqlServerCe.SqlCeException
    MessageBox.Show(sqlexception.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error)

    Catch ex As Exception
    MessageBox.Show(ex.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error)

    Finally
    connection.Close()

    End Try

    End Sub

    ‘ ======================================== Events

    Private Sub CreateDataBaseBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateDataBaseBtn.Click

    Dim fileName As String = “\ArcaneCode.sdf”
    Dim connectionStr As String = ConnectionString()
    Dim engine As System.Data.SqlServerCe.SqlCeEngine

    If System.IO.File.Exists(fileName) Then
    System.IO.File.Delete(fileName)
    End If

    engine = New System.Data.SqlServerCe.SqlCeEngine(connectionStr)

    engine.CreateDatabase()

    ResultsLbl.Text = “Database Created.”
    End Sub

    Private Sub CreateTableBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CreateTableBtn.Click
    ‘ Issue a SQL command to create a table
    ‘ Note: this only creates the table, it doesnt put any rows in it.
    Dim connectionStr As String = ConnectionString()

    Dim connection As New System.Data.SqlServerCe.SqlCeConnection(connectionStr)

    If connection.State = ConnectionState.Closed Then
    connection.Open()
    End If

    Dim cmd As System.Data.SqlServerCe.SqlCeCommand

    Dim sqlCreateTableStatement As String = “create table CoolPeople (LastName nvarchar (40) not null, ”
    sqlCreateTableStatement = sqlCreateTableStatement & “FirstName nvarchar (40), URL nvarchar (256) )”

    cmd = New System.Data.SqlServerCe.SqlCeCommand(sqlCreateTableStatement, connection)

    Try

    cmd.ExecuteNonQuery()

    ResultsLbl.Text = “Table created.”

    Catch sqlexception As System.Data.SqlServerCe.SqlCeException
    MessageBox.Show(sqlexception.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error)

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

    Finally
    connection.Close()

    End Try
    End Sub

    Private Sub LoadGridBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadGridBtn.Click

    Dim connectionStr As String = ConnectionString()

    Dim connection As New System.Data.SqlServerCe.SqlCeConnection(connectionStr)

    If connection.State = ConnectionState.Closed Then
    connection.Open()
    End If

    Try
    ‘ Set the command to use the table, not a query
    Dim cmd As System.Data.SqlServerCe.SqlCeCommand
    cmd = New System.Data.SqlServerCe.SqlCeCommand(“CoolPeople”, connection)
    cmd.CommandType = CommandType.TableDirect

    ‘ Get the Table

    Dim resultSet As System.Data.SqlServerCe.SqlCeResultSet
    resultSet = cmd.ExecuteResultSet(System.Data.SqlServerCe.ResultSetOptions.Scrollable)

    ‘ Load the result set into the database
    dgvCoolPeople.DataSource = resultSet

    Catch sqlexception As System.Data.SqlServerCe.SqlCeException
    MessageBox.Show(sqlexception.Message, “Error”, 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

    Private Sub LoadTableBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadTableBtn.Click

    Try

    LoadRow(“Carl”, “Franklin”, “http:\\www.dnrtv.com”)
    LoadRow(“Richard”, “Campbell”, “http:\\www.dotnetrocks.com”)
    LoadRow(“Leo”, “Laporte”, “http:\\www.twit.tv”)
    LoadRow(“Steve”, “Gibson”, “http:\\www.grc.com”)
    LoadRow(“Arcane”, “Code”, “http:\\arcanecode.wordpress.com”)

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

    End Try

    End Sub

    Private Sub ReadRecordsBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ReadRecordsBtn.Click

    Dim connectionStr As String = ConnectionString()

    Dim connection As New System.Data.SqlServerCe.SqlCeConnection(connectionStr)

    If connection.State = ConnectionState.Closed Then
    connection.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 sqlQuery As String = “select LastName, FirstName from CoolPeople ”

    If (NameTxt.Text.Length > 0) Then
    sqlQuery = sqlQuery & “where LastName like ‘” & NameTxt.Text & “%'”
    End If

    Try

    Dim cmd As new System.Data.SqlServerCe.SqlCeCommand(sqlQuery, connection)
    cmd.CommandType = CommandType.Text

    ‘ if you don’t set the result set to scrollable HasRows does not work
    Dim resultSet As System.Data.SqlServerCe.SqlCeResultSet
    resultSet = cmd.ExecuteResultSet(System.Data.SqlServerCe.ResultSetOptions.Scrollable)

    ‘ If you need to be able to update the result set, instead use:
    ‘ cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

    If resultSet.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 = resultSet.GetOrdinal(“LastName”)
    Dim ordFirstname As Integer = resultSet.GetOrdinal(“FirstName”)

    ‘ Hold the output
    Dim stringBuilder As New System.Text.StringBuilder

    ‘ Read the first record and get it’s data
    resultSet.ReadFirst()
    stringBuilder.AppendLine(resultSet.GetString(ordFirstname) & ” ” & resultSet.GetString(ordLastName))

    ‘ Now read thru the rest of the records. When there’s no more data, .Read returns false.
    While resultSet.Read()
    stringBuilder.AppendLine(resultSet.GetString(ordFirstname) & ” ” & resultSet.GetString(ordLastName))
    End While

    ‘Show the output in the label
    ResultsLbl.Text = stringBuilder.ToString()

    Else

    ResultsLbl.Text = “No Rows Found.”
    End If

    Catch sqlexception As System.Data.SqlServerCe.SqlCeException
    MessageBox.Show(sqlexception.Message, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error)

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

    Finally
    ‘we Don’t need it anymore so we’ll be good and close it. in a ‘real life’ situation
    ‘ connection would likely be class level
    connection.Close()
    End Try
    End Sub
    End Class

  11. Richard Says:

    Superb descriptions and sample code for novices getting to grips with SQL and CE.

    Note to Paul (and maybe others): The issue with parsing errors is due to the fact that WordPress changes quote characters to open/close pairs. When you copy and paste back into VS it does not recognise them and they must be changed manually. The double quotes show up (in C#) as syntax errors, but the singles are within strings so do not get spotted by the compiler. You need to search for them and change as necessary.

  12. Alex Says:

    Hi, good web blog, I ´m mexican and your code is very useful, congratulations ¡¡¡¡
    greetingd from mexico

  13. Matthew Says:

    Hi

    I am trying to create an sdf file with c# webservices on a windows 2003 server box but get this error and can’t find any other help.

    Unspecified error [ sqlcese30.sys.dll ]
    – this is the error when the program does “engine.CreateDatabase();”
    My code runs fine on my workstation. I’ve checked folder permissions, I’ve copied sqlcese30.sys.dll into the bin folder, I’ve installed the SQL Server 2005 Compact Edition Developers SDK on the server…

    Can you help me solve this error, please?

    Cheers

    Matthew

  14. Raguraman Says:

    Insert statement is not working in SQLCE. Generates no error. executeNonQuery() returns 1 for affected rows too. but, I am not able to view the added records in the SDF file.

    any suggestion or provide help!

    Thanks.

    • Ugo Says:

      We have the same problem : executeNonQuery() returns 1 for affected rows but when we open the table nothing!
      App : Vb.Net 2005 + SQLCE 3.5

      any suggestion or provide help!

      Thanks.

  15. Matthew Says:

    We abandoned SQLce and started using SQLite. I doubt we’ll even use SQLce again. It’s got everything the MS product hasn’t, including any silly restrictions where you can use it.
    It’s also faster and smaller. The learning curve to begin using it is short too. Never say I don’t give you anything.

  16. Haroon Says:

    Hi,

    fantastic example. It gave me the kick for building a solution.

    Having the same error as Matthew two posts above on random installs (XP SP2 and SP3) never had this error on Vista or W7, yet.

    The error appears when opening a database.

  17. Problemas con Windows Vista - Foro Says:

    [...] Microsoft .NET Compact Framework 3.5 Redistributable – MS Windows Vista Compatible Software SQL Server Compact Edition with C# and VB.Net Arcane Code ErikEJ blogs about SQL Compact, .NET and related stuff: Running SQL Compact from CD-ROM (read only [...]

  18. Agustin Says:

    I am using:
    SqlCeConnection cn = new SqlCeConnection(strConnection);

    if (cn.State == ConnectionState.Closed)
    {
    cn.Open();
    }

    SqlCeCommand cmd;

    string sql = “insert into ” + vTabla +
    “(Nombre) ” +
    “values (@Nombre)”;

    try
    {
    cmd = new SqlCeCommand(sql, cn);
    cmd.Parameters.AddWithValue(“@Nombre”, “xxxxx”);
    cmd.ExecuteNonQuery();
    }
    catch
    { }

    The program data are inserted, but when you exit debug mode, the data is not in the database.

    Thanks.

  19. Barry G. Sumpter Says:

    Visual Studio 2008 Pro SP1
    .net Framework 3.5 SP1
    Reference
    System.Data.SqlServerCe 3.5.1.0

    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

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

    ‘http://msdn.microsoft.com/en-us/library/bb986876.aspx

    ‘http://msdn.microsoft.com/en-us/library/aa983341.aspx
    ‘http://msdn.microsoft.com/en-us/library/13kw2t64(VS.90).aspx

    Public Class Form1

    ‘====================== assumed controls on the form:
    ‘buttons:
    ‘ CreateDataBaseBtn
    ‘ CreateTableBtn
    ‘ LoadGridBtn
    ‘ LoadTableBtn
    ‘ ReadRecordsBtn
    ‘labels:
    ‘ ResultsLbl
    ‘textboxes:
    ‘ NameTxt
    ‘DataGridView:
    ‘ dgvCoolPeople

    ‘====================================== Services

    Private Function ConnectionString() As String
    Dim fileName As String = “ArcaneCode.sdf”
    Dim password As String = “arcanecode”
    Dim connectionStr As String

    connectionStr = “DataSource=\” & fileName & “; Password=” & password

    Return connectionStr

    End Function

    Private Sub LoadRow(ByVal firstName As String, ByVal lastName As String, ByVal url As String)
    Dim connectionStr As String = ConnectionString()
    Dim connection As New System.Data.SqlServerCe.SqlCeConnection(connectionStr)

    If connection.State = ConnectionState.Closed Then
    connection.Open()
    End If

    Dim cmd As System.Data.SqlServerCe.SqlCeCommand

    Dim sqlInsertRowStatement As String = “insert into CoolPeople (LastName, FirstName, URL) values ”
    sqlInsertRowStatement = sqlInsertRowStatement & “(@lastname, @firstname, @url)”

    Try

    cmd = New System.Data.SqlServerCe.SqlCeCommand(sqlInsertRowStatement, connection)
    cmd.Parameters.AddWithValue(“@lastname”, lastName)
    cmd.Parameters.AddWithValue(“@firstname”, firstName)
    cmd.Parameters.AddWithValue(“@url”, url)
    cmd.ExecuteNonQuery()

    ResultsLbl.Text = “Row Added.”

    Catch sqlexception As System.Data.SqlServerCe.SqlCeException
    MsgBox(sqlexception.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Error”)

    Catch ex As Exception
    MsgBox(ex.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Error”)

    Finally
    connection.Close()

    End Try

    End Sub

    ‘ ======================================== Events

    Private Sub btnCreateDatabase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDatabase.Click
    Dim fileName As String = “\ArcaneCode.sdf”
    Dim connectionStr As String = ConnectionString()
    Dim engine As System.Data.SqlServerCe.SqlCeEngine

    If System.IO.File.Exists(fileName) Then
    System.IO.File.Delete(fileName)
    End If

    engine = New System.Data.SqlServerCe.SqlCeEngine(connectionStr)

    engine.CreateDatabase()

    ResultsLbl.Text = “Database Created.”
    End Sub

    Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTable.Click
    ‘ Issue a SQL command to create a table
    ‘ Note: this only creates the table, it doesnt put any rows in it.
    Dim connectionStr As String = ConnectionString()

    Dim connection As New System.Data.SqlServerCe.SqlCeConnection(connectionStr)

    If connection.State = ConnectionState.Closed Then
    connection.Open()
    End If

    Dim cmd As System.Data.SqlServerCe.SqlCeCommand

    Dim sqlCreateTableStatement As String = “create table CoolPeople (LastName nvarchar (40) not null, ”
    sqlCreateTableStatement = sqlCreateTableStatement & “FirstName nvarchar (40), URL nvarchar (256) )”

    cmd = New System.Data.SqlServerCe.SqlCeCommand(sqlCreateTableStatement, connection)

    Try

    cmd.ExecuteNonQuery()

    ResultsLbl.Text = “Table created.”

    Catch sqlexception As System.Data.SqlServerCe.SqlCeException
    MsgBox(sqlexception.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Error”)

    Catch ex As Exception
    MsgBox(ex.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Oh Crap.”)

    Finally
    connection.Close()

    End Try
    End Sub

    Private Sub btnLoadGrid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadGrid.Click
    Dim connectionStr As String = ConnectionString()

    Dim connection As New System.Data.SqlServerCe.SqlCeConnection(connectionStr)

    If connection.State = ConnectionState.Closed Then
    connection.Open()
    End If

    Try
    ‘ Set the command to use the table, not a query
    Dim cmd As System.Data.SqlServerCe.SqlCeCommand
    cmd = New System.Data.SqlServerCe.SqlCeCommand(“CoolPeople”, connection)
    cmd.CommandType = CommandType.TableDirect

    ‘ Get the Table

    Dim resultSet As System.Data.SqlServerCe.SqlCeResultSet
    resultSet = cmd.ExecuteResultSet(System.Data.SqlServerCe.ResultSetOptions.Scrollable)

    ‘ Load the result set into the database
    dgvCoolPeople.DataSource = resultSet

    Catch sqlexception As System.Data.SqlServerCe.SqlCeException
    MsgBox(sqlexception.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Error”)

    Catch ex As Exception
    MsgBox(ex.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Oh Crap.”)

    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

    Private Sub btnLoadTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadTable.Click
    Try

    LoadRow(“Carl”, “Franklin”, “http:\\www.dnrtv.com”)
    LoadRow(“Richard”, “Campbell”, “http:\\www.dotnetrocks.com”)
    LoadRow(“Leo”, “Laporte”, “http:\\www.twit.tv”)
    LoadRow(“Steve”, “Gibson”, “http:\\www.grc.com”)
    LoadRow(“Arcane”, “Code”, “http:\\arcanecode.wordpress.com”)

    Catch ex As Exception
    MsgBox(ex.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Oh Crap.”)

    End Try
    End Sub

    Private Sub btnReadRecords_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReadRecords.Click
    Dim connectionStr As String = ConnectionString()

    Dim connection As New System.Data.SqlServerCe.SqlCeConnection(connectionStr)

    If connection.State = ConnectionState.Closed Then
    connection.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 sqlQuery As String = “select LastName, FirstName from CoolPeople ”

    If (NameTxt.Text.Length > 0) Then
    sqlQuery = sqlQuery & “where LastName like ‘” & NameTxt.Text & “%’”
    End If

    Try

    Dim cmd As New System.Data.SqlServerCe.SqlCeCommand(sqlQuery, connection)
    cmd.CommandType = CommandType.Text

    ‘ if you don’t set the result set to scrollable HasRows does not work
    Dim resultSet As System.Data.SqlServerCe.SqlCeResultSet
    resultSet = cmd.ExecuteResultSet(System.Data.SqlServerCe.ResultSetOptions.Scrollable)

    ‘ If you need to be able to update the result set, instead use:
    ‘ cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

    If resultSet.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 = resultSet.GetOrdinal(“LastName”)
    Dim ordFirstname As Integer = resultSet.GetOrdinal(“FirstName”)

    ‘ Hold the output
    Dim stringBuilder As New System.Text.StringBuilder

    ‘ Read the first record and get it’s data
    resultSet.ReadFirst()
    stringBuilder.AppendLine(resultSet.GetString(ordFirstname) & ” ” & resultSet.GetString(ordLastName))

    ‘ Now read thru the rest of the records. When there’s no more data, .Read returns false.
    While resultSet.Read()
    stringBuilder.AppendLine(resultSet.GetString(ordFirstname) & ” ” & resultSet.GetString(ordLastName))
    End While

    ‘Show the output in the label
    ResultsLbl.Text = stringBuilder.ToString()

    Else

    ResultsLbl.Text = “No Rows Found.”
    End If

    Catch sqlexception As System.Data.SqlServerCe.SqlCeException
    MsgBox(sqlexception.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Error”)

    Catch ex As Exception
    MsgBox(ex.Message, MessageBoxButtons.OK + MessageBoxIcon.Hand, “Oh Crap.”)

    Finally
    ‘we Don’t need it anymore so we’ll be good and close it. in a ‘real life’ situation
    ‘ connection would likely be class level
    connection.Close()
    End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
    End Class

    • Barry G. Sumpter Says:

      Thaks to all who contributed.
      I wanted to add my findings
      for developing my first database app
      for my windows mobile 6.5 platform on my HTC HD2.

      PC development additional installs
      over and above my previous installs
      from my initial post directly above:
      SSCEBOL-ENU.msi
      SSCEDeviceRuntime-ENU.msi
      SSCERuntime-ENU.exe

      HTC HD2 installs:
      sqlce.ppc.wce5.armv4i.CAB
      sqlce.repl.ppc.wce5.armv4i.CAB

  20. Dezso Says:

    This segment looks fine acording to the SQL Compact reference.
    “string sql = “SLECT LastName, FirstName FROM CoolPeople “;
    if (txtName.Text.Length > 0)
    {
    sql += “WHERE (LastName LIKE ‘” + txtName.Text + “%’)”;

    }”
    ref:
    “SELECT cvchar from talltypes0
    WHERE cvchar like ‘ab’ escape 7″

    but I’m getting a error
    [Token line number = 1, Token line number = 7, Token in error = LastName ]

    Whats wrong here ?!

  21. jSQL Says:

    Thank you! You saved me a whole lot of time!!!

    for the sql += “WHERE (LastName LIKE ‘” + txtName.Text + “%’)”; issue try this:

    sql += “WHERE(LastName LIKE N'” + txtName.Text + “%’)”;

  22. sofiene Says:

    thnks, your code is very useful, congratulations

    but can i use it on pocket pc?

  23. sofiene Says:

    thanks, your code is very useful, congratulations

    but can i use it on pocket pc?

  24. aroratushar Says:

    I have thoroughly used Sqlite in the past, it is complete, but lacks speed and has bugs, I discovered that Sqlite can’t be used by even 3 Users doing long database operations at the same time. It just locks up. Now only yesterday, I finally realized and was shocked that Sql Server Compact Edition is a hidden magic tool, does all the things i dreamt of. Sql Server Compact Edition 3.5 is the best Embedded Database that you will ever find in the entire world. It is free! No bugs came ever with my handling of Sql CE.

    Tushar

  25. seekaye2 Says:

    This code is very useful, many thanks

    How can I sort the DGV by a column other than the primary key?

    I tried

    DGV.Sort(DGV.Columns(0), System.ComponentModel.ListSortDirection.Ascending)

    but got the error “DataGridView control cannot be sorted if it is bound to an IBindingList that does not support sorting.”

    thanks

  26. Nauman Shah Says:

    I know that SQL Server CE supports Arabic but for that the device must support that locale. If the device does not have Arabic font installed and it does not support Arabic (Windows Mobile CE 4.20) what can be the alternative to handle this. I ll really appreciate if you or any one else can help. Thanks

  27. Jim Heath Says:

    This is very useful! I am building an app for my archery club to track events and archers scores and awards. We have been using Excel so I need to blast a bunch of data into my SSCE DB. This is the ticket! Thanks!

  28. Javith Says:

    Where does the database would hae been stored??

  29. Fredy Says:

    Excelente explicación compañero.
    Gracias.

  30. John Says:

    I copied the code as it is, and ran it on my computer, it takes almost a minute to insert 100 records in to the table.
    Is it this slow for anyone else?

  31. Eduardo Arias M. Says:

    Hi, this information its fine, runs even in a Visual Studio 2012; but i need some help connecting my PC with my Pocket PC; im reading a few manuals about it. I found a RAPI.DLL information but i have a lot of troubles.

    Some one can help me with the code needed for make a program like this controled from the PC computer?

    Thanks again for youre help


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 101 other followers

%d bloggers like this: