通用的处理ACCESS数据库的类

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#学习
上一篇C#加密解密的类
想对作者说点什么? 我来说一句

VB.Net访问Access数据库操作示例

2009年06月05日 139KB 下载

没有更多推荐了,返回首页

关闭
关闭