stand-alone single user db

[bushmobile]

what's folks thoughts on the best database solution for a single user desktop app?

I'd just plonk it in an Access database (for ease / familiarity), but I was wondering if there was anything else I should be considering...

[RhinoBull]

If "database" is small and security isn't an issue then text files might even be better.

[si_the_geek]

As above, it depends on your needs... one option would be the new Compact edition of SQL Server, which is less functional (and much easier to install) than its bigger brothers - making it about the same level as Access, but with extra features (I think it has decent security for a start!).

[szlamany]

I agree with SI (actually I was trying to beat him to this post - but was searching for something).

http://www.vbforums.com/showthread.php?t=468627

Using SQL CE is a good option...

And encryption is a standard feature of it...

[RhinoBull]

For a single user SQL Server even though it is CE?! Way to much of an overhead... Common guys...

[szlamany]

For a single user SQL Server even though it is CE?! Way to much of an overhead... Common guys...

It's not a server based database. It runs in the processor space of your application. Kind of like ACCESS - but just more SQL-like.

[szlamany]

Here's the comparison link:

http://www.microsoft.com/sql/edition...omparison.mspx

With the release Compact Edition for desktop scenarios, Microsoft is positioning Compact Edition as the default local database.

[RhinoBull]

I don't buy that - single file distribution is still better than whatever they try to promote.

[Hack]

I'd just plonk it in an Access database (for ease / familiarity),

My vote would be to plonk away.

Once you have it built, and you discover a need for functionality that Access isn't giving you, it is no big deal to switch.

I have a single user stand alone Access app (my Code Library) that I toyed with upgrading to SQL CE. When I researched the additional functionality that I could get, I really didn't find anything that I needed, so, I have left in Access.

[RhinoBull]

I've just noticed something (referring to Steve's link) that somehow I failed to see before:

SQL Server 2005 Compact Edition
Embeddable in applications

If it's true then I may take my words back but until I see it in action ...

[Hack]

I've read that as well, although I've not seen any applications that use it as yet.

[Jape]

I always use SQLCE over access in my stand-alone apps. They behave just like access files so you can copy, back them up etc. as easy as access. The files required are also only a couple MB so imho SQLCE is perfect for using them in stand-alone apps. Only thing i miss are stored procedures.

Another thing that is very handy with SQLCE is that it integrates within visual studio IDE so you can create your tables etc. directly in visual studio. According to an msdn article ive read somewhere it also should be, depending on the situation, about 1.1 to 4.0 times as fast as access.

[szlamany]

I always use SQLCE over access in my stand-alone apps. They behave just like access files so you can copy, back them up etc. as easy as access. The files required are also only a couple MB so imho SQLCE is perfect for using them in stand-alone apps. Only thing i miss are stored procedures.

Another thing that is very handy with SQLCE is that it integrates within visual studio IDE so you can create your tables etc. directly in visual studio. According to an msdn article ive read somewhere it also should be, depending on the situation, about 1.1 to 4.0 times as fast as access.

Thank you very much for this input.

I'm going to quote you on the other thread where we were discussing this.

[bushmobile]

cheers for the thoughts guys, some definite things to mull over.

now for a very dumb question:

I've got the runtimes, got the SDK - how do I make a SQLce DB?

[si_the_geek]

As a guess, I would say you probably need to use Management Studio (as you need to use this for managing the Express edition).

[bushmobile]

cheers si, that would seem to be the solution -

You can manage a SQL Server Compact Edition database on a desktop computer or on a mobile device by using SQL Server Management Studio or SQL Server Management Studio Express. link

but I can't figure out how to get SSMSE to open one - it just keeps on wanting to connect to a server

[si_the_geek]

On the "An introduction to SQL Server 2005 Compact Edition" page I only found this:

Consider the following scenario. You have a computer that is running Microsoft Windows Server Code Name "Longhorn". You install SQL Server 2005 Service Pack 2 on this computer. In the Connect Database dialog box in SQL Server Management Studio, you create a new SQL Server Compact Edition database. In this scenario, you notice that the database is created in the %WINDIR%\SYSTEM32 folder.
Workaround for issue 5
Specify a path together with the file name when you create a new SQL Server Compact Edition database in SQL Server Management Studio.

..not ideal, but led to me checking out what was going on - I found that by clicking the "Options>>" button you can set the connection details, which may well include something useful.

If not, I can only suggest looking at Books Online (my version is a little out of date, so does not have info for CE).

[Jape]

Keep in mind:

- That you can do all the same stuff in Visual Studio that you can do with SQL Management Studio (so also creating a new SQLCE db)
- That if you want to add constraints you can only do it trough TSQL statements and not in VS nor SMSE (the logic behind this i fail to see, but ok.)
- That if you want to connect to/work with SQLCE databases you need the latest SP updates of SMSE (should be there already if you've installed it recently)

To connect to an SQLCE db in SMSE goto the Object Explorer -> Connect -> SQL Server Mobile.

[bushmobile]

To connect to an SQLCE db in SMSE goto the Object Explorer -> Connect -> SQL Server Mobile.

I go to the Object Explorer, click Connect Object Explorer and I get the attached - i can't see any mention of SQL Server Mobile (nor when i look in the options bit)

I basically just ran all the various MSIs, whether there was something else I should have done - i dunno

regarding having the latest versions, I d/l and installed all this SQL Server stuff today.

and regarding being able to do it from within VS2005 - i'm using C# Express, so that's not going to be an option for me (i think)


si - as you said, the documentation isn't ideal, and i've found little to no reference to this on the internet

[Jape]

My guess is you need SMSE with Advanced Services. I Know i had the same problem you had once, but i fail to remember exactly how i resolved it. When i now click that Connect button it gives me a drop down list to connect to "SQL Server, SQL Server Mobile, Integration Services etc."

and regarding being able to do it from within VS2005 - i'm using C# Express, so that's not going to be an option for me (i think)

I dont think that should be an issue, i sometimes work on VB Express when im not at home and there i can also work with my SQLCE Db from within VS (Server Explorer)

[techgnome]

I had the same issue.... Wasn;'t until I rebotted the machine that the option showed up (that wasnt in the documentation.)

-tg

[szlamany]

@Bushmobile

We did all the "create" stuff right in code - not sure if that's an option for you.

We call our SQLce DB a .DLH file (don't look here) so people won't easily know what it is - but it's encrypted anyway (a plus with SQLce).

        Dcn.ConnectionString = "DataSource=" & strBaseFolder _
                              & "\APC.dlh; Password=" & strPassword
.
.
. we try to open it - the following code only runs if it's not found
.
.
            Dim dse As New SqlCeEngine("DataSource=" & strBaseFolder _
                    & "\APC.dlh; Password=" & strPassword & ";encrypt database=TRUE")
            Try
                lblStatus.Text = "Creating database"
                lblStatus.Refresh()
                dse.CreateDatabase()
                Dcn.Open()
                lblStatus.Text = ""
                lblStatus.Refresh()
                If LoadDatabase(Dcn, LDcn) = False Then
                    Dcn.Close()
                    Dcn.Dispose()
                    System.IO.File.Delete(strBaseFolder & "\APC.dlh")
                    Cursor.Current = Cursors.Default
                    MessageBox.Show("Database did not sync!")
                    OpenDatabase = False
                    Me.Close()
                    Exit Function
                End If
                txtInput.PasswordChar = ""
            Catch ex As SqlCeException
                MessageBox.Show(ex.Message)
            Catch ex As SqlException
                MessageBox.Show(ex.Message)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try

The LOADATABASE function has code that looks like this:

        lblWhat.Text = "Syncing..."
        lblWhat.Refresh()
        Drc.Connection = Dcn1
        Drc.CommandType = Data.CommandType.Text
        Drc.CommandText = "Create Table APC (Created nvarchar(50))"
        booError = False
        Try
            Drc.ExecuteNonQuery()
        Catch ex As Exception
            booError = True
            MessageBox.Show(ex.Message)
        End Try
.
.
.
        Drc.Connection = Dcn1
        Drc.CommandType = Data.CommandType.Text
        Drc.CommandText = "Insert into APC values ('" & strDateTime & "')"
        booError = False
        Try
            Drc.ExecuteNonQuery()
        Catch ex As Exception
            booError = True
            MessageBox.Show(ex.Message)
        End Try

[bushmobile]

My guess is you need SMSE with Advanced Services. I Know i had the same problem you had once, but i fail to remember exactly how i resolved it. When i now click that Connect button it gives me a drop down list to connect to "SQL Server, SQL Server Mobile, Integration Services etc."

I had to install the version of SSMSE that comes with SQL Server 2005 Express Edition with Advanced Services, not the stand alone one - then I was able to choose SQLCE databases - cheers

however, having now had a look at SSMSE it appears that it doesn't really provide the sort of GUI for DB interaction that I can get from connecting to a mdb through C# Express (i.e. queries have to be built up from code, can't edit tables through typing, etc) - unless I'm missing something that is

I dont think that should be an issue, i sometimes work on VB Express when im not at home and there i can also work with my SQLCE Db from within VS (Server Explorer)

When I go to add a new data source in C# Express i can only choose mdb or mdf, not sdf, the SQLCE format, - are you able to choose it in your VB.Net Express version?


@szlamany: yeah, i know I can do it through code but I was looking for a more integrated visual solution - proving a bit of a nightmare though

[bushmobile]

to answer my own question - it appears that the next version of C# Express will have builtin SQLCE support - Orcas CTP here I come...

[Jape]

When I go to add a new data source in C# Express i can only choose mdb or mdf, not sdf, the SQLCE format, - are you able to choose it in your VB.Net Express version?

I've never actually created it in Express, only edited the tables, but if i can edit the tables i should also be able to add a new SQLCE DB i assume. What i do know is that you first need to switch the data source when you're in the add connection dialog. Instead of 'SQL Server (SqlClient)' you need to pick the 'SQL Server Compact Edition' first before being able to add .sdf to your project.