通用的处理ACCESS数据库的类

原创 2005年04月29日 23:05:00

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;


 //AInte都要修改为你的类名,下面所有函数都要改一下

namespace AInte
{
 /// <summary>
 /// DbAccess类,即进行数据库访问时需要调用的类
 /// </summary>
 public  class DbAccess:System.Web.UI.Page
 {
  /// <summary>
  /// DbAccess构造函数
  /// </summary>
  
  public DbAccess()
  {
  }

  /// <summary>
  /// 无条件查询操作,即查询表中所有记录
  /// </summary>
  /// <param name="strTableName">表名</param>
  /// <param name="strColumn">列名组</param>
  /// <returns>无条件查询结果</returns>
  public static void  SelectAll(ref DataSet ds,string strTableName, string[] strColumn)
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();
   try
   {
    string strSql = "SELECT ";
    for(int i = 0; i < strColumn.Length - 1; i++)
    {
     strSql += (strColumn[i] + ", ");
    }
    strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName);
    command.CommandText = strSql;
    ada.SelectCommand = command;   
    ada.Fill(ds);
    concreteDbTrans.Commit();
   }
   catch
   {
    concreteDbTrans.Rollback();
    ds.Clear();
    throw;
   }
   finally
   {
    conn.Close();
   } 
  }
  /// <summary>
  /// 条件查询guojin
  /// </summary>
  /// <param name="ds"></param>
  /// <param name="strSql">SQL语句</param>
  public static void SelectBySql(ref DataSet ds,string strSql)
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();

   command.CommandText = strSql;
   ada.SelectCommand = command;   
   ada.Fill(ds);
   concreteDbTrans.Commit();

  }


  /// <summary>
  /// 条件查询操作
  /// </summary>
  /// <param name="strTableName">表名</param>
  /// <param name="strColumn">列名组</param>
  /// <param name="strCondition">条件</param>
  /// <returns>条件查询结果</returns>
  public static DataSet Select(string tableName, string[] columnNameArray, string conditionString)
  {
   DataSet dataSet= new DataSet();
   AInte.DbAccess db = new DbAccess();
   string connectionString=db.getconn();
   OleDbConnection concreteConnection=new OleDbConnection(connectionString);
   concreteConnection.Open();
   OleDbCommand concreteCommand=new OleDbCommand();
   OleDbDataAdapter concreteDataAdapter;
   //OleDbTransaction concreteTransaction=concreteConnection.BeginTransaction();
   concreteCommand.Connection=concreteConnection;
   //concreteCommand.Transaction=concreteTransaction;
   try
   {
    string commandString = "SELECT ";
    for(int i = 0; i < columnNameArray.Length - 1; i++)
    {
     commandString += (columnNameArray[i] + ", ");
    }
    commandString += (columnNameArray[columnNameArray.Length - 1] + " FROM " + "'" + tableName + "'" +" WHERE " + conditionString);
    concreteCommand.CommandText = commandString;
    concreteDataAdapter = new OleDbDataAdapter(commandString,concreteConnection);  
    concreteDataAdapter.Fill(dataSet);
    //concreteTransaction.Commit();
   }
   catch(Exception eee)
   {
    string xxx=eee.Message;
    //concreteTransaction.Rollback();
    dataSet.Clear();
    return null;
   }
   finally
   {
    //concreteConnection.Close();
   }
   return dataSet;
  }

  /// <summary>
  /// 可排序的条件查询操作
  /// </summary>
  /// <param name="ds">引用</param>
  /// <param name="strTableName">表名</param>
  /// <param name="strColumn">列名组</param>
  /// <param name="strCondition">条件</param>
  /// <param name="orderColumn">排序列</param>
  /// <param name="riseorfall">升序或降序</param>
  public static void OrderSelect(ref DataSet ds, string strTableName, string[] strColumn, string strCondition, string orderColumn, bool riseorfall)
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();;

   try
   {
    string strSql = "SELECT ";
    for(int i = 0; i < strColumn.Length - 1; i++)
    {
     strSql += (strColumn[i] + ", ");
    }
    strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName + " WHERE " + strCondition);
    if(orderColumn != "")
    {
     strSql += " ORDER BY " + orderColumn;
     if(riseorfall)
     {
      strSql += " ASC";
     }
     else
     {
      strSql += " DESC";
     }
    }
    command.CommandText = strSql;
    ada.SelectCommand = command;   
    ada.Fill(ds);
    concreteDbTrans.Commit();
   }
   catch
   {
    concreteDbTrans.Rollback();
    ds.Clear();
    throw;
   }
   finally
   {
    conn.Close();
   }
  }

  /// <summary>
  /// 单条记录的插入操作
  /// </summary>
  /// <param name="strTableName">表名</param>
  /// <param name="strColumn">列名组</param>
  /// <param name="strValue">值组</param>
  public static void Insert(string strTableName, string[] strColumn, object[] strValue)
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();;

   try
   {
    string strSql = "INSERT INTO " + strTableName + " (";
    for(int i = 0; i < strColumn.Length - 1; i++)
    {
     strSql += (strColumn[i] + ", ");
    }
    strSql += (strColumn[strColumn.Length - 1] + ") VALUES ('");
    for(int i = 0; i < strValue.Length - 1; i++)
    {
     strSql += (strValue[i] + "', '");
    }
    strSql += (strValue[strValue.Length - 1] + "')");
    command.CommandText = strSql;
    command.ExecuteNonQuery();
    concreteDbTrans.Commit();
   }
   catch
   {
    concreteDbTrans.Rollback();
    throw;
   }
   finally
   {
    conn.Close();
   }  
  }

  /// <summary>
  /// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录
  /// </summary>
  /// <param name="ds">批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名</param>
  public static void InsertSet(ref DataSet ds)
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();;
   
   try
   {
    foreach(DataTable dt in ds.Tables)
    {
     foreach(DataRow dr in dt.Rows)
     {
      string strSql = "INSERT INTO " + dt.TableName + " (";
      for(int i = 0; i < dt.Columns.Count - 1; i++)
      {
       strSql += (dt.Columns[i].Caption + ", ");
      }
      strSql += (dt.Columns[dt.Columns.Count - 1].Caption + ") VALUES ('");
      for(int i = 0; i < dt.Columns.Count - 1; i++)
      {
       strSql += (dr[i] + "', '");
      }
      strSql += (dr[dt.Columns.Count - 1] + "')");
      command.CommandText = strSql;
      command.ExecuteNonQuery();
     }
    }
    concreteDbTrans.Commit();
   }
   catch
   {
    concreteDbTrans.Rollback();
    throw;
   }

   finally
   {
    conn.Close();
   }  
  }

  /// <summary>
  /// 无条件删除操作,即删除表中所有记录
  /// </summary>
  /// <param name="strTableName">表名</param>
  public static void DeleteAll(string strTableName)
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();;
   try
   {
    string strSql = "DELETE FROM " + strTableName;
    command.CommandText = strSql;
    command.ExecuteNonQuery();
    concreteDbTrans.Commit();
   }
   catch
   {
    concreteDbTrans.Rollback();
    throw;
   }
   finally
   {
    conn.Close();
   }  
  }

  /// <summary>
  /// 条件删除操作
  /// </summary>
  /// <param name="strTableName">表名</param>
  /// <param name="strCondition">条件</param>
  public static bool Delete(string strTableName, string strCondition)//修改成了Bool类型的返回值
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();;
   try
   {
    string strSql = "DELETE FROM " + strTableName + " WHERE " + strCondition;
    command.CommandText = strSql;
    command.ExecuteNonQuery();
    concreteDbTrans.Commit();
    return true;
   }
   catch
   {
    concreteDbTrans.Rollback();
    throw;
   }
   finally
   {
    conn.Close();
   }
  }

  /// <summary>
  /// 无条件更新操作,即更新表中所有记录
  /// </summary>
  /// <param name="strTableName">表名</param>
  /// <param name="strColumn">列名组</param>
  /// <param name="strValue">值组</param>
  public static void UpdateAll(string strTableName, string[] strColumn, object[] strValue)
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();;
   try
   {
    string strSql = "UPDATE " + strTableName + " SET ";
    for(int i = 0; i < strColumn.Length - 1; i++)
    {
     strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
    }
    strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' ");
    command.CommandText = strSql;
    command.ExecuteNonQuery();
    concreteDbTrans.Commit();
   }
   catch
   {
    concreteDbTrans.Rollback();
    throw;
   }
   finally
   {
    conn.Close();
   }
  }

  /// <summary>
  /// 条件更新操作
  /// </summary>
  /// <param name="strTableName">表名</param>
  /// <param name="strColumn">列名组</param>
  /// <param name="strValue">值组</param>
  /// <param name="strCondition">条件</param>
  public static void Update(string strTableName, string[] strColumn, object[] strValue, string strCondition)
  {
   AInte.DbAccess db = new DbAccess();
   string constring = db.getconn();
   OleDbConnection conn = new OleDbConnection(constring);
   conn.Open();
   OleDbCommand command = new OleDbCommand();
   OleDbTransaction concreteDbTrans = conn.BeginTransaction();    
   command.Connection = conn;
   command.Transaction = concreteDbTrans;
   OleDbDataAdapter ada = new OleDbDataAdapter();;
   try
   {
    string strSql = "UPDATE " + strTableName + " SET ";
    for(int i = 0; i < strColumn.Length - 1; i++)
    {
     strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
    }
    strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' " + " WHERE " + strCondition);
    command.CommandText = strSql;
    command.ExecuteNonQuery();
    concreteDbTrans.Commit();
   }
   catch
   {
    concreteDbTrans.Rollback();
    throw;
   }
   finally
   {
    conn.Close();
   }
  }

  public string getconn()
  {
   string ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+ Server.MapPath("") +"file://AInte.mdb/"; //需要修改为你的数据库名
   return ConString;
  }
 }
 
}

C#实现Access通用底层数据库类【源代码】

using System; using System.Collections.Generic; using System.Text; using System.Data; using Syst...

Oracle,SQL Server,Access万能数据库通用类DBHelper

using System; using System.Collections; using System.Collections.Specialized; using System.Data; ...
  • fjyts
  • fjyts
  • 2011年04月04日 16:36
  • 3259

asp.net下Oracle,SQL Server,Access万能数据库通用类

ASP.NET屏蔽底层数据库(Access,Sqlserver,Oracle)的经典代码 using System; using System.Collections; using Syst...
  • zz_mm
  • zz_mm
  • 2012年05月30日 10:46
  • 1043

C#通用数据库操作类 支持Access/MSSql/Orale/MySql等数据库

using System; using System.Collections.Generic; using System.Data; using System.Data.Common; n...

C#通用数据库操作类 支持Access/MSSql/Orale/MySql等数据库

出处:http://blog.csdn.net/smartsmile2012/article/details/8235297 using System;   using System.Colle...

C#通用数据库操作类 支持Access/MSSql/Orale/MySql等数据库

[csharp] view plaincopyprint? using System;   using System.Collections.Generic;   u...
  • ksp416
  • ksp416
  • 2015年09月18日 11:27
  • 840

C#---数据库访问通用类、Access数据库操作类、mysql类

//C# 数据库访问通用类 (ADO.NET) using System; using System.Collections.Generic; using System.Text; using Sys...

Access和Excel数据库表32位64位程序通用访问方法

从前在写读excel文件的程序时,会采用这样的oledb数据连接字符串: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Ext...
  • hsg77
  • hsg77
  • 2014年06月26日 15:50
  • 4230

一个通用数据库操作组件DBUtil(c#)、支持SqlServer、Oracle、Mysql、Access、SQLITE

支持情况       支持SQLSERVER、oracle、mysql、sqlite、access等oldeb连接。 使用说明 以sqlserver为例 1.添加对DBUtil.dll的引用,添加命名...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:通用的处理ACCESS数据库的类
举报原因:
原因补充:

(最多只允许输入30个字)