最近在做項目中要求能夠要求動態添加資料庫並建表。具體思路如下 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");
}
