Category Archives: VB.Net

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!

Advertisements