Has anyone tried using Compact Sql or Sql Server Express as datastore

Topics: Business Logic Layer
Jun 2, 2009 at 8:35 AM

I was wondering, if anyone had attempted to the standalone file option with Sql Server Express for a datastore? This would make it much easier to move the datastore around, when testing?

It really is just another alternative to SqlLite and VistaDB, I know.

Alternatively, is it even possible to use the Compact Sql Server from Microsoft for web application?

The reason I am asking, is that I am comfortable with the operation and use of Microsoft's database, but I only have the option of adding 1 database to my web host, and I operate 3-4 blogs on that domain (each in its own subdomain).

Jun 2, 2009 at 5:02 PM

SQL Compact was designed for mobile and desktop applications.  From what I understand, it's not possible to use it in web applications.

SQL Server Express is a trimmed down version of SQL Server and would have to be running on your hosted server, but if your server is running the full SQL Server, this option becomes moot.

I'm big on Microsoft stuff, so I was hesitant to jump to SqlLite and VistaDB, but I found using them was not much different than using SQL Server.  The syntax is a little different, but only a small learning curve.

Because of limitations with my hosted server, I couldn't use SqlLite (my preference), so I wound up going with the XML files.  If you don't want to mess with learning a new database, I'd suggest this method--moving data from "test" to "production" is a little more difficult than copying a standalone database, but it works.

Jun 3, 2009 at 6:23 AM

Right, that is my understanding too, in fact, I made an experiment, and asp.net does not even allow me to use a compact database so that idea is out.

The idea of a user instance (as I have discovered it is called) in Sql Express is possible on my host in as many instances as I need, i.e. one for each blog, whereas, due to limitation in my agreement regarding the host, is limited to 1 "full" database total.

I am using the xml files as of now, but i sometimes have difficulties connecting via ftp to my host, and it is a drag to copy/update through the online control panel. Switching to a database (preferably Sql Server 2005, as I am very familiar with this, although not as user instances) is preferable. 

I'll figure it out, I was just wondering if anyone had any experience with it. :)

Jun 3, 2009 at 1:40 PM

SQLite requires that your application be running in a full trust environment, but if that is not a limitation on your host, I'd encourage you to give SQLite a try.  The instructions in the Web\Setup\SQLite folder are simple to follow, and there are a number of simple admin tools out there if you need to poke into the database.

Jul 23, 2009 at 6:04 AM

what about this scenario:

you have a hosted provider that gives you an sql server db.  But you want to do some development on your local box, say for a new control.  And since express is waaay cheaper than a full-blown copy of sql server it would seem to be a good idea to start with express for light development.  So would SQLite still be the prefered cheap solution for a developer in this scenario?