Under the .NET platform, it is more convenient to deploy Web solutions. We can use Visual Studio.NET 2003 to add a WEB installation project, add the main output and content files of the project in the deployed "File System Editor", and complete the installation program very easily.
However, the installation program created in this way only installs the DLL files compiled by the Web page and ASP.NET program into the IIS directory of the target machine. It is suitable for general applications (such as Access databases, which can be packaged into the installation program together. ); If the database is SQL SERVER, the database needs to be installed during deployment, and the installation program will be more complicated, and we need to customize the installation program class. Execute the SQL script in the installer class and write the connection string to Web.config.
l Installing the database
Microsoft MSDN introduces the establishment of a database when deploying applications. For example:
This method is to create an installer class and call it in the installer class ADO.NET executes SQL statements (SQL statements are placed in a text file) to create a database.
However, there is a problem with this method. If you use SQL Server2000 to generate a script file for all tables, views, and stored procedures, and use ADO.NET to execute this script file, there will be many "GO" statements in the script. An error occurred. Of course, we can replace "GO" with a newline character and use ADO.NET to execute SQL statements one by one. Obviously, this 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 the 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 description of DBCustomAction.
/// </summary>
[RunInstaller(true)]
public class DBCustomAction : System.Configuration.Install.Installer
{
/// <summary>
///@author:overred
/// </summary>
private System.ComponentModel.Container components = null;
public DBCustomAction()
{
// This call is required by the designer.
InitializeComponent();
// TODO: Add any initialization after InitializeComponent is called
}
/// <summary>
/// Clean up all resources in use.
/// </summary>
protected override void Dispose( bool disposing)
{
if(disposing)
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Component Designer generated code
/// <summary>
/// Designer supports required methods - do not use code editor to modify
/// The 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 entered database name
ExecuteSql(connString,"master","create database "+this.Context.Parameters["dbname"]);
//Calling 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();//Waiting for execution
sqlProcess.Close();
//Delete 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 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='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
}
}