Write to SQL Server and web.config when you deploy the installation

Server|sql|web

Under the. NET platform, it is more convenient to deploy a WEB solution. We can use Visual Studio.NET 2003 to add a Web Setup project, add the project's primary output and content files to the deployed File System Editor, and make the setup process very easy.

However, the installer, which installs the Web page and the asp.net DLL file to the target machine's IIS directory, is OK for general applications (such as using an Access database, packaged together into the installer), and if the database is SQL SERVER, You need to install the database at the time of deployment, and the setup process will be more complex, and we need to customize the installer class. Executes the SQL script in the Installer class and writes the connection string to the web.config.

L Install the database

Microsoft MSDN has described the creation of a database when deploying applications. Such as:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsintro7/html/ Vxwlkwalkthroughusingcustomactiontocreatedatabaseduringinstallation.asp

This method creates a database by creating an installer class that invokes the Ado.net Execute SQL statement (SQL statement in a text file) in the Installer class.

However, there is a problem with this approach, if you use SQL Server2000 to generate all the tables, views, stored procedures, a script file, the Ado.net to execute the script file, will be because there are many "go" statements in the script error. Of course, we can replace "go" with line breaks, using ado.net to execute SQL statements. Obviously, this is less efficient.

The best way to do this is to invoke osql to execute the script. (or create a database project of the cmd file, and the cmd file set up when the database is also called osql).

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 description of the dbcustomaction.
</summary>
[Runinstaller (True)]
public class DBCustomAction:System.Configuration.Install.Installer
{
<summary>
@author: overred
</summary>
Private System.ComponentModel.Container components = null;

Public DBCustomAction ()
{
The call is required by the designer.
InitializeComponent ();

TODO: Add any initialization after the InitializeComponent call
}

<summary>
Clean up all resources that are in use.
</summary>
protected override void Dispose (bool disposing)
{
if (disposing)
{
if (Components!= null)
{
Components. Dispose ();
}
}
Base. Dispose (disposing);
}

Code generated #region Component Designer
<summary>
Designer supports required methods-do not use the Code editor to modify
The contents 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};p assword={2}", this. context.parameters["Server"],this. context.parameters["User"],this. context.parameters["PWD"]);

Establish a database based on the name of the database entered
ExecuteSQL (connstring, "Master", "CREATE Database" +this. context.parameters["dbname"]);

Invoke osql Execution 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 ()/Waiting for execution
Sqlprocess.close ();

deleting script files
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 connection string to Web.config
System.IO.FileInfo 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};d atabase={1};user id={2};p assword={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 = ' connstring ']");
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
}
}