I was previously using a database in SQL Server CE. That's kind of easy, because I can have a database in a simple .sdf file and deploy it with my application (either empty or with data already added).
But what if I want to change the database to a SQL Server Express one?
Of course, if my client already has the database I need, I don't have to modify anything (well, maybe the connection string).
What should I do if I want to deliver an application to a client that requires a local SQL Server Express database created? Is there a way of doing this with .mdf files?
Of course, I have the SQL code for creating all the tables, with the corresponding foreign keys, etc, in case it is necessary.
I suppose that is something lot of developers have faced with, but I don't really know how to do it.
Please, do not suggest using SQLite, MySQL, or other database. I'm asking this question because I want to know how to do it particulary on SQL Server.
You can connect to an SQL Express with attached DB file:
data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
Also you can backup your db and then restore it when deploying.
UPDATE But if you don't want to copy data, only structure, I suggest that you should go on with SQL scripts. You can check if the DB exists and create it if it is not. You can even init some tables with bulk inserts.
if db_id('dbname') is null BEGIN CREATE DATABASE ... etc
Depending on type of your application you can either specify your scripts for deployment (AFAIK, VS2010 offers this for web applications) or call sql.exe with your script as input as a deployment step.