Deploying a SQL Server database in the. NET Setup program

server| Program | data | Database directory

L Summary
L General installation program production
• Deploy the SQL Server database in the Setup program
1. Execute database script file by calling osql
2. By loading the script file as a resource file
3. Create a database by sp_attach_db
L Summary

Summary
After a project is completed, the installation process is a necessary procedure. If you need to deploy the database, making the installation level more cumbersome. Using Visual Studio.NET makes it easy to make applications and the databases you need in your deployment programs. This article describes how to use Visual Studio.NET to make an installer and how to deploy a database in Setup. This article assumes that you are familiar with ado.net.

1. Production of general installation procedures

Visual Studio.NET provides a powerful and very convenient setup program to make features. The general installation process can refer to the following steps:
(1) Create a new Setup project (for general Windows applications) or Web Setup projects (for web-based applications) in Visual Studio.NET, such as Setupface.
(2) Right-click on the Setupface in the project within the solution to see the following view:

Figure 1 View in Setup program
The items that appear in the view are the items that need to be involved in making the installer.
A. The File System section provides a way to package into the installer the need to install the target executable program and the DLLs needed by the runtime, as well as how to create shortcuts and folders on the desktop of the target machine and in the Program menu.
B. The registry section provides a way to add related key values to the registry of the target machine.
C. File types provide the ability to create a file type associated with an application on the target machine, and you can add the relevant right-click menu functionality to the registered file type. For example, register a. pdf file and use your own program to open it.
D. The user interface provides the interface during the installation process, as well as the ability to create a Readme file, a registration code checksum, etc. as required. Some basic images have been provided when creating a project.
E. The Custom Actions section primarily provides a way to accomplish different tasks at different stages of installation. such as creating a database during the installation process, deleting the database during the uninstall process, and so on.
F. The Launch Conditions section provides what you need to accomplish before you install your application. Visual Studio.NET provides the ability to search for a launch condition on a user's computer. For example, require a program to be installed, you can search the file system or search the registry to complete.
(3) Select the file system in the view, you will see the following screen:

Figure 2 File System interface
If you need to complete a simple installer, select the file in the Add, select you, and the compiled application, Visual Studio.NET will automatically introduce the DLL needed for the application (only for visual Studio.NET developed applications, other programs have not tried it, then create shortcuts to your needs on the user's Programs menu and user's desktop, and then select the shortcuts to the applications that you import in the application folder.
(4) Finally, you can directly compile the project, the installation of the production of the program can be completed.
If you need more control and modify the contents of the installer process, you can edit it according to the different views mentioned in step (2). If you need to make a Web project installer, choose to create a Web project when you create the project, and the rest is similar. You can also refer directly to the following links to MSDN:
Http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsintro7/html/vxconATourOfVisualStudio.asp

2. To deploy the SQL Server database in an installer

As described above, the production of a generic application is very easy, basically the process of dragging your application into the installer. If the operation of your application requires database support (such as SQL Server), the problem becomes more complex.
There are several main problems:
(1) How to obtain the value of the installation screen, such as the user entered the user name password.
(2) How to design a program to interact with the database server to create a new database.
How to obtain the values in the installer, you can see the following links, we do not do too much introduction here.
This example demonstrates how to require the user to enter information in the User Interface Customization dialog box, then pass the obtained value through CustomActionData to the master output in the custom action in the custom action, and finally create a database in the program inside the main output physiognomy.
By understanding the above example, you can basically create a database in this way, which completes the creation of the database by ado.net executing the SQL statement. However, if you have a large number of data tables and stored procedures that need to be built, this is a very cumbersome way to do this. Can you do this directly by creating a corresponding database generation script file directly in SQL Server?
We have 3 solutions:
(1) Execute database script file by calling osql
(2) by embedding the corresponding script file into the project as a resource file, and then executing it through ado.net
(3) Directly attach a database by invoking the sp_attach_db stored procedure of SQL Server.

2.1 Execute database script file by calling osql

From the example above we already know how to pass a value, so we simply execute osql in the program section of the project file.
Here is a question of where to find the database script file. We can put the script file into the file system and install it directly on the user's machine, and we can get to the location of the file by the following method.
Assembly asm=assembly.getexecutingassembly ();
String setuppath=asm. Location;
So we can know the location of the script file, the following file is how to start the osql program. We can do this by using 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 is the username, PWD is the password, ServerIP is the ip,spath of the target server for the path of the database script
Sqlprocess. Startinfo.windowstyle=processwindowstyle.hidden;
Sqlprocess. Start ();
Sqlprocess. WaitForExit (); Waiting for program execution
Sqlprocess. Close ();

As you can see from the above, the method must require the client that installed the program and the SQL Server installed to use (osql the program under the command line provided after you installed SQL Server). Meanwhile, in the actual testing process, we found that if the user in the installer is to select a path with a space As The installation failed when C:\program files\yourappliaction\. This is a more serious problem.

2.2 Loading the script file as a resource file

From the previous discussion we can see that the direct use of ado.net execution is more cumbersome, need a sentence execution, if the use of script, the database generated in the script in the "Go" command, in the ado.net execution will cause problems. Therefore, based on the analysis of the database installation script, we can use the following alternative scenario.
During the installation of the database, there are several parts:
(1) Create a database
(2) Create a table
(3) Create an attempt or stored procedure
And these three parts are sequential, the order cannot be reversed, but there is no sequential relationship in each process. During the test we found that we could create more than one table or more stored procedures in a single statement. In other words, we can execute the execution process in the order above. And we embed the database script as a resource file, as long as the call into execution is OK, so that the process of simplifying the creation of a command is achieved.
Get the resource file code as follows:
Assembly asm=assembly.getexecutingassembly ();
StreamReader str;
Str=new StreamReader (Asm.getmanifestresourcestream) (Asm.getname (). Name+ "." +FILENAME)/filename for you need to pick up the resource file. As a resource file, you can simply import the file into your project and change the build operation to an embedded resource.
Here we get the file stream directly, so read the contents of the stream directly.
We can do this by doing the following:
(1) Establish a database link to create a database. For example: connectionstring= "Server=127.0.0.1,uid=sa,pwd=pwd";
(2) Recreate a database link that points to the database you created. Connectionstring= "Server=127.0.0.1,uid=sa,pwd=pwd,database=yourdatabase";
(3) Execute the code that creates the data table and the data stored procedure in the new link.

Note that you don't have go naming in your script, and other commands can be executed directly.
The advantage of this method is that it does not require SQL Server on the installed target machine and does not exist because the installer failed because of a space problem in the file path. Of course, if your database is Oracle or DB2, you can do it in a similar way.

2.3 Creating a database through sp_attach_db

Through the above discussion, we already know how to install the database. In the process of installing the database, we can also attach the database through SQL Server's system stored procedure sp_attach_db, in addition to the database script that we can create.
Here we solve two problems:
(1) Determine the location of the database files (. mdf and. ldf).
(2) Execute the stored procedure.

For question 1, we can use the first method of installing the data to install the. mdf and. ldf through the file system to the target machine, and then
Assembly asm=assembly.getexecutingassembly ();
String setuppath=asm. Location;
Gets the path to the file.

Finally, by calling sp_attach_db plus the corresponding parameters can be completed.
Note that this method can also be installed only for databases that are installed on the local computer.

Summarize

This article introduces three different ways to install the database in the installer, and also analyzes the pros and cons of different methods, users can choose the installation method according to their actual needs. At the same time, through the proposed method, the user can also complete the Setup program is on the database configuration work.