Programatically Create tables in SQL Compact Edn (from SQL 2008) 1

[Kieran777]

I am wanting to use [red]SQL Compact Edn database[/red] for a single user application being written in Visual Studio 2008. I can programatically create the Database as such:

 Private Sub CreateDatabase()
    Protected Const SQLDBfullfilename = "C:\Users\KGM\Documents\Visual Studio 2008\Projects\myDB_2008\RPMSDAL\myDB_2008.sdf"
    Protected Const SqlConnectionString As String = _
        "Data Source='" & SQLDBfullfilename & "'; LCID=1033;Password='sa'; Encrypt = TRUE"

        Dim engine = New SqlCeEngine
        engine.LocalConnectionString = NEWDBconnectionString
        Try
            engine.CreateDatabase()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Create Compact Database v3.5 failed.")
        End Try
        engine.Dispose()
    End Sub

However, I cannot find [red]code[/red] that shows [red]how to create tables programatically[/red] nor how to [red]query the Compact SQL Edn Database programatically[/red]. Can anyone point me in the right direction on how to do this. (I can create the tables and query via SS Management Studio but need to do it programatically) using visual basic in visual studio 2008.

regards
kieran777

 

[AlexCuse]

You just need to build the same make-table query from your application, and execute it against the database.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[ESquared]

I am not familiar with the sql library tools so I can't help you use those objects & object models to creat tables.

However I can at least offer you the traditional scripting method:

CREATE TABLE MyTable (
   MyTableID int NOT NULL CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED,
   FullName varchar(128) NOT NULL
)

To begin to learn this kind of scripting, do a SQL Profiler trace against a database when you make changes to it. You should be able to use the same SQL statements on your compact database, subject to language support (and basic things like table creation and alteration are bound to be supported), as you use in a full-fledged SQL server.

 

[AlexCuse]

Couple other things I noticed:

1. I don't think SQL CE supports the full file path in a connection string. It expects it in a format like this: "\Users\KGM\Documents\Visual Studio 2008\Projects\myDB_2008\RPMSDAL\myDB_2008.sdf". At least this has been my experience. (perhaps NEWDBconnectionString does provide the path properly though?)

2. I'm assuming because you have created the database successfully that you have referenced the necessary library. You'll then find everything else you need in the System.Data.SqlServerCe namespace. You work with it the same way you would with any other database, using SqlCeConnection, SqlCeCommand, etc... This should be enough to get you started. If you get stuck post back, I have some code on another PC that will help you (in C#, but you should be able to translate easily enough).

ESquared -

I know that SQL Ce does not support clustered indexes, and I don't think that it will allow you to declare named constraints inline the way you've demonstrated either (although I have not tried the latter). If you decide to do some work with SQL Ce, hopefully this saves you from at least one of the headaches I ran into ;-)

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[AlexCuse]

Here is a way to execute a query against a SQL Ce database that does not return anything (easy enough to convert to vb, I'd do it for you but I've gotta run)

        public Boolean NonQuery(String sql, SqlCeConnection ceCon)
        {
            try
            {

                ceCon.Open();

                SqlCeCommand ex = ceCon.CreateCommand();

                ex.CommandText = sql;

                ex.ExecuteNonQuery();

                return true;
            }
            catch (SqlCeException ex)
            {
                return false;
            }
            finally
            {
                ceCon.Close();
            }
        }

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[ESquared]

Thanks for the info. "subject to language support" indeed.

 

[Kieran777]

Excellent, thanks to all those that replied. Alexcus post has set me on the right path. I will post the VB code here in case others are also seeking such. Don't forget to add a reference to System.Dat.SqlServerCE [Compact Database v3.5 which comes with SQL2008] in your project.

It is so simple, yet I spent a lot of time searching for it.

Imports System.Data.SqlServerCe

Public Class Form1
    Protected Const SQLDBfullfilename As String = "C:\Users\KGM\Documents\Visual Studio 2008\Projects\RPMS_2008\RPMSDAL\myDB_2008a.sdf"
    Protected Const SqlCeConnectionString As String = _
        "Data Source='" & SQLDBfullfilename & "'; LCID=1033;Password='sa'; Encrypt = TRUE"

    Private Sub btnCreateDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateDB.Click
        CreateDatabase()
    End Sub
    Private Sub btnCreateTbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTbl.Click
        CreateTable()
    End Sub

    Private Sub CreateDatabase()
        Dim engine = New SqlCeEngine
        engine.LocalConnectionString = SqlCeConnectionString
        Try
            engine.CreateDatabase()
            MessageBox.Show("Database " & SqlCeConnectionString.ToString & " successfully created.", "Database successfully created", _
                MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception
            MsgBox(ex.ToString & "; Message: " & ex.Message, MsgBoxStyle.OkOnly, "SQL CE Exception Error! Create Compact Database v3.5 failed.")
        End Try
        engine.Dispose()
    End Sub
    Private Sub CreateTable()
        Dim sqlCreateTable As String = _
            "CREATE TABLE Contact3 (" & _
            "ContactID Int NOT NULL," & _
            "FirstName NVarChar(20) NOT NULL," & _
            "LastName NVarChar(20) NOT NULL" & ")"
        Dim ceConn As New SqlCeConnection(SqlCeConnectionString)
        Dim ceCmd As New SqlCeCommand(sqlCreateTable, ceConn)
        Dim returnValue As Integer

        Try
            ceConn.Open()
            returnValue = ceCmd.ExecuteNonQuery()
            MessageBox.Show("Table 'Contact' successfully created.", _
                "Table Creation Status", _
                MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch sqlceException As Exception
            MessageBox.Show(sqlceException.ToString, "SQL CE Exception Error! Create Table failed.", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            ceConn.Close()
        End Try
    End Sub

End Class