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.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();
sqltransaction什么意思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>
/// <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))

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。