Deploy the SQL Server database in the. NET Installer

General applications Program Is very easy to create, basically is to drag your application into the installation process. 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 use the following Code Segment to complete:
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. You need to execute the statement in one sentence. If you use the script, 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) creation attempt or stored procedure
and the three parts are sequential. 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. 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 simply read the content in the stream.
you can follow these steps:
(1) Create a database link and create a database. for example, connectionstring = "Server = 127.0.0.1, uid = SA, Pwd = PWD";
(2) recreate a 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.