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