Dot Net Installer that automatically install SQL 2005 Express

Download Installation_-_SQLEXPR.zip - 1.08 MB

Introduction

Programmer often has problem likes
1. Setup and Deployment that contains SQL Express 2005 can only install with default database instance name (SQLEXPRESS), where programmer often want a different instance name. Which is impossible by using Setup and Deployment project
2. When the database is install, some installation need programmer to manually tell where the backup database is, so it can restore, or morely programmer do it manually using Management Studio.
3. Restore often has trouble because the original MDF LDF location and restore destination are different, statement 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 occur when the SQL running restore try to look for path in another SQL database instance (usually it is the default instance) and since it does not has 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 install dotNet, windows installer, sql express all automatically

This project solves all troubles like above, enable 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 contain 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 contain a file name 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 show up in programmer application, for example programmer has a startup form name Login.cs, change the constructor as follow :

 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

1. First is Food, which is the application name that shows in your installtion progress
2. Second is JOHN, which is your computer name, you can just simply use "(local)" to replace "JOHN" to say that the installation 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 different than SQLExpress, but with this InstallationSQLEXPR class we can do it, and we can even specify a sa password by our own.
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 you suit.

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 success, 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 is already exist name SetupMakananSqlExpr.
2. In the application folder create a new folder name 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 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 is the dot 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 run your application, it checks the IsInstall and IsRestart parameter 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 install the application, it will install Windows Installer and DotNet if not exist, and when you first run your application it check if the database instance exist, 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 opotion) in order to be able to install using command line arguments, which this library use.
5. The installation process of SQL 2005 Express can be seen by user, but user just click next and watch the progress bar without configuring it since it is configured by the arguments passed by library.

History

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