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;   }  } }}