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

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.

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
			LanceDéveloppement()
		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"))

		Try

			cmd.Connection.Open()

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

			'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) & "';"
				cmd.ExecuteNonQuery()

				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.
			Throw
			Return False

		Finally
			If cmd.Connection.State = Data.ConnectionState.Open Then
				cmd.Connection.Close()
			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

Select allOpen 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.

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.

/gustav

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.

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.