Introduction
A programmer often has problem like:
- 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.
- 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.
- 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.
- 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
- Download the sample project, and extract it to for example D:\Software Pekerjaan\Makanan.
- 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.
- 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.
- 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:
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:
- First is Food, which is the application name that shows in your installation progress
- 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.
- 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. - Fourth is Food, which is the database name that the application installs.
- 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. - 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
- Create a new Setup and Deployment project, in this sample already exists name
SetupMakananSqlExpr
. - 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.
- 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:
- 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.
- 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
RestoreDatabase
method in library can restore to database though the backup original MDF location is different to restore one (different database instance/PC)- Library has
JohnKenedy.DataAccess
namespace and must be included to programmer Windows Form project - 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 - 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
- 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.