C#實(shí)現(xiàn)較為實(shí)用的SQLhelper
更新時(shí)間：2016年10月22日 15:27:24 作者：天空的影子
這篇文章主要為大家詳細(xì)介紹了C#實(shí)現(xiàn)較為實(shí)用SQLhelper的相關(guān)資料,具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
第一次寫博客，想不到寫什么好b（￣▽￣）d ，考慮的半天決定從sqlhelper開始，sqlhelper對(duì)程序員來說就像helloworld一樣，很簡(jiǎn)單卻又很重要，helloworld代表著程序員萌新第一次寫代碼，而sqlhelper則是初次接觸數(shù)據(jù)庫（不知道這種說法對(duì)不對(duì)）。
好了不廢話了，下面直接上代碼（無話可說了）：
public class SQLHelper
{
// 超時(shí)時(shí)間
private static int Timeout = 1000;
// 數(shù)據(jù)庫名稱
public const String BestNet = "BestNet";
//存儲(chǔ)過程名稱
public const String UserInfoCURD = "UserInfoCURD";
// 數(shù)據(jù)庫連接字符串
private static Dictionary<String, String> ConnStrs = new Dictionary<String, String>();
/// <summary>
/// SQLServer操作類(靜態(tài)構(gòu)造函數(shù))
/// </summary>
static SQLHelper()
{
ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings;
foreach (ConnectionStringSettings config in configs)
{
ConnStrs.Add(config.Name, config.ConnectionString);
}
}
/// <summary>
/// 獲取數(shù)據(jù)庫連接
/// </summary>
/// <param name="database">數(shù)據(jù)庫(配置文件內(nèi)connectionStrings的name)</param>
/// <returns>數(shù)據(jù)庫連接</returns>
private static SqlConnection GetConnection(string database)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未設(shè)置參數(shù)：database");
}
if (!ConnStrs.ContainsKey(database))
{
throw new Exception("未找到數(shù)據(jù)庫：" + database);
}
return new SqlConnection(ConnStrs[database]);
}
/// <summary>
/// 獲取SqlCommand
/// </summary>
/// <param name="conn">SqlConnection</param>
/// <param name="transaction">SqlTransaction</param>
/// <param name="cmdType">CommandType</param>
/// <param name="sql">SQL</param>
/// <param name="parms">SqlParameter數(shù)組</param>
/// <returns></returns>
private static SqlCommand GetCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string sql, SqlParameter[] parms)
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = cmdType;
cmd.CommandTimeout = Timeout;
if (transaction != null)
cmd.Transaction = transaction;
if (parms != null && parms.Length != 0)
cmd.Parameters.AddRange(parms);
return cmd;
}
/// <summary>
/// 查詢數(shù)據(jù)，返回DataTable
/// </summary>
/// <param name="database">數(shù)據(jù)庫</param>
/// <param name="sql">SQL語句或存儲(chǔ)過程名</param>
/// <param name="parms">參數(shù)</param>
/// <param name="cmdType">查詢類型(SQL語句/存儲(chǔ)過程名)</param>
/// <returns>DataTable</returns>
public static DataTable QueryDataTable(string database, string sql, SqlParameter[] parms, CommandType cmdType)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未設(shè)置參數(shù)：database");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未設(shè)置參數(shù)：sql");
}
try
{
using (SqlConnection conn = GetConnection(database))
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
}
catch (SqlException ex)
{
System.Text.StringBuilder log = new System.Text.StringBuilder();
log.Append("查詢數(shù)據(jù)出錯(cuò)：");
log.Append(ex);
throw new Exception(log.ToString());
}
}
/// <summary>
/// 查詢數(shù)據(jù)，返回DataSet
/// </summary>
/// <param name="database">數(shù)據(jù)庫</param>
/// <param name="sql">SQL語句或存儲(chǔ)過程名</param>
/// <param name="parms">參數(shù)</param>
/// <param name="cmdType">查詢類型(SQL語句/存儲(chǔ)過程名)</param>
/// <returns>DataSet</returns>
public static DataSet QueryDataSet(string database, string sql, SqlParameter[] parms, CommandType cmdType)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未設(shè)置參數(shù)：database");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未設(shè)置參數(shù)：sql");
}
try
{
using (SqlConnection conn = GetConnection(database))
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
}
catch (SqlException ex)
{
System.Text.StringBuilder log = new System.Text.StringBuilder();
log.Append("查詢數(shù)據(jù)出錯(cuò)：");
log.Append(ex);
throw new Exception(log.ToString());
}
}
/// <summary>
/// 執(zhí)行命令獲取唯一值(第一行第一列)
/// </summary>
/// <param name="database">數(shù)據(jù)庫</param>
/// <param name="sql">SQL語句或存儲(chǔ)過程名</param>
/// <param name="parms">參數(shù)</param>
/// <param name="cmdType">查詢類型(SQL語句/存儲(chǔ)過程名)</param>
/// <returns>獲取值</returns>
public static object QueryScalar(string database, string sql, SqlParameter[] parms, CommandType cmdType)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未設(shè)置參數(shù)：database");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未設(shè)置參數(shù)：sql");
}
try
{
using (SqlConnection conn = GetConnection(database))
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
{
return cmd.ExecuteScalar();
}
}
}
catch (SqlException ex)
{
System.Text.StringBuilder log = new System.Text.StringBuilder();
log.Append("處理出錯(cuò)：");
log.Append(ex);
throw new Exception(log.ToString());
}
}
/// <summary>
/// 執(zhí)行命令更新數(shù)據(jù)
/// </summary>
/// <param name="database">數(shù)據(jù)庫</param>
/// <param name="sql">SQL語句或存儲(chǔ)過程名</param>
/// <param name="parms">參數(shù)</param>
/// <param name="cmdType">查詢類型(SQL語句/存儲(chǔ)過程名)</param>
/// <returns>更新的行數(shù)</returns>
public static int Execute(string database, string sql, SqlParameter[] parms, CommandType cmdType)
{
if (string.IsNullOrEmpty(database))
{
throw new Exception("未設(shè)置參數(shù)：database");
}
if (string.IsNullOrEmpty(sql))
{
throw new Exception("未設(shè)置參數(shù)：sql");
}
//返回(增刪改)的更新行數(shù)
int count = 0;
try
{
using (SqlConnection conn = GetConnection(database))
{
conn.Open();
using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
{
if (cmdType == CommandType.StoredProcedure)
cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;
count = cmd.ExecuteNonQuery();
if (count <= 0)
if (cmdType == CommandType.StoredProcedure)
count = (int)cmd.Parameters["@RETURN_VALUE"].Value;
}
}
}
catch (SqlException ex)
{
System.Text.StringBuilder log = new System.Text.StringBuilder();
log.Append("處理出錯(cuò)：");
log.Append(ex);
throw new Exception(log.ToString());
}
return count;
}
/// <summary>
/// 查詢數(shù)據(jù)，返回DataTable
/// </summary>
/// <param name="database">數(shù)據(jù)庫</param>
/// <param name="sql">SQL語句或存儲(chǔ)過程名</param>
/// <param name="cmdType">查詢類型(SQL語句/存儲(chǔ)過程名)</param>
/// <param name="values">參數(shù)</param>
/// <returns>DataTable</returns>
public static DataTable QueryDataTable(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
{
SqlParameter[] parms = DicToParams(values);
return QueryDataTable(database, sql, parms, cmdType);
}
/// <summary>
/// 執(zhí)行存儲(chǔ)過程查詢數(shù)據(jù)，返回DataSet
/// </summary>
/// <param name="database">數(shù)據(jù)庫</param>
/// <param name="sql">SQL語句或存儲(chǔ)過程名</param>
/// <param name="cmdType">查詢類型(SQL語句/存儲(chǔ)過程名)</param>
/// <param name="values">參數(shù)
/// <returns>DataSet</returns>
public static DataSet QueryDataSet(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
{
SqlParameter[] parms = DicToParams(values);
return QueryDataSet(database, sql, parms, cmdType);
}
/// <summary>
/// 執(zhí)行命令獲取唯一值(第一行第一列)
/// </summary>
/// <param name="database">數(shù)據(jù)庫</param>
/// <param name="sql">SQL語句或存儲(chǔ)過程名</param>
/// <param name="cmdType">查詢類型(SQL語句/存儲(chǔ)過程名)</param>
/// <param name="values">參數(shù)</param>
/// <returns>唯一值</returns>
public static object QueryScalar(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
{
SqlParameter[] parms = DicToParams(values);
return QueryScalar(database, sql, parms, cmdType);
}
/// <summary>
/// 執(zhí)行命令更新數(shù)據(jù)
/// </summary>
/// <param name="database">數(shù)據(jù)庫</param>
/// <param name="sql">SQL語句或存儲(chǔ)過程名</param>
/// <param name="cmdType">查詢類型(SQL語句/存儲(chǔ)過程名)</param>
/// <param name="values">參數(shù)</param>
/// <returns>更新的行數(shù)</returns>
public static int Execute(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
{
SqlParameter[] parms = DicToParams(values);
return Execute(database, sql, parms, cmdType);
}
/// <summary>
/// 創(chuàng)建參數(shù)
/// </summary>
/// <param name="name">參數(shù)名</param>
/// <param name="type">參數(shù)類型</param>
/// <param name="size">參數(shù)大小</param>
/// <param name="direction">參數(shù)方向(輸入/輸出)</param>
/// <param name="value">參數(shù)值</param>
/// <returns>新參數(shù)對(duì)象</returns>
public static SqlParameter[] DicToParams(IDictionary<string, object> values)
{
if (values == null) return null;
SqlParameter[] parms = new SqlParameter[values.Count];
int index = 0;
foreach (KeyValuePair<string, object> kv in values)
{
SqlParameter parm = null;
if (kv.Value == null)
{
parm = new SqlParameter(kv.Key, DBNull.Value);
}
else
{
Type t = kv.Value.GetType();
parm = new SqlParameter(kv.Key, NetToSql(kv.Value.GetType()));
parm.Value = kv.Value;
}
parms[index++] = parm;
}
return parms;
}
/// <summary>
/// .net類型轉(zhuǎn)換為Sql類型
/// </summary>
/// <param name="t">.net類型</param>
/// <returns>Sql類型</returns>
public static SqlDbType NetToSql(Type t)
{
SqlDbType dbType = SqlDbType.Variant;
switch (t.Name)
{
case "Int16":
dbType = SqlDbType.SmallInt;
break;
case "Int32":
dbType = SqlDbType.Int;
break;
case "Int64":
dbType = SqlDbType.BigInt;
break;
case "Single":
dbType = SqlDbType.Real;
break;
case "Decimal":
dbType = SqlDbType.Decimal;
break;
case "Byte[]":
dbType = SqlDbType.VarBinary;
break;
case "Boolean":
dbType = SqlDbType.Bit;
break;
case "String":
dbType = SqlDbType.NVarChar;
break;
case "Char[]":
dbType = SqlDbType.Char;
break;
case "DateTime":
dbType = SqlDbType.DateTime;
break;
case "DateTime2":
dbType = SqlDbType.DateTime2;
break;
case "DateTimeOffset":
dbType = SqlDbType.DateTimeOffset;
break;
case "TimeSpan":
dbType = SqlDbType.Time;
break;
case "Guid":
dbType = SqlDbType.UniqueIdentifier;
break;
case "Xml":
dbType = SqlDbType.Xml;
break;
case "Object":
dbType = SqlDbType.Variant;
break;
}
return dbType;
}
}
可以直接這樣調(diào)用：
IDictionary<string, object> values = new Dictionary<string, object>();
values.Add("@UserName", UserName);
values.Add("@PassWord", passWord);
object Scalar = SQLHelper.QueryScalar(SQLHelper.BestNet, SQLHelper.UserInfoCURD, CommandType.StoredProcedure, values);
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
您可能感興趣的文章:
- C#實(shí)現(xiàn)操作MySql數(shù)據(jù)層類MysqlHelper實(shí)例
- C#基于SQLiteHelper類似SqlHelper類實(shí)現(xiàn)存取Sqlite數(shù)據(jù)庫的方法
- 詳解使用C#編寫SqlHelper類
- C#編寫SqlHelper類
- c#中SqlHelper封裝SqlDataReader的方法
- C# SqlHelper應(yīng)用開發(fā)學(xué)習(xí)
- C# Oracle數(shù)據(jù)庫操作類實(shí)例詳解
- c#連接access數(shù)據(jù)庫操作類分享
- C#數(shù)據(jù)庫操作類AccessHelper實(shí)例
- C#實(shí)現(xiàn)的ACCESS數(shù)據(jù)庫操作類完整實(shí)例
- C#實(shí)現(xiàn)的封裝CURD到SqlHelper類用法簡(jiǎn)單分析
相關(guān)文章
C#入門學(xué)習(xí)之集合、比較和轉(zhuǎn)換
本文詳細(xì)講解了C#中的集合、比較和轉(zhuǎn)換，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-05-05
C#實(shí)現(xiàn)的UDP收發(fā)請(qǐng)求工具類實(shí)例
這篇文章主要介紹了C#實(shí)現(xiàn)的UDP收發(fā)請(qǐng)求工具類,結(jié)合具體實(shí)例形式分析了C#針對(duì)UDP請(qǐng)求的監(jiān)聽、接收、發(fā)送等相關(guān)操作技巧,需要的朋友可以參考下2017-06-06
C# dynamic關(guān)鍵字的使用方法，需要的朋友可以參考一下2013-04-04
這篇文章主要介紹了c# 通過WinAPI播放PCM聲音的方法，幫助大家更好的理解和使用c#編程語言，感興趣的朋友可以了解下2020-12-12
這篇文章主要介紹了C#調(diào)用Oracle存儲(chǔ)過程的方法,包含數(shù)據(jù)庫及C#對(duì)應(yīng)的調(diào)用代碼,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2015-01-01
這篇文章主要介紹了基于C#實(shí)現(xiàn)XML文件讀取工具類,涉及C#針對(duì)XML文件各節(jié)點(diǎn)獲取的相關(guān)技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2015-07-07
C#中字段、屬性、只讀、構(gòu)造函數(shù)賦值、反射賦值的問題
這篇文章主要介紹了C#中字段、屬性、只讀、構(gòu)造函數(shù)賦值、反射賦值的問題 ,非常不錯(cuò)，具有一定的參考借鑒借鑒價(jià)值，需要的朋友可以參考下2018-08-08
C#/.Net開發(fā)chatGPT、openAI的簡(jiǎn)單步驟
OpenAI處于科技行業(yè)下一件大事件的最前沿,具有初創(chuàng)公司史詩般的標(biāo)志,下面這篇文章主要給大家介紹了關(guān)于C#/.Net開發(fā)chatGPT和openAI的相關(guān)資料,需要的朋友可以參考下2023-02-02
這篇文章主要介紹了c# 字段和方法設(shè)計(jì)建議，幫助大家更好的理解和使用c#編程語言，感興趣的朋友可以了解下2020-11-11
C#/VB.NET?實(shí)現(xiàn)彩色PDF轉(zhuǎn)為灰度PDF
本文以C#代碼為例介紹如何實(shí)現(xiàn)將彩色PDF文件轉(zhuǎn)為灰度（黑白）的PDF文件，即將PDF文檔里面的彩色圖片或者文字等通過調(diào)用方法轉(zhuǎn)為文檔頁面為灰色調(diào)、無彩色效果的文檔?？靵砀S小編一起學(xué)習(xí)吧2021-11-11