Visual C# 動態操作 SQL Server 資料庫執行個體教程：通用資料訪問類（SqlHelper）

本文介紹的通用資料庫訪問類，是本人的個人網站實際使用的一個基類，它是一組通用的訪問資料庫的代碼集，在本人網站對資料庫的訪問絕大部分都使用這一個類。其主要功能有：

1.判斷資料庫是否存在？

2.判斷資料庫表是否存在？

3.判斷資料庫預存程序是否存在？

4.判斷視圖是否存在？

5.自動建立資料庫

6.自動建立資料庫表、預存程序

7.不帶參數的 SQL 陳述式ExecuteNonQuery的執行方法

8.執行一條不返回結果的SqlCommand。通過指定專用的連接字串，執行一個不需要傳回值的SqlCommand命令。

9.執行一條不返回結果的SqlCommand。通過一個已經存在的資料庫事件處理

10執行一條不返回結果的SqlCommand，通過一個已經存在的資料庫事物處理

11.執行一條返回結果集的SqlCommand命令，通過專用的連接字串。

12.執行一條返回第一條記錄第一列的SqlCommand命令，通過專用的連接字串。

13.執行一條返回第一條記錄第一列的SqlCommand命令，通過已經存在的資料庫連接。

其代碼清單如下：

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Collections;
using System.Data.SqlClient;

///

/// 資料庫的通用存取碼
/// 此類為抽象類別，不允許執行個體化，在應用時直接調用即可
///

public abstract class SqlHelper
{
//擷取資料庫連接字串，其屬於靜態變數且唯讀，項目中所有文檔可以直接使用，但不能修改
public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["MysqldataConnectionString"].ConnectionString;

// 雜湊表用來儲存緩衝的參數資訊，雜湊表可以儲存任意類型的參數。
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

///

///判斷資料庫是否存在
/// 通過指定專用的連接字串，執行一個不需要傳回值的SqlCommand命令。
///

///
/// 使用樣本：
/// bool databaseExist = SqlHelper.CheckExistsDatebase(dataBaseNameStr);
///
//////
public static bool CheckExistsDatebase(string dataBaseName)
{
string connstring = SqlHelper.ConnectionStringLocalTransaction;
String dataBaseNameStr = "select count(1) From master.dbo.sysdatabases where name='" + dataBaseName + "'";
using (SqlConnection con = new SqlConnection(connstring))
{

con.Open();
SqlCommand cmd = new SqlCommand(dataBaseNameStr, con);
int result = Convert.ToInt32(cmd.ExecuteScalar());
if (result == 0)
{
return false;
}
else
{
return true;
}
}
}
///

返回頁頭
///判斷資料庫表是否存在
/// 通過指定專用的連接字串，執行一個不需要傳回值的SqlCommand命令。
///

///
/// 使用樣本：
/// bool databaseExist = SqlHelper.CheckExistsDatebase(dataBaseNameStr);
///
//////
public static bool CheckExistsTable(string dataBaseNameStr, string tablename)
{
string connstring = "server=“伺服器名”;database='" + dataBaseNameStr + "'" + ";Trusted_Connection=SSPI";
String tableNameStr = "select count(1) from sysobjects where name = '" + tablename + "'";
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
SqlCommand cmd = new SqlCommand(tableNameStr, con);
int result = Convert.ToInt32(cmd.ExecuteScalar());
if (result == 0)
{
return false;
}
else
{
return true;
}
}
}
///

返回頁頭
///判斷資料庫預存程序是否存在
/// 通過指定專用的連接字串，執行一個不需要傳回值的SqlCommand命令。
///

///
/// 使用樣本：
/// bool databaseExist = SqlHelper.CheckExistsDatebase(dataBaseNameStr);
///
/////////
public static bool CheckExistsProc(string dataBaseNameStr, string procName)
{
string connstring = "server=“伺服器名”;database='" + dataBaseNameStr + "'" + ";Trusted_Connection=SSPI";
//String procNameStr = "select count(1) from sysobjects where name = '" + procName + "'";
String procNameStr = "select count(1) from sysobjects where name = '" + procName + "'" + " AND type = 'P'";
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
SqlCommand cmd = new SqlCommand(procNameStr, con);
int result = Convert.ToInt32(cmd.ExecuteScalar());
if (result == 0)
{
return false;
//不存在
}
else
{
con.Close();
return true;
//存在
}
}
}
///

返回頁頭
///判斷視圖是否存在
///

///
/// 使用樣本：
/// bool databaseExist = SqlHelper.CheckExistsDatebase(dataBaseNameStr);
///
/////////
public static bool CheckExistsView(string dataBaseNameStr, string tablename, string ViewName)
{
string connstring = "server=“伺服器名”;database='" + dataBaseNameStr + "'" + ";Trusted_Connection=SSPI";
String viewNameStr = "select count(1) from sysobjects where name = '" + ViewName + "'" + " AND type = 'V'";
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
SqlCommand cmd = new SqlCommand(viewNameStr, con);
int result = Convert.ToInt32(cmd.ExecuteScalar());
if (result == 0)
{
return false;
//不存在
}
else
{
con.Close();
return true;
//存在
}
}
}
///

返回頁頭
///調用ExecuteNonQuery()執行 建立資料庫
/// 通過指定專用的連接字串，執行一個不需要傳回值的SqlCommand命令。
///

///
/// 使用樣本：
/// SqlHelper.CreateSqlDatabase(connstring, "master", CreateStr);
///
////////////
public static bool CreateSqlDatabase(string connstring, string DatabaseName, string CreateStr)
{
//
using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connstring))
{
SqlCommand Command = new System.Data.SqlClient.SqlCommand(CreateStr, conn);
conn.Open();
Command.Connection.ChangeDatabase(DatabaseName);
try
{
Command.ExecuteNonQuery();
}
catch (System.Exception ex)
{
Command.Connection.Close();
throw ex;
}
finally
{
Command.Connection.Close();
}
}
return true;
}
///

///調用ExecuteNonQuery()執行 建立資料庫表、預存程序
/// 通過指定專用的連接字串，執行一個不需要傳回值的SqlCommand命令。
///

///
/// 使用樣本：
/// SqlHelper.CreateSqlTable(connstring, TableStr);
///
/////////
public static bool CreateSqlTable(string connstring, string CreateStr)
{
//
using (SqlConnection conn = new SqlConnection(connstring))
{
SqlCommand Command = new SqlCommand(CreateStr, conn);
conn.Open();
try
{
Command.ExecuteNonQuery();
return true;
}
catch (System.Exception ex)
{
return false;
}
finally
{
Command.Connection.Close();
}
}
}

///

///不帶參數的 SQL 陳述式ExecuteNonQuery的執行方法
/// 通過指定專用的連接字串，執行一個不需要傳回值的SqlCommand命令。
/// 使用參數數組形式提供參數列表
///

///
/// 使用樣本：
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/////////////// 返回一個數值表示此SqlCommand命令執行後影響的行數
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{

SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
//通過PrePareCommand方法將參數逐個加入到SqlCommand的參數集合中
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();

//清空SqlCommand中的參數列表
cmd.Parameters.Clear();
return val;
}
}

///

///執行一條不返回結果的SqlCommand，通過一個已經存在的資料庫連接
/// 使用參數數組提供參數
///

///
/// 使用樣本：
/// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/////////////// 返回一個數值表示此SqlCommand命令執行後影響的行數
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}

///

/// 執行一條不返回結果的SqlCommand，通過一個已經存在的資料庫事物處理
/// 使用參數數組提供參數
///

///
/// 使用樣本：
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/////////////// 返回一個數值表示此SqlCommand命令執行後影響的行數
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}

///

/// 執行一條返回結果集的SqlCommand命令，通過專用的連接字串。
/// 使用參數數組提供參數
///

///
/// 使用樣本：
/// SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/////////////// 返回一個包含結果的SqlDataReader
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);

// 在這裡使用try/catch處理是因為如果方法出現異常，則SqlDataReader就不存在，
//CommandBehavior.CloseConnection的語句就不會執行，觸發的異常由catch捕獲。
//關閉資料庫連接，並通過throw再次引發捕捉到的異常。
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}

///

/// 執行一條返回第一條記錄第一列的SqlCommand命令，通過專用的連接字串。
/// 使用參數數組提供參數
///

///
/// 使用樣本：
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/////////////// 返回一個object類型的資料，可以通過 Convert.To{Type}方法轉換類型
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();

using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}

///

/// 執行一條返回第一條記錄第一列的SqlCommand命令，通過已經存在的資料庫連接。
/// 使用參數數組提供參數
///

///
/// 使用樣本：
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
///
/////////////// 返回一個object類型的資料，可以通過 Convert.To{Type}方法轉換類型
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{

SqlCommand cmd = new SqlCommand();

PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}

///

/// 緩衝參數數組
///

//////public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}

///

/// 擷取被緩衝的參數
///

////// 返回緩衝的參數數組
public static SqlParameter[] GetCachedParameters(string cacheKey)
{
SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

if (cachedParms == null)
return null;

//建立一個參數的複製列表
SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

//通過迴圈為複製參數列表賦值
for (int i = 0, j = cachedParms.Length; i < j; i++)
//使用clone方法複製參數列表中的參數
clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

return clonedParms;
}

///

/// 為執行命令準備命令參數
///

//////////////////private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{

//判斷資料庫連接狀態
if (conn.State != ConnectionState.Open)
conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;

//判斷是否需要事物處理
if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}返回頁頭

以上代碼中，大多數都加了注釋，Using除處理對命名空間的引用外，當 Using用“{}”定義一個範圍，在陳述式完成時會自動釋放語句內所使用的資源。參數資料的添加由PrepareCommand方法來完成。要瞭解更多情況可查看本人網站的相關內容。