How to create a SQL Express DB for app

[Elmo Watson]

I've got VS.Net 2005 and SQL Server 2005 on my computer (no Express
products)

I'm creating a new application that I would like to have the database
separate from a server (like an SQL Server Express database), to be
distributed with the application.

I've created DBs plenty of times, but I don't really know how to create one
of these 'detached' type databases...

Can anyone help me on how to create one, and save it to my app directory, so
it can be available to the application, somewhat like MS Access?

(or is this reasonable? Should I just stick to MS Access?)

 

[Norman Yuan]

There is no "detached" type of database with SQL Server. You need to have
database (contained in *.mdf/*.ldf files) attached to a SQL Server/Express
so that database becomes a database that you can access.

However, with SQL Server Express, you can attach the database when your
application needs to open a connection to SQL Server Express and detach it
without requiring user having admin priviledge. In this case you must enable
USER INSTANCE. More importantly, you must understand what USER INSTANCE is
and if you really need it for your application (for example, data is only
accessible to single user, and you still need SQL Server Express
preinstalled on running computer,...)

You can develop/design you database with full SQL Server on your computer,
and deploy the database to production/user box by detaching/attaching the
*.mdf/*.ldf files or by backiing up /restoring the database to other
computer. If you do use SQL Server express USER INSTANCE, you simply copy
the *.mdf file to running computer and do the attaching within your
application (assuming the computer has SQL Server Express installed already.
You can also script out all your SQL Server database and run the scripts on
the user/production computer to create your database during your application
installation.

SQL Server/Express is a server software package, not like file base Access
database, you cannot simple copy it and use it as database.

 

[Paul]

To create you can use the .net 2.0 smo classes.

string serverName = @".\SQLEXPRESS";
Server smoServer = new Server(serverName);
Database dbNewDB = new Database(smoServer, "MyDB");
dbNewDB.DatabaseOptions.AutoClose = true;
smoServer.DetachDatabase("MyDB", false);

Then you can open the .mdf file directly using AttachDBFileName in the connection string. Add tables with ado.net, etc.

I too have been enticed by the shiny lures of slq server express. The devil is in the details. If you are writing an app for a small computer-literate user base you might be able to succeed. In my case, I have a large group of dunderheaded users that don't know a disk drive from a peach cobbler, so I am considering going back to Access for a relational store. I have not had Access fail once in almost 10 years, with hundreds of users of my windows application. Do not underestimate the hassles of installing and supporting sql express. It's so frustrating that microsoft is so close to an elegant killer product but can never seem to quite get there. I blame the management.