一个不错的Access 数据访问类 (转载)

于 2005-09-25 13:47:00 发布
阅读量109 收藏
点赞数
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/michaelowenii/archive/2005/09/25/243673.html
此博客展示了一个C#语言编写的数据库查询类DBQuery。该类包含多个方法，如OpenQuery用于执行SQL查询，OpenTable可根据不同条件查询数据表，还有ExceuteNonQuery执行非查询SQL语句、ExecuteScalar返回单个值等，能满足多种数据库操作需求。

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

namespace _211
{
 /// <summary>
 /// DBQuery 的摘要说明。
 /// </summary>
 public sealed class DBQuery
 {
  static OleDbConnection conn=DBConnection.getConn();
  static DataTable dt;

  public static DataTable OpenQuery(string sql)
  {
   OleDbDataAdapter da=new OleDbDataAdapter(sql,conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName)
  {
   OleDbDataAdapter da=new OleDbDataAdapter("select * from "+TableName,conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,int count)
  {
   OleDbDataAdapter da=new OleDbDataAdapter("select top "+count.ToString()+" * from "+TableName,conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,string IndexColumn,bool IsAsc)
  {
   OleDbDataAdapter da=new OleDbDataAdapter("select * from "+TableName+" order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc"),conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,int count,string IndexColumn,bool IsAsc)
  {
   OleDbDataAdapter da=new OleDbDataAdapter("select top "+count.ToString()+" * from "+TableName+" order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc"),conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,string[] column,bool IsCondition)
  {
   string tempsql="";
   for(int i=0;i<column.Length;i++)
   {
    tempsql+=column[i];
    if(i<column.Length-1)
    {
     if(IsCondition)
      tempsql+=" and ";
     else
      tempsql+=",";
    }
   }

   ;
   string sql="";
   if(IsCondition)
    sql="select * from "+TableName+" where "+tempsql;
   else
    sql="select "+tempsql+" from "+TableName;

   OleDbDataAdapter da=new OleDbDataAdapter(sql,conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }


  public static DataTable OpenTable(string TableName,string[] column,string[] condition)
  {
   string tempsql="";
   string tempsql2="";

   for(int i=0;i<column.Length;i++)
   {
    tempsql+=column[i];
    if(i<column.Length-1)tempsql+=",";
   }

   for(int i=0;i<condition.Length;i++)
   {
    tempsql2+=condition[i];
    if(i<condition.Length-1)tempsql2+=" and ";
   }

   OleDbDataAdapter da=new OleDbDataAdapter("select "+tempsql+" from "+TableName+" where "+tempsql2,conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,int count,string[] column)
  {
   string tempsql="";
   for(int i=0;i<column.Length;i++)
   {
    tempsql+=column[i];
    if(i<column.Length-1)tempsql+=",";
   }

   OleDbDataAdapter da=new OleDbDataAdapter("select top "+count.ToString()+" "+tempsql+" from "+TableName,conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,int count,string[] column,string[] condition)
  {
   string tempsql="";
   string tempsql2="";

   for(int i=0;i<column.Length;i++)
   {
    tempsql+=column[i];
    if(i<column.Length-1)tempsql+=",";
   }

   for(int i=0;i<condition.Length;i++)
   {
    tempsql2+=condition[i];
    if(i<condition.Length-1)tempsql2+=" and ";
   }

   OleDbDataAdapter da=new OleDbDataAdapter("select top "+count.ToString()+" "+tempsql+" from "+TableName+" where "+tempsql2,conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,int count,string[] column,string IndexColumn,bool IsAsc)
  {
   string tempsql="";
   for(int i=0;i<column.Length;i++)
   {
    tempsql+=column[i];
    if(i<column.Length-1)tempsql+=",";
   }

   OleDbDataAdapter da=new OleDbDataAdapter("select top "+count.ToString()+" "+tempsql+" from "+TableName+" order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc"),conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,int count,string[] column,string[] condition,string IndexColumn,bool IsAsc)
  {
   string tempsql="";
   string tempsql2="";

   for(int i=0;i<column.Length;i++)
   {
    tempsql+=column[i];
    if(i<column.Length-1)tempsql+=",";
   }

   for(int i=0;i<condition.Length;i++)
   {
    tempsql2+=condition[i];
    if(i<condition.Length-1)tempsql2+=" and ";
   }

   OleDbDataAdapter da=new OleDbDataAdapter("select top "+count.ToString()+" "+tempsql+" from "+TableName+" where "+tempsql2+" order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc"),conn);
   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,string[] column,string IndexColumn,bool IsAsc,int PageSize,int Page)
  {
   string tempsql="";
   for(int i=0;i<column.Length;i++)
   {
    tempsql+=column[i];
    if(i<column.Length-1)tempsql+=",";
   }

   string sql="";
   if(Page==0)
    sql="select top "+PageSize.ToString()+" "+tempsql+" from "+TableName+" order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc");
   else
    sql="select top "+PageSize.ToString()+" "+tempsql+" from "+TableName+" where "+IndexColumn+(IsAsc?">":"<")+"(select "+(IsAsc?"max":"min")+"("+IndexColumn+") from "+TableName+" where "+IndexColumn+" in (select top "+(Page*PageSize)+" "+IndexColumn+" from "+TableName+" order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc")+")) order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc");

   OleDbDataAdapter da=new OleDbDataAdapter(sql,conn);

   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static DataTable OpenTable(string TableName,string[] column,string[] condition,string IndexColumn,bool IsAsc,int PageSize,int Page)
  {
   string tempsql="";
   string tempsql2="";

   for(int i=0;i<column.Length;i++)
   {
    tempsql+=column[i];
    if(i<column.Length-1)tempsql+=",";
   }

   for(int i=0;i<condition.Length;i++)
   {
    tempsql2+=condition[i];
    if(i<condition.Length-1)tempsql2+=" and ";
   }

   string sql="";
   if(Page==0)
    sql="select top "+PageSize.ToString()+" "+tempsql+" from "+TableName+" where "+tempsql2+" order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc");
   else
    sql="select top "+PageSize.ToString()+" "+tempsql+" from "+TableName+" where "+tempsql2+" and "+IndexColumn+(IsAsc?">":"<")+"(select "+(IsAsc?"max":"min")+"("+IndexColumn+") from "+TableName+" where "+IndexColumn+" in (select top "+(Page*PageSize)+" "+IndexColumn+" from "+TableName+" where "+tempsql2+" order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc")+")) order by "+IndexColumn+" "+(IsAsc?"Asc":"Desc");

   OleDbDataAdapter da=new OleDbDataAdapter(sql,conn);

   dt=new DataTable();
   try
   {
    da.Fill(dt);
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    da.Dispose();
   }

   return dt;
  }

  public static void ExceuteNonQuery(string sql)
  {
   OleDbCommand cmd=new OleDbCommand(sql,conn);
   conn.Open();
   try
   {
    cmd.ExecuteNonQuery();
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    cmd.Dispose();
    conn.Close();
   }
  }

  public static object ExecuteScalar(string sql)
  {
   object o;

   OleDbCommand cmd=new OleDbCommand(sql,conn);
   conn.Open();
   try
   {
    o=cmd.ExecuteScalar();
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    cmd.Dispose();
    conn.Close();
   }

   return o;
  }
 }
}

转载于:https://www.cnblogs.com/michaelowenii/archive/2005/09/25/243673.html

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值