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

来源：互联网发布：印刷字体有哪些知乎编辑：程序博客网时间：2024/05/18 15:22

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

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

USE [Test]GO /****** Object:  Table [dbo].[Test]    Script Date: 01/13/2014 15:13:51 ******/SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER ONGO 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();        }    }}

0 0