您好,登錄后才能下訂單哦!
這篇文章主要介紹C#中Sql數(shù)據(jù)庫SQLHelper類的示例代碼,文中介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們一定要看完!
using System;using System.Collections.Generic;using System.Text;using System.Collections;using System.Data.SqlClient;using System.Data;using System.Configuration; public class SQLHelper { //取得數(shù)據(jù)庫連接web.config 中配置 public static readonly string ConnectString = ConfigurationManager.ConnectionStrings["DBString"].ConnectionString; /// <summary> /// 無事務(wù),數(shù)據(jù)查詢 /// </summary> /// <param name="cmdType">存儲過程或Sql語句</param> /// <param name="cmdText">存儲過程名或Sql語句內(nèi)容</param> /// <param name="CommandParams">參數(shù)列表</param> /// <returns></returns> public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } catch { throw; } finally { conn.Close(); } } /// <summary> /// 有事務(wù),數(shù)據(jù)操作類 /// </summary> /// <param name="trans">事務(wù)</param> /// <param name="cmdType">操作類別 (stored procedure,sql)</param> /// <param name="cmdText">存儲過程名或Sql語句</param> /// <param name="CommandParams">參數(shù)</param> /// <returns>返回影響的數(shù)據(jù)行數(shù)</returns> public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); /*if (cmdType == CommandType.StoredProcedure) { cmd.Parameters.Add("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue; cmd.ExecuteNonQuery(); val = (int)cmd.Parameters["@RETURN_VALUE"].Value; } else if (cmdType==CommandType.Text) { val = cmd.ExecuteNonQuery(); }*/ int val = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return val; } /// <summary> /// 返回數(shù)據(jù)集 DataReader /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="CommandParams"></param> /// <returns></returns> public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } catch (Exception ex) { conn.Close(); // throw new Exception("操作失敗!"); throw new Exception(ex.Message); } } /// <summary> /// 有事務(wù)的取數(shù)據(jù) /// </summary> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="CommandParams"></param> /// <returns></returns> public static SqlDataReader ExecuteReader(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); return rdr; } public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } catch { throw; } finally { conn.Close(); } } public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return val; } /// <summary> /// 根據(jù)Sql語句取得表 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="CommandParams"></param> /// <returns></returns> public static DataTable ExecuteTable(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { DataTable temptable = new DataTable(); SqlCommand cmd = new SqlCommand(); SqlConnection conn = new SqlConnection(ConnectString); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder scb = new SqlCommandBuilder(da); da.Fill(temptable); } finally { conn.Close(); } return temptable; } public static DataTable ExecuteTable(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { DataTable temptable = new DataTable(); SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlCommandBuilder scb = new SqlCommandBuilder(da); da.Fill(temptable); cmd.Parameters.Clear(); return temptable; } /// <summary> /// 根據(jù)Sql語句或存儲過程取得數(shù)據(jù) /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="CommandParams"></param> /// <returns></returns> public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlConnection conn = new SqlConnection(ConnectString); SqlCommand cmd = new SqlCommand(); DataSet TempDataSet = new DataSet(); try { PrepareCommand(cmd, conn, null, cmdType, cmdText, CommandParams); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(TempDataSet); cmd.Parameters.Clear(); return TempDataSet; } finally { conn.Close(); } } public static DataSet ExecuteDataSet(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] CommandParams) { SqlCommand cmd = new SqlCommand(); DataSet TempDataSet = new DataSet(); PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, CommandParams); SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd); sqlAdapter.Fill(TempDataSet); cmd.Parameters.Clear(); return TempDataSet; } /// <summary> /// 生成Sql語句或準(zhǔn)備 /// </summary> /// <param name="cmd"></param> /// <param name="conn"></param> /// <param name="trans"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> 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); } } public static object ToDBValue(object value) { return value == null ? DBNull.Value : value; } public static object FromDBValue(object dbValue) { return dbValue == DBNull.Value ? null : dbValue; } }
以上是“C#中Sql數(shù)據(jù)庫SQLHelper類的示例代碼”這篇文章的所有內(nèi)容,感謝各位的閱讀!希望分享的內(nèi)容對大家有幫助,更多相關(guān)知識,歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。