C#操作Access通用類實例。本站提示廣大學習愛好者：（C#操作Access通用類實例）文章只能為提供參考，不一定能成為您想要的結果。以下是C#操作Access通用類實例正文
本文實例講述了C#操作Access通用類。分享給年夜家供年夜家參考。詳細以下：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Collections;
/// <summary>
/// AcceHelper 的摘要解釋
/// </summary>
public static class AccessHelper
{
//數據庫銜接字符串
public static readonly string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
// 用於緩存參數的HASH表
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 給定銜接的數據庫用假定參數履行一個sql敕令（不前往數據集）
/// </summary>
/// <param name="connectionString">一個有用的銜接字符串</param>
/// <param name="commandText">存儲進程稱號或許sql敕令語句</param>
/// <param name="commandParameters">履行敕令所用參數的聚集</param>
/// <returns>履行敕令所影響的行數</returns>
public static int ExecuteNonQuery(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 用現有的數據庫銜接履行一個sql敕令（不前往數據集）
/// </summary>
/// <remarks>
///舉例:
/// int result = ExecuteNonQuery(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">一個現有的數據庫銜接</param>
/// <param name="commandText">存儲進程稱號或許sql敕令語句</param>
/// <param name="commandParameters">履行敕令所用參數的聚集</param>
/// <returns>履行敕令所影響的行數</returns>
public static int ExecuteNonQuery(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
///應用現有的SQL事務履行一個sql敕令（不前往數據集）
/// </summary>
/// <remarks>
///舉例:
/// int result = ExecuteNonQuery(trans, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="trans">一個現有的事務</param>
/// <param name="commandText">存儲進程稱號或許sql敕令語句</param>
/// <param name="commandParameters">履行敕令所用參數的聚集</param>
/// <returns>履行敕令所影響的行數</returns>
public static int ExecuteNonQuery(OleDbTransaction trans, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 用履行的數據庫銜接履行一個前往數據集的sql敕令
/// </summary>
/// <remarks>
/// 舉例:
/// OleDbDataReader r = ExecuteReader(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">一個有用的銜接字符串</param>
/// <param name="commandText">存儲進程稱號或許sql敕令語句</param>
/// <param name="commandParameters">履行敕令所用參數的聚集</param>
/// <returns>包括成果的讀取器</returns>
public static OleDbDataReader ExecuteReader(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
{
//創立一個SqlCommand對象
OleDbCommand cmd = new OleDbCommand();
//創立一個SqlConnection對象
OleDbConnection conn = new OleDbConnection(connectionString);
//在這裡我們用一個try/catch構造履行sql文本敕令/存儲進程，由於假如這個辦法發生一個異常我們要封閉銜接，由於沒有讀取器存在，
//是以commandBehaviour.CloseConnection 就不會履行
try
{
//挪用 PrepareCommand 辦法，對 SqlCommand 對象設置參數
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
//挪用 SqlCommand 的 ExecuteReader 辦法
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//消除參數
cmd.Parameters.Clear();
return reader;
}
catch
{
//封閉銜接，拋出異常
conn.Close();
throw;
}
}
/// <summary>
/// 前往一個DataSet數據集
/// </summary>
/// <param name="connectionString">一個有用的銜接字符串</param>
/// <param name="cmdText">存儲進程稱號或許sql敕令語句</param>
/// <param name="commandParameters">履行敕令所用參數的聚集</param>
/// <returns>包括成果的數據集</returns>
public static DataSet ExecuteDataSet(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
{
//創立一個SqlCommand對象，並對其停止初始化
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdText, commandParameters);
//創立SqlDataAdapter對象和DataSet
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
//填充ds
da.Fill(ds);
// 消除cmd的參數聚集
cmd.Parameters.Clear();
//前往ds
return ds;
}
catch
{
//封閉銜接，拋出異常
conn.Close();
throw;
}
}
}
/// <summary>
/// 用指定的數據庫銜接字符串履行一個敕令並前往一個數據集的第一列
/// </summary>
/// <remarks>
///例如:
/// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
///<param name="connectionString">一個有用的銜接字符串</param>
/// <param name="commandText">存儲進程稱號或許sql敕令語句</param>
/// <param name="commandParameters">履行敕令所用參數的聚集</param>
/// <returns>用 Convert.To{Type}把類型轉換為想要的 </returns>
public static object ExecuteScalar(string connectionString, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 用指定的數據庫銜接履行一個敕令並前往一個數據集的第一列
/// </summary>
/// <remarks>
/// 例如:
/// Object obj = ExecuteScalar(connString, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">一個存在的數據庫銜接</param>
/// <param name="commandText">存儲進程稱號或許sql敕令語句</param>
/// <param name="commandParameters">履行敕令所用參數的聚集</param>
/// <returns>用 Convert.To{Type}把類型轉換為想要的 </returns>
public static object ExecuteScalar(OleDbConnection connection, string cmdText, params OleDbParameter[] commandParameters)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 將參數聚集添加到緩存
/// </summary>
/// <param name="cacheKey">添加到緩存的變量</param>
/// <param name="cmdParms">一個將要添加到緩存的sql參數聚集</param>
public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters)
{
parmCache[cacheKey] = commandParameters;
}
/// <summary>
/// 找回緩存參數聚集
/// </summary>
/// <param name="cacheKey">用於找回參數的症結字</param>
/// <returns>緩存的參數聚集</returns>
public static OleDbParameter[] GetCachedParameters(string cacheKey)
{
OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms =(OleDbParameter[])((ICloneable)cachedParms).Clone();
return clonedParms;
}
/// <summary>
/// 預備履行一個敕令
/// </summary>
/// <param name="cmd">sql敕令</param>
/// <param name="conn">Sql銜接</param>
/// <param name="trans">Sql事務</param>
/// <param name="cmdText">敕令文本,例如：Select * from Products</param>
/// <param name="cmdParms">履行敕令的參數</param>
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
{
//斷定銜接的狀況。假如是封閉狀況，則翻開
if (conn.State != ConnectionState.Open)
conn.Open();
//cmd屬性賦值
cmd.Connection = conn;
cmd.CommandText = cmdText;
//能否須要用到事務處置
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = CommandType.Text;
//添加cmd須要的存儲進程參數
if (cmdParms != null)
{
foreach (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
願望本文所述對年夜家的C#法式設計有所贊助。
