.NET Installer that Automatically Installs SQL 2005 Express

• Download source - 1.08 MB

Introduction

A programmer often has problem like:

1. Setup and Deployment that contains SQL Express 2005 can only install with default database instance name (SQLEXPRESS), where programmer often wants a different instance name. This is impossible by using Setup and Deployment project.
2. When the database is installed, some installations need the programmer to manually tell where the backup database is, so it can restore, or mostly programmer do it manually using Management Studio.
3. Restore often has trouble because the original MDF LDF location and restore destination are different, statements like:

   "[Microsoft][ODBC SQL Server Driver][SQL Server]Directory lookup for the file
   failed with the operating system error 5(Access is denied.)."
   or
   File 'XXX_Log' cannot be restored to 'x:\yyy'. Use WITH MOVE to identify a valid
   location for the file.
   

   The first error often occurs when the SQL running restore tries to look for path in another SQL database instance (usually it is the default instance) and since it does not have permission to the folder, it generates Access is denied. The second error is when the original MDF location and restore MDF location are different.

4. Programmer wants a setup file, that installs .NET, Windows installer, SQL Express all automatically.

   This project solves all troubles like the above, enables a Setup file to install needed applications without user knowing how to create database, to restore, to configure, the connection string, and so on, and the application can do it all automatically.

Steps

1. Download the sample project, and extract it to for example D:\Software Pekerjaan\Makanan.
2. Download SQL 2005 Express Edition from http://download.microsoft.com/download/e/a/4/ea4b699b-bec4-4722-96d3-254580ed7f9e/SQLEXPR32.EXE Copy it to any folder, and from Command Line (CMD.exe), go to the folder that contains the file, and run "SQLEXPR32.exe /x" and a window will show up asking where to extract the files. Choose a location.
3. Copy the extracted files and folders to MenuMakanan Project's bin\debug\SQLEXPR2005 folder, the folder already contains a file named Food_Restore.bak which is the database backup programmer created, it must be put there.
4. Now we will use the InstallationSQLEXPR class from the main Form that shows up in programmer application, for example programmer has a startup form name Login.cs, change the constructor as follows:
   C#

   public Login()
          {
              InitializeComponent();
   
              InstallationSQLEXPR _ins = new InstallationSQLEXPR("Food", "JOHN",
              "MenuMakanan", "Food", "JOHNKENEDYDAL", "FOOD_RESTORE.BAK");
              if (_ins.IsDone == false) _ins.ShowDialog();
              if (_ins.IsRestart == true)
              {
                  Application.Exit();
                  this.Close();
                  return;
              }
          }
   

Remember to add using JohnKenedy.DataAccess; so that Installation class can be found. This Installation class accepts 6 parameters, which I explain below:

1. First is Food, which is the application name that shows in your installation progress
2. Second is JOHN, which is your computer name, you can just simply use "(local)" to replace "JOHN" to say that the installation is done to the computer running Setup.
3. Third is MenuMakanan, which is the new database instance that will be installed, by using Setup and Deployment Project, we cannot name a different database instance other than SQLExpress, but with this InstallationSQLEXPR class we can do it, and we can even specify a sa password by ourselves.
4. Fourth is Food, which is the database name that the application installs.
5. Fifth is JOHNKENEDYDAL, which is the sa password for the newly installed SQL EXPRESS, this password is required when you use Management Studio to connect to the database instance.
6. Food_Restore.BAK is your database backup file name, that is stored in MenuMakanan project bin\debug\SQLEXPR2005 folder. You can change this as it suits you.

As you notice, there will be code to check IsDone and IsRestart, left both of this checking as it is, since it is the condition that Installation class left for us to tell the main Startup Form what to do, whether to close the application or to show the form.

Now compile the application. Your compiled MenuMakanan.exe will automatically install your application because the configuration is IsInstall=1 and IsRestart=0, and if the installation succeeds, the value will be both 0 which is automatically set by the application and so the application will just start normally.

Create Setup and Deployment Project

1. Create a new Setup and Deployment project, in this sample already exists name SetupMakananSqlExpr.
2. In the application folder, create a new folder named SQLEXPR2005, select all files extracted from Step 2 (previous section) and drop to this newly created folder.
3. Add your database backup file, in this example it is Food_Restore.bak (from MenuMakanan bin\debug\SQLEXPR2005 folder, because the Installation class will look for the backup file in this folder when restoring it. After step two and three, it will look like:

   

   Click here for bigger picture.
4. Now right click SetupMakanan project and select properties, then click Prerequisites button:

   

And then select the second radio button, download prerequisites from the same location in my application. Click OK, and save.

5. Build your whole solution, and then build SetupMakananSqlExpr project, and you will see in SetupMakananSqlExpr project debug folder Setup.exe SetupMakanan.msi, and two new folders; one is Windows Installer 3_1 and another is DotNetFX35 which are the .NET prerequisites.

Now you can ship these files to a cd or zip it for distribution, the Setup application will install prerequisites and then your application to the folder. And when user runs your application, it checks the IsInstall and IsRestart parameters to determine whether database installation is needed, if yes, then it will install it for user and then ask for restart and after restarting it will check again whether to attach database or not, if yes then it will attach, and finally it will run your application.

When user installs the application, it will install Windows Installer and .NET if it does not exist, and when you first run your application, it checks if the database instance exists, if not it will install the database instance, and restore the backup database to the new database instance.

Notes

1. RestoreDatabase method in library can restore to database though the backup original MDF location is different to restore one (different database instance/PC)
2. Library has JohnKenedy.DataAccess namespace and must be included to programmer Windows Form project
3. SQL 2005 Express cannot be installed with different database instance other than SQLExpress, but this library can do it because of using ProcessStartInfo and install it when application finished install and first run
4. SQLEXPR32.exe provided by Microsoft must be extracted (with /x option) in order to be able to install using command line arguments, which this library uses
5. The installation process of SQL 2005 Express can be seen by user, but user just clicks next and watches the progress bar without configuring it since it is configured by the arguments passed by the library.

History

This library is not fully ready for Installation, instead the library has Data Access feature - the article can be found at http://www.codeproject.com/kb/library/johnkenedy_library.aspx.

I have created another article that installs MSDE and attaches database to MSDE too, you can read about it at http://www.codeproject.com/KB/dotnet/_Net_Installer_With_MSDE.aspx?
msg=2654769#xx2654769xx.