贾青的老公:c#安装数据库并自动修改Web.config类

using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Configuration.Install;
using System.Collections;
using System.Management;
namespace DLL
{
   public class CreatSQL
    {
        //成员定义

        private System.Data.SqlClient.SqlConnection sqlConn=new SqlConnection();
        private System.Data.SqlClient.SqlCommand Command=new SqlCommand();
        private string _ServerName = ".";
        private string _dbname = "";
        private string _userid = "sa";
        private string _pwd = "sa";
        private string _sqlfile = "dbsql.sql";

//属性定义
        //服务器名称
        public string ServerName
        {
            set { _ServerName = value; }
            get { return _ServerName; }
        }
        //数据库名称
        public string dbname
        {
            set { _dbname = value; }
            get { return _dbname; }
        }
        //用户名
        public string userid
        {
            set { _userid = value; }
            get { return _userid; }
        }
        //密码
        public string pwd
        {
            set { _pwd = value; }
            get { return _pwd; }
        }
        //sql脚本文件名
        public string sqlfile
        {
            set { _sqlfile = value; }
            get { return _sqlfile; }
        }

//方法定义
        //连接数据库服务器到方法：
        #region ConnectDatabase 连接数据库
        private bool Conn()
        {
            sqlConn.ConnectionString = "Data Source=" + this.ServerName + ";Initial Catalog=master;User ID=" + this.userid + ";Password=" +this.pwd;
                try
                {
                    sqlConn.Open();
                    if (sqlConn.State == ConnectionState.Open)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                   
                }
                catch
                {
                    return false;
                }
           
        
        }
        #endregion
      
        //读取SQL文件的方法：
        #region GetSql 从文件中读取SQL，在读取包含SQL脚本的文件时需要用到
        public  bool ExecuteSqlFile(string FileName)
        {
         
            if (!File.Exists(FileName))
            {
                return false;
            }

            StreamReader sr = File.OpenText(FileName);
            ArrayList alSql = new ArrayList();
            string commandText = "";
            string varLine = "";
            while (sr.Peek() > -1)
            {
                varLine = sr.ReadLine();
                if (varLine == "")
                {
                    continue;
                }
                if (varLine != "GO")
                {
                    commandText += varLine;
                    commandText += "\r\n";
                }
                else
                {
                    alSql.Add(commandText);
                    commandText = "";
                }
            }
            sr.Close();
            try
            {
                ExecuteCommand(alSql);
            }
            catch
            {
                return false;
            }

            return true;
        }
        #endregion
        //执行SQL语句的方法：
        #region ExecuteSql 执行SQL语句，参考自MSDN
        public void ExecuteSql(string DataBaseName, string sqlstring)
        {

            if (Conn())
            {
                Command = new System.Data.SqlClient.SqlCommand(sqlstring, sqlConn);
                try
                {
                    //Command.Connection.ChangeDatabase(DataBaseName);
                    Command.ExecuteNonQuery();
                }
                finally
                {
                    Command.Connection.Close();
                }
            }
           
        }
        #endregion
        //创建数据库及数据库表：
        #region CreateDBAndTable 创建数据库及数据库表，参考自MSDN

        public bool CreateDBAndTable()
        {
            bool Restult = false;
            try
            {
                ExecuteSql("master", "USE MASTER IF EXISTS (SELECT NAME FROM SYSDATABASES WHERE NAME='" + this.dbname + "') DROP DATABASE " + this.dbname);
                ExecuteSql("master", "CREATE DATABASE " + this.dbname);
                ExecuteSqlFile(this.sqlfile);
                Restult = true;
            }
            catch
            {
            }
            return Restult;
        }
        #endregion
        #region WriteWebConfig 修改web.config的连接数据库的字符串
        public bool WriteWebConfig(string config, string ConnectionString)
        {
            System.IO.FileInfo FileInfo = new System.IO.FileInfo(config);
            if (!FileInfo.Exists)
            {
                throw new InstallException("Missing config file :" +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("Data Source={0};Initial Catalog={1};User ID={2};Password={3};Packet Size=4096", this.ServerName,this.dbname,this.userid,this.pwd);
                        FoundIt = true;
                    }
                }
            }
            if (!FoundIt)
            {
                throw new InstallException("Error when writing the config file: web.config");
            }
            xmlDocument.Save(FileInfo.FullName);
            return FoundIt;
        }
        #endregion
        #region 执行SQL脚本的每一行命令
        private  void ExecuteCommand(ArrayList varSqlList)
        {   
            try
            {
               if (Conn())
                {                 
                    foreach (string commandText in varSqlList)
                    {
                        Command = new System.Data.SqlClient.SqlCommand(commandText, sqlConn);
                        Command.ExecuteNonQuery();
                    }
                }            
            }
            catch (Exception ex)
            {           
                throw ex;
            }
        }
        #endregion
    }

}

调用方法示例：

 DLL.CreatSQL cb = new DLL.CreatSQL();
   string dir = @"C:\Documents and Settings\nature\My Documents\Visual Studio 2008\Projects\huatu\Web\";
            cb.dbname = "test";
            cb.sqlfile = dir+"dbsql2.sql";
            cb.userid = "sa";
            cb.pwd = "sa";
            cb.ServerName = ".";
            cb.CreateDBAndTable();
            cb.WriteWebConfig(dir + "Web.config", "ConnectionString");

dbsql2.sql，文件示例：

use [test]

GO

CREATE TABLE [dbo].[TT] (
 [NAME] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [DEPARTMENT] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
 [wage] [decimal](9, 2) NULL
) ON [PRIMARY]
GO