Deploy the SQL Server database in the. NET Installer

Reprinted csdn

Directory

L Abstract
L General Installation Program Creation
L deploy the SQL Server database in the installation program
1. Execute database script files by calling osql
2. Load the script file as a resource file
3. Use sp_attach_db to create a database
L Summary

Summary
After a project is completed, it is necessary to create the installation program. If you need to deploy the database, the installation degree is troublesome. You can use visual studio.net to easily create applications and databases required for program deployment. This document describes how to use visual studio.net to create an installer and how to deploy a database in the installer. This document assumes that you are familiar with ado.net.

1. General Installation Program Creation

Visual studio.net provides powerful and convenient installation functions. For general installation procedures, refer to the following steps:
(1) Create an installation project (for general Windows applications) or web installation project (for Web applications) in visual studio.net, such as setupface.
(2) Right-click setupface in the project in the solution and you can see the following view:
 
Figure 1 view in the installer
The project that appears in the view is the project that needs to be involved in creating the installation program.
A. the file system Section provides how to package the executable programs to be installed on the target machine and the DLL required during running into the installation program, it also provides how to create shortcuts and folders on the desktop of the target machine and in the program menu.
B. The Registry Section provides how to add related key values to the registry of the target machine.
C. The file type allows you to create an association between the file type and the application on the target machine, and add related right-click menu functions for the registered file type. For example, to register a license file, use your own program to open it.
D. the user interface provides the interface during the installation process. You can also create functions such as self-report files and registration code verification as needed. Some basic images are provided when you create a project.
E. The custom Operations Section mainly provides how to complete different tasks in different stages of installation. For example, you can create a database during the installation process and delete the database during the uninstallation process.
F. The startup conditions Section provides the conditions to complete before installing your application. Visual studio.net provides the ability to search startup conditions on users' computers. For example, if a program has been installed, you can search for the file system or the registry.
(3) Select a file system in the view. The following figure is displayed:
 
Figure 2 File System Interface
If you need to complete a simple installation program, select a file in add, select you and the compiled application, visual studio.net automatically introduces the DLL required by the application (only for applications developed by Visual studio.net, but not for other programs ), then, create a shortcut in the user's "program" menu and user desktop as needed, and then point the shortcut to the application imported in the application folder.
(4) Finally, you can directly compile the project and complete the installation process.
If you need more control and modification of the content during the installation process, you can edit it according to the different views mentioned in step (2. If you need to create a web project installer, you can create a web project when creating a project. The other parts are similar. You can also directly refer to the following link of msdn:
Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/vsintro7/html/vxconatourofvisualstudio. asp

2. Deploy the SQL Server database in the installer

Through the above introduction, it is generally very easy to create an application, basically the process of dragging your application into the installation program. If your applications require database support (such as SQL Server), the problem becomes more complex.
There are several problems:
(1) how to obtain the value of the installation screen, such as the user name and password entered by the user.
(2) how to design a program to interact with the database server and create a new database.
For more information about how to obtain values in the installer, see the following link.
Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/vsintro7/html/vxconatourofvisualstudio. asp>
This example demonstrates how to request the user to input information in the user interface custom dialog box, and then pass the obtained value to the master output in the Custom operation through customactiondata, finally, create a database in the program in the main output phase.
By understanding the above example, we can basically create a database using this method. It executes SQL statements through ado.net to create a database. However, it is very troublesome to create a large number of data tables and stored procedures. Can I directly create the corresponding database generation script file in SQL Server?
We have three solutions:
(1) execute database script files by calling osql
(2) embed the corresponding script file into the project as a resource file and execute it through ado.net.
(3) Call the storage procedure of sp_attach_db of SQL Server to directly attach the database.

2.1 execute database script files by calling osql

As we have learned how to pass values from the above example, we only need to execute osql in the program section of the project file.
The problem here is where to find the script file of the database. We can put the script file in the file system and install it directly on the user's machine. We can obtain the location of the file through the following method.
Assembly ASM = assembly. getexecutingassembly ();
String setuppath = ASM. location;
In this way, we can know the location of the script file. The following file is how to start the osql program. We can do this through the following code snippet:
Process sqlprocess = new process (); sqlprocess. startinfo. filename = "osql.exe ";
Sqlprocess. startinfo. arguments = string. format ("-U {0}-P {1}-s {2}-I {3}", this. UID, this. PWD, this. serverip, this. spath); // uid indicates the user name, PWD indicates the password, serverip indicates the IP address of the target server, and Spath indicates the path of the database script.
Sqlprocess. startinfo. windowstyle = processwindowstyle. hidden;
Sqlprocess. Start ();
Sqlprocess. waitforexit (); // wait for Program Execution
Sqlprocess. Close ();

As you can see above, this method must require the client of the installation program and the SQL Server to be used (osql is the program under the command line provided after the SQL Server is installed ). in addition, during the actual test, we found that if you select a path with spaces in the installation program, for example, c: \ Program Files \ yourappliaction \, the installation fails. This is a serious problem.

2.2 load a script file as a resource file

As we can see from the previous discussion, it is troublesome to directly use ado.net for execution and requires sentence execution. If the script is used, the script generated in the database contains the "go" command, problems may occur during execution in ado.net. Therefore, based on the analysis of the database installation script, we can use the following alternative solution.
In the database installation process, there are almost the following parts:
(1) Create a database
(2) create a table
(3) create an attempt or stored procedure
In addition, the three parts are sequential and the order cannot be reversed, but there is no sequential relationship in each process. During the test, we found that multiple tables or stored procedures can be created in one statement. That is to say, we can execute the execution process in the above sequence. In addition, we can embed the database script as a resource file and call it for execution. This simplifies the process of creating commands.
The code for obtaining the resource file is as follows:
Assembly ASM = assembly. getexecutingassembly ();
Streamreader STR;
STR = new streamreader (ASM. getmanifestresourcestream (ASM. getname (). Name + "." + filename) // filename indicates the resource file to be extracted for you. As a resource file, you only need to import the file to your project and change the generated operation to an embedded resource.
Here we directly obtain the file stream of the file, so we can directly read the content in the stream.
You can perform the following operations:
(1) Create a database link and create a database. For example, connectionstring = "Server = 127.0.0.1, uid = SA, Pwd = PWD ";
(2) create a new database link pointing to the created database. Connectionstring = "Server = 127.0.0.1, uid = SA, Pwd = PWD, database = yourdatabase ";
(3) execute the code for creating a data table and a data storage process in the new link.

Note: Do not name go in your script. Other commands can be executed directly.
The advantage of this method is that you do not need to have an SQL server installed on the target machine or the installation program fails because of space in the file path. Of course, if your database is Oracle or DB2, you can use a similar method.

2.3 use sp_attach_db to create a database

Through the above discussion, we are very clear about how to install the database. During database installation, in addition to creating a database through the database script, we can also append the database through the SQL server system storage process sp_attach_db.
Here we can solve two problems:
(1) determine the location of the database file (. MDF and. LDF.
(2) execute the stored procedure.

For question 1, we can refer to the first method of data installation, that is, to install. MDF and. LDF on the target machine through the file system, and then through
Assembly ASM = assembly. getexecutingassembly ();
String setuppath = ASM. location;
Obtain the file path.

Finally, call sp_attach_db and add the corresponding parameters.
Note that this method can only be installed when the database is installed on the local machine.

Summary

This article introduces three different methods to install the database in the installation program, and also analyzes the advantages and disadvantages of different methods. You can choose the installation method based on your actual needs. At the same time, through this method, you can also complete the configuration of the database in the installation program.