Solved

Embedding MSSQL with a Windows Forms Application

Posted on 2011-09-15
10
249 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:Dragonseer
  • 4
  • 4
  • 2
10 Comments
 
LVL 15

Expert Comment

by:Minh Võ Công
ID: 36546981
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.
0
 

Author Comment

by:Dragonseer
ID: 36546989
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.
0
 
LVL 15

Expert Comment

by:Minh Võ Công
ID: 36547002
0
 
LVL 40
ID: 36547043
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.
0
 

Author Comment

by:Dragonseer
ID: 36547549
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?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 40
ID: 36547787
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

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"
0
 

Author Comment

by:Dragonseer
ID: 36547839
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.
0
 
LVL 40
ID: 36550188
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.
0
 

Author Comment

by:Dragonseer
ID: 36564394
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();

Open in new window

0
 
LVL 40

Accepted Solution

by:
Jacques Bourgeois (James Burger) earned 500 total points
ID: 36564513
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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now