C# 动态创建SQL数据库（一）

最近在做项目中要求能够要求动态添加数据库并建表。具体思路如下

1 提供数据名，根据数据库创建数据库

2 自定数据库与数据表，提供数据表自定与数据类型创建表

创建sqlhelper类，用于数据库操作

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

/******************************************************************* 
* Copyright (C)  版权所有
* 文件名称:SqlHelper
* 命名空间:TestRecentMenu
* 创建时间:2018/12/18 14:26:01
* 作    者: wangyonglai
* 描    述：
* 修改记录:
* 修改人:
* 版 本 号:v1.0.0
**********************************************************************/
namespace TestRecentMenu
{
    public static class SqlHelper
    {
        /// <summary>
        /// 使用锁防止多线程同时操作数据库表
        /// </summary>
        private static readonly object sqlLock = new object();

        /// <summary>
        /// SQL连接
        /// </summary>
        private static SqlConnection connection;

        private static string connStr = "server=127.0.0.1; database=; user id=sa;password=Root123";
        /// <summary>
        /// 创建SQL连接属性
        /// </summary>
        public static SqlConnection Connection
        {
            get
            {
                try
                {
                    if (connection == null)//如果没有创建连接，则先创建
                    {
                        //从配置文件中获取SQL连接字段
                        //string connStr = ConfigurationManager.ConnectionStrings["ConnetcionNmae"].ToString();

                        connection = new SqlConnection(connStr);//创建连接
                        connection.Open();//打开连接
                    }
                    else if (connection.State == ConnectionState.Broken)//如果连接中断，则重现打开
                    {
                        connection.Close();
                        connection.Open();
                    }
                    else if (connection.State == ConnectionState.Closed)//如果关闭，则打开
                    {
                        connection.Open();
                    }
                    return connection;
                }
                catch (Exception ex)
                {
                    if (connection != null)
                    {
                        connection.Close();
                        connection.Dispose();
                    }
                    //Log4netHelper.WriteErrorLog("Connection" + ex.Message, ex);
                    return null;
                }
            }
        }

        /// <summary>
        /// 重置连接
        /// </summary>
        public static void ResetConnection()
        {
            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
                connection = null;
            }
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="str">执行字符串</param>
        /// <returns></returns>
        public static DataSet GetDataSet(string str)
        {
            lock (sqlLock)
            {
                try
                {
                    SqlDataAdapter sda = new SqlDataAdapter(str, Connection);
                    DataSet ds = new DataSet();
                    sda.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    ResetConnection();
                    //Log4netHelper.WriteErrorLog(str, ex);
                    return null;
                }
            }
        }

        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="str"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string str, params SqlParameter[] values)
        {
            lock (sqlLock)
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = Connection;
                    cmd.CommandText = str;
                    cmd.Parameters.AddRange(values);
                    SqlDataAdapter sda = new SqlDataAdapter();
                    sda.SelectCommand = cmd;
                    DataSet dt = new DataSet();
                    sda.Fill(dt);
                    return dt;
                }
                catch (Exception ex)
                {
                    ResetConnection();
                    //Log4netHelper.WriteErrorLog(str, ex);
                    return null;
                }
            }
        }

        /// <summary>
        /// 获取表格
        /// </summary>
        /// <param name="str">执行字符串</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string str)
        {
            return GetDataSet(str).Tables[0];
        }

        /// <summary>
        /// 获取表格
        /// </summary>
        /// <param name="str">执行字符串</param>
        /// <param name="values">参数值数组</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string str, params SqlParameter[] values)
        {
            return GetDataSet(str, values).Tables[0];
        }


        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="str"></param>
        public static void ExecuteNonQuery(string str)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Connection;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = str;
                cmd.ExecuteNonQuery();
            }
            catch
            {
 
            }
            
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        public static bool ExecuteSql(string str)
        {
            lock (sqlLock)
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Connection;
                SqlTransaction trans = Connection.BeginTransaction();
                try
                {
                    if (cmd.Connection != null && cmd.Connection.State == ConnectionState.Open)
                    {
                        cmd.Transaction = trans;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = str;
                        cmd.ExecuteNonQuery();
                        trans.Commit();
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (SqlException ex)
                {
                    trans.Rollback();//事物回滚
                    //ResetConnection();
                    //Log4netHelper.WriteErrorLog(str, ex);
                    return false;
                }
            }
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="str"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static bool ExecuteSql(string str, params SqlParameter[] values)
        {
            lock (sqlLock)
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Connection;
                SqlTransaction trans = Connection.BeginTransaction();
                try
                {
                    if (cmd.Connection != null && cmd.Connection.State == ConnectionState.Open)
                    {
                        cmd.Transaction = trans;
                        cmd.CommandText = str;
                        cmd.Parameters.AddRange(values);
                        cmd.ExecuteNonQuery();
                        trans.Commit();
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (SqlException ex)
                {
                    trans.Rollback();//事物回滚
                    ResetConnection();
                    //Log4netHelper.WriteErrorLog(str, ex);
                    return false;
                }
            }
        }

    }
}

　 

编写调用函数

/// <summary>
        /// 判断数据库是否存在
        /// </summary>
        /// <param name="db">数据库名称</param>
        /// <returns></returns>
        public Boolean IsDBExist(string db)
        {
            string createDbStr = " select * from master.dbo.sysdatabases where name " + "= '" + db + "'";
            DataTable dt = SqlHelper.GetDataTable(createDbStr);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断数据库中指定表格是否存在
        /// </summary>
        /// <param name="db"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        public Boolean IsTableExist(string db, string tb)
        {
            string createTbStr = "USE " + db + " select 1 from sysobjects where id =object_id('" + tb + "') and type = 'U'";
            DataTable dt = SqlHelper.GetDataTable(createTbStr);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <param name="db"></param>
        public void CreateDataBase(string db)
        {
            if (IsDBExist(db))
            {
                throw new Exception("数据库已经存在！");
            }
            else//不存在则创建
            {
                string createDbStr = "Create DATABASE " + db;
                SqlHelper.ExecuteNonQuery(createDbStr);
                
            }
        }


        /// <summary>
        /// 创建数据库表
        /// </summary>
        /// <param name="db">数据库名</param>
        /// <param name="tb">表名</param>
        public void CreateDataTable(string db, string tb)
        {
            if (IsDBExist(db) == false)
            {
                throw new Exception("数据库不存在！");
            }
            if (IsTableExist(db, tb))
            {
                throw new Exception("数据库表已经存在！");
            }
            else
            {
                string content = "usseId int IDENTITY(1,1) PRIMARY KEY ,userName nvarchar(50)";
                string createTableStr = "USE " + db + " Create table " + tb + "(" + content + ")";

                SqlHelper.ExecuteNonQuery(createTableStr);
            }
        }

　　最后调用

private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                CreateDataBase(database);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

private void button2_Click(object sender, EventArgs e)
        {
            CreateDataTable(database, "usertb");
        }