Including Sql Express DB with Winforms Application

We are thinking of using a Sql Server Express database (.mdb.mdf) as a data resource in a Windows Forms application that we will be distributing to clients. When the application is built, the mdb file is copied to the bin directory.

In order for clients to access this database, will they need to have an instance of the SQLEXPRESS server installed on their computer? Or since the .mdb.mdf file is being accessed from a .Net 2.0 application, there is no need to install a separate db component?

If I do have to separately install the db component on the client end,
is this the way to go about it? This? Or is there a different/better way?

If your file really is .mdb, this is an Access file, and your program will connect to it with out any trouble.

SQL Express files are .mdf

For SQL Express, just drop your data file into your resources directory, and it will be published with your project. (This works with any file you want to include in your project, like graphic files used in your reports, etc.)

Set your Build Action to "Content" and make sure you select "Copy if newer", and not "Copy Always" as this will erase any data you have everytime you test your program.

- Richard.

|||

When you do distribute you can package up the SQL Engine (SQL Express) into the installer... but yes the clients do need the engine installed on there system. Now depending on how you are access the database you have several options. With express you have two ways of connecting to the database, the first is to just install the engine and attach the files to that engine, then you can connect to the database from the client as well as from other clients.... ie remote clients (After turning on the remote Access) using the one database server. The other option is to use a User Instance of the database. With this method the database is attached to the engine by the SQL Native client at runtime. This means that when you start the application that you have developed, the SQL Native client connects using the Connection string you provide (With the Attach option) and attaches the DB.... when the app is finished with it it will detatch the database. One problem with this is that generally it is set to single user and only the security context of the application running the App will be able to access the database.

So in summary if only the client needs to connect to the DB at the one time the best option would be to dist the sql engine and client app seperatly and allow the sql native client to do it's job and attach the db.... but if more then one client needs to access the same database at the one time You should attach the db your self and use the standard client server setup.

Hope this helps..

|||Richard - Thanks, you are right. I meant to say "mdf"|||Thanks Glenn.

In my situation, we will have an instance of SQL Express installed on the client machine, with only that one client connecting to the database. So you think that I should connect to the DB with a connection string that attaches the DB, and when the APP is finished it will detach the DB? What is the format of such a string. Right now I am using a connection string of the following format:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\db.mdf;Database=DBName;Integrated Security=False;User Instance=False;User ID=USERNAME;Password=PASSWORD;Connect Timeout=30;

As I understand it, this connection string will attach the DB to the database with the name provided in the "Database" parameter. In the future, the DB will already be attached to the server under that name. How should the connection string be modified so that when the APP is finished it will detach the DB? Will this happen when the application closes? Or does this happen before and after every single database interaction (thus, while the application is open, you could be attaching and detaching the database thousands of times)? If so, what kind of extra overhead does this constant attaching and detaching cause?

Additionally, we would like to prevent any access to the database unless it is with the Username and Password (ie: we do not want them to access the database directly, rather through the application that we provide. In the application itself, the connection string will be encrypted). Is there a way to prevent the user from connecting to the database through the SA account or the Admin account for the computer, to ensure that all connections are done only using the username and password?