跳转到主内容
极星编程网：以代码为星，赴技术山海！
📁 网站地图

C#实现的ACCESS数据库操作类完整实例

苏承栈
苏承栈
• 👁 0人浏览过

本文实例讲述了C#实现的ACCESS数据库操作类。分享给大家供大家参考，具体如下：这个是针对ACCESS数据库操作的类，同样也是从SQLHELPER提取而来，分页程序的调用可以参考MSSQL那个类的调用，差不多的，只是提取所有记录的数量的时候有多一个参数，这个需要注意一下！

using System;using System.Text;using System.Collections;using System.Collections.Specialized;using System.Data;using System.Data.OleDb;using System.Configuration;namespace HoverTree.Web.DBUtility{ ///

/// 数据访问抽象基础类(ACCESS)/// Copyright (C) 2006-2007 hovertree.net/// All rights reserved/// public abstract class DbHelperACE{//数据库连接字符串(web.config来配置)//public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];// public static string connectionString = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["AccessConnectionString"]);public static string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"];public DbHelperACE(){}#region 公用方法public static int GetMaxID(string FieldName, string TableName){string strsql = "select max(" + FieldName + ")+1 from " + TableName;object obj = DbHelperACE.GetSingle(strsql);if (obj == null){return 1;}else{return int.Parse(obj.ToString());}}public static bool Exists(string strSql){object obj = DbHelperACE.GetSingle(strSql);int cmdresult;if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){cmdresult = 0;}else{cmdresult = int.Parse(obj.ToString());}if (cmdresult == 0){return false;}else{return true;}}public static bool Exists(string strSql, params OleDbParameter[] cmdParms){object obj = DbHelperACE.GetSingle(strSql, cmdParms);int cmdresult;if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){cmdresult = 0;}else{cmdresult = int.Parse(obj.ToString());}if (cmdresult == 0){return false;}else{return true;}}#endregion#region 执行简单SQL语句/// /// 执行SQL语句，返回影响的记录数/// /// SQL语句/// 影响的记录数public static int ExecuteSql(string SQLString){using (OleDbConnection connection = new OleDbConnection(connectionString)){using (OleDbCommand cmd = new OleDbCommand(SQLString, connection)){try{connection.Open();int rows = cmd.ExecuteNonQuery();return rows;}catch (System.Data.OleDb.OleDbException E){connection.Close();throw new Exception(E.Message);}}}}/// /// 执行SQL语句，设置命令的执行等待时间/// /// /// /// public static int ExecuteSqlByTime(string SQLString, int Times){using (OleDbConnection connection = new OleDbConnection(connectionString)){using (OleDbCommand cmd = new OleDbCommand(SQLString, connection)){try{connection.Open();cmd.CommandTimeout = Times;int rows = cmd.ExecuteNonQuery();return rows;}catch (System.Data.OleDb.OleDbException E){connection.Close();throw new Exception(E.Message);}}}}/// /// 执行多条SQL语句，实现数据库事务。

///

/// 多条SQL语句public static void ExecuteSqlTran(ArrayList SQLStringList){using (OleDbConnection conn = new OleDbConnection(connectionString)){conn.Open();OleDbCommand cmd = new OleDbCommand();cmd.Connection = conn;OleDbTransaction tx = conn.BeginTransaction();cmd.Transaction = tx;try{for (int n = 0; n < SQLStringList.Count; n++){string strsql = SQLStringList[n].ToString();if (strsql.Trim().Length > 1){cmd.CommandText = strsql;cmd.ExecuteNonQuery();}}tx.Commit();}catch (System.Data.OleDb.OleDbException E){tx.Rollback();throw new Exception(E.Message);}}}/// /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)/// /// SQL语句/// 图像字节,数据库的字段类型为image的情况/// 影响的记录数public static int ExecuteSqlInsertImg(string strSQL, byte[] fs){using (OleDbConnection connection = new OleDbConnection(connectionString)){OleDbCommand cmd = new OleDbCommand(strSQL, connection);System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", SqlDbType.Image);myParameter.Value = fs;cmd.Parameters.Add(myParameter);try{connection.Open();int rows = cmd.ExecuteNonQuery();return rows;}catch (System.Data.OleDb.OleDbException E){throw new Exception(E.Message);}finally{cmd.Dispose();connection.Close();}}}/// /// 执行一条计算查询结果语句，返回查询结果（object）。

///

/// 计算查询结果语句/// 查询结果（object）public static object GetSingle(string SQLString){using (OleDbConnection connection = new OleDbConnection(connectionString)){using (OleDbCommand cmd = new OleDbCommand(SQLString, connection)){try{connection.Open();object obj = cmd.ExecuteScalar();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (System.Data.OleDb.OleDbException e){connection.Close();throw new Exception(e.Message);}}}}/// /// 执行查询语句，返回SqlDataReader(使用该方法切记要手工关闭SqlDataReader和连接)/// /// 查询语句/// SqlDataReaderpublic static OleDbDataReader ExecuteReader(string strSQL){OleDbConnection connection = new OleDbConnection(connectionString);OleDbCommand cmd = new OleDbCommand(strSQL, connection);try{connection.Open();OleDbDataReader myReader = cmd.ExecuteReader();return myReader;}catch (System.Data.OleDb.OleDbException e){throw new Exception(e.Message);}//finally //不能在此关闭，否则，返回的对象将无法使用//{// cmd.Dispose();// connection.Close();//}}/// /// 执行查询语句，返回DataSet/// /// 查询语句/// DataSetpublic static DataSet Query(string SQLString){using (OleDbConnection connection = new OleDbConnection(connectionString)){DataSet ds = new DataSet();try{connection.Open();OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);command.Fill(ds, "ds");}catch (System.Data.OleDb.OleDbException ex){throw new Exception(ex.Message);}return ds;}}/// /// 执行查询语句，返回DataSet,设置命令的执行等待时间/// /// /// /// public static DataSet Query(string SQLString, int Times){using (OleDbConnection connection = new OleDbConnection(connectionString)){DataSet ds = new DataSet();try{connection.Open();OleDbDataAdapter command = new OleDbDataAdapter(SQLString, connection);command.SelectCommand.CommandTimeout = Times;command.Fill(ds, "ds");}catch (System.Data.OleDb.OleDbException ex){throw new Exception(ex.Message);}return ds;}}#endregion#region 执行带参数的SQL语句/// /// 执行SQL语句，返回影响的记录数/// /// SQL语句/// 影响的记录数public static int ExecuteSql(string SQLString, params OleDbParameter[] cmdParms){using (OleDbConnection connection = new OleDbConnection(connectionString)){using (OleDbCommand cmd = new OleDbCommand()){try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);int rows = cmd.ExecuteNonQuery();cmd.Parameters.Clear();return rows;}catch (System.Data.OleDb.OleDbException E){throw new Exception(E.Message);}}}}/// /// 执行多条SQL语句，实现数据库事务。

///

/// SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter[]）public static void ExecuteSqlTran(Hashtable SQLStringList){using (OleDbConnection conn = new OleDbConnection(connectionString)){conn.Open();using (OleDbTransaction trans = conn.BeginTransaction()){OleDbCommand cmd = new OleDbCommand();try{//循环foreach (DictionaryEntry myDE in SQLStringList){string cmdText = myDE.Key.ToString();OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;PrepareCommand(cmd, conn, trans, cmdText, cmdParms);int val = cmd.ExecuteNonQuery();cmd.Parameters.Clear();trans.Commit();}}catch{trans.Rollback();throw;}}}}/// /// 执行一条计算查询结果语句，返回查询结果（object）。

///

/// 计算查询结果语句/// 查询结果（object）public static object GetSingle(string SQLString, params OleDbParameter[] cmdParms){using (OleDbConnection connection = new OleDbConnection(connectionString)){using (OleDbCommand cmd = new OleDbCommand()){try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);object obj = cmd.ExecuteScalar();cmd.Parameters.Clear();if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))){return null;}else{return obj;}}catch (System.Data.OleDb.OleDbException e){throw new Exception(e.Message);}}}}/// /// 执行查询语句，返回SqlDataReader (使用该方法切记要手工关闭SqlDataReader和连接)/// /// 查询语句/// SqlDataReaderpublic static OleDbDataReader ExecuteReader(string SQLString, params OleDbParameter[] cmdParms){OleDbConnection connection = new OleDbConnection(connectionString);OleDbCommand cmd = new OleDbCommand();try{PrepareCommand(cmd, connection, null, SQLString, cmdParms);OleDbDataReader myReader = cmd.ExecuteReader();cmd.Parameters.Clear();return myReader;}catch (System.Data.OleDb.OleDbException e){throw new Exception(e.Message);}//finally //不能在此关闭，否则，返回的对象将无法使用//{// cmd.Dispose();// connection.Close();//}}/// /// 执行查询语句，返回DataSet/// /// 查询语句/// DataSetpublic static DataSet Query(string SQLString, params OleDbParameter[] cmdParms){using (OleDbConnection connection = new OleDbConnection(connectionString)){OleDbCommand cmd = new OleDbCommand();PrepareCommand(cmd, connection, null, SQLString, cmdParms);using (OleDbDataAdapter da = new OleDbDataAdapter(cmd)){DataSet ds = new DataSet();try{da.Fill(ds, "ds");cmd.Parameters.Clear();}catch (System.Data.OleDb.OleDbException ex){throw new Exception(ex.Message);}return ds;}}}private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms){if (conn.State != ConnectionState.Open) conn.Open();cmd.Connection = conn;cmd.CommandText = cmdText;if (trans != null) cmd.Transaction = trans;cmd.CommandType = CommandType.Text;//cmdType;if (cmdParms != null){foreach (OleDbParameter parameter in cmdParms){if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&(parameter.Value == null)){parameter.Value = DBNull.Value;}cmd.Parameters.Add(parameter);}}}#endregion#region 获取根据指定字段排序并分页查询。

///

/// 分页查询数据记录总数获取/// /// ----要显示的表或多个表的连接/// ----主表的主键/// ----查询条件,不需where/// ----是否添加查询字段的 DISTINCT 默认0不添加/1添加/// public static string getPageListCounts(string _ID, string _tbName, string _strCondition, int _Dist){//---存放取得查询结果总数的查询语句//---对含有DISTINCT的查询进行SQL构造//---对含有DISTINCT的总数查询进行SQL构造string strTmp = "", SqlSelect = "", SqlCounts = "";if (_Dist == 0){SqlSelect = "Select ";SqlCounts = "COUNT(*)";}else{SqlSelect = "Select DISTINCT ";SqlCounts = "COUNT(DISTINCT " + _ID + ")";}if (_strCondition == string.Empty){strTmp = SqlSelect + " "+ SqlCounts + " FROM " + _tbName;}else{strTmp = SqlSelect + " "+ SqlCounts + " FROM " + " Where (1=1) " + _strCondition;}return strTmp;}/// /// 智能返回SQL语句/// /// 主键（不能为空）/// 提取字段（不能为空）/// 表（理论上允许多表）/// 条件（可以空）/// 排序，格式：字段名+""+ASC（可以空）/// 分页数（不能为空）/// 当前页，起始为：1（不能为空）/// public static string getPageListSql(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex){string strTmp = ""; //---strTmp用于返回的SQL语句string SqlSelect = "", SqlPrimaryKeySelect = "", strOrderBy = "", strWhere = " where 1=1 ", strTop = "";//0：分页数量//1:提取字段//2:表//3:条件//4:主键不存在的记录//5:排序SqlSelect = " select top {0} {1} from {2} {3} {4} {5}";//0:主键//1:TOP数量,为分页数*(排序号-1)//2:表//3:条件//4:排序SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) ";if (orderBy != "") strOrderBy = " order by " + orderBy;if (condition != "") strWhere += " and " + condition;int pageindexsize = (pageIndex - 1) * pageSize;if (pageindexsize > 0){strTop = " top " + pageindexsize.ToString();SqlPrimaryKeySelect = String.Format(SqlPrimaryKeySelect, primaryKey, strTop, tableName, strWhere, strOrderBy);strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, SqlPrimaryKeySelect, strOrderBy);}else{strTmp = String.Format(SqlSelect, pageSize.ToString(), queryFields, tableName, strWhere, "", strOrderBy);}return strTmp;}/// /// 获取根据指定字段排序并分页查询。DataSet/// /// 每页要显示的记录的数目/// 要显示的页的索引/// 要查询的数据表/// 要查询的字段,如果是全部字段请填写：*/// 主键字段，类似排序用到/// 是否为升序排列：0为升序，1为降序/// 查询的筛选条件/// 返回排序并分页查询的DataSetpublic static DataSet GetPagingList(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex){string sql = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex);return Query(sql);}public static string GetPagingListSQL(string primaryKey, string queryFields, string tableName, string condition, string orderBy, int pageSize, int pageIndex){string sql = getPageListSql(primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex);return sql;}public static int GetRecordCount(string _ID, string _tbName, string _strCondition, int _Dist){string sql = getPageListCounts( _ID, _tbName, _strCondition, _Dist);object obj = DbHelperACE.GetSingle(sql);if (obj == null){return 1;}else{return int.Parse(obj.ToString());}}#endregion}}

更多关于C#相关内容感兴趣的读者可查看本站专题：《C#程序设计之线程使用技巧总结》、《C#操作Excel技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》希望本文所述对大家C#程序设计有所帮助。

您可能感兴趣的文章:C#使用Oracle.ManagedDataAccess.dll组件连接Oracle数据库C# 操作 access 数据库的实例代码c# 使用Entity Framework操作Access数据库的示例C#连接Oracle数据库使用Oracle.ManagedDataAccess.dll C#编程实现连接ACCESS数据库实例详解C#动态创建Access数据库及表的方法c#连接access数据库操作类分享C# Access数据库增删查改的简单方法C#操作Access数据库的实现过程(vs2019)

相关文章