Printable Version of Topic

Click here to view this topic in its original format

Dream.In.Code _ VB.NET Tutorials _ Create a SQLCE database with tables from code.

Posted by: jens 23 Mar, 2009 - 02:43 AM

Hi all!

I happend to need to be able to create a SQLCEDB (SQL Compact Edition DataBase) on the fly from within my application. I found it to be less than obvious how to do so I made a little tutorial from my experiments. This is tested with VB2008EE on WinXP.

First of all:
In order to use SQLCE you'll have to Import it into your project, in order to be able to import it it must be referenced in the project, in my installation it never is by default (I don't know why) and this is what I have to do: Go to Solution Explorer - My Project - References - Add - .NET - System.Data.SqlServerCe - OK. Now it's referenced into your project. As mentioned you need to import it too, together with some other stuff and you should set a few options. At the very top of your code put this:

CODE

Option Explicit On
Option Strict On

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlServerCe

Now you have access to the classes that you need and make sure that your code is checked as much as possible.

Lets put the creation of the DB together with a check if there already is a DB in place in a function. The following is just an outline to get started. I'll call the function as the program loads. Replace all code in your new project with the following:

CODE

Notice the connection string, it's very basic and I assume we'll keep the DB in the directory of our program. The "File Mode" and "Persist Security Info" may be omitted. So, now we are ready to start working on creating the DB. Let's concentrate on the MakeSureHaveDB() function.

We can't just create the database every time we start the application, that would erase any data we have stored since before. We must check if there's already a data base file and depending on the outcome create one or just leave things the way they are.

CODE

    Private Function MakeSureHaveDB() As String
        Dim SQLCEDB As New SqlCeEngine(ConnStr)
        Dim msg As String

        Try
            If Not System.IO.File.Exists(".\TestSQLCE.sdf") Then
                msg = "No DB, created in " & Application.StartupPath
                SQLCEDB.CreateDatabase()
            Else
                msg = "DB already present"
            End If
        Catch ex As Exception
            msg = ex.Message
        End Try

        Return msg
    End Function

We create a SqlCeEngine object and initialize it with our connection string ConnStr. Then check if there is a DB-file present. If there is none we create one. If you put this code together with the previous code you'll be able to run it and see your DB being created. This is great. We're almost done, actually we are done if we only wanted to create a DB. However, I'd like some tables in my DB too. In order to get them in place we need to connect to the DB and issue some SQL commands to it, like this:

CODE

            If Not System.IO.File.Exists(".\TestSQLCE.sdf") Then
                msg = "No DB, created in " & Application.StartupPath
                SQLCEDB.CreateDatabase()
                cmd.Connection = conn
                conn.Open()
                cmd.CommandText = "CREATE TABLE [Persons](" & _
                                    "[PersonsID] [int] IDENTITY(1,1) NOT NULL, " & _
                                    "[Name] [nvarchar](50) NOT NULL, " & _
                                    "[Phone] [nvarchar](50) NOT NULL)"
                cmd.ExecuteNonQuery()
            Else

These are all the pieces we need. By now you'll have an application that creates a DB and creates tables in it if there is no DB file. You could of course (and maybe you should) check if an existing DB has the tables and fields that you need. That however is beyond the scope of this little tutorial.

Here's the complete final code for checking if there is a database file and creating one - with tables - if there isn't:

CODE

Option Explicit On
Option Strict On

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlServerCe

Public Class Form1

    'My connection string. It shouldn't be hard coded like this but in a tutorial...
    Public Const ConnStr As String = "Data Source = .\TestSQLCE.sdf;File Mode=Shared Read;Persist Security Info=False"

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        MsgBox(MakeSureHaveDB())
        Me.Dispose()
    End Sub

    Private Function MakeSureHaveDB() As String
        Dim conn As New SqlCeConnection(ConnStr)
        Dim cmd As New SqlCeCommand()
        Dim SQLCEDB As New SqlCeEngine(ConnStr)
        Dim msg As String

        Try
            If Not System.IO.File.Exists(".\TestSQLCE.sdf") Then
                msg = "No DB, created in " & Application.StartupPath
                SQLCEDB.CreateDatabase()
                cmd.Connection = conn
                conn.Open()
                cmd.CommandText = "CREATE TABLE [Persons](" & _
                                    "[PersonsID] [int] IDENTITY(1,1) NOT NULL, " & _
                                    "[Name] [nvarchar](50) NOT NULL, " & _
                                    "[Phone] [nvarchar](50) NOT NULL)"
                cmd.ExecuteNonQuery()
            Else
                msg = "DB already present"
            End If
        Catch ex As Exception
            msg = ex.Message
        End Try

        Return msg
    End Function

End Class

Regards
/Jens

PS: I'm happy if you comment on the tutorial. Criticism is good too - I'll learn.

Posted by: benloveguit 23 Apr, 2009 - 06:11 PM

hello this is benjamin here... I would like to enquire about how can we reead from the DB once it is created? Hoping to hearing from you soon... this is my email acc

crazyferjesus@gmail.com

and msn: benlovedrumming@hotmail.com

thanks! (:

Posted by: jens 27 Apr, 2009 - 12:01 AM

Hi!

You will find help in this http://www.dreamincode.net/forums/showtopic32392.htm by Psycho Coder.

Regards
Jens