打包过程中如何创建SQL数据库

来源：互联网发布：mysql concat用法编辑：程序博客网时间：2024/06/03 15:36

一、在安装项目下先添加“安装项目类库”，在此类库下添加“安装程序类”。

在此类中命名空间要包括以下信息：

在此项目中，添加对System.EnterpriseServices和System.DirectoryServices的引用，在操作IIS的时候，需要用到。在文件中添加：

using System;

using System.IO;

using System.DirectoryServices;

using System.Reflection;

using System.Data;

using System.Data.SqlClient;

using System.Configuration.Install;

using System.Management;

using System.Collections;

using Microsoft.Win32;

using System.Collections.Specialized;如果编译的时候出错，请添加相关引用。

二、将DBSQL.txt文件放到此项目中，在属性中设置为“嵌入的资源”

三、

在customActionData中输入：

/dbname=[DBNAME] /server=[DBSERVERNAME] /user=[USERNAME] /pwd=[PASSWORD] /iis=[IISSERVER] /port=[PORT] /targetdir="[TARGETDIR]/"

四、

打开SetupClassLibrary项目下的MyInstaller.Designer.cs，修改此文件。

申明几个变量：

private System.Data.SqlClient.SqlConnection sqlConn;

private System.Data.SqlClient.SqlCommand Command;

private string DBName;

private string ServerName;

private string AdminName;

private string AdminPwd;

private string iis;

private string port;

private string dir;

public static string VirDirSchemaName = "IIsWebVirtualDir";

private string _target;

private DirectoryEntry _iisServer;

private ManagementScope _scope;

private ConnectionOptions _connection;

连接数据库服务器到方法：

#region ConnectDatabase 连接数据库

private bool ConnectDatabase()

{

if (Command.Connection.State != ConnectionState.Open)

{

try

{

Command.Connection.Open();

}

catch(Exception e)

{

return false;

}

return true;

}

#endregion

如果不能正确连接数据库服务器，请检查你的连接字符串，或者将连接字符串写入文件查看。不好意思，我不知道如何对这种安装部署程序进行debug，sorry咯！

读取SQL文件的方法：

#region GetSql 从文件中读取SQL，在读取包含SQL脚本的文件时需要用到，参考自MSDN

private string GetSql(string Name)

{

try

{

Assembly Asm = Assembly.GetExecutingAssembly();

Stream strm = Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name);

StreamReader reader = new StreamReader(strm);

return reader.ReadToEnd();

}

catch (Exception getException)

{

throw new ApplicationException(getException.Message);

}

#endregion

可以将此需要执行的SQL脚本放在此文本中

执行SQL语句的方法：

#region ExecuteSql 执行SQL语句，参考自MSDN

private void ExecuteSql(string DataBaseName, string sqlstring)

{

Command = new System.Data.SqlClient.SqlCommand(sqlstring, sqlConn);

if (ConnectDatabase())

{

try

{

Command.Connection.ChangeDatabase(DataBaseName);

Command.ExecuteNonQuery();

}

finally

{

Command.Connection.Close();

}

#endregion

创建数据库及数据库表：

#region CreateDBAndTable 创建数据库及数据库表，参考自MSDN

protected bool CreateDBAndTable(string DBName)

{

bool Restult = false;

try

{

ExecuteSql("master", "USE MASTER IF EXISTS (SELECT NAME FROM SYSDATABASES WHERE NAME='" + DBName + "') DROP DATABASE " + DBName);

ExecuteSql("master", "CREATE DATABASE " + DBName);

ExecuteSql(DBName, GetSql("DBSQL.txt"));

Restult = true;

}

Catch

{

}

return Restult;

}

#endregion

从备份文件恢复数据库及数据库表

#region RestoreDB 从备份文件恢复数据库及数据库表

///

/// 从备份文件恢复数据库及数据库表

///

数据库名

///

配件中数据库脚本资源的名称

///

protected bool RestoreDB(string DBName)

{

dir = this.Context.Parameters["targetdir"];

bool Restult = false;

string MSQL = "RESTORE DATABASE " + DBName +

" FROM DISK = '" + dir + @"data.bak' " +

" WITH MOVE 'Test' TO '" + @"c:/" + DBName + ".mdf', " +

" MOVE 'Test_log' TO '" + @"c:/" + DBName + ".ldf' ";

try

{

ExecuteSql("master", "USE MASTER IF EXISTS (SELECT NAME FROM SYSDATABASES WHERE NAME='" + DBName + "') DROP DATABASE " + DBName);

ExecuteSql("master", MSQL);

Restult = true;

}

finally

{

// 删除备份文件

try

{

File.Delete(dir + @"data.bak");

}

catch

{

}

return Restult;

}

#endregion

这里可以到注册表读取SQL Server的安装路径，把恢复后的数据库文件放到data目录地下。在本例中，只是实现了恢复，并未进行标准的操作。其中Test和Test_log时备份时数据库的文件信息。如果想要从备份文件中恢复，请把文件包含到项目里并且设置和DBSQL.txt一样，嵌入到程序里。最后执行删除。不过我想应该有办法不把文件先安装到目标机器上，而是有方法想读取DBSQL.txt文件一样，直接恢复数据库，不过确实没想到办法，失败！

网站安装好后，需要设置web.config文件，这里只涉及到连接字符串到设置，其他的可以同理修改。

#region WriteWebConfig 修改web.config的连接数据库的字符串

private bool WriteWebConfig()

{

System.IO.FileInfo FileInfo = new System.IO.FileInfo(this.Context.Parameters["targetdir"] + "/web.config");

if (!FileInfo.Exists)

{

throw new InstallException("Missing config file :" + this.Context.Parameters["targetdir"] + "/web.config");

}

System.Xml.XmlDocument xmlDocument = new System.Xml.XmlDocument();

xmlDocument.Load(FileInfo.FullName);

bool FoundIt = false;

foreach (System.Xml.XmlNode Node in xmlDocument["configuration"]["appSettings"])

{

if (Node.Name == "add")

{

if (Node.Attributes.GetNamedItem("key").Value == "ConnectionString")

{

Node.Attributes.GetNamedItem("value").Value = String.Format("Persist Security Info=False;Data Source={0};database={1};User ID={2};Password={3};Packet Size=4096;Pooling=true;Max Pool Size=100;Min Pool Size=1", ServerName, DBName, AdminName, AdminPwd);

FoundIt = true;

}

if (!FoundIt)

{

throw new InstallException("Error when writing the config file: web.config");

}

xmlDocument.Save(FileInfo.FullName);

return FoundIt;

}

#endregion

#region WriteRegistryKey 写注册表。安装部署中，直接有一个注册表编辑器，可以在那里面设置。

private void WriteRegistryKey()

{

// 写注册表

RegistryKey hklm = Registry.LocalMachine;

RegistryKey cqfeng = hklm.OpenSubKey("SOFTWARE", true);

RegistryKey F = cqfeng.CreateSubKey("cqfeng");

F.SetValue("FilePath", "kkkk");

}

#endregion

操作IIS

#region Connect 连接IIS服务器

public bool Connect()

{

if (iis == null)

return false;

try

{

_iisServer = new DirectoryEntry("IIS://" + iis + "/W3SVC/1");

_target = iis;

_connection = new ConnectionOptions();

_scope = new ManagementScope(@"//" + iis + @"/root/MicrosoftIISV2", _connection);

_scope.Connect();

}

catch

{

return false;

}

return IsConnected();

}

public bool IsConnected()

{

if (_target == null || _connection == null || _scope == null) return false;

return _scope.IsConnected;

}

#endregion

#region IsWebSiteExists 判断网站是否已经存在

public bool IsWebSiteExists(string serverID)

{

try

{

string siteName = "W3SVC/" + serverID;

ManagementObjectSearcher searcher = new ManagementObjectSearcher(_scope, new ObjectQuery("SELECT * FROM IIsWebServer"), null);

ManagementObjectCollection webSites = searcher.Get();

foreach (ManagementObject webSite in webSites)

{

if ((string)webSite.Properties["Name"].Value == siteName)

return true;

}

return false;

}

catch

{

return false;

}

#endregion

#region GetNextOpenID 获得一个新的ServerID

private int GetNextOpenID()

{

DirectoryEntry iisComputer = new DirectoryEntry("IIS://localhost/w3svc");

int nextID = 0;

foreach (DirectoryEntry iisWebServer in iisComputer.Children)

{

string sname = iisWebServer.Name;

try

{

int name = int.Parse(sname);

if (name > nextID)

{

nextID = name;

}

catch

{

}

return ++nextID;

}

#endregion

#region CreateWebsite 添加网站

public string CreateWebSite(string serverID, string serverComment, string defaultVrootPath, string HostName, string IP, string Port)

{

try

{

ManagementObject oW3SVC = new ManagementObject(_scope, new ManagementPath(@"IIsWebService='W3SVC'"), null);

if (IsWebSiteExists(serverID))

{

return "Site Already Exists...";

}

ManagementBaseObject inputParameters = oW3SVC.GetMethodParameters("CreateNewSite");

ManagementBaseObject[] serverBinding = new ManagementBaseObject[1];

serverBinding[0] = CreateServerBinding(HostName, IP, Port);

inputParameters["ServerComment"] = serverComment;

inputParameters["ServerBindings"] = serverBinding;

inputParameters["PathOfRootVirtualDir"] = defaultVrootPath;

inputParameters["ServerId"] = serverID;

ManagementBaseObject outParameter = null;

outParameter = oW3SVC.InvokeMethod("CreateNewSite", inputParameters, null);

// 启动网站

string serverName = "W3SVC/" + serverID;

ManagementObject webSite = new ManagementObject(_scope, new ManagementPath(@"IIsWebServer='" + serverName + "'"), null);

webSite.InvokeMethod("Start", null);

return (string)outParameter.Properties["ReturnValue"].Value;

}

catch (Exception ex)

{

return ex.Message;

}

public ManagementObject CreateServerBinding(string HostName, string IP, string Port)

{

try

{

ManagementClass classBinding = new ManagementClass(_scope, new ManagementPath("ServerBinding"), null);

ManagementObject serverBinding = classBinding.CreateInstance();

serverBinding.Properties["Hostname"].Value = HostName;

serverBinding.Properties["IP"].Value = IP;

serverBinding.Properties["Port"].Value = Port;

serverBinding.Put();

return serverBinding;

}

catch

{

return null;

}

#endregion

五、

好了，准备工作已经做完，现在开始写最重要的Install方法了

整个方法写完后如下：

#region Install 安装

///

/// 安装数据库

///

public override void Install(IDictionary stateSaver)

{

base.Install(stateSaver);

dir = this.Context.Parameters["dir"];

DBName = this.Context.Parameters["DBNAME"].ToString();

ServerName = this.Context.Parameters["server"].ToString();

AdminName = this.Context.Parameters["user"].ToString();

AdminPwd = this.Context.Parameters["pwd"].ToString();

iis = this.Context.Parameters["iis"].ToString(); ;

port = this.Context.Parameters["port"].ToString();

//写入获取的安装程序中的变量，此段代码为调试用可以不添加

this.sqlConn.ConnectionString = "Packet size=4096;User ID=" + AdminName + ";Data Source=" + ServerName + ";Password=" + AdminPwd + ";Persist Security Info=False;Integrated Security=false";

// 执行SQL 安装数据库可选择时恢复或者时直接创建

if(!CreateDBAndTable(DBName))

{

throw new ApplicationException("创建数据库时出现严重错误！");

}

// 从备份数据库文件恢复数据库

if (!RestoreDB(DBName))

{

throw new ApplicationException("恢复数据库时出现严重错误！");

}

// 添加网站

Connect();

//string serverID = GetNextOpenID().ToString();

//string serverComment = websitenName;

// 下面的信息为测试，可以自己编写文本框来接收用户输入信息

string serverID = "5555";

string serverComment = "cqfeng";

string defaultVrootPath = this.Context.Parameters["targetdir"];

if (defaultVrootPath.EndsWith(@"/"))

{

defaultVrootPath = defaultVrootPath.Substring(0, defaultVrootPath.Length-1);

}

string HostName = "";

string IP = "";

string Port = port;

string sReturn = CreateWebSite(serverID, serverComment, defaultVrootPath, HostName, IP, Port);

// 修改web.config

if (!WriteWebConfig())

{

throw new ApplicationException("设置数据库连接字符串时出现错误");

}

// 写注册表

WriteRegistryKey();

}

#endregion

六、添加卸载程序

在添加你的应用程序项目的时候，多添加一个msiexec.exe进去,
这个文件在c:/windows/system32文件夹下,

添加的时候可以改名：Uninstall.exe