最近在做项目中要求能够要求动态添加数据库并建表。具体思路如下
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");
}