how to write a portable application using an SQL Database with Visual Studio

Posted on 2012-03-12
Last Modified: 2012-05-10
I have been programming in visual basic for years, and I typically use Access Databases as containers for my data. My most recent project has forced me to save some image files to my database, and allthough I have kept them to a minimum, size is quickly becoming an issue. I know that I can use an SQL database with SQL Express, and can double the available size of my database to 4 GB, and much more than that if my clients want to use a dedicated SQL server with a full version of SQL on it...

My question is how do I go about using an SQL database in my app, configuring the connection string, and copying the database to the target computer... so that my application remains portable, and easily installed (Without requiring me to manually configure their server after the app is installed)...

I am sure this can be done, but as I said I have been using access databases exclusively for years, and don't have any idea of how to transfer an SQL database to another PC programatically, or how to configure the connection string so that it will reconnect  once it has been copied.


Question by:CyberSpace100011
LVL 39

Expert Comment

ID: 37713221
May be it is not answer to your question, but idea to store images in DB seems me not very good. Image files can be placed in some (network) folder and in DB you can store only path to this folder and image name.
LVL 40
ID: 37713353
There are many ways to do that, depending on the situation. Here are a few possibilities.

First of all, you might consider not storing the images in the database. I have worked in the audio-visual field, and created a couple of databases that dealt with photo inventories, and this is usually the method that we preferred.

Working with images in a database can be a pain and can be bad on performance. And with the size of picture files that seem to grow continually as people are uselessly going crazy about the number of pixels, even 4GB can become a limit. You might prefer to store the images in a directory and simply save the name of the file in the database. Add the path if the images need to be stored in many different directories.

That way, you could still keep working with Access, although I would recommend that you make the step up to SQL Server. Access is rapidly losing ground for serious development, and SQL Server is a lot more powerful. Just too bad that you have to write most of your SQL. The query generator in Access made you work a lot faster for the generic SQL that is used most of the time.

Then, depending on the type of use (multiuser vs single user), you could create a local SQL Server database. This is a type of connection where the database attach automatically to the local server, which is usually the way SQL Server Express is installed. Make a search for 'local data overview' in the documentation to get more information.

Another way is to attach the database through your code. This is the way I install my sample database when I give training in-house. Here is the code I use. Sorry for the comments in French, this is code I use in my training that is always in French. You might try to send this through an online translator. What it does is first check to see if the database is already installed on the server, and if not, it installs it.

fichiers contains the name of the 2 files that contains the database (.mdf and .ldf files)

PhotoCat is the name of my database (by coincidence, my VB course sample database is used to catalog photos)

JBFI.Photo.PhotoData.ServeurSQL is a constant containing the name of the server. I set it manually in the code at the beginning of each training session, but this could be something that is simply defined through a dialog or a text file.

		'Récupère les noms et la localisation des fichiers de base de données, en les installant
		'  dans un répertoire accessible au besoin
		Dim fichiers As System.Collections.Specialized.StringCollection = FichiersSQL()

		If fichiers Is Nothing Then
			'L'utilisateur a décidé de ne pas copier le fichier dans un répertoire public
			Return False
		End If

		'Dans mon environnement de développement personnel, lance le serveur au besoin
		If System.Security.Principal.WindowsIdentity.GetCurrent.Name = DevEnvironnement Then
		End If

		'Attache la base de données au serveur au besoin

		Dim cmd As New SqlCommand("Select * From sysdatabases Where name like 'PhotoCat'", New SqlConnection("Data Source=" & JBFI.Photo.PhotoData.ServeurSQL & ";Integrated Security=True"))



			''Réinitialise pour tester pendant le développement
			'cmd.CommandText = "EXEC sp_detach_db 'PhotoCat'"
			'cmd.CommandText = "Select * From sysdatabases Where name like 'PhotoCat'"

			'Make sure that the database is attached to the server

			If cmd.ExecuteScalar() Is Nothing Then

				'BD non présente sur le serveur. On demande confirmation pour l'attacher.
				If MessageBox.Show("Vous vous apprêtez à attacher la base de données d'exemple (PhotoCat) au serveur " & JBFI.Photo.PhotoData.ServeurSQL & "." & Environment.NewLine &
				 "Vous devrez la détacher manuellement une fois que vous aurez fini de travailler avec l'application d'exemple." & Environment.NewLine &
				 Environment.NewLine &
				 "Si le nom de serveur mentionné ci-dessus ne correspond pas au vôtre, annulez tout et spécifier le nom du serveur utilisé dans la variable ServeurSQL, définie dans le fichier PhotoData du projet Photo.", Application.ProductName, MessageBoxButtons.OKCancel, MessageBoxIcon.Information) = DialogResult.Cancel Then
					MessageBox.Show("Vous devrez relancer PhotoCat en utilisant une configuration différente que ""BD SQL Server"".", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
					Return False
				End If

				'Attache la BD au serveur
				cmd.CommandText = "EXEC sp_attach_db @dbname = 'PhotoCat', " &
				 "@filename1 = N'" & fichiers(0) & "', " &
				 "@filename2 = N'" & fichiers(1) & "';"

				Debug.WriteLine("PhotoCat : Base de données attachée au serveur.")

			End If

		Catch ex As Exception
			'Une erreur ici est habituellement causée par un mauvais nom de serveur. Pour corriger, activez le menu contextuel (clic
			'du bouton droit) sur la variable ServeurSQL dans la ligne qui suit, allez à sa définition et ajustez le nom du serveur
			'pour celui utilisé dans votre environnement.
			PhotoData.ServeurSQL = PhotoData.ServeurSQL	'Inutile, mais aide à la navigation si on a besoin de régler le nom du serveur au début du cours.
			Return False

			If cmd.Connection.State = Data.ConnectionState.Open Then
			End If

		End Try

		My.Settings.PhotoCatConnectionString = PhotoData.ConSqlClientSqlServer	'Ajustement nécessaire pour LINQ to ADO, parce que la ConnectionString change en fonction de la salle de cours

		Return True

Open in new window

This works no matter if I am working on a local SQL Server Express server or on the big production server in the company where I give my training.

As for the connection string, there are many scenarios depending on the situations.

With SQL Server Express, Microsoft recommends to always make an installation by default, and use Windows authentification. The default server name is then .\SQLEXPRESS and you can use the same connection string everywhere.

If the server name changes from place to place or if you need specific login credentials, then it would have to be provided from outside the application. One way to do that is to use a UDL file. To make one, you simply create an empty .txt file and rename it with the .udl extension. Double-click on the file, and you will have an interface that enables you to define a connection string. That job could be done by the administrator everywhere where you install your application. You connection string then becomes simply something like "File Name = PhotoCat.udl". Add the path to the file if it is not installed in the application working directory. The system will open the .udl and retrieve the connection string from there.

There are techniques I use, there are others. It is really a case by case scenario.
LVL 49

Expert Comment

by:Gustav Brock
ID: 37713459
A very simple option is to store the pictures in a separate Access database with just one table containing a picture field and a unique Id you join to the relevant table in the otherdata-database. Of course, you are still limited by the max. size of an Access database.

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

LVL 84
ID: 37713845
Note also that many installers can automate the installation of SQL Server, so your initial install program could add a new instance of SSE if desired (which many prefer). From there, you'd just run your DDL scripts to generate the database, and populate it as needed with INSERT statements.

Author Comment

ID: 37930040
I apologize for my delay in getting back to you, I had a situation that kept me tied up on another vastly different project for a while, and couldn't find the time to get back...
All of these solutions help somewhat... but let me be a little more specific ....

For starters, I will be storing all of the images inside of the database... I have my reasons for this, but they are irrelevant at this point... my real issue is  duplicating my database on the target computer, and archiving the data for storing it offsite and / or restoring that data if and when needed...

 LSMConsulting mentioned running a DDL script to generate the database if it didn't exist... This is what I need to know how to do...

I have to be able to install SQL Express (Or whatever flavor would work the best) on the target machine (I suppose I can do this with the visual studio Setup and Deployment wizard by checking it as a prerequisite to my app when building the installer)... Then I need to generate the database, or copy it (This is what I typically do using access, as I can just copy the file from the installer... but how does this work in SQL? ... and Lastly, I need to be able to create / Restore backups of the data, which I suppose is loosely related to generating the DB...  All of the other ins and outs I believe I can deal with, but these items (Specifically the last two I listed here) elude me.
LVL 39

Assisted Solution

als315 earned 250 total points
ID: 37932060
You can follow these steps:
1. Install SQL Express on your computer (Full version with Management studio and tools)
2. Create New DB in separate folder
3. Import all your tables from Access DB with Import and Export WIzard.
4. Create connection to your Access FE and test it.
5. Detach DB from SQL Server.
6. Copy content of folder with created DB to new computer with installed SQL
7. Use JamesBurger's code to attach DB to New SQL server and your FE.
Read also this:
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 37932381
Is your database going to be pre-populated? If so, then you can deploy your .mdf file and reattach, as suggested earlier, although there are some gotchas with that. See this posting below:

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

947 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