欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C# 动态创建SQL数据库

程序员文章站 2022-06-25 08:23:48
最近在做项目中要求能够要求动态添加数据库并建表。具体思路如下 1 提供数据名,根据数据库创建数据库 2 自定数据库与数据表,提供数据表自定与数据类型创建表 创建sqlhelper类,用于数据库操作 编写调用函数 最后调用 ......

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

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