Hi,

I have been developing an application for a while (in C#.NET) using an Access database. The reason for an Access database is simple: there's just a single database for the entire application and the database is stored in a simple file (mbb file). The end user doesn't need any additional programs installed for my application to work.

Recently however I have expanded my application quite a bit, and after doing some calculations on the amount of data I will be gathering, I got to quite a large number... This is probably an overestimate, but in the worst case scenario my database will grow by about 2GB per month. As far as I know Access can only handle 2GB (a bit less due to system tables) so that is out of the question. So, I am now trying to switch to SQL Server instead.

However, I don't want my end users to have SQL Server management studio installed. SQL Express is fine (I can let that install automatically with my application's installer, right?) but obviously the end user should not need anything more than that.

Is it possible to use an SQL Server database similar to how I have been using my Access database? In other words: the database is a simple file (I think an mdf file for SQL Server?) that I simply deploy with my application (I can place it in the application's AppData folder or something) and point to that file via the connection string.

I have been trying this for a while, but I don't have a lot of experience with SQL Server. The only experience I have is with existing databases that other people have installed on my work laptop for me.

I have managed to create an MDF file (along with a log file) via SQL Server Management Studio 2008 (I do have this, but my end users shouldn't need it) but I am unable to connect to it. I've tried various connection strings, a few from www.connectionstrings.com, as well as adding the MDF file to my solution in which case Visual Studio wants to generate either the Entity Framework classes or a DataSet or something, and in this case it also gives you a connection string. But none of them work, I keep getting different errors, some mention being simply unable to connect, some mention not finding the database, etc...

Example connection string I've tried:
Code:
Data Source=.\SQLEXPRESS;AttachDbFilename=%PROJECT%\Database1.mdf;Integrated Security=True;User Instance=True
(In my code, the '%PROJECT%' is replaced by the project directory; the resulting path is correct)

My question basically is: how do I create a SQL Server database MDF file which I can deploy with my application (SQL Express can be a prerequisite if required) to a known location and connect to that database file?

Thanks!