The available official help in using a Sql Server Compact edition in a .Net program is not very extensive. In fact, it does not mention some elemental aspects that you need in order to create a database. This post will show you how to create a new database or connect to an existing one.

I assume you have already downloaded and installed the latest version of the SQL Server Compact edition, and have added a reference to it in your VB.Net project. Also, that you have imported the right namespace (System.Data.SqlServerCe).

To start, we need a class-wide variable to hold the connectionstring. We also need one for the connection.

Dim m_objSqlConnection As SqlCeConnection
Dim m_strConnectionString As String

The connectionstring is slightly different from other Sql Server editions. For my purposes, I only used the Data Source and the Persist Security Info properties. You can set the connection string in the startup, or in any other event in your form. Mine looked like this:

m_strConnectionString = _
    "Data Source=SimpleDatafile.sdf;Persist Security Info=False"

Then, you can use the following method to setup a connection to a database. It checks if the database already exists–as the database is a single file, we only need to check if the file exists. If it is a new database, it will create it (this is the bit I couldn’t find in the official guide). The method takes one parameter: the database filename.

Private Function CreateDatabaseConnection( _
            p_strDatabaseFilename As String) As Boolean

    ' Create a database connection
    m_strConnectionString = "Data source=" & p_strDatabaseFilename & _
                            "; Persist Security Info=False"
    m_objSqlConnection = New SqlCeConnection(m_strConnectionString)

    ' Define local objects to access the database
    Dim l_objDatabaseEngine As New SqlCeEngine(m_strConnectionString)

    ' If the file already exists, the database already exists
    If Not System.IO.File.Exists(p_strDatabaseFilename) Then
        ' Create the database
        l_objDatabaseEngine.CreateDatabase()
    End If

    ' Check if we can connect
    Try
        m_objSqlConnection.Open()
        m_objSqlConnection.Close()
        Return True

    Catch ex As Exception
        m_objSqlConnection = Nothing
        Return False

    End Try

End Function

Using the SqlCeConnection object, you can proceed to execute SqlCeCommand objects and queries. They work like in other Sql Server editions and allow you to create tables, and execute queries. One thing it does not do is create and execute stored procedures; these are not supported in the Sql Server Compact edition.

Nevertheless, I think the Sql Server Compact edition is a great alternative to using large XML files, where you just want (temporary) storage fast and through .Net classes. The created database file (.sdf extension) can be accessed with Sql Server Management Studio, and is available for further processing.