Would building an application using a Sql Server Database File (mdf) be a terrible idea?

Question

I'm working on a side project that would be a simple web application to maintain a list of classes and their upcoming schedules.

I would really like to use Linq to SQL for this project, but unfortunately the server environment I'm developing for only has MySql available. I've dabbled briefly with Subsonic but it just doesn't get the job done.

The database requirements for this application aren't that great, though, so I'm curious if using an MDF file in App_Data would be a workable solution. Otherwise, it appears I'm going to have to hand-code sql queries which I want to avoid at all costs.

Answer 1

I don't understand... what do you mean by "having an MDF file in App_Data"? You need a proper SQL Server installation for that to work. You can always use the free SQL Server Express for developing the application, and then move the database to the proper SQL Server once you are done. Check here.

Answer 2

More likely you'd put an Access database in App_Data. If you're using a MSSQL MDF file, you'll definitely still need either MSSQL or MSSQL-Express.

Your question is confusing, however. You seem to interchanging data access, ORM and the actual database. You can use SubSonic with MySQL, but you cannot use LINQ to SQL with non-MS databases or MS Access.

Answer 3

It appears that I was misunderstanding how mdf files are accessed through .net. There is no MS SQL Server available on the server, so it looks like I'm screwed.

Answer 4

One of the few differences between SQL Server Express and the "full" SQL Server is the ability to automatically attach to MDF files - what Microsoft call "xcopy deployment".

SQL Server Express is free (as in beer) so unless you have no administrator rights on the box for installation, this should work fine.

Answer 5

Take a look at Microsoft SQL Server Compact Edition. I believe you can work with MDF files without having to run a server. All code runs in process. I believe it has some limitations but it may work for you and I think it's free.

Answer 6

+1 for SQL Server Compact. It's free and there's no 'engine' in the sense of a full-time service, but you do have to deploy a runtime (it's just two .dll files).

Answer 7

To the best of my knowledge, you can attach directly to the MDF (called a "user instance", rather than attaching the MDF to a "server instance") only if SQL Server Express is installed on that machine. So your machine that has MySql on it would also have to run SQL Server Express.

Answer 8

+1 for SQL Server Compact. It's free and there's no 'engine' in the sense of a full-time service, but you do have to deploy a runtime (it's just two .dll files).

Does linq to sql work with that though?

Answer 9

you can't use SQL Server Compact with asp.net or web development

Answer 10

I've long since completed the project which prompted this question, but recently I've had another project come along with very minor data requirements, so I spent some more time experimenting with this.

I had assumed that Sql Server Express required licensing fees to deploy, but this is not in fact the case. According to Microsoft's website, you are free to use it with certain restrictions:

• Maximum database size: 4 GB
• Maximum memory used: 1 GB
• Maximum CPUs used: 1 (complete procs, not cores)

Sql Server Compact is a bad idea for web applications because it requires a hack to make it work, and it isn't built for the concurrent access you'd need for the web. But if your application can fit within the modest limitations of Sql Server Express, it works pretty well. And since it speaks regular T-SQL like its larger siblings, you can use Linq to SQL with it.

I hear that Linq to Sql support is now in the Mono trunk for the 2.6 release, so L2S' tight-coupling to Sql Server will likely be a moot point in the near future. I will either end up porting my code to use Mono's superior Linq to Sql implementation on the db of my choice, or go another route entirely (SubSonic has improved by leaps and bounds since I last tried it). But for the time being, Sql Server Express is a valid choice for very small database-driven apps.