• 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

  • 相关阅读:
    How to function call using 'this' inside forEach loop
    jquery.validate.unobtrusive not working with dynamic injected elements
    Difference between jQuery.extend and jQuery.fn.extend?
    Methods, Computed, and Watchers in Vue.js
    Caution using watchers for objects in Vue
    How to Watch Deep Data Structures in Vue (Arrays and Objects)
    Page: DOMContentLoaded, load, beforeunload, unload
    linux bridge
    linux bridge
    EVE-NG网卡桥接
  • 原文地址:https://www.cnblogs.com/yeye518/p/2231670.html
Copyright © 2020-2023  润新知