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]](https://arcanecode.files.wordpress.com/2007/04/ssce_vbversion.jpg)
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!
Arcane Code 
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
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.
Great, clear example, a delight after trudging through the gruesome pathetic Microsoft documentation.
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!!!
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.
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.
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.
thanks for this reference… thanks..!!!
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
Thanks for the VB 2008 example. It just solved a huge problem for me.
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.
Hi, good web blog, I ´m mexican and your code is very useful, congratulations ¡¡¡¡
greetingd from mexico
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
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.
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.
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.
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.
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.
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
‘https://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
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
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 ?!
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 + “%’)”;
thnks, your code is very useful, congratulations
but can i use it on pocket pc?
thanks, your code is very useful, congratulations
but can i use it on pocket pc?
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
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
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
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!
Where does the database would hae been stored??
Excelente explicación compañero.
Gracias.
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?
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