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");
        }