Accesss資料庫的DBhelper類（帶分頁）

首先配置web.config，使配置文件連接access資料庫：

<connectionStrings>

<add name="DBConnection" connectionString="Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|#lcng.mdb" />  //資料庫在本網站App_Data的文件夾中
</connectionStrings>

下麵是DBhelper類：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Reflection;
using System.Security.Cryptography;

public class DBHelper
{
public static string connectionString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
//不帶參數的執行命令
public static int ExecuteCommand(string safeSql)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbCommand cmd = new OleDbCommand(safeSql, connection);
return cmd.ExecuteNonQuery();
}
}
//帶參數的執行命令
public static int ExecuteCommand(string sql, params OleDbParameter[] values)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbCommand cmd = new OleDbCommand(sql, connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
}

public static int GetScalar(string safeSql)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbCommand cmd = new OleDbCommand(safeSql, connection);
return Convert.ToInt32(cmd.ExecuteScalar());
}
}
/// <summary>
/// 得到一個欄位的值
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static string GetOneValue(string sql)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
using (OleDbCommand cmd = new OleDbCommand(sql, connection))
{
object obj = cmd.ExecuteScalar();
cmd.Prepare();
return obj != null ? obj.ToString() : string.Empty;
}
}
}

public static int GetScalar(string sql, params OleDbParameter[] values)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
connection.Open();
OleDbCommand cmd = new OleDbCommand(sql, connection);
cmd.Parameters.AddRange(values);
return Convert.ToInt32(cmd.ExecuteScalar());
}
}

public static OleDbDataReader GetReader(string safeSql)
{
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand cmd = new OleDbCommand(safeSql, connection);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

public static OleDbDataReader GetReader(string sql, params OleDbParameter[] values)
{
OleDbConnection connection = new OleDbConnection(connectionString);
connection.Open();
OleDbCommand cmd = new OleDbCommand(sql, connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}

public static DataSet GetDataSet(string safeSql)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand(safeSql, connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds;
}
}

public static DataTable GetDataSet(string sql, params OleDbParameter[] values)
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand(sql, connection);
cmd.Parameters.AddRange(values);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
}
/// <summary>
/// 分頁使用
/// </summary>
/// <param name="query"></param>
/// <param name="passCount"></param>
/// <returns></returns>
private static string recordID(string query, int passCount)
{
using (OleDbConnection m_Conn = new OleDbConnection(connectionString))
{
m_Conn.Open();
OleDbCommand cmd = new OleDbCommand(query, m_Conn);
string result = string.Empty;
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (passCount < 1)
{
result += "," + dr.GetInt32(0);
}
passCount--;
}
}
m_Conn.Close();
m_Conn.Dispose();
return result.Substring(1);
}
}
/// <summary>
/// ACCESS高效分頁
/// </summary>
/// <param name="pageIndex">當前頁碼</param>
/// <param name="pageSize">分頁容量</param>
/// <param name="strKey">主鍵</param>
/// <param name="showString">顯示的欄位</param>
/// <param name="queryString">查詢字元串，支持聯合查詢</param>
/// <param name="whereString">查詢條件，若有條件限制則必須以where 開頭</param>
/// <param name="orderString">排序規則</param>
/// <param name="pageCount">傳出參數：總頁數統計</param>
/// <param name="recordCount">傳出參數：總記錄統計</param>
/// <returns>裝載記錄的DataTable</returns>
public static DataTable ExecutePager(int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount)
{
if (pageIndex < 1) pageIndex = 1;
if (pageSize < 1) pageSize = 10;
if (string.IsNullOrEmpty(showString)) showString = "*";
if (string.IsNullOrEmpty(orderString)) orderString = strKey + " asc ";
using (OleDbConnection m_Conn = new OleDbConnection(connectionString))
{
m_Conn.Open();
string myVw = string.Format(" ( {0} ) tempVw ", queryString);
string where1 = string.Empty;
if (String.IsNullOrEmpty(whereString))
{
where1 = "";
}
else
{
where1 = whereString.Trim();
if (where1.StartsWith("and", StringComparison.CurrentCultureIgnoreCase))
{
where1 = where1.Substring(3);
}
}
string where2 = String.IsNullOrEmpty(where1) ? "" : "where " + where1;
OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, where2), m_Conn);
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, showString, myVw, where2, orderString), m_Conn);
}
else if (pageIndex > pageCount)//超出總頁數
{
cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_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, strKey, myVw, where2, orderString), pageUpperBound);
cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw, strKey, recordIDs, orderString), m_Conn);

}
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
m_Conn.Close();
m_Conn.Dispose();
return dt;
}
}
/// <summary>
/// MD5加密
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static string MD5DecryptString(string str)
{
MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
byte[] md5Source = System.Text.Encoding.UTF8.GetBytes(str);
byte[] md5Out = md5.ComputeHash(md5Source);
return Convert.ToBase64String(md5Out);
}

/// <summary>
/// DES加密字元串
/// </summary>
/// <param name="sInputString">輸入字元</param>
/// <param name="sKey">Key</param>
/// <returns>加密結果</returns>
public string DESEncryptString(string sInputString, string sKey)
{
try
{
byte[] data = Encoding.Default.GetBytes(sInputString);
byte[] result;
DESCryptoServiceProvider DES = new DESCryptoServiceProvider();
DES.Key = ASCIIEncoding.ASCII.GetBytes(sKey); //密鑰
DES.IV = ASCIIEncoding.ASCII.GetBytes(sKey); //初始化向量
ICryptoTransform desencrypt = DES.CreateEncryptor(); //加密器對象
result = desencrypt.TransformFinalBlock(data, 0, data.Length); //轉換指定位元組數組的指定區域
return BitConverter.ToString(result);
}
catch (Exception ex)
{
//ex.Message = "DES加密異常";
throw ex;
}
}
/// <summary>
/// DES解密字元串
/// </summary>
/// <param name="sInputString">輸入字元</param>
/// <param name="sKey">Key</param>
/// <returns>解密結果</returns>
public string DESDecryptString(string sInputString, string sKey)
{
try
{
//將字元串轉換為位元組數組
string[] sInput = sInputString.Split("-".ToCharArray());
byte[] data = new byte[sInput.Length];
byte[] result;
for (int i = 0; i < sInput.Length; i++)
{
data[i] = byte.Parse(sInput[i], System.Globalization.NumberStyles.HexNumber);
}

DESCryptoServiceProvider DES = new DESCryptoServiceProvider();
DES.Key = ASCIIEncoding.ASCII.GetBytes(sKey);
DES.IV = ASCIIEncoding.ASCII.GetBytes(sKey);
ICryptoTransform desencrypt = DES.CreateDecryptor();
result = desencrypt.TransformFinalBlock(data, 0, data.Length);
return Encoding.Default.GetString(result);
}
catch (Exception ex)
{
//ex.Message = "DES解密異常";
throw ex;
}
}
}