• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Embedding MSSQL with a Windows Forms Application

Hello all.

I am currently working on an application that allows my users to connect to a database and change some basic values.

Currently, that database is in the cloud and I connect to it using System.Data.SqlClient and a simple connection string. However, I need to bring that application to the users desktop.

How can I embed a local SQL instance into my program that can be installed with my program?
  • 4
  • 4
  • 2
1 Solution
Minh Võ CôngCommented:
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.
DragonseerAuthor Commented:

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.
Minh Võ CôngCommented:
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Jacques Bourgeois (James Burger)PresidentCommented:
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.
DragonseerAuthor Commented:
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?
Jacques Bourgeois (James Burger)PresidentCommented:
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.


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.CommandText = "EXEC sp_attach_db @dbname = 'PhotoCat', " &
	 "@filename1 = N'" & fichiers(0) & "', " &
	 "@filename2 = N'" & fichiers(1) & "';"

Open 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"
DragonseerAuthor Commented:
A couple of questions here:
How do I detach the database? I don't seem to see any option in the Management Studio
Could you please elaborate on "When the application starts, check if the database is already copied in the users directory (required in Vista and Windows 7)."?
What are the alternatives? I ask because I slightly dislike the idea of the application checking for the database every single time, since the operation only needs to be done once.
Jacques Bourgeois (James Burger)PresidentCommented:
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

Open 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.
DragonseerAuthor Commented:
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);

Open in new window

Jacques Bourgeois (James Burger)PresidentCommented:
Unfortunately, I am not a "system geek". I develop my applications either for personal use (and usually do not have those type of problems) or for customers that have an IT department that takes care of those issues. So my expertise in those matters are very limited.

The use of Integrated Security = SSPI has never been really clear to me. In some places, it is specified that it is the same as True. In other places, they seem to implicate that it is used when working when you call the database from ASP.NET or a network service.

Personnally, I have always used the ConnectionString that is generated by the DataConnections entry in the Server Explorer. It generates True when you request Windows Authentification. It has always worked for me.

I would thus cross my fingers, lose my eyes, and try to change SSPI for True.

I also do not use the database through the ConnectionString. Old habit and routines I had before the AttachDbFileName became available, I have my own routines that attach the database permanently to the server by calling the sp_attach_db stored system procedure in SQL Server. There might be issues with AttachDbFileName that I am not aware of.

I hope that True instead or SSPI will solve your problem, otherwise, let's hope somebody that knows SQL server administration better than I do will be able to give you the solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now