operatedatabase_pro.cs

以构建的方式来实现对通用的查询统计

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

namespace dist.hb.EnvQueryStat
{
  public class OperateDatabase
  {
    private System.String m_ConnectionString;
    private OleDbConnection m_DbConn = new OleDbConnection();

    public OperateDatabase(System.String connectionString)
    {
      this.m_ConnectionString = connectionString;
    }

    ~OperateDatabase()
    {
      this.m_DbConn.Dispose();
    }

    public OleDbConnection DbConn
    {
      get { return m_DbConn; }
    }

    public void OpenDatabase()
    {
      try
      {
        if (this.m_DbConn.State == ConnectionState.Closed)
        {
          this.m_DbConn.ConnectionString = this.m_ConnectionString;
          this.m_DbConn.Open();
        }
      }
      catch (Exception ex)
      {
        throw new ArgumentException(ex.Message);
      }
    }

    private void CloseDatabase()
    {
      try
      {
        if (this.m_DbConn.State != ConnectionState.Closed)
        {
          this.m_DbConn.Close();
        }
      }
      catch (Exception ex)
      {
        throw new ArgumentException(ex.Message);
      }
    }

    public int ExecuteNonquery(System.String sql, System.Boolean isStoredProcedure, params  System.Object[] parameters)
    {
      int iResult = -1;
      try
      {
        OleDbCommand cmd = new OleDbCommand(sql, this.m_DbConn);
        foreach (System.Object obj in parameters)
        {
          cmd.Parameters.Add((OleDbParameter)obj);
        }
        if (isStoredProcedure)
        {
          cmd.CommandType = CommandType.StoredProcedure;
        }
        this.OpenDatabase();
        iResult = cmd.ExecuteNonQuery();
        cmd.Dispose();
        this.CloseDatabase();
      }
      catch (Exception ex)
      {
        throw new ArgumentException(ex.Message);
      }
      return iResult;
    }


    public DataSet GetDataSet(System.String sql, System.Boolean isStoredProcedure, params System.Object[] parameters)
    {
      DataSet ds = new DataSet();
      try
      {
        this.m_DbConn.ConnectionString = this.m_ConnectionString;
        OleDbCommand cmd = new OleDbCommand(sql, this.m_DbConn);
        foreach (System.Object obj in parameters)
        {
          cmd.Parameters.Add((OleDbParameter)obj);
        }
        if (isStoredProcedure)
        {
          cmd.CommandType = CommandType.StoredProcedure;
        }
        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
        da.Fill(ds);
        da.Dispose();
        cmd.Dispose();
      }
      catch (Exception ex)
      {
        throw new ArgumentException(ex.Message);
      }
      return ds;
    }

    public System.Object ExecuteScalar(System.String sql, System.Boolean isStoredProcedure, params System.Object[] parameters)
    {
      System.Object obj = null;
      try
      {
        OleDbCommand cmd = new OleDbCommand(sql, this.m_DbConn);
        foreach (System.Object o in parameters)
        {
          cmd.Parameters.Add((OleDbParameter)o);
        }
        if (isStoredProcedure)
        {
          cmd.CommandType = CommandType.StoredProcedure;
        }
        this.OpenDatabase();
        obj = cmd.ExecuteScalar();
        cmd.Dispose();
        this.CloseDatabase();
      }
      catch (Exception ex)
      {
        throw new ArgumentException(ex.Message);
      }
      return obj;
    }
  }

}