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

转载 2006年06月15日 15:53:00

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
  
 }

}

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

using System;using System.Data;using System.Data.SqlClient;using System.Data.Common ;using System.Da...
  • 50277
  • 50277
  • 2006年05月15日 10:01
  • 610

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

using System;using System.Data;using System.Data.SqlClient;using System.Data.OleDb;using System.Data...
  • boy_north
  • boy_north
  • 2006年05月08日 20:10
  • 1164

SQL 语法之“增”、“删”、“改”、“查”

/* 四、查 1.普通查询 语法:select from [where ] [order by [asc或desc]] */ /*    1).查询所有数据行和列     ...
  • Carvin_Zh
  • Carvin_Zh
  • 2015年07月29日 15:19
  • 1523

jsp操作MySQL实现查询/插入/删除功能示例

jsp操作MySQL实现查询、插入与删除功能(query、insert、delete)示例如下,感兴趣的朋友可以参考下。 直接贴代码吧: 首先,index_test.jsp页面的代码如下: ...
  • qq_24474911
  • qq_24474911
  • 2016年08月19日 09:01
  • 300

数据库的增、删、改、查操作-----(一)

数据库的增、删、改、查操作
  • Aimee09
  • Aimee09
  • 2016年09月13日 14:28
  • 641

创建通讯录实现(增,删,查,改,显示,清空)功能

contact.h#ifndef __CONTACT_H__ #define __CONTACT_H__#define NAME_LEN 20 #define SEX_LEN 5 #define T...
  • yangrujing
  • yangrujing
  • 2015年05月20日 23:46
  • 289

通用的增、删、改和查方法 DAO

// 通用的增、删、改的方法( version 2.0 )       public void update( String sql) {            // 1.获取数据库的连接   ...
  • u010834071
  • u010834071
  • 2014年09月07日 10:12
  • 873

数据的增、删、改、查;

DROP DATABASE gjp; CREATE DATABASE gjp; CREATE TABLE gjp_sort(   sid INT PRIMARY KEY AUTO...
  • weixin_38841312
  • weixin_38841312
  • 2018年01月20日 09:04
  • 11

数据表数据的增,删,改 ,查

create table table_name1 ( 学号 int primary key identity, 姓名 char(6) not null, 专业方向 varchar(10) not nu...
  • xulXl
  • xulXl
  • 2016年08月07日 20:01
  • 164

tp框架增删改查

[php] view plain copy 在CODE上查看代码片派生到我的代码片 一些简单的增删改查语句 在CODE上查看代码片派生到我的代码片 ...
  • caozaifei
  • caozaifei
  • 2016年11月19日 09:13
  • 435
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:数据操作层的基类,主要封装了数据的增,删,改,查功能
举报原因:
原因补充:

(最多只允许输入30个字)