asp.net 安装数据库，以及修改web.config文件

    CSDN广告是越来越多了，所有博客笔记不再更新，新网址 DotNet笔记

因为项目需求，要实现在线安装数据库的功能。核心代码是在网上找的，来自哪个网址也没记下。

第一步：准备一份sql文件脚本，类似如下：

USE [Test]
GO
 
/****** Object:  Table [dbo].[Test]    Script Date: 01/13/2014 15:13:51 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[Test](
        [ID] [bigint] IDENTITY(1,1) NOT NULL,
        [Title] [nvarchar](250) NOT NULL,
        [ClassID] [int] NOT NULL,
        [Content] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
        [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

第二步：把下面的这个类库添加到项目中，如下：

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 Sql.util
{
    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"]["connectionStrings"])
            {
                if (Node.Name == "add")
                {
                    if (Node.Attributes.GetNamedItem("name").Value == ConnectionString)
                    {
                        Node.Attributes.GetNamedItem("connectionString").Value = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", 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
    }
}

第三步：调用，如下：

using Sql.util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace Sql.Controllers
{
    public class InstallController : Controller
    {
        // GET: /Install/
        public ActionResult Index()
        {
            CreatSQL cb = new CreatSQL();
            string dir = Server.MapPath("~/");
            cb.dbname = "test";//数据库名称
            cb.sqlfile = dir + "test.sql";//脚本的路径
            cb.userid = "zhi*fei*ya";//数据库的可用用户名
            cb.pwd = "zfy********";//数据库的可用密码
            cb.ServerName = ".";//数据库地址
            cb.CreateDBAndTable();
            //修改【重写】Web.config文件的连接字符串节点
            //第一个参数是Web.config的路径，第二个参数是节点的名称
            cb.WriteWebConfig(dir + "Web.config", "DefaultConnection");
            Response.Write("安装成功!");
            return View();
        }
    }
}