Dieser Artikel beschreibt die Implementierung der Access Universal Access-Klasse OleDbHelper in C#. Ich teile es Ihnen als Referenz mit:
Ich arbeite derzeit an einer Projektdatenbank mit Access. Es war zunächst etwas umständlich. Der Betrieb der Datenbank unterscheidet sich geringfügig von dem von SqlServer. Nach mehreren Tagen wiederholter Suche nach Problemen habe ich endlich eine Klasse für den Zugriff auf die Access-Datenbank gelöst Spezialzugriff zum Betreiben der Datenbank, einschließlich der Ausführung von Datenbankbefehlen, der Rückgabe von DataSet, der Rückgabe eines einzelnen Datensatzes, der Rückgabe von DataReader, der allgemeinen Paging-Methode und mehreren anderen häufig verwendeten Betriebsmethoden. Bitte geben Sie Ihre Meinung ab, damit ich diesen Kurs verbessern kann. Obwohl es sich um einen Verweis auf SqlHelper handelt, ist es viel einfacher. Alle Codes lauten wie folgt:
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
namespace Common
{
/// <summary>
/// OleDb 书库访问类
/// </summary>
public static class OleDbHelper
{
/// <summary>
/// Access 的数据库连接字符串格式.
/// </summary>
public const string ACCESS_CONNECTIONSTRING_TEMPLATE = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};";
// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
/// </summary>
/// <param name="connString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static int ExecuteNonQuery(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 针对 System.Data.OleDb.OleDbCommand.Connection 执行 SQL 语句并返回受影响的行数.
/// </summary>
/// <param name="trans"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms, ConnectionActionType.None);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
/// </summary>
/// <param name="connString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static OleDbDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
OleDbDataReader rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 将 System.Data.OleDb.OleDbCommand.CommandText 发送到 System.Data.OleDb.OleDbCommand.Connection 并生成一个 System.Data.OleDb.OleDbDataReader.
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static OleDbDataReader ExecuteReader(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
OleDbDataReader rdr = cmd.ExecuteReader();
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
/// </summary>
/// <param name="connString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。忽略其他列或行.
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static object ExecuteScalar(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 执行查询，并返回查询所返回的结果数据集.
/// </summary>
/// <param name="connString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static DataSet ExecuteDataset(string connString, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.Open);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果数据集.
/// </summary>
/// <param name="conn"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
public static DataSet ExecuteDataset(OleDbConnection conn, CommandType cmdType, string cmdText, params OleDbParameter[] cmdParms)
{
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms, ConnectionActionType.AutoDetection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
/// <summary>
/// 缓存查询的 OleDb 参数对象.
/// </summary>
/// <param name="cacheKey"></param>
/// <param name="cmdParms"></param>
public static void CacheParameters(string cacheKey, params OleDbParameter[] cmdParms)
{
parmCache[cacheKey] = cmdParms;
}
/// <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[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
/// <summary>
/// 准备命令对象.
/// </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>
/// <param name="connActionType"></param>
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms, ConnectionActionType connActionType)
{
if (connActionType == ConnectionActionType.Open)
{
conn.Open();
}
else
{
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 (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
/// <summary>
/// 统一分页显示数据记录
/// </summary>
/// <param name="connString">数据库连接字符串</param>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">每页显示的条数</param>
/// <param name="fileds">显示的字段</param>
/// <param name="table">查询的表格</param>
/// <param name="where">查询的条件</param>
/// <param name="order">排序的规则</param>
/// <param name="pageCount">out：总页数</param>
/// <param name="recordCount">out：总条数</param>
/// <param name="id">表的主键</param>
/// <returns>返回DataTable集合</returns>
public static DataTable ExecutePager(string connString, int pageIndex, int pageSize, string fileds, string table, string where, string order, out int pageCount, out int recordCount, string id)
{
if (pageIndex < 1) pageIndex = 1;
if (pageSize < 1) pageSize = 10;
if (string.IsNullOrEmpty(fileds)) fileds = "*";
if (string.IsNullOrEmpty(order)) order = "ID desc";
using (OleDbConnection conn = new OleDbConnection(connString))
{
string myVw = string.Format(" {0} ", table);
string sqlText = string.Format(" select count(0) as recordCount from {0} {1}", myVw, where);
OleDbCommand cmdCount = new OleDbCommand(sqlText, conn);
if (conn.State == ConnectionState.Closed)
conn.Open();
recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());
if ((recordCount % pageSize) > 0)
pageCount = recordCount / pageSize + 1;
else
pageCount = recordCount / pageSize;
OleDbCommand cmdRecord;
if (pageIndex == 1)//第一页
{
cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, where, order), conn);
}
else if (pageIndex > pageCount)//超出总页数
{
cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, fileds, myVw, "where 1=2", order), conn);
}
else
{
int pageLowerBound = pageSize * pageIndex;
int pageUpperBound = pageLowerBound - pageSize;
string recordIDs = RecordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, id, myVw, where, order), pageUpperBound, conn);
cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {4} in ({2}) order by {3} ", fileds, myVw, recordIDs, order, id), conn);
}
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
}
private static string RecordID(string query, int passCount, OleDbConnection conn)
{
OleDbCommand cmd = new OleDbCommand(query, conn);
string result = string.Empty;
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (passCount < 1)
{
result += "," + dr.GetInt32(0);
}
passCount--;
}
}
return result.Substring(1);
}
/// <summary>
/// 连接操作类型枚举.
/// </summary>
enum ConnectionActionType
{
None = 0,
AutoDetection = 1,
Open = 2
}
}
}Nach dem Login kopieren
Das obige ist der detaillierte Inhalt vonC#-Beispielcode zum Implementieren der Access Universal Access-Klasse OleDbHelper. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
