[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 + "assword=" + 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}
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
}
}