c#数据库连接类

源文来自http://www.113317.com/blog/article.asp?id=491

今天空余时间写了个数据库操作仅供参考还末测试。如果有什么不正确的地方请多多指教。

程序代码

using System;
using System.Data;
using System.Data.OleDb;
namespace Power_AppCode
{
 public class DataBase
 {
  // 连接数据源
  private OleDbConnection conn = null;
  /// <summary>
  /// 数据源
  /// </summary>
  /// <param name="conStr">数据源连接字符串</param>
  public DataBase(string conStr)
  {
   conn = new OleDbConnection(conStr);
  }

  /// <summary>
  /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
  /// </summary>
  /// <param name="sql">查询语句</param>
  /// <returns>DataSet</returns>
  public DataSet GetDataSet(string sql)
  {

   DataSet ds = new DataSet();
   try
   {

    OleDbCommand cmd = new OleDbCommand(sql, conn);
    cmd.CommandTimeout = 20;
    System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
    adapter.Fill(ds, "tempTable");
   }
   catch (Exception e)
   {
    ds = null;
   }
   finally
   {
    this.Close();
   }

   return ds;

  }

  /// <summary>
  /// 根据SQL查询返回DataSet对象，如果没有查询到则返回NULL
  /// </summary>
  /// <param name="sql">查询语句</param>
  /// <param name="sRecord">开始记录数</param>
  /// <param name="mRecord">最大记录数</param>
  /// <returns>DataSet</returns>
  public DataSet GetDataSet(string sql, int sRecord, int mRecord)
  {

   DataSet ds = new DataSet();
   try
   {

    OleDbCommand cmd = new OleDbCommand(sql, conn);
    cmd.CommandTimeout = 20;

    System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
    adapter.Fill(ds, sRecord, mRecord, "tempTable");

   }
   catch (Exception e)
   {
    ds = null;
   }
   finally
   {
    this.Close();
   }

   return ds;

  }

  /// <summary>
  /// 对数据库的增，删，改的操作
  /// </summary>
  /// <param name="sql">SQL语句</param>
  /// <returns>是否成功</returns>
  public bool ExecuteDataBase(string sql)
  {
   bool succeed = false;
   int cnt = 0;
   try
   {
    OleDbCommand cmd = new OleDbCommand(sql, conn);
    cmd.CommandTimeout = 20;
    if (this.Open())
     cnt = cmd.ExecuteNonQuery();
   }
   catch (Exception e)
   {
    e.ToString();
   }
   finally
   {
    if (cnt > 0)
    {
     succeed = true;
    }
    this.Close();
   }

   return succeed;
  }

  /// <summary>
  /// 获得该SQL查询返回的第一行第一列的值，如果没有查询到则返回NULL
  /// </summary>
  /// <param name="sql">查询语句</param>
  /// <returns>返回的第一行第一列的值</returns>
  public string GetScalar(string sql)
  {
   string str = null;
   try
   {
    OleDbCommand cmd = new OleDbCommand(sql, conn);
    if (this.Open())
     str = cmd.ExecuteScalar().ToString();
   }
   catch (Exception e)
   {
    e.ToString();
   }
   finally
   {
    this.Close();
   }

   return str;
  }

  /// <summary>
  ///  获得该SQL查询返回DataTable，如果没有查询到则返回NULL
  /// </summary>
  /// <param name="sql">查询语句</param>
  /// <returns></returns>
  public DataTable GetDataTable(string sql)
  {
   DataTable tb = null;
   DataSet ds = this.GetDataSet(sql);
   if (ds != null)
   {
    tb = ds.Tables["tempTable"];
   }

   return tb;
  }

  /// <summary>
  /// 打开数据库连接.
  /// </summary>
  private bool Open()
  {
   bool succeed = false;
   try
   {

    if (conn.State == System.Data.ConnectionState.Closed)
    {
     conn.Open();
     succeed = true;
    }
    else if (conn.State == System.Data.ConnectionState.Broken)
    {
     conn.Close();
     conn.Open();
     succeed = true;
    }
    else if(conn.State == System.Data.ConnectionState.Open)
    {
     succeed = true;
    }
   }
   catch(Exception e)
   {
    e.ToString();
   }
           
   return succeed;
  }

  /// <summary>
  /// 关闭数据库连接
  /// </summary>
  public void Close()
  {
   if (conn != null)
   {
    conn.Close();
   }
  }

  /// <summary>
  /// 释放数据库连接资源
  /// </summary>
  public void Dispose()
  {
   if (conn != null)
   {
    conn.Dispose();
    conn = null;
   }
  }

 }
}