Write SQL Server and web. config during deployment and Installation

This article from: http://aspxboy.com/private/5280/default.aspx
It is convenient to deploy Web solutions on the. NET platform. We can use Visual Studio. NET 2003 adds a web installation project, and adds the project's main output and content files to the deployed "File System Editor" to easily complete the installation program.

However, the installation program created in this way only includes the web page and ASP. install the DLL files compiled by the net program to the IIS Directory of the target machine. This is acceptable for general applications (for example, the ACCESS database can be packaged into the installer together ); if the database is SQL Server, you need to install the database at the time of deployment. The creation of the installation program will be more complicated. You need to customize the installation program class. Execute the SQL script in the installer class and write the connection string to Web. config.

L install the database

Microsoft msdn introduced how to create a database when deploying an application. For example:

Http://msdn.microsoft.com/library/default.asp? Url =/library/en-US/vsintro7/html/vxwlk?throughusingcustomactiontocreatedatabaseduringinstallation. asp

This method creates an installer class. In the installer class, you can call ado.net to execute an SQL statement (the SQL statement is placed in a text file) to create a database.

 

However, this method has a problem. If SQL Server2000 is used to generate a script file for all table creation, views, and stored procedures, ADO is used. net to execute this script file, it will be because the script contains many "go" statements and errors. Of course, we can replace "go" with a line break and use ADO. Net to execute SQL statements one by one. Obviously, the efficiency is relatively low.

The best way is to call osql to execute the script. (Or create a CMD file for a database project, and the CMD file also calls osql when creating a database ).

Using system;
Using system. collections;
Using system. componentmodel;
Using system. configuration. Install;
Using system. Data. sqlclient;
Using system. IO;
Using system. reflection;
Using system. diagnostics;
Using system. xml;

Namespace dbcustomaction
{
/// <Summary>
/// Summary of dbcustomaction.
/// @ Author: overred
/// </Summary>
[Runinstaller (true)]
Public class dbcustomaction: system. configuration. Install. Installer
{
/// <Summary>
/// Required designer variables.
/// </Summary>
Private system. componentmodel. Container components = NULL;

Public dbcustomaction ()
{
// This call is required by the designer.
Initializecomponent ();

// Todo: add any initialization after initializecomponent calls
}

/// <Summary>
/// Clear all resources in use.
/// </Summary>
Protected override void dispose (bool disposing)
{
If (disposing)
{
If (components! = NULL)
{
Components. Dispose ();
}
}
Base. Dispose (disposing );
}

# Code generated by the region component designer
/// <Summary>
/// The designer supports the required methods-do not use the code editor to modify
/// Content of this method.
/// </Summary>
Private void initializecomponent ()
{
Components = new system. componentmodel. Container ();
}
# Endregion

# Region custom setup

 

Private void executesql (string connstring, string databasename, string SQL)
{
Sqlconnection conn = new sqlconnection (connstring );
Sqlcommand cmd = new sqlcommand (SQL, Conn );
Conn. open ();
Cmd. Connection. changedatabase (databasename );
Try
{
Cmd. executenonquery ();
}
Catch (exception E)
{
Streamwriter W = new streamwriter (@ "E: \ log.txt", true );
W. writeline ("=== in executesql ===== ");
W. writeline (E. tostring ());
W. Close ();
}
Finally
{
Conn. Close ();
}
}

Public override void install (idictionary statesaver)
{
Createdb ();
Updateconfig ();
}

Private void createdb ()
{
Try
{
String connstring = string. format ("Server = {0}; user id = {1}; Password = {2}", this. context. parameters ["server"], this. context. parameters ["user"], this. context. parameters ["PWD"]);

// Create a database based on the input database name
Executesql (connstring, "Master", "create database" + this. Context. Parameters ["dbname"]);

// Call osql to execute the script
String cmd = string. format ("-S {0}-U {1}-P {2}-d {3}-I {4} dB. SQL ", this. context. parameters ["server"], this. context. parameters ["user"], this. context. parameters ["PWD"], this. context. parameters ["dbname"], this. context. parameters ["targetdir"]);
System. Diagnostics. Process sqlprocess = new process ();
Sqlprocess. startinfo. filename = "osql.exe ";
Sqlprocess. startinfo. Arguments = cmd;
Sqlprocess. startinfo. windowstyle = processwindowstyle. hidden;
Sqlprocess. Start ();
Sqlprocess. waitforexit (); // wait for execution
Sqlprocess. Close ();

// Delete the script file
System. Io. fileinfo sqlfileinfo = new fileinfo (string. Format ("{0} dB. SQL", this. Context. Parameters ["targetdir"]);
If (sqlfileinfo. exists)
Sqlfileinfo. Delete ();
}
Catch (exception E)
{
Streamwriter W = new streamwriter (@ "E: \ log.txt", true );
W. writeline ("=== in install ==== ");
W. writeline (E. tostring ());
W. Close ();
}
}

Private void updateconfig ()
{
Try
{
// Write the connection string to Web. config
System. Io. fileinfo = new fileinfo (string. Format ("{0} web. config", this. Context. Parameters ["targetdir"]);

If (! Fileinfo. exists)
Throw new installexception ("can't find the Web. config ");

Xmldocument Doc = new xmldocument ();
Doc. Load (fileinfo. fullname );
Bool foundit = false;

String connstring = string. format ("Server = {0}; database = {1}; user id = {2}; Password = {3}", this. context. parameters ["server"], this. context. parameters ["dbname"], this. context. parameters ["user"], this. context. parameters ["PWD"]);

String ENCs = securityhelper. encryptdbconnectionstring (connstring );

Xmlnode NO = Doc. selectsinglenode ("// appsettings/Add [@ key = 'constring']");
If (No! = NULL)
{
No. Attributes. getnameditem ("value"). value = ENCs;
Foundit = true;
}

If (! Foundit)
Throw new installexception ("can't find the connstring setting ");
Doc. Save (fileinfo. fullname );
}
Catch (exception E)
{
Streamwriter W = new streamwriter (@ "E: \ log.txt", true );
W. writeline ("=== in updata connstring = tjtj ==== ");
W. writeline (E. tostring ());
W. writeline (E. stacktrace );
W. Close ();
}
}

# Endregion
}
}