数据库操作类（2）

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Base
{
 /// <summary>
 /// DBClass 的摘要说明。
 /// </summary>
 public class DBClass
 {
  private SqlConnection m_conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnString"].ToString());
  private SqlCommand m_cmd = new SqlCommand();
  private string m_tableName = null;
  private int exeResult = -1;

  private DBClass(){}//不允许用户使用空的构造函数

  #region 析构函数
   ~DBClass()
  {
   m_cmd.Dispose();
   if(m_conn.State == ConnectionState.Open)
    m_conn.Close();
  }
  #endregion

  #region 构造函数
/// <summary>
/// 构造函数
/// </summary>
/// <param name="tableName">数据库类要操作的表名</param>
  public DBClass(string tableName)
  {
   m_tableName = tableName;
   m_cmd.Connection = m_conn;
  }
  #endregion

  #region 打开数据库
/// <summary>
/// 打开数据库
/// </summary>
  public void open()
  {
   if(m_conn.State == ConnectionState.Closed)
    m_conn.Open();
  }
  #endregion

  #region 关闭数据库
/// <summary>
/// 关闭数据库
/// </summary>
  public void close()
  {
   if(m_conn.State == ConnectionState.Open)
    m_conn.Close();
  }
  #endregion

  #region 执行Sql语句(包括插入,删除,更新)
/// <summary>
/// 执行Sql语句(包括插入,删除,更新)
/// </summary>
/// <param name="sql">要执行的语句</param>
/// <returns>返回执行是否成功</returns>
  public bool Execute(string sql)
  {
   m_cmd.CommandText = sql;
   m_conn.Open();
   exeResult = m_cmd.ExecuteNonQuery();
   m_conn.Close();
   if(exeResult > 0)
    return true;
   else return false;
  }
  #endregion

  #region 数据插入数据库
/// <summary>
/// 参数据入数据库
/// </summary>
/// <param name="insertSql">参入操作的Sql语句</param>
/// <returns>返回执行状态</returns>
  public bool insert(string insertSql)
  {
   return Execute(insertSql);
  }
  #endregion
  
  #region 指定域来插入数据
/// <summary>
/// 指定域来插入数据
/// </summary>
/// <param name="Fields">要插入的数据域</param>
/// <param name="Values">要插入的值</param>
/// <returns>返回执行状态</returns>
  public bool insert(string []Fields, string []Values)
  {
   if(Fields.Length != Values.Length)
    return false;
   string sql = "insert into " + m_tableName + "(";
   string values = "values(";
   string []para = new string[Fields.Length];
   for(int i = 0; i < Fields.Length-1; i++)
   {
    sql += Fields[i] + ",";
    values += "@" + Fields[i] + ",";
    para[i] = "@" + Fields[i];
   }
   sql += Fields[Fields.Length-1] + ")";
   values += "@" + Fields[Fields.Length-1] + ")";
   para[Fields.Length-1] = "@" + Fields[Fields.Length-1];
   sql += values;

   m_cmd.CommandText = sql;
   for(int i = 0; i < Fields.Length; i++)
   {
    m_cmd.Parameters.Add(new SqlParameter(para[i],Values[i]));
   }
   if(m_conn.State == ConnectionState.Closed)
    m_conn.Open();
   if(m_cmd.ExecuteNonQuery() > 0)
    return true;
   else return false;
  }
  #endregion

  #region 根据完整SQL语句删除数据
/// <summary>
/// 根据完整SQL语句参数删除数据
/// </summary>
/// <param name="deleteSql">要执行的Sql语句</param>
/// <returns>返回执行状态</returns>
  public bool delete(string deleteSql)
  {
   return Execute(deleteSql);
  }
  #endregion

  #region 删除整个表格
  /// <summary>
  /// 删除整个表格
  /// </summary>
  /// <returns></returns>
  public bool deleteAll()
  {
   string deleteall = "delete from "+m_tableName;
   return Execute(deleteall);
  }
  #endregion

  #region 根据指定的ID从表中删除数据
/// <summary>
/// 根据指定的ID从表中删除数据
/// </summary>
/// <param name="deleteID">要删除项的ID</param>
/// <returns>返回执行状态</returns>
  public bool delete(int deleteID)
  {
   string deletesql = "delete from " + m_tableName + " where id = " + deleteID;
   return Execute(deletesql);
  }
  #endregion

  #region 读取数据库表中的所有数据
/// <summary>
/// 读取数据库表中的所有数据
/// </summary>
/// <returns>返回DataSet数据集合</returns>

  public DataSet SelectAll()
  {
   SqlDataAdapter da = new SqlDataAdapter();
   DataSet ds = new DataSet();
   da.SelectCommand = new SqlCommand("select * from "+m_tableName, m_conn);
   da.Fill(ds);
   return ds;
  }
  #endregion

  #region 根据指定的ID从数据库读取数据项
/// <summary>
/// 根据指定的ID从数据库读取数据项
/// </summary>
/// <param name="id">要读取数据的ID</param>
/// <returns>返回读取的结果</returns>
  public SqlDataReader SelectItem(int id)
  {
   m_cmd.CommandText = "select * from " + m_tableName + " where id =" + id;
   m_conn.Open();
   SqlDataReader dr = m_cmd.ExecuteReader(CommandBehavior.CloseConnection);
   return dr;
  }

  #endregion

  public bool update(string updatesql)
  {
   return Execute(updatesql);
  }

  #region 用指定的数据更新数据库
  /// <summary>
  /// 用指定的数据更新数据库
  /// </summary>
  /// <param name="Fields">要更新的域</param>
  /// <param name="Values">更新的值</param>
  /// <param name="id">要更新的项</param>
  /// <returns>返回执行结果</returns>
  public bool update(string []Fields, string []Values, string id)
  {
   if(Fields.Length != Values.Length)
    return false;
   string sql = "update " + m_tableName + " set";
   string []para = new string[Fields.Length];
   for(int i=0; i < Fields.Length-1;i++)
   {
    para[i] = "@" + Fields[i];
    sql += Fields[i] + "=" + para[i] + ",";
   }
   para[Fields.Length-1] = "@" + Fields[Fields.Length-1];
   sql += Fields[Fields.Length-1] + para[Fields.Length-1];
   sql += " where id = " + id;

   m_cmd.CommandText = sql;
   for(int i=0; i < Fields.Length; i++)
   {
    m_cmd.Parameters.Add(new SqlParameter(para[i], Values[i]));
   }
   if(m_conn.State == ConnectionState.Closed)
    m_conn.Open();
   if(m_cmd.ExecuteNonQuery() > 0)
    return true;
   else return false;
  }
  #endregion
 }
}