Embedding MSSQL with a Windows Forms Application

1. In you MSSQL server you create an account for the client connect to your database.
2. All client app connect to MSSQL server via that account to retriave data.

minhvc:

That is the currently configuration. I have a MSSQL server that I am connecting to and getting my data from.

I need a local installation of MSSQL, on the users machine, who do not have internet access. I want the program, when installed to deploy its own SQL instance to which it then connects to.

you can try:
http://msdn.microsoft.com/en-us/library/bb264562(v=sql.90).aspx

It's very easy if you use ClickOnce deployment.

If the deployment is done in-house, ClickOnce is usually the most interesting option anyway, because it is the easiest way to update the applications when there are new versions. On your side of the funnel, it takes but a few seconds to send the new version to your server, and the users are told of a new version the next time they start their program.

You prepare a ClickOnce deployment trhough the Publish tab of the project's Properties window. Simply click all the buttons in orders to see and set possible options. For simple programs, the basic options might already be set properly when you start to look at them. Most options are evident at first sight. For the others, F1 is usually sufficient.

SQL Server Express can be flagged for automatic intallation under the Prerequisites button, as simple as that. Usually, Visual Studio has detect that you use SqlClient in your application and the option is already checked when you open the prerequisites.

Hello JamesBurger.

Thanks for your reply. ClickOnce is an awesome system that I was completely unaware of until now. My follow up question would be: how do I tell the installer how to set up my database so that my program can correctly access the tables that I need it to?

There are many different ways to do that. Its late for me right now, so I will be brief. Come back if you need more.

You will find many discussions on the subject if you Bing or Google "SQL Server database" ClickOnce.

Personnally, my preferred way is the following:

Detach the database from the development server.

Include the database files (.mdf and .ldf) in the deployment.

Deploy

When the application starts, check if the database is already copied in the users directory (required in Vista and Windows 7).

If yes, then the database is already set to go.

If no, copy or move the database files from the installation directory to a user directory.

Attach the database to the server by calling the sp_attach system procedure, where fichiers(0) and (1) are the path and name of the 2 database files:

Dim cmd As New SqlCommand("", New SqlConnection("<ConnectionString>")
cmd.Connection.Open
cmd.CommandText = "EXEC sp_attach_db @dbname = 'PhotoCat', " &
	 "@filename1 = N'" & fichiers(0) & "', " &
	 "@filename2 = N'" & fichiers(1) & "';"
cmd.ExecuteNonQuery()
cmd.Connection.Close

Select allOpen in new window

The database is ready to use.

It might be useful to know that by default, SQL Server Express use the name of the computer .SQLEXPRESS

So the following server name usually works by default in the connection string: ".\SQLEXPRESS"

A couple of questions here:

To detach a database from Management Studio, right click on the database. The Detach is at the top of the Task menu.

-----

I used to leave the database files in the application directory in Program Files, but that does not work anymore. Because of increased security in Windows Vista and Windows 7, the database needs to be located in a directory that is available for read and write operations.

So in those environments, I use another technique. I distribute an empty copy of the database (or one that has just the minimum to be started) with application and leave it in the application directory, but before using it I copy it to a Public directory so that it can be used according to the security constraint.

"checking for the database every single time" is nothing. You usually do a If File.Exists before opening a file to make sure that it has not been deleted, and you do it everytime the user wants to open the file, which may be many times during the same run of the application. Simply do a If File.Exists once in each run to make sure that the database files are present will slow things just a few milliseconds. It is also better than using exception handling in case the files would have been deleted by mistake.

Not only is something like that required for the newer operating systems, but it gives you some latitude for differnt types of installations. As an exemple, if you want different users on the same computer to each have their set of data, simply copy to the user's application data directory instead of a public directory.

Here is a piece of sample code. It assumes that the directory in which you want to copy already exist. You could use the same technique to create the directory if needed:

Public Const DossierVista As String = "C:\Users\Public\Data\Cours VS2010\"
Public Const BDSqlBase As String = "PhotoCat.mdf"
Public Const BDSqlLogBase As String = "PhotoCat_log.ldf"

If Not System.IO.File.Exists(DossierVista & BDSqlBase) Then
    System.IO.File.Copy(BDSqlBase , DossierVista & BDSqlBase)
    System.IO.File.Copy(BDSqlLogBase , DossierVista & BDSqlLogBase)
End If

Select allOpen in new window

Is this really a problem if this code runs everytime the application starts, specially since it will go in the If only the first time.

As a purist, I would find this stupid. But as a programmer that needs to deal with real life and real environments, I can easily live with that. Since ClickOnce does not leave you any control on where the files are installed, this is the best solution I have found. Very small price to pay for what ClickOnce brings to the table.

-----

Yes there are alternatives, this is why I suggested a search for "SQL Server database" ClickOnce.

The solution I am proposing is probably the simplest, and for the type of applications I am doing, this has suited me in the 3 applications for which I had to deploy the server with the application since Windows Vista exists.

Hello James Burger.

I have almost completely implemented this solution but I am coming up against a roadblock now.

How did you handle permissions ?

I am getting the following error message:
Directory lookup for the file "C:\Users\User\AppData\Program\dataBase.mdf" failed with the operating system error 5(access is denied.).
Cannot attach the file "C:\Users\User\AppData\Program\dataBase.mdf" as database "dataBase".

I've done some reading that this might be an access issue. ClickOnce installs MSSQL with the Network Service account and, even though I add that account to my database file, when the program runs and places the file in the AppData folder, it losses those permissions.

My code as follows.
 

static string dbLocation = Application.LocalUserAppDataPath + "\\dataBase.mdf";
   
if (!File.Exists(Application.LocalUserAppDataPath + "\\dataBase.mdf"))
{
                    System.IO.File.Copy(Path.GetDirectoryName(Application.ExecutablePath) + "\\dataBase.mdf", Application.LocalUserAppDataPath + "\\dataBase.mdf");
                    System.IO.File.Copy(Path.GetDirectoryName(Application.ExecutablePath) + "\\dataBase_log.ldf", Application.LocalUserAppDataPath + "\\dataBase_log.ldf");

                }


                connectionString =
                    "server = localhost\\SQLExpress;" +
                    "Integrated Security = SSPI;" +
                    "AttachDbFileName=" + dbLocation + ";" +
                    "Database = dataBase;";

                connection = new SqlConnection(connectionString);
                connection.Open();

Select allOpen in new window