SQL访问类

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

namespace llin.DataOperate.Sql
{
 /// <summary>
 /// DbOperate 的摘要说明。
 /// </summary>
 public class DbOperate
 {
  private System.Data.SqlClient.SqlConnection SqlConnection=null; 
  private System.Data.SqlClient.SqlTransaction SqlTransaction=null;
  public System.Data.SqlClient.SqlCommand paramCommand=null;//用于带参数的SqlCommand类对象
  /// <summary>
  /// 与数据库连接的当前状态 
  /// </summary>
  public System.Data.ConnectionState State
  {
   get
   {
    return SqlConnection.State;
   }
  }

  public DbOperate()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
   SqlConnection=new SqlConnection();
  }

  public DbOperate(string strConn)
  {
   SqlConnection=new SqlConnection(strConn);
   paramCommand=SqlConnection.CreateCommand();
  }
       
  ///<summary>  
  ///打开与数据库中的连接
  ///</summary>
  public void Open()
  {
   try
   {
    if(this.State==System.Data.ConnectionState.Closed)
    {
     this.SqlConnection.Open();
    }
   }
   catch(Exception)
   {}
  }

  ///<summary>
  ///关闭与数据库的连接
  ///</summary>
  public void Close()
  {
   try
   {
    if(this.State!=System.Data.ConnectionState.Closed)
    {
     this.SqlConnection.Close();
    }
   }
   catch(Exception)
   {}
  }

  /// <summary>
  /// 更新数据集
  /// </summary>
  /// <param name="dataSet"></param>
  /// <returns></returns>
  public bool UpdateDataSet(DataSet dataSet,string selectString)
  {
   try
   {    
    SqlDataAdapter da=new SqlDataAdapter(selectString,this.SqlConnection);    
    SqlCommandBuilder builder=new SqlCommandBuilder(da);
    
    if(this.SqlTransaction!=null)
    {
     da.SelectCommand.Transaction=this.SqlTransaction;
     da.InsertCommand.Transaction=this.SqlTransaction;
     da.UpdateCommand.Transaction=this.SqlTransaction;
     da.DeleteCommand.Transaction=this.SqlTransaction;
    }
    if(dataSet.HasChanges(DataRowState.Added))
    {
     DataSet ds=dataSet.GetChanges(DataRowState.Added);
     da.Update(ds);
    }
    
    if(dataSet.HasChanges(DataRowState.Modified))
    {
     DataSet ds=dataSet.GetChanges(DataRowState.Modified);
     da.Update(ds);
    }

    if(dataSet.HasChanges(DataRowState.Deleted))
    {
     DataSet ds=dataSet.GetChanges(DataRowState.Deleted);
     da.Update(ds);
    }     
    return true;
   }
   catch(Exception ex)
   {     
    throw ex;
   }
  }

  ///<summary>
  ///开始事务
  ///</summary>
  public void BeginTrans()
  {
   try
   {
    this.Open();
    this.SqlTransaction=this.SqlConnection.BeginTransaction();
   }
   catch(Exception)
   {}
  }

  ///<summary>
  ///执行事务
  ///</summary>
  public void CommitTrans()
  {
   try
   {
    this.SqlTransaction.Commit();
    this.Close();
   }
   catch(Exception)
   {}
  }

  ///<summary>
  ///回滚事务
  ///</summary>
  public void RollBackTrans()
  {
   try
   {
    this.SqlTransaction.Rollback();
    this.Close();
   }
   catch(Exception)
   {}
  }

  ///<summary>
  ///清理所有正在使用的资源
  ///</summary>
  public void Dispose()
  {
   this.Close();
   GC.SuppressFinalize(true);
  }

  ///<summary>
  ///返回满足特定条件的记录数
  ///</summary>
  ///<param name=sqlString>select count(*) from table where...</param>
  ///<returns></returns>
  public int GetRecordCount(string sqlString)
  {
   int number=-1;
   SqlCommand command=new SqlCommand(sqlString,this.SqlConnection);
   command.Transaction=this.SqlTransaction;
   try
   {
    this.Open();
    number=Convert.ToInt32(command.ExecuteScalar().ToString());    
   }
   catch(Exception)
   {}
   return number;
  }

  ///<summary>
  ///增强数据适配器
  ///</summary>
  public void BuildAdapter(SqlDataAdapter adapter)
  {
   SqlCommandBuilder builder=new SqlCommandBuilder(adapter);
   adapter.DeleteCommand=builder.GetDeleteCommand();
   adapter.InsertCommand=builder.GetInsertCommand();
   adapter.UpdateCommand=builder.GetUpdateCommand();
  }

  ///<summary>
  ///数据适配器在事务中提交更新
  ///</summary>
  public void UpdateAdapter(string sqlString)
  {
   DataTable tbl=new DataTable();
   SqlDataAdapter da=new SqlDataAdapter(sqlString,this.SqlConnection);

   this.Open();
   da.Fill(tbl);
   SqlTransaction txn=this.SqlConnection.BeginTransaction();
   try
   {
    da.UpdateCommand.Transaction=txn;
    da.InsertCommand.Transaction=txn;
    da.DeleteCommand.Transaction=txn;

    //提交更改
    da.Update(tbl);

    //接受更改
    txn.Commit();
    this.Close();
   }
   catch(Exception)
   {
    txn.Rollback();
    this.Close();
   }
  }

  ///<summary>
  ///向数据库中提交查询，返回一个dataSet
  ///</summary>
  public DataSet ExecuteSelect(string sqlString)
  {
   DataSet dataSet=new DataSet();
   SqlDataAdapter da=new SqlDataAdapter(sqlString,this.SqlConnection);
   da.SelectCommand.Transaction=this.SqlTransaction;   
   da.Fill(dataSet);   
   return dataSet;   
  }

  public DataSet ExecuteSelect(string sqlString,string tableName)
  {
   DataSet dataSet=new DataSet();
   SqlDataAdapter da=new SqlDataAdapter(sqlString,this.SqlConnection);
   da.SelectCommand.Transaction=this.SqlTransaction;   
   da.Fill(dataSet,tableName);   
   return dataSet; 
  }

 

  ///<summary>
  ///向数据库中提交更新
  ///</summary>
  ///<param name="sqlString">插入、修改、删除的SQL语句</param>
  ///<returns>成功时返回0，失败时返回-1</returns>
  public int ExecuteUpdate(string sqlString)
  {
   try
   {
    this.Open();
    SqlCommand command=this.SqlConnection.CreateCommand();
    command.CommandText=sqlString;
    if(this.SqlTransaction!=null)
    {
     command.Transaction=this.SqlTransaction;
    }
    int lines=command.ExecuteNonQuery();
    return lines;
   }
   catch(Exception e)
   {    
    throw e;       
   }
  }
  /// <summary>
  /// 通过一个SELECT语句，判断返回的记录集是否为空
  /// </summary>
  /// <param name="selectString"></param>
  /// <returns>如果有记录返回true,否则返回false</returns>
  public bool HasRecord(string selectString)
  {
   try
   {
    this.Open();
    SqlCommand command=this.SqlConnection.CreateCommand();
    command.CommandText=selectString;
    command.Transaction=this.SqlTransaction;
    SqlDataReader reader=command.ExecuteReader();
    if(reader.Read())
    {
     reader.Close();     
     return true;
    }
    else
    {
     reader.Close();    
     return false;
    }

   }
   catch(Exception e)
   {    
    throw e;
   }
  }

  /// <summary>
  /// 通过一个选择单列的SQL语句，返回所取的值
  /// </summary>
  /// <param name="?"></param>
  /// <returns></returns>
  public object getValue(string selectString)
  {
   try
   {
    this.Open();
    SqlCommand command=this.SqlConnection.CreateCommand();
    command.CommandText=selectString;
    command.Transaction=this.SqlTransaction;
    object obj=command.ExecuteScalar();
    return obj;
   }
   catch(Exception e)
   {
    throw e;
   }
  }

  /// <summary>
  /// 用于带参数的数据库更新语句
  /// </summary>
  /// <param name="sqlString"></param>
  /// <returns></returns>
  public int ParamUpdate(string sqlString)
  {
   try
   {
    this.Open();
    if(this.SqlTransaction!=null)
    {
     this.paramCommand.Transaction=this.SqlTransaction;
    }
    int lines=this.paramCommand.ExecuteNonQuery();
    return lines;
   }
   catch(Exception e)
   {
    throw e;
   }
  }
 }
}