用winform安装SQL数据库

此程序是用来创建web网站的数据库的,需放在web发布文件夹根目录中,数据库脚本也是.

为了方便操作,程序的App.config和网站的web.config完全一样.

所以界面的初始数据全都是读取App.config得来的,创建完数据库后还要修改2个配置文件的连接字符串(web.config用xml操作).

这个小程序当时是练手的,所以很多地方可以根据需要自行更改.

首先,界面长这样:

弄成这样不难...随便搞搞就行...

密码如果不想显示,在输入框属性那可以设置:

一:最重要的第一步,构建数据库语句,目标字符串:

字符1:

CREATE DATABASE 数据库名ON(    NAME = '数据库名_data',    FILENAME = 'D:\SQL\数据库名_data.mdf',    SIZE = 5MB,    MAXSIZE=5,    FILEGROWTH = 20)LOG ON(    NAME = '数据库名_log',    FILENAME = 'D:\SQL\数据库名_log.ldf',    SIZE = 2MB,    MAXSIZE=20,    FILEGROWTH = 1MB);

字符2:

USE 数据库名 GO 

注意事项:
眼号啊,逗号啊,括号啊全都不能省,而且字符2的GO必须单独占一行.

创建的方法:

        /// <summary>        /// 拼接数据库语句,移除脚本中的创建数据库语句        /// </summary>        private void CreateSqlFile()        {            StringBuilder sb = new StringBuilder();            using (FileStream fsr = new FileStream(Sqlpath[0], FileMode.Open, FileAccess.Read))//读            {                StreamReader sr = new StreamReader(fsr, Encoding.Default);                sb.Append(sr.ReadToEnd());//将脚本原有数据全部读出                sr.Close();            }            //判断            CompareInfo Compare = CultureInfo.InvariantCulture.CompareInfo;            int cnum = Compare.IndexOf(sb.ToString(), "CREATE DATABASE ", CompareOptions.IgnoreCase);            int gnum = Compare.IndexOf(sb.ToString(), "GO", CompareOptions.IgnoreCase);            if (cnum > -1 && gnum > -1)            {                sb = sb.Remove(cnum, gnum + 2);//移除旧的创建及USE语句                using (FileStream fsw = new FileStream(Sqlpath[0], FileMode.Create, FileAccess.Write))//写                {                    StreamWriter sw = new StreamWriter(fsw, Encoding.Default);                    sw.Write(sb.ToString());//写入移除后的                    sw.Close();                }            }            _Sql.Append(@"CREATE DATABASE " + _database + @" ON ");            _Sql.Append(@"(NAME='" + _database + @"_data',FILENAME='" + _path + @"\" + _database);            _Sql.Append(@"_data.mdf',size=3," + @"FILEGROWTH = 10%)");            _Sql.Append(@"LOG ON(NAME='" + _database + @"_log',FILENAME='" + _path + @"\" + _database);            _Sql.Append(@"_log.ldf',size=3," + @"FILEGROWTH = 10%);");            _Sqlgo = @"USE " + _database + @"GO";        }

以防万一,先检索脚本,发现有创建的语句立马删除.

构建的SQL语句会感觉怪怪的,没办法,为了达到在数据库实现换行效果只能这样.

因为用了字段,一旦出错还要清下数据:

        /// <summary>        /// 清空数据        /// </summary>        public void ClearData()        {            connStr = "";            _Sql.Clear();            _Sqlgo = "";            Sqlpath.Clear();        }

二:接下来贴出字段混个脸熟,因为懒,所以把大部分形参弄成字段,这样也比较直观:

        public string _uid, _pwd, _server, _database, _path;        /// <summary>        /// 连接到用户指定的数据库字符串        /// </summary>        public string connStr = "";        /// <summary>        /// 当前目录        /// </summary>        public string _SqlFile = "";        /// <summary>        /// 创建数据库语句        /// </summary>        public StringBuilder _Sql = new StringBuilder();        /// <summary>        /// 运行数据库GO语句        /// </summary>        public string _Sqlgo = "";        /// <summary>        /// 数据库脚本路径表        /// </summary>        public List<string> Sqlpath = new List<string>();

在构造函数中加上获取当前目录的方法:

_SqlFile = Directory.GetCurrentDirectory();//获取应用程序的当前工作目录

因为界面上的数据来自App.config,所以构造里还要加上读取方法:

GetConnectionStrings("ConnectionString");

        /// <summary>        /// 读取数据库连接字符串,初始化界面        /// </summary>        /// <param name="connectionName"></param>        /// <returns></returns>        public void GetConnectionStrings(string connectionName)        {            try            {                string sConstr = ConfigurationManager.AppSettings[connectionName];                if (sConstr == null || sConstr == "")                {                    MessageBox.Show("配置文件出错!无数据库连接字符串!");                    return;                }                String[] config = sConstr.Split(';');                ServicetextBox.Text = config[0].Substring(config[0].IndexOf("=") + 1);                SQLtextBox.Text = config[1].Substring(config[1].IndexOf("=") + 1);                UsertextBox.Text = config[2].Substring(config[2].IndexOf("=") + 1);                PWtextBox.Text = config[3].Substring(config[3].IndexOf("=") + 1);            }            catch (Exception ex)            {                MessageBox.Show("获取配置数据失败，可能无权限或文件出错！\n\n" + ex.ToString());            }        }

下面开始介绍代码:
先看看点击创建那个按钮,这个是主按钮:

        /// <summary>        /// 点击创建数据库        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void CreateButton_Click(object sender, EventArgs e)        {            DirectoryInfo folder = new DirectoryInfo(_SqlFile);            #region 检查数据            for (int i = 0; i < folder.GetFiles("*.sql").Length; i++)//遍历数据库文件            {                Sqlpath.Add(_SqlFile + @"\" + folder.GetFiles("*.sql")[i].Name);            }            if (Sqlpath[0].IndexOf(".sql") < 0 || Sqlpath.Count > 1)//脚本判断            {                MessageBox.Show("创建数据库失败，未找到数据库脚本文件！\r\n或有多个数据库脚本！请移出多余的脚本！");                return;            }            if (GetconnStr())//拼接连接字符串            {                return;            }            ArrayList DBList = new ArrayList();            DBList = GetAllDataBase(_server, _uid, _pwd);//获取所有数据库实例名            if (DBList.Count == 0)            {                return;            }           foreach (var item in DBList)            {                if (item.ToString() == _database)//判断是否有同名数据库                {                    MessageBox.Show("已有同名数据库:" + _database);                    ClearData();                    return;                }            }            CreateSqlFile();//拼接数据库语句            #endregion            if (!Directory.Exists(_path))//创建文件夹            {                Directory.CreateDirectory(_path);            }            #region 运行数据库脚本            string Cconn = "server=" + _server + ";uid=" + _uid + ";pwd=" + _pwd + ";database=master;Integrated Security=True";            FileInfo file = new FileInfo(Sqlpath[0].ToString());            string script = file.OpenText().ReadToEnd();            SqlConnection connection = new SqlConnection(Cconn);            Server server = new Server(new ServerConnection(connection));            try            {                server.ConnectionContext.ExecuteNonQuery(_Sql.ToString());                server.ConnectionContext.ExecuteNonQuery(_Sqlgo);                server.ConnectionContext.ExecuteNonQuery(script);                connection.Close();                MessageBox.Show("数据库创建成功!");            }            catch (Exception ex)            {                connection.Close();                ClearData();               MessageBox.Show("数据库创建失败:\r\n" + ex.ToString());                return;            }            connection.Close();            #endregion            UpWebconfig("ConnectionString", connStr);//更新Web配置文件            UpAppconfig("ConnectionString", connStr);//更新App配置文件            ClearData();           }

根据文件后缀来查找数据库脚本,无或多个则罢工.

统一给字段赋值:

        /// <summary>        /// 拼接字符串        /// </summary>        /// <returns></returns>        public bool GetconnStr()        {            if (SQLtextBox.Text.Trim() == "" || ServicetextBox.Text.Trim() == "" || UsertextBox.Text.Trim() == "" || PWtextBox.Text.Trim() == "" || PathtextBox.Text.Trim() == "")            {                MessageBox.Show("请将信息填完整！");                return true;            }            _database = SQLtextBox.Text.Trim();            _pwd = PWtextBox.Text.Trim();            _uid = UsertextBox.Text.Trim();            _server = ServicetextBox.Text.Trim();            _path = PathtextBox.Text.Trim();            connStr = "server=" + _server + ";database=" + _database + ";uid=" + _uid + ";pwd=" + _pwd + ";Connect Timeout=36000;";             return false;        }

为了防止用户故意找茬把信息放空,所以多了个判空......返回值可以设置成bool

判断是否有同名数据库:

        /// <summary>        /// 获取服务器所有数据库实例名        /// </summary>        /// <param name="server"></param>        /// <param name="usename"></param>        /// <param name="password"></param>        /// <returns></returns>        public ArrayList GetAllDataBase(string server, string usename, string password)        {            ArrayList DBNameList = new ArrayList();            SqlConnection conn = new SqlConnection(String.Format("server={0};database=master;uid={1};pwd={2}", server, usename, password));            DataTable dt = new DataTable();            try            {                SqlDataAdapter da = new SqlDataAdapter("select name from master..sysdatabases", conn);                lock (da)                {                    da.Fill(dt);                }            }            catch (Exception ex)            {                ClearData();                //只输出第一句错误,太长了                MessageBox.Show("获取服务器数据库实例失败:\r\n" + ex.ToString().Split("。".ToCharArray())[0] + "。\r\n请检查输入信息是否有误!");                return DBNameList;            }            foreach (DataRow row in dt.Rows)            {                DBNameList.Add(row["name"]);            }            return DBNameList;        }

获取服务器中所有数据库,逐个对比,同时测试链接也是这个方法...感觉画蛇添足了....

最后就是修改2个配置文件了,其实可以合并成一个方法,当时为了练手多弄了个...

为防止出错,把App.config配置文件也复制到网站根目录吧,不影响网站...

        /// <summary>        /// 更新Web.config文件(appSettings)        /// </summary>        private void UpWebconfig(string key, string sqlvalue)        {            XmlDocument xconfig = new XmlDocument();            try            {                xconfig.Load(_SqlFile + @"\Web.config");                XmlNode xnode = xconfig.SelectSingleNode(@"//appSettings");//切换节点                XmlElement xelem = (XmlElement)xnode.SelectSingleNode(@"//add[@key='" + key + "']");//读取key元素                if (xelem == null)                {                    MessageBox.Show("Web.config文件未找到key='" + key + "'的元素!!!");                    return;                }                xelem.Attributes["value"].Value = sqlvalue;//修改                xconfig.Save(_SqlFile + @"\Web.config");                MessageBox.Show("Web.config配置文件更新成功!");            }            catch (Exception ex)            {                MessageBox.Show("Web.config配置文件更新失败:\r\n" + ex.ToString());            }        }        /// <summary>        /// 更新App.config文件(appSettings)        /// </summary>        public void UpAppconfig(string key, string sqlvalue)        {            XmlDocument xconfig = new XmlDocument();            string StartPath = Application.StartupPath.Trim();            int intPos = StartPath.IndexOf("bin") - 1;            string strDirectoryPath = Path.Combine(Application.StartupPath.Substring(0, intPos), "App.config");            try            {                xconfig.Load(strDirectoryPath);//获取文件                XmlNode xnode = xconfig.SelectSingleNode(@"//appSettings");//切换节点                XmlElement xelem = (XmlElement)xnode.SelectSingleNode(@"//add[@key='" + key + "']");//读取key元素                if (xelem == null)                {                    MessageBox.Show("App.config文件未找到key='" + key + "'的元素!!!");                    return;                }                xelem.Attributes["value"].Value = sqlvalue;//修改                xconfig.Save(strDirectoryPath);                MessageBox.Show("App.config配置文件更新成功!");            }            catch (Exception ex)            {                MessageBox.Show("App.config配置文件更新失败:\r\n" + ex.ToString());            }        }

最后,也是第二个重点:

这是头文件:

using System;using System.Collections.Generic;using System.Data;using System.Text;using System.Configuration;using System.Windows.Forms;using System.IO;using System.Data.SqlClient;using System.Collections;using Microsoft.SqlServer.Management.Common;using Microsoft.SqlServer.Management.Smo;using System.Globalization;using System.Xml;

你会发现那2个sqlserver找不着,不管是VS默认的程序集,还是项目里.

因为这2只有安装了SQL2005或2008才能在安装目录下找到,惊不惊喜,意不意外...

其他版本SQL没试,路径大概是:C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

上传我的吧,在项目里引用就行:

http://download.csdn.net/download/qq_24554581/10040426

有了这2程序源,就可以把SQL语句直接丢到数据库运行.

如果实在找不着,又没积分下载的话,用ADO.NET吧...

end.