asp.net安装数据库并修改web.config中数据库连接

Monn

这个是在网上找到的一个代码，感觉挺实用的，自己测试过后就搞过来了，需要添加引用System.Configuration.Install ，其他的不多说，直接上代码：

installHelper.cs

[C#] 纯文本查看 复制代码

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 installHelper
{
    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
    }
}

调用方法：

[C#] 纯文本查看 复制代码

protected void Page_Load(object sender, EventArgs e)
        {
            CreatSQL cb = new CreatSQL();
            string dir = Server.MapPath("~/");

            cb.dbname = "test";
            cb.sqlfile = dir + "test.sql";
            cb.userid = "sa";
            cb.pwd = "sa";
            cb.ServerName = ".";
            cb.CreateDBAndTable();
            cb.WriteWebConfig(dir + "Web.config", "ConnectionString");
            Response.Write("安装成功!");
        }

test.sql

[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

web.config内容需要添加：

[C#] 纯文本查看 复制代码

  <!-- 数据库连接字符串-->
  <connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=.;Initial Catalog=test;User ID=sa;Password=sa" />
  </connectionStrings>

完整工程下载（net4.0）：

游客，如果您要查看本帖隐藏内容请回复

站长苏飞

我只是路过看看的。

wing仙

非常感谢你帮了我的大忙，真的太感谢你啦！

wing仙

受教了，学习中……

伊米晨熙

我只是路过看看的。

ZZG

学习一下了。

Kenney

我也路过看看

259876lr

很好，可以借鉴

ly7374520

非常感谢你帮了我的大忙，真的太感谢你啦！

pandonghai

非常感谢你帮了我的大忙，真的太感谢你啦！