数据操作层的基类,主要封装了数据的增,删,改,查功能
来源：互联网 发布：python exec eval区别 编辑：程序博客网 时间：2024/05/15 00:54
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
}
}
- 数据操作层的基类,主要封装了数据的增,删,改,查功能
- 数据操作层的基类,主要封装了数据的增,删,改,查功能
- 数据操作层的基类,主要封装了数据的增,删,改,查功能
- sql数据增删改查的封装
- 封装的ExtGrid 实现增、删、改、查等功能
- 数据表数据的增，删，改 ，查
- extjs4 数据的增删改查操作 数据的操作
- DAO层数据操作的封装
- SqlHelper工具类封装了对数据库的增删改查操作
- Javscript Json数据操作(数据增,删,改,查)
- Java程序操作Oracle数据库实现增,删,改,查的封装类*
- MFC MySql对数据的操作（增、删、改、查）
- php DOM模型对象操作XML格式的数据 增、删、改、查
- java连接redis中的数据查、增、改、删操作的方法
- 数据的增删改查
- 数据的增删改查
- EF5 通用数据层 增删改查操作，泛型类
- EF 通用数据层 增删改查操作，泛型类II
- 读出文件夹中的所有文件
- XML学习笔记
- 常用代码
- 项目经理的十大成功态度
- 如何在sqlserver2000中实现oracle的序列
- 数据操作层的基类,主要封装了数据的增,删,改,查功能
- 如何在面试时脱颖而出
- PHP 数据导出到EXECEL类
- 收藏1
- regsvr32命令详解
- oracle 进程说明
- Hibernate分页查询原理解读
- 基于GPS和PDA的电子导游系统的关键技术
- Web在线编辑器大全（知名）
