locked
SQL Server Compact Edition RRS feed

  • Question

  • I did a little searching on compact but i was wondering what the advantages over express are.

    It talks about compact being embedded but what does that mean exactly?

     

    And does it also require sql server to be installed to use it or is it just the runtime?

    What i mean is - is it a file that can used like you would an access database file or does it require you to have sql server installed to connect to it?

     

    Since it supports the same size database as express it might be better to use for a stand alone application because of the small file size of the runtime being less than 2 mb versus the 36 mb of express.  And the memory size is considerably less than express also.

     

    Anyone that has any experience using it can you explain some of the details on it.

     

    Thanks
    Jeff

    Sunday, January 6, 2008 2:06 AM

Answers

  •  

    I am soooooo glad you started this thread, Jeff.

     

    In a way, I think the SQL compact edition is really neat. I've had one day of experience with it and I wanted to tell you about it. I've been working on a project for about two years now called KnowledgeNavigator. It looks like Windows Explorer but it has an internal file system. The neat thing about it, it that it has seach and a tabbed webbrowser and stores HTML documents. ENough of that, let me talk about the database.

     

    KN began as strictly an Access database. I also put it on net briefly on an SQL server and it worked nicely. But the database structure was not good. KN has about 40,000 lines of code and recently I made radical changes in the database structure and used T-SQL. It has really reduced the number of lines of code.

     

    But the days of Access are limited. There is no 64 bit Jet engine (MDAC) and everything is moving to 64 bit. I just moved my laptop to it. Of course using Access, I'd be limited to 32 bit applications and there really is a speed increase in 64 bit apps.

     

    Today in one day I collected the tools to play with Compact Sql and downloaded the documentation. I wrote a program that created a database and made the tables something that was always awkward with Access. Then I converted the database. In about 90 mintues, I converted the ADO.Net code in KN to SQLCE and now KN is working on compact SQL.

     

    I like Compact SQL. Although I like playing with SQL, I like to use it for large applications. It is such a waste of resources for localized projects. That's what was advantageous about Access. The Jet Engine runs in my memory and emulates a server. That's exactly what Compact SQL does. It has 32 and 64 bit engines to run in my process space.

     

    It uses the same logic and code. The Connection, adapter and command classes have a different name but other than than the code is the same.

     

    Compact SQL does not support Stored procs. There is a version of it to run on CE applications so you can make applications for your desktop and PDA or cellphone with Compact SQL.

     

    I really, really like it. Performance is about the same as with ADO and an Access database. The code for managing Compact SQL such as for repair and compaction is profoundly simple.

     

    Compact SQL databases can be managed and queried in VS2008 (and 2005) and with SMSS.

     

    The only down side, is that Access is just a hair more convenient than is SMSS. I can't cut and paste table designs for discussion anymore. I think we should be pointing new developers to Compact SQL rather than SQLE because running SQLE for a phonebook is about as wasteful of resources as they come. Compact SQL has portable database files just like Access as there is no server to worry about although deployment will require me to distribute Compact SQL which is free for redistribution.

     

    Phew.... I think that is about what I know at my level of ignorance.

     

     

     

     

    Sunday, January 6, 2008 2:29 AM
  •  

    I'd be happy to show you database creation and some before and after. One thing that you have to do is to add a project reference System.Data.SQLServerCE and Import: Imports System.Data.SqlServerCe

     

    Ok Here is how to create an empty database:

     

    Dim engine As New SqlCeEngine("Data Source='C:\Temp\CreateCompactDatabase\nDatabase.sdf'")
    engine.CreateDatabase()

     

    There is it.... and that's all there is to it.

     

    here are some code differences:

     

     

    ADO:

     

    Imports System.Data.OleDb

    Friend Class Query

        Private Con As OleDbConnection

        Private dr As OleDbDataReader

        Private Adapter As OleDbDataAdapter

     

        Friend Sub New(ByVal Connection As OleDbConnection)

            Try

                Me.Con = Connection

     

            Catch e As Exception

                MessageBox.Show(e.Message, "Query.New")

            End Try

        End Sub

     

     

    SqlServerCe:

     

    Imports System.Data

    Imports System.Data.SqlServerCe

    Friend Class SqlceNet

        Private Con As SqlCeConnection

        Private dr As SqlCeDataReader

        Private Adapter As SqlCeDataAdapter

     

        Friend Sub New(ByVal Connection As SqlCeConnection)

            Try

                Me.Con = Connection

     

            Catch e As Exception

                MessageBox.Show(e.Message, "SqlceNet.New")

            End Try

        End Sub

     

     

    There is no difference in Logic at all only datatypes which can easily be changed with some goos find and replace techniques.

     

    So a couple of more samples:

     

    ADO:

     

    'Insert a record into the deletedObjects Table Representing the
            ‘deleted MetaData

     

            Using InsertDelMetaDataCommand As OleDbCommand = New OleDbCommand( _

             "INSERT INTO " + Tables.DeletedObjects + "
              (EntityID,CreationDate,DeletionDate,RecordType) Values "
    + _

             "(@EntityID,@CreationDate,@DeletionDate,@RecordType);", Con)

                With InsertDelMetaDataCommand.Parameters

                    .Add("@EntityID", OleDb.OleDbType.Guid, 8).Value = EntityID

                    .Add("@CreationDate", OleDb.OleDbType.Date).Value = _
                          mdCreationDate

                    .Add("@DeletionDate", OleDb.OleDbType.Date).Value = DT

                    .Add("@RecordType", OleDb.OleDbType.VarChar, 12).Value = _

                          mdRecordType

                End With

                Try ' Step 2

                    Status = (InsertDelMetaDataCommand.ExecuteNonQuery() = 1)

                    Debug.Assert(Status)

                    If Not Status Then : Con.Close() : Return False : End If

                Catch ex As Exception

                    Con.Close()

                    MessageBox.Show(ex.Message, HDR + "Error writing Data Delete
                             Record"
    , MessageBoxButtons.OK, MessageBoxIcon.Error)

                    Return False

                End Try

            End Using

     

    SQLServerCe:

     

    'Insert a record into the deletedObjects Table Representing the deleted MetaData

     

            Using InsertDelMetaDataCommand As SqlCeCommand = New SqlCeCommand( _

             "INSERT INTO " + Tables.DeletedObjects + "
              (EntityID,CreationDate,DeletionDate,RecordType) Values "
    + _

             "(@EntityID,@CreationDate,@DeletionDate,@RecordType);", Con)

                With InsertDelMetaDataCommand.Parameters

                    .Add("@EntityID", SqlDbType.UniqueIdentifier, 8).Value =
                          EntityID

                    .Add("@CreationDate", SqlDbType.Date).Value = mdCreationDate

                    .Add("@DeletionDate", SqlDbType.Date).Value = DT

                    .Add("@RecordType", SqlDbType.VarChar, 12).Value =
                            mdRecordType

                End With

                Try ' Step 2

                    Status = (InsertDelMetaDataCommand.ExecuteNonQuery() = 1)

                    Debug.Assert(Status)

                    If Not Status Then : Con.Close() : Return False : End If

                Catch ex As Exception

                    Con.Close()

                    MessageBox.Show(ex.Message, HDR + "Error writing Data Delete
                      Record"
    , MessageBoxButtons.OK, MessageBoxIcon.Error)

                    Return False

                End Try

            End Using

     

     

     

    So there you are !

     

    And the table creation code:

     

    Friend Function CreateTables() As Boolean

            Dim Status As Boolean

            Dim MDTCmd As String = "Create Table Metadata (Name
            nvarchar(80),RecordType nvarchar(12),Author nvarchar(80),"
    + _

            "DataType nvarchar(12),URL nvarchar(255),Attributes int,DateCreated
            datetime,DateModified datetime,"
    + _

            "ReferenceCount int,EntityID uniqueidentifier PRIMARY KEY,ParentID
            uniqueidentifier,"
    + "DataID uniqueidentifier)"

            Dim Command As New SqlCeCommand

            Command.CommandText = MDTCmd : Command.Connection = con

            Try

                con.Open()

                Command.ExecuteNonQuery() : Status = True

            Catch ex As Exception

                con.Close()

                MessageBox.Show(ex.Message, "Failed to create new Metadatable",
                MessageBoxButtons.OK, MessageBoxIcon.Error)

                Return False

            End Try

     

            Dim DocTblcmd As String = _

            "Create Table DocumentTable (DataID uniqueidentifier PRIMARY
             KEY,DataAttributes int,DRefCount int,Document image,DateCreated” +_

             datetime,DateModified datetime)"

            Command.CommandText = DocTblcmd

            Try

                Command.ExecuteNonQuery() : Status = True

            Catch ex As Exception

                con.Close()

                MessageBox.Show(ex.Message, "Failed to create new Document
                Table"
    , MessageBoxButtons.OK, MessageBoxIcon.Error)

                Return False

            End Try

     

            Dim DeletedObjectsTblcmd As String = _

            "Create Table DeletedObjects(EntityID uniqueidentifier PRIMARY
             KEY,CreationDate datetime,Modificationdate datetime,"
    + _

            "RecordType nvarchar(12))"

            Command.CommandText = DeletedOBjectsTblcmd

            Try

                Command.ExecuteNonQuery() : Status = True

            Catch ex As Exception

                con.Close()

                MessageBox.Show(ex.Message, "Failed to create new DO Table",
                MessageBoxButtons.OK, MessageBoxIcon.Error)

                Return False

            Finally

                con.Close()

            End Try

            Command.Dispose()

            Return Status

        End Function

     

    End Class

     

     

     

     

     

    Sunday, January 6, 2008 3:52 AM

All replies

  •  

    I am soooooo glad you started this thread, Jeff.

     

    In a way, I think the SQL compact edition is really neat. I've had one day of experience with it and I wanted to tell you about it. I've been working on a project for about two years now called KnowledgeNavigator. It looks like Windows Explorer but it has an internal file system. The neat thing about it, it that it has seach and a tabbed webbrowser and stores HTML documents. ENough of that, let me talk about the database.

     

    KN began as strictly an Access database. I also put it on net briefly on an SQL server and it worked nicely. But the database structure was not good. KN has about 40,000 lines of code and recently I made radical changes in the database structure and used T-SQL. It has really reduced the number of lines of code.

     

    But the days of Access are limited. There is no 64 bit Jet engine (MDAC) and everything is moving to 64 bit. I just moved my laptop to it. Of course using Access, I'd be limited to 32 bit applications and there really is a speed increase in 64 bit apps.

     

    Today in one day I collected the tools to play with Compact Sql and downloaded the documentation. I wrote a program that created a database and made the tables something that was always awkward with Access. Then I converted the database. In about 90 mintues, I converted the ADO.Net code in KN to SQLCE and now KN is working on compact SQL.

     

    I like Compact SQL. Although I like playing with SQL, I like to use it for large applications. It is such a waste of resources for localized projects. That's what was advantageous about Access. The Jet Engine runs in my memory and emulates a server. That's exactly what Compact SQL does. It has 32 and 64 bit engines to run in my process space.

     

    It uses the same logic and code. The Connection, adapter and command classes have a different name but other than than the code is the same.

     

    Compact SQL does not support Stored procs. There is a version of it to run on CE applications so you can make applications for your desktop and PDA or cellphone with Compact SQL.

     

    I really, really like it. Performance is about the same as with ADO and an Access database. The code for managing Compact SQL such as for repair and compaction is profoundly simple.

     

    Compact SQL databases can be managed and queried in VS2008 (and 2005) and with SMSS.

     

    The only down side, is that Access is just a hair more convenient than is SMSS. I can't cut and paste table designs for discussion anymore. I think we should be pointing new developers to Compact SQL rather than SQLE because running SQLE for a phonebook is about as wasteful of resources as they come. Compact SQL has portable database files just like Access as there is no server to worry about although deployment will require me to distribute Compact SQL which is free for redistribution.

     

    Phew.... I think that is about what I know at my level of ignorance.

     

     

     

     

    Sunday, January 6, 2008 2:29 AM
  • Hi Renee,

     

    Thanks for all the info.

     

    I just added a compact database to a new project and i see that the connection string is practically just a path

    The reason i had considered using access before was that sql express relied on a server and required the install of sql server to use it.  Access is so easy to deploy and connect with an app.

     

    I agree that maybe for most new people compact may be the way to start out if they are looking for local database.  My apps that i have been working on are all local but at the same time i kept thinking that if i was to consider working for a company then it would be a good idea to know sql server.  I guess there might not be that big a transition from compact to server.  Aside from sprocs (stored procedures for those who don't know what sprocs are) i guess.

     

    I am considering moving my current app to compact since i plan on having it available to download from the internet and would like a small download and a simple install.  Then i can get away from click once for good.

     

    What about the code for compact, does it use sqlclient or is oledb?  I haven't created a table with compact yet.  I sort of stared at it for a minute and realized i would rather move my current app to it but dread having to create my tables over again.  i only have 5 tables to redo but it gets tiring redoing what has already been done before.  That is why i created my connection maker app to write the code for me for sql statements and all the objects.  It eases the dreaded repitition of code writing for multiple tables.

     

    Would you mind posting a small, simple code sample for compact if it is any different from sqlclient?

     

    Thanks

    Jeff

    Sunday, January 6, 2008 3:18 AM
  •  

    I'd be happy to show you database creation and some before and after. One thing that you have to do is to add a project reference System.Data.SQLServerCE and Import: Imports System.Data.SqlServerCe

     

    Ok Here is how to create an empty database:

     

    Dim engine As New SqlCeEngine("Data Source='C:\Temp\CreateCompactDatabase\nDatabase.sdf'")
    engine.CreateDatabase()

     

    There is it.... and that's all there is to it.

     

    here are some code differences:

     

     

    ADO:

     

    Imports System.Data.OleDb

    Friend Class Query

        Private Con As OleDbConnection

        Private dr As OleDbDataReader

        Private Adapter As OleDbDataAdapter

     

        Friend Sub New(ByVal Connection As OleDbConnection)

            Try

                Me.Con = Connection

     

            Catch e As Exception

                MessageBox.Show(e.Message, "Query.New")

            End Try

        End Sub

     

     

    SqlServerCe:

     

    Imports System.Data

    Imports System.Data.SqlServerCe

    Friend Class SqlceNet

        Private Con As SqlCeConnection

        Private dr As SqlCeDataReader

        Private Adapter As SqlCeDataAdapter

     

        Friend Sub New(ByVal Connection As SqlCeConnection)

            Try

                Me.Con = Connection

     

            Catch e As Exception

                MessageBox.Show(e.Message, "SqlceNet.New")

            End Try

        End Sub

     

     

    There is no difference in Logic at all only datatypes which can easily be changed with some goos find and replace techniques.

     

    So a couple of more samples:

     

    ADO:

     

    'Insert a record into the deletedObjects Table Representing the
            ‘deleted MetaData

     

            Using InsertDelMetaDataCommand As OleDbCommand = New OleDbCommand( _

             "INSERT INTO " + Tables.DeletedObjects + "
              (EntityID,CreationDate,DeletionDate,RecordType) Values "
    + _

             "(@EntityID,@CreationDate,@DeletionDate,@RecordType);", Con)

                With InsertDelMetaDataCommand.Parameters

                    .Add("@EntityID", OleDb.OleDbType.Guid, 8).Value = EntityID

                    .Add("@CreationDate", OleDb.OleDbType.Date).Value = _
                          mdCreationDate

                    .Add("@DeletionDate", OleDb.OleDbType.Date).Value = DT

                    .Add("@RecordType", OleDb.OleDbType.VarChar, 12).Value = _

                          mdRecordType

                End With

                Try ' Step 2

                    Status = (InsertDelMetaDataCommand.ExecuteNonQuery() = 1)

                    Debug.Assert(Status)

                    If Not Status Then : Con.Close() : Return False : End If

                Catch ex As Exception

                    Con.Close()

                    MessageBox.Show(ex.Message, HDR + "Error writing Data Delete
                             Record"
    , MessageBoxButtons.OK, MessageBoxIcon.Error)

                    Return False

                End Try

            End Using

     

    SQLServerCe:

     

    'Insert a record into the deletedObjects Table Representing the deleted MetaData

     

            Using InsertDelMetaDataCommand As SqlCeCommand = New SqlCeCommand( _

             "INSERT INTO " + Tables.DeletedObjects + "
              (EntityID,CreationDate,DeletionDate,RecordType) Values "
    + _

             "(@EntityID,@CreationDate,@DeletionDate,@RecordType);", Con)

                With InsertDelMetaDataCommand.Parameters

                    .Add("@EntityID", SqlDbType.UniqueIdentifier, 8).Value =
                          EntityID

                    .Add("@CreationDate", SqlDbType.Date).Value = mdCreationDate

                    .Add("@DeletionDate", SqlDbType.Date).Value = DT

                    .Add("@RecordType", SqlDbType.VarChar, 12).Value =
                            mdRecordType

                End With

                Try ' Step 2

                    Status = (InsertDelMetaDataCommand.ExecuteNonQuery() = 1)

                    Debug.Assert(Status)

                    If Not Status Then : Con.Close() : Return False : End If

                Catch ex As Exception

                    Con.Close()

                    MessageBox.Show(ex.Message, HDR + "Error writing Data Delete
                      Record"
    , MessageBoxButtons.OK, MessageBoxIcon.Error)

                    Return False

                End Try

            End Using

     

     

     

    So there you are !

     

    And the table creation code:

     

    Friend Function CreateTables() As Boolean

            Dim Status As Boolean

            Dim MDTCmd As String = "Create Table Metadata (Name
            nvarchar(80),RecordType nvarchar(12),Author nvarchar(80),"
    + _

            "DataType nvarchar(12),URL nvarchar(255),Attributes int,DateCreated
            datetime,DateModified datetime,"
    + _

            "ReferenceCount int,EntityID uniqueidentifier PRIMARY KEY,ParentID
            uniqueidentifier,"
    + "DataID uniqueidentifier)"

            Dim Command As New SqlCeCommand

            Command.CommandText = MDTCmd : Command.Connection = con

            Try

                con.Open()

                Command.ExecuteNonQuery() : Status = True

            Catch ex As Exception

                con.Close()

                MessageBox.Show(ex.Message, "Failed to create new Metadatable",
                MessageBoxButtons.OK, MessageBoxIcon.Error)

                Return False

            End Try

     

            Dim DocTblcmd As String = _

            "Create Table DocumentTable (DataID uniqueidentifier PRIMARY
             KEY,DataAttributes int,DRefCount int,Document image,DateCreated” +_

             datetime,DateModified datetime)"

            Command.CommandText = DocTblcmd

            Try

                Command.ExecuteNonQuery() : Status = True

            Catch ex As Exception

                con.Close()

                MessageBox.Show(ex.Message, "Failed to create new Document
                Table"
    , MessageBoxButtons.OK, MessageBoxIcon.Error)

                Return False

            End Try

     

            Dim DeletedObjectsTblcmd As String = _

            "Create Table DeletedObjects(EntityID uniqueidentifier PRIMARY
             KEY,CreationDate datetime,Modificationdate datetime,"
    + _

            "RecordType nvarchar(12))"

            Command.CommandText = DeletedOBjectsTblcmd

            Try

                Command.ExecuteNonQuery() : Status = True

            Catch ex As Exception

                con.Close()

                MessageBox.Show(ex.Message, "Failed to create new DO Table",
                MessageBoxButtons.OK, MessageBoxIcon.Error)

                Return False

            Finally

                con.Close()

            End Try

            Command.Dispose()

            Return Status

        End Function

     

    End Class

     

     

     

     

     

    Sunday, January 6, 2008 3:52 AM
  • Renee,

     

    Thank you

     

    I think i have decided to move to compact now.

    I would have done it much sooner had i known.

    Never too late though

     

    Jeff

    Sunday, January 6, 2008 4:44 AM
  • Jeff,

     

    For you connection generator to be complete, you need to add connections for Sql Compact.

     

    MS, in failing to provide for a 64 bit Access engine is more or less saying that the end is near for Jet. That's very sad for me.

     

    Actually, we need a flexible console for compact SQL. What's included in SSMS I feel is deficient. For example, SQL has very rich importing and exporting capabilities. I can take an Access database, create an SQL database and import the data in a couple of minutes. There is no such capability for Compact SQL. More is needed.

     

    Don't forget to download the Docset

     

    http://www.microsoft.com/downloads/details.aspx?FamilyID=e6bc81e8-175b-46ea-86a0-c9dacaa84c85&displaylang=en

     

    I don't know what is going on with VS2008. SQL documentation is not integrated with SQL and it doesn't appear that Compact SQL is integrated With SQL. It was far better integrated in 2005.

    Sunday, January 6, 2008 4:52 AM
  • Renee,

     

    Yes, i realized after this thread that compact is going to be necessary for the connection maker.  I am guessing you saw my thread about the source code.  I am hoping that others will be interested in helping add functionality like this to it.  I can only do so much with everything else i have going on.  The reason i have asked those who are interested to show me they are first is that i realize that there are so many out there that are takers and not givers.  It is easy to take but difficult to give back.  I would like to see a small effort before i just give away my hard work.  I have invested many hours making it work well.  I am curious to see how many will be givers or just takers.

     

     

    I have not had a need to import data from one database to another but i guess that won't particularly bother me if the need comes up.  I can see that compact is not as good as sql in many ways but the fact that it does not require server to be installed is enough for me to switch.  The easy connection is what was making me want to use access originally.

     

    To be honest, i have only looked at the sql documentation a handful of times.  Mostly to find information on character bytes and get information on the different data types.  Aside from that i have been sticking to the basics that i know work and try not to get into anything too advanced.  I like sticking to what works.  That's why i dread the update method and when i see others trying to combine it with other methods that just seem to cause problems when they could just get to the basic code and make it work for them instead of them working for the method.  But that is just my view on it.  I have actually thought about suggesting the designer and update method to some because of the repetitive attempts to explain the code behind it all.  I hope that more will take an interest in helping with the connection maker so that they can see how the code works as they interact with the table and column selection.  Time will tell i guess.

     

    Thanks for the link

    Jeff

    Sunday, January 6, 2008 5:55 AM