关闭

数据操作层的基类,主要封装了数据的增,删,改,查功能

858人阅读 评论(0) 收藏 举报

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

namespace DataAccess
{
 /// <summary>
 /// 数据操作层的基类,主要封装了数据的增,删,改,查功能
 /// </summary>
 public abstract class BaseAccess
 {
  #region 私有和保护成员

  protected DbManager  _DbManager = null;    // 数据库连接对象
  protected  string _TableName    = string.Empty ; // 表名
  protected  string _PKName       = string.Empty ; // 主键字段名

  #endregion

  #region 私有属性
  /// <summary>
  /// 基本SELECT SQL语句
  /// </summary>
  protected string SelectSql
  {
   get
   {
    return string.Format("SELECT * FROM [{0}] " ,_TableName) ;
    
   }
  }
  #endregion

  #region 构造函数

  /// <summary>
  /// 构造方法
  /// </summary>
  /// <param name="objDbManager">访问管理器</param>
  public BaseAccess(DbManager  objDbManager)
  {
   this._DbManager = objDbManager;
  }
  #endregion

  #region 公共属性

  /// <summary>
  /// 当前活动数据库连接对象
  /// </summary>
  public SqlConnection CurrentConnection
  {
   get
   {
    return this._DbManager.CurrentConnection;
    
   }
   
  }
  
  
  /// <summary>
  /// 当前活动事务对象
  /// </summary>
  public SqlTransaction CurrentTransaction
  {
   get
   {
    return this._DbManager.CurrentTransaction ;
   }
  }

  #endregion

  #region 公共方法
  /// <summary>
  /// 数据库更新方法
  /// </summary>
  /// <param name="objDataSet">数据集</param>
  /// <returns>返回更新行数</returns>
  public int Update(string strSql)
  {    
   return ExecuteSql(strSql);
  }

  /// <summary>
  /// 数据库插入方法
  /// </summary>
  /// <param name="strSql">插入SQL语句</param>
  /// <returns>返回插入的行</returns>
  public int Insert(string strSql)
  {
   return ExecuteSql(strSql); 
  }

  /// <summary>
  /// 查询全部数据方法
  /// </summary>
  /// <param name="objDataSet">返回数据集</param>
  /// <returns>返回查询行数</returns>
  public int QueryAll(ref DataSet objDataSet)
  { 
   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(SelectSql,CurrentConnection ); 
     
   if(this.CurrentTransaction != null)
   {
    objSqlDataAdapter.SelectCommand.Transaction = CurrentTransaction;
   }
   
   return objSqlDataAdapter.Fill (objDataSet,_TableName);
  }

  /// <summary>
  /// 查询全部数据方法
  /// </summary>
  /// <param name="objDataSet">返回数据集</param>
  /// <returns>返回查询行数</returns>
  public int QueryAll(ref DataTable objDataTable)
  { 
   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(SelectSql,CurrentConnection ); 
     
   if(this.CurrentTransaction != null)
   {
    objSqlDataAdapter.SelectCommand.Transaction = CurrentTransaction;
   }
   DataSet ds = new DataSet();
   int nRows = objSqlDataAdapter.Fill (ds,_TableName);
   objDataTable = ds.Tables[0];
   return nRows;
  }

  /// <summary>
  /// 得到ID对应的数据
  /// </summary>
  /// <param name="objDataSet">返回数据集</param>
  /// <param name="ID">主键值</param>
  /// <returns>返回查询行数</returns>
  public int QueryByID(ref DataSet objDataSet,int ID)
  {
   string strSql = string.Format("SELECT * FROM {0} WHERE {1} = {2}", _TableName, _PKName, ID) ;  

   SqlCommand  objCommand = new SqlCommand (strSql);
   
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction != null)
   {
    objCommand.Transaction = CurrentTransaction;
   }
   
   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter();     
   objSqlDataAdapter.SelectCommand    = objCommand;  

   return  objSqlDataAdapter.Fill(objDataSet,_TableName);
   
  }

  /// <summary>
  /// 得到ID对应的数据
  /// </summary>
  /// <param name="objDataSet">返回数据集</param>
  /// <param name="ID">主键值</param>
  /// <returns>返回查询行数</returns>
  public int QueryByID(ref DataTable objDataTable,int ID)
  {
   string strSql = string.Format("SELECT * FROM {0} WHERE {1} = {2}", _TableName, _PKName, ID) ;  

   SqlCommand  objCommand = new SqlCommand (strSql);
   
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction != null)
   {
    objCommand.Transaction = CurrentTransaction;
   }
   
   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter();     
   objSqlDataAdapter.SelectCommand    = objCommand;  

   DataSet ds = new DataSet();
   int nRows = objSqlDataAdapter.Fill (ds,_TableName);
   objDataTable = ds.Tables[0];
   return nRows;
   
  }


  /// <summary>
  /// 得到ID对应的数据
  /// </summary>
  /// <param name="objDataSet">返回数据集</param>
  /// <param name="ID">主键值</param>
  /// <returns>返回查询行数</returns>
  public int QueryByID(ref DataSet objDataSet,string ID)
  { 
   string strSql = string.Format("{0} WHERE {1} = '{2}'", SelectSql, _PKName, ID) ;  
   SqlCommand  objCommand = new SqlCommand (strSql);
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction != null)
   {
    objCommand.Transaction = CurrentTransaction;
   }

   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter();
   objSqlDataAdapter.SelectCommand    = objCommand;

   return objSqlDataAdapter.Fill (objDataSet,_TableName);
   
  }

  /// <summary>
  /// 得到ID对应的数据
  /// </summary>
  /// <param name="objDataSet">返回数据集</param>
  /// <param name="ID">主键值</param>
  /// <returns>返回查询行数</returns>
  public int QueryByID(ref DataTable objDataTable,string ID)
  { 
   string strSql = string.Format("{0} WHERE {1} = '{2}'", SelectSql, _PKName, ID) ;  
   SqlCommand  objCommand = new SqlCommand (strSql);
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction != null)
   {
    objCommand.Transaction = CurrentTransaction;
   }

   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter();
   objSqlDataAdapter.SelectCommand    = objCommand;

   DataSet ds = new DataSet();
   int nRows = objSqlDataAdapter.Fill (ds,_TableName);
   objDataTable = ds.Tables[0];
   return nRows;
   
  }


  /// <summary>
  /// 用SQL查询数据
  /// </summary>
  /// <param name="objDataSet">返回数据集</param>
  /// <param name="strWhere">SQL语句(WHERE 以后部分)</param>
  /// <returns>返回查询行数</returns>
  public int QueryByWhere(ref DataSet objDataSet,string strWhere)
  {     
   SqlCommand  objCommand = new SqlCommand (SelectSql + " WHERE " + strWhere);
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction != null)
   {
    objCommand.Transaction = CurrentTransaction;
   }
   
   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter() ;
   objSqlDataAdapter.SelectCommand    = objCommand ;

   return objSqlDataAdapter.Fill (objDataSet,_TableName);
    
  }

  /// <summary>
  /// 用SQL查询数据
  /// </summary>
  /// <param name="objDataSet">返回数据集</param>
  /// <param name="strWhere">SQL语句(WHERE 以后部分)</param>
  /// <returns>返回查询行数</returns>
  public int QueryByWhere(ref DataTable objDataTable,string strWhere)
  {     
   SqlCommand  objCommand = new SqlCommand (SelectSql + " WHERE " + strWhere);
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction != null)
   {
    objCommand.Transaction = CurrentTransaction;
   }
   DataSet ds = new DataSet();
   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter() ;
   objSqlDataAdapter.SelectCommand    = objCommand ;

   int nRows = objSqlDataAdapter.Fill(ds,_TableName);
   objDataTable = ds.Tables[0];
   return nRows;
    
  }

  /// <summary>
  /// 用SQL查询数据
  /// </summary>
  /// <param name="dataSet">返回数据集</param>
  /// <param name="strSql">整个SQL语句</param>
  /// <returns>返回查询行数</returns>
  public int QueryBySql(ref DataSet dataSet ,string strSql)
  {
   SqlCommand objCommand = new SqlCommand (strSql);
  
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction != null)
   {
    objCommand.Transaction = CurrentTransaction;
   }
  
   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter();   
   objSqlDataAdapter.SelectCommand    = objCommand ;
 
   return objSqlDataAdapter.Fill (dataSet,_TableName);
   
  }

  /// <summary>
  /// 用SQL查询数据
  /// </summary>
  /// <param name="objTable">返回数据集</param>
  /// <param name="strSql">整个SQL语句</param>
  /// <returns>返回查询行数</returns>
  public int QueryBySql(ref DataTable objTable ,string strSql)
  {
   SqlCommand objCommand = new SqlCommand (strSql);
  
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction != null)
   {
    objCommand.Transaction = CurrentTransaction;
   }
   
  
   SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter();   
   objSqlDataAdapter.SelectCommand    = objCommand ;
   DataSet ds = new DataSet() ;
 
   int nRows = objSqlDataAdapter.Fill(ds,_TableName);
   objTable = ds.Tables[0] ;
   return nRows ;
   
  }


  /// <summary>
  /// 删除数据
  /// </summary>
  /// <param name="strSql">删除SQL语句</param>
  /// <returns>删除行数</returns>
  public int DeleteData(string strSql)
  {
   return ExecuteSql(strSql);
  }

  /// <summary>
  /// 更新scType为1
  /// </summary>
  /// <param name="ID"> 主键ID</param>
  /// <returns>返回更新行</returns>
  public int UpdateScType(int ID)
  {
   string strSql = string.Format("UPDATE {0} set scType=1 WHERE {1} = {2}", _TableName, _PKName, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ;  
  }

  /// <summary>
  /// 更新scType为1
  /// </summary>
  /// <param name="ID"> 主键ID</param>
  /// <returns>返回更新行</returns>
  public int UpdateScType(string ID)
  {
   string strSql = string.Format("UPDATE {0} set scType=1 WHERE {1} = '{2}'", _TableName, _PKName, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ;  
  }

  /// <summary>
  /// 更新State状态
  /// </summary>
  /// <param name="strFieldName">字段名</param>
  /// <param name="strText">更新值</param>
  /// <param name="ID">主键ID</param>
  /// <returns>返回更新行</returns>
  public int UpdateState(string strFieldName,int intText,string ID)
  {
   string strSql = string.Format("UPDATE {0} set {1}={2} WHERE {3} = '{4}'", _TableName,strFieldName,intText, _PKName, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ; 
  }

  /// <summary>
  /// 更新State状态
  /// </summary>
  /// <param name="strFieldName">字段名</param>
  /// <param name="strText">更新值</param>
  /// <param name="ID">主键ID</param>
  /// <returns>返回更新行</returns>
  public int UpdateState(string strFieldName,string strText,string ID)
  {
   string strSql = string.Format("UPDATE {0} set {1}='{2}' WHERE {3} = '{4}'", _TableName,strFieldName,strText, _PKName, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ; 
  }

  /// <summary>
  /// 更新State状态
  /// </summary>
  /// <param name="strFieldName">字段名</param>
  /// <param name="strText">更新值</param>
  /// <param name="ID">主键ID</param>
  /// <returns>返回更新行</returns>
  public int UpdateState(string strFieldName,string strText,string strFieldName2, string ID)
  {
   string strSql = string.Format("UPDATE {0} set {1}='{2}' WHERE {3} = '{4}'", _TableName,strFieldName,strText, strFieldName2, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ; 
  }

  /// <summary>
  /// 更新State状态
  /// </summary>
  /// <param name="strFieldName">字段名</param>
  /// <param name="strText">更新值</param>
  /// <param name="ID">主键ID</param>
  /// <returns>返回更新行</returns>
  public int UpdateState(string strFieldName,int intText,string strFieldName2, int ID)
  {
   string strSql = string.Format("UPDATE {0} set {1}={2} WHERE {3} = {4}", _TableName,strFieldName,intText, strFieldName2, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ; 
  }

  /// <summary>
  /// 更新State状态
  /// </summary>
  /// <param name="strFieldName">字段名</param>
  /// <param name="strText">更新值</param>
  /// <param name="ID">主键ID</param>
  /// <returns>返回更新行</returns>
  public int UpdateState(string strFieldName,string strText,int ID)
  {
   string strSql = string.Format("UPDATE {0} set {1}='{2}' WHERE {3} = {4}", _TableName,strFieldName,strText, _PKName, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ; 
  }


  /// <summary>
  /// 更新State状态
  /// </summary>
  /// <param name="strFieldName">字段名</param>
  /// <param name="strText">更新值</param>
  /// <param name="ID">主键ID</param>
  /// <returns>返回更新行</returns>
  public int UpdateState(string strFieldName,int intText,int ID)
  {
   string strSql = string.Format("UPDATE {0} set {1}={2} WHERE {3} = {4}", _TableName,strFieldName,intText, _PKName, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ; 
  }

  /// <summary>
  /// 删除数据库中ID对应数据
  /// </summary>
  /// <param name="ID">对象ID</param>
  /// <returns>删除行数</returns>
  public int Delete(int ID)
  {
   string strSql = string.Format("DELETE FROM {0} WHERE {1} = {2}", _TableName, _PKName, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql) ;   
   objCommand.Connection = CurrentConnection ;
   if(CurrentTransaction != null)
   {
    objCommand.Transaction =CurrentTransaction ;
   }
   return objCommand.ExecuteNonQuery() ;
   
  }

  /// <summary>
  /// 删除数据库中ID对应数据
  /// </summary>
  /// <param name="ID">对象ID</param>
  /// <returns>删除行数</returns>
  public int Delete(string ID)
  {
   string strSql = string.Format("DELETE FROM {0} WHERE {1} = '{2}'", _TableName, _PKName, ID) ;
   SqlCommand  objCommand = new SqlCommand(strSql);
   objCommand.Connection = CurrentConnection;
   if(CurrentTransaction !=null)
   {
    objCommand.Transaction =CurrentTransaction;
   }
   return objCommand.ExecuteNonQuery();
   
  }

  /// <summary>
  /// 执行SQL语句
  /// </summary>
  /// <param name="strSql"></param>
  /// <returns>所影响数据库记录的行数</returns>
  public int ExecuteSql(string strSql)
  {
   try
   {
    SqlCommand objCmd = new SqlCommand(strSql) ;
   
    objCmd.Connection = this.CurrentConnection ;
    if(CurrentTransaction != null)
    {
     objCmd.Transaction = CurrentTransaction ;
    }
    return objCmd.ExecuteNonQuery() ;
   }
   catch(Exception ex)
   {
    string strTemp = ex.Message;
    return -1;
   }
  }

  /// <summary>
  /// 获取当前一个唯一标识,插入记录后用此函数获取系统自动生成的ID值
  /// </summary>
  /// <returns></returns>
  public int GetCurIdentity()
  {
   string strSql=string.Format("SELECT IDENT_CURRENT('{0}')", _TableName) ;
   SqlCommand objCommand = new SqlCommand (strSql);
   
   objCommand.Connection = CurrentConnection;
   if(this.CurrentTransaction !=null)
   {
    objCommand.Transaction =CurrentTransaction;
   }

   object objValue=objCommand.ExecuteScalar();
   return int.Parse(objValue.ToString());
   
  }

  /// <summary>
  /// 获取下一个种子值
  /// </summary>
  /// <returns></returns>
  public int GetNextSeed()
  {
   DbManager objManager = new DbManager();
   
   try
   {
    string strSql = string.Format("SELECT iCount FROM tblAutoIncrease WHERE tblName = '{0}' and fieldName='{1}'", _TableName,_PKName) ;
    SqlCommand objCommand = new SqlCommand (strSql);   
    objCommand.Connection = objManager.CurrentConnection;
    object objValue=objCommand.ExecuteScalar() ; // 暂无种子值
    int intReturn = 1 ;
    if(objValue == null)
    {    
     strSql = string.Format("INSERT INTO tblAutoIncrease(tblName,fieldName,iCount) VALUES('{0}', '{1}',2)", _TableName,_PKName) ;
    }
    else
    {
     intReturn = int.Parse(objValue.ToString()) ;
     strSql += string.Format("UPDATE tblAutoIncrease SET iCount = {0} WHERE tblName = '{1}'", intReturn + 1, _TableName) ;
    }

    // 更新种子值
    objCommand.CommandText = strSql ;
    objCommand.ExecuteNonQuery() ;
    return intReturn ;
   }
   finally
   {
    objManager.Dispose();
   }

  }

  /// <summary>
  /// 是否存在字段值
  /// </summary>
  /// <param name="strName">返回0表示不存在,返回1表示已经存在</param>
  /// <returns></returns>
  public int IsExistFieldValue(string strFieldName,string strFieldValue)
  {
   string strSql = string.Format("select count(*) from {0} where {1}='{2}' and scType=0",_TableName,strFieldName,strFieldValue);
   DataTable objTable = new DataTable();
   QueryBySql(ref objTable,strSql);
   return Convert.ToInt32(objTable.Rows[0][0]);
  }

  /// <summary>
  /// 是否存在字段值
  /// </summary>
  /// <param name="strName">返回0表示不存在,返回1表示已经存在</param>
  /// <returns></returns>
  public int IsExistFieldValue(string strFieldName,int intFieldValue)
  {
   string strSql = string.Format("select count(*) from {0} where {1}={2} and scType=0",_TableName,strFieldName,intFieldValue);
   DataTable objTable = new DataTable();
   QueryBySql(ref objTable,strSql);
   return Convert.ToInt32(objTable.Rows[0][0]);
  }

  #endregion
  
 }

}

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:2262308次
    • 积分:25867
    • 等级:
    • 排名:第229名
    • 原创:1篇
    • 转载:1662篇
    • 译文:0篇
    • 评论:98条
    最新评论