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...
Printable View
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...
If "database" is small and security isn't an issue then text files might even be better.
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!).
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...
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.Quote:
Originally Posted by RhinoBull
Here's the comparison link:
http://www.microsoft.com/sql/edition...omparison.mspx
Quote:
With the release Compact Edition for desktop scenarios, Microsoft is positioning Compact Edition as the default local database.
I don't buy that - single file distribution is still better than whatever they try to promote.
My vote would be to plonk away.Quote:
Originally Posted by bushmobile
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.
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 ... :D
I've read that as well, although I've not seen any applications that use it as yet.
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.Quote:
Originally Posted by Jape
I'm going to quote you on the other thread where we were discussing this.
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?
As a guess, I would say you probably need to use Management Studio (as you need to use this for managing the Express edition).
cheers si, that would seem to be the solution -but I can't figure out how to get SSMSE to open one - it just keeps on wanting to connect to a serverQuote:
Originally Posted by Microsoft
On the "An introduction to SQL Server 2005 Compact Edition" page I only found this:
..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.Quote:
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.
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).
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.
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)Quote:
Originally Posted by Jape
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
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 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)Quote:
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 had the same issue.... Wasn;'t until I rebotted the machine that the option showed up (that wasnt in the documentation.)
-tg
@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).
The LOADATABASE function has code that looks like this:Code: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
Code: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
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 - cheersQuote:
Originally Posted by Jape
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
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?Quote:
Originally Posted by Jape
@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 :(
to answer my own question - it appears that the next version of C# Express will have builtin SQLCE support - Orcas CTP here I come...
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.Quote:
Originally Posted by bushmobile