Working directly with a SQL Server CE Database in VB.NET

In order to work directly with a SQL Server CE database (without using a dataset or mapping tables as classes), we need to directly manipulate objects of classes like SqlCeConnection or SqlCeCommand.

SqlCeConnection is an class that allows you to establish a direct connection to the database. In order for the SqlCeConnection object to identify the database you want on your harddisk (since it's a local/desktop database), he needs to have an connection string. The connection string is formatted string that contains, among others specifiers, the path to the your database file.

SqlCeCommand is a class that allows you to execute queries and non-query commands (like INSERT, UPDATE, DELETE, etc). In order to create an SqlCeCommand you need to specify a SqlCeConnection and a string containing the command you want to execute.

SQL statements can be grouped in 3 categories:
-table queries (ex. "SELECT * FROM myTable")
-single (scalar) queries (ex. "SELECT AVG(salary) from Employees)
-non-queries (ex. "INSERT INTO myTable VALUES(1,"John")" ).

A simple class that allows communication with an SQL Server CE database could be implemented like:

As you probably observed by now, every method is placed around a try/catch block. This was done because if the connection string is wrong, the method SqlCeConnection open() will trigger an exception. Also incorrect SQL statements can also trigger exceptions.

Imports System.Data.SqlServerCe
 
Public Class SimpleSqlCeAdapter
    ''' 

    ''' The connection object used for communicating with the database
    ''' 
    Private sqlConnection As SqlCeConnection
 
    ''' 

    ''' Creates a new SimpleSqlCeAdapter object for an unencrypted
    ''' database.
    ''' 
    ''' The path to the database file
    Public Sub New(ByVal path As String)
        Dim connectionString As String = "Data Source=" & path
        sqlConnection = New SqlCeConnection(connectionString)
    End Sub
    ''' 

    ''' Creates a new SimpleSqlCeAdapter object
    ''' 
    ''' The connection string used for 
    ''' connecting to the database
    ''' The path to the database file
    Public Sub New(ByVal path As String, ByVal connection As String)
        Dim connectionString As String = connection & path
        sqlConnection = New SqlCeConnection(connectionString)
    End Sub
    ''' 

    ''' Queries the database for a DataTable object
    ''' 
    ''' A SQL SELECT statment
    ''' A datatable if the sql statment is correct. 
    ''' Otherwise it returns Nothing
    Public Function QueryDatabaseForTable(ByVal queryCommand As String) _
                    As DataTable
        Try
            Dim table1 As New DataTable("Table1")
            Dim sqlCommand As New SqlCeCommand(queryCommand, sqlConnection)
            sqlConnection.Open()
            Dim sqlReader = sqlCommand.ExecuteReader()
            table1.Load(sqlReader)
            sqlReader.Close()
            Return table1
        Catch ex As Exception
            Console.Error.Write(ex.Message)
            Return Nothing
        Finally
            sqlConnection.Close()
        End Try
    End Function
    ''' 

    ''' Queries the database for a String object.
    ''' 
    ''' A SQL SELECT statement that
    ''' returns a single value.
    ''' 
    Public Function QueryDatabaseForScalar(ByVal queryCommand As String) _
                    As String
        Try
            Dim sqlCommand As New SqlCeCommand(queryCommand, sqlConnection)
            sqlConnection.Open()
            Return CType(sqlCommand.ExecuteScalar(), String)
        Catch ex As Exception
            Console.Error.Write(ex.Message)
            Return Nothing
        Finally
            sqlConnection.Close()
        End Try
    End Function
    ''' 

    ''' Executes a SQL statement which returns no value (Which is not
    ''' a query).
    ''' 
    ''' The SQL statement which will be
    ''' executed
    Public Sub ExecuteNonQuery(ByVal queryCommand As String)
        Try
            Dim sqlCommand As New SqlCeCommand(queryCommand, sqlConnection)
            sqlConnection.Open()
            sqlCommand.ExecuteNonQuery()
        Catch ex As Exception
            Console.Error.Write(ex.Message)
        Finally
            sqlConnection.Close()
        End Try
    End Sub
End Class

It is very important to note that the SqlCeConnection must be closed even if an exception was triggered. Because of this the method SqlCeConnection.close() was called inside a finally block.

In order to see the use of this class let's take an example:

We shall consider that we have SQL Server CE database called MyDatabase. This database consists of a single table called Table1 who contains the fields ID (numeric) and Name (nvarchar). The table contains the following 3 records:
1 John
2 Bill
3 James
The database is unencrypted (has no password) and its path is "D:\MyDatabase". 

Public Class DataClient
    Public Shared Sub Main(ByVal args As String())
        'Creating the simple SqlceAdapter by specifying the path
        Dim di As New SimpleSqlCeAdapter("D:\MyDatabase.sdf")
        'Inserting a new record
        di.ExecuteNonQuery("INSERT INTO table1 VALUES(4,'Bob')")
        'A table query
        Dim dt As DataTable = di.QueryDatabaseForTable("SELECT * FROM table1")
        For Each dr As DataRow In dt.Rows
            Console.WriteLine("{0} {1}", dr(0), dr(1))
        Next
        'A simple scalar query
        Dim s As String = di.QueryDatabaseForScalar("SELECT AVG(ID) FROM table1")
        Console.WriteLine(s)
        Console.Read()
        'Output
        '1 John
        '2 Bill
        '3 James
        '4 Bob
        '2.500000
    End Sub
End Class

A simple program that would insert a new record and then show the table and the average id would be:  

Download latest version

Change log

• v1.1
• Added documentation
• Errors are now redirected to an error stream.
• v.1.0
• First release

Useful links:
Connection strings for Sql Server C# 2005