asp.net 數(shù)據(jù)庫連接類代碼(SQL)
public class SqlOperation
{
#region 屬性
/// <summary>
/// 保存在Web.config中的連接字符串
/// </summary>
protected static string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["hao"].ConnectionString;
/// <summary>
/// SqlConnection對(duì)象
/// </summary>
protected static SqlConnection conn = new SqlConnection();
/// <summary>
/// SqlCommand對(duì)象
/// </summary>
protected static SqlCommand comm = new SqlCommand();
#endregion
#region 內(nèi)部函數(shù)
/// <summary>
/// 打開數(shù)據(jù)庫連接
/// </summary>
private static void ConnectionOpen()
{
if (conn.State != ConnectionState.Open)
{
conn.Close();
conn.ConnectionString = connectionstring;
comm.Connection = conn;
try
{
conn.Open();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
}
/// <summary>
/// 關(guān)閉數(shù)據(jù)庫連接
/// </summary>
private static void ConnectionClose()
{
conn.Close();
conn.Dispose();
comm.Dispose();
}
#endregion
/// <summary>
/// 執(zhí)行SQL語句
/// </summary>
/// <param name="SqlString">要執(zhí)行的SQL語句</param>
public static void ExecuteSQL(string SqlString)
{
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// 執(zhí)行存儲(chǔ)過程
/// </summary>
/// <param name="ProcedureName">存儲(chǔ)過程名稱</param>
/// <param name="coll">存儲(chǔ)過程需要的參數(shù)集合</param>
public static void ExecuteProcedure(string ProcedureName, params SqlParameter[] coll)
{
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
comm.Parameters.Clear();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// 執(zhí)行Sql查詢并返回第一行的第一條記錄,返回object,使用時(shí)需要拆箱 -> unbox
/// </summary>
/// <param name="sqlstr">傳入的Sql語句</param>
/// <returns>返回object類型的第一行第一條記錄</returns>
public static object ExecuteScalar(string SqlString)
{
object obj = new object();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
obj = comm.ExecuteScalar();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return obj;
}
/// <summary>
/// 執(zhí)行SQL語句,同時(shí)進(jìn)行事務(wù)處理
/// </summary>
/// <param name="sqlstr">要執(zhí)行的SQL語句</param>
public static void ExecuteTransactionSQL(string SqlString)
{
SqlTransaction trans;
trans = conn.BeginTransaction();
comm.Transaction = trans;
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
comm.ExecuteNonQuery();
trans.Commit();
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
}
/// <summary>
/// 執(zhí)行指定SQL查詢,返回DataSet
/// </summary>
/// <param name="sqlstr">要執(zhí)行的SQL語句</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetBySQL(string SqlString)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// 通過存儲(chǔ)過程返回DataSet
/// </summary>
/// <param name="ProcedureName">存儲(chǔ)過程名稱</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetByProcedure(string ProcedureName, params SqlParameter[] coll)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Clear();
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
comm.CommandText = ProcedureName;
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// 通過存儲(chǔ)過程返回DataSet
/// </summary>
/// <param name="ProcedureName">存儲(chǔ)過程名稱</param>
/// <returns>DataSet</returns>
public static DataSet GetDataSetByProcedure(string ProcedureName)
{
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
try
{
ConnectionOpen();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
comm.Parameters.Clear();
da.SelectCommand = comm;
da.Fill(ds);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return ds;
}
/// <summary>
/// 返回指定sql語句的DataTable
/// </summary>
/// <param name="sqlstr">傳入的Sql語句</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableBySQL(string SqlString)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.CommandType = CommandType.Text;
comm.CommandText = SqlString;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
/// <summary>
/// 根據(jù)存儲(chǔ)過程返回DataTable
/// </summary>
/// <param name="ProcedureName">存儲(chǔ)過程名</param>
/// <param name="coll">SqlParameter集合</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByProcedure(string ProcedureName, params SqlParameter[] coll)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
for (int i = 0; i < coll.Length; i++)
{
comm.Parameters.Add(coll[i]);
}
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
/// <summary>
/// 根據(jù)存儲(chǔ)過程返回DataTable
/// </summary>
/// <param name="ProcedureName">存儲(chǔ)過程名稱</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTableByProcedure(string ProcedureName)
{
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
try
{
ConnectionOpen();
comm.Parameters.Clear();
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = ProcedureName;
da.SelectCommand = comm;
da.Fill(dt);
}
catch (Exception ex)
{
try
{
ConnectionClose();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
throw new Exception(ex.Message);
}
finally
{
ConnectionClose();
}
return dt;
}
}
- 一個(gè)ASP.NET的MYSQL的數(shù)據(jù)庫操作類自己封裝的
- asp.net Oracle數(shù)據(jù)庫訪問操作類
- asp.net下使用DbProviderFactories的數(shù)據(jù)庫操作類
- ASP.NET對(duì)SQLServer的通用數(shù)據(jù)庫訪問類
- asp.net下Oracle,SQL Server,Access萬能數(shù)據(jù)庫通用類
- asp.net 數(shù)據(jù)庫的連接和datatable類
- ASP.NET封裝的SQL數(shù)據(jù)庫訪問類
- ASP.NET web.config中數(shù)據(jù)庫連接字符串connectionStrings節(jié)的配置方法
- asp.net連接查詢SQL數(shù)據(jù)庫并把結(jié)果顯示在網(wǎng)頁上(2種方法)
- ASP.NET 6種常用數(shù)據(jù)庫的連接方法
- ASP.NET2.0 SQL Server數(shù)據(jù)庫連接詳解
- Asp.net把圖片存入數(shù)據(jù)庫和讀取圖片的方法
- ASP.NET數(shù)據(jù)庫操作類實(shí)例
相關(guān)文章
有潛在危險(xiǎn)的 Request.Form 值避免方法
在 .net framework 4.0中在 system.web 中加上httpRuntime requestValidationMode="2.0" 這句即可解決,需要的朋友可以了解下2013-12-12ASP.NET MVC學(xué)習(xí)之NuGet在VS中的運(yùn)用淺談
這篇文章主要給大家介紹了關(guān)于ASP.NET MVC學(xué)習(xí)之NuGet在VS中運(yùn)用的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧。2018-01-01.Net?Core跨平臺(tái)應(yīng)用開發(fā)串口篇HelloArm
這篇文章介紹了.Net?Core跨平臺(tái)應(yīng)用開發(fā)串口篇HelloArm,對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-01-01ASP.NET Core 1.0實(shí)現(xiàn)郵件發(fā)送功能
這篇文章主要為大家詳細(xì)介紹了ASP.NET Core 1.0實(shí)現(xiàn)郵件發(fā)送功能的相關(guān)資料,需要的朋友可以參考下2016-07-07ASP.NET JSON字符串與實(shí)體類的互轉(zhuǎn)換示例代碼
本篇文章主要是對(duì)ASP.NET JSON字符串與實(shí)體類的互轉(zhuǎn)換的示例代碼進(jìn)行了介紹,需要的朋友可以過來參考下,希望對(duì)大家有所幫助2014-01-01.NET?Core使用Autofac容器的DI依賴注入,IOC控制反轉(zhuǎn)及AOP切面編程
本文詳細(xì)講解了.NET?Core使用Autofac容器的DI依賴注入,IOC控制反轉(zhuǎn)及AOP切面編程,文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-02-02ASP.NET2.0緩存(Cache)技術(shù)深入理解
緩存技術(shù)是ASP.NET2.0非常重要的一個(gè)特性,它提供了一種非常好的本地?cái)?shù)據(jù)緩存機(jī)制,從而有效的提高數(shù)據(jù)訪問的性能2012-11-11ASP.NET MVC5使用MiniProfiler監(jiān)控MVC性能
這篇文章主要為大家詳細(xì)介紹了ASP.NET MVC5使用MiniProfiler監(jiān)控MVC性能,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2017-07-07