/// <summary>
/// 执行分页数据查询操作,返回PageResponseData
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionStr"></param>
/// <param name="request"></param>
/// <returns></returns>
public override PageResponseData GetPageList <T>(string connectionStr, PageRequestData pageRequest)
{
PageResponseData responsResult = new PageResponseData();
try
{
#region 相关变量定义
Type type = typeof(T);
string tableName = type.Name;
int startNum = pageRequest.PageSize * (pageRequest.PageIndex - 1) + 1;
int endNum = pageRequest.PageSize * pageRequest.PageIndex;
var searchEntity = pageRequest.SearchEntityObj;
#endregion
#region 分页查询处理
EntityColumn idColumn = new EntityColumnUtility().GetIdColumn <T>(null);
if (idColumn != null)
{
responsResult.TotalCount = GetCount <T>(connectionStr, searchEntity);
if (responsResult.TotalCount > 0)
{
if (string.IsNullOrEmpty(searchEntity.ColumnSql))
{
searchEntity.ColumnSql = "b.*";
}
if (string.IsNullOrEmpty(searchEntity.SortColumn))
{
searchEntity.SortColumn = idColumn.ColumnName;
searchEntity.SortMethod = "asc";
}
StringBuilder cmdText = new StringBuilder();
cmdText.AppendFormat("select {0} from ", searchEntity.ColumnSql);
cmdText.Append("(");
cmdText.AppendFormat("select ROW_NUMBER() over(order by {0} {1}) num,{2} from {3} ", searchEntity.SortColumn, searchEntity.SortMethod, idColumn.ColumnName, tableName);
if (!string.IsNullOrEmpty(searchEntity.WhereSql))
{
cmdText.AppendFormat("where {0} ", searchEntity.WhereSql);
}
cmdText.Append(") ");
cmdText.AppendFormat("a inner join {0} b on a.{1}=b.{1} and a.num between {2} and {3} ", tableName, idColumn.ColumnName, startNum, endNum);
IDataReader dataReader;
if (searchEntity.WhereParam != null)
{
dataReader = SqlDbBase.ExecuteReaderWithParam(connectionStr, cmdText.ToString(), CommandType.Text, searchEntity.WhereParam);
}
else
{
dataReader = SqlDbBase.ExecuteReader(connectionStr, cmdText.ToString(), CommandType.Text, null);
}
responsResult.Data = Map <T>(dataReader);
}
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
return(responsResult);
}
/// <summary>
/// 根据实体主键值查询数据,返回单个实体对象
/// </summary>
/// <typeparam name="T">实体模型名称</typeparam>
/// <param name="connectionStr">数据库字符串连接</param>
/// <param name="Id">主键值</param>
/// <returns>T</returns>
public virtual T QueryFirst <T>(string connectionStr, object Id)
{
T result = default(T);
try
{
EntityColumn column = new EntityColumnUtility().GetIdColumn <T>(Id);
if (column != null)
{
var dbBase = new DbUtility(DbType);
Type type = typeof(T);
string tableName = type.Name;
StringBuilder cmdText = new StringBuilder();
cmdText.AppendFormat("select * from {0} where {1}={2}{3}", tableName, column.ColumnName, DbPramStr, column.ColumnName);
IDbDataParameter[] dbParameters =
{
new AdoNetUtility(DbType).CreateDbParameter(column.ColumnName, column.ColumnValue)
};
IDataReader dataReader = ExecuteReader(connectionStr, cmdText.ToString(), CommandType.Text, dbParameters);
List <T> list = Map <T>(dataReader);
if (list.Count > 0)
{
result = list[0];
}
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <summary>
/// 执行删除数据操作,返回影响行数
/// </summary>
/// <typeparam name="T">实体模型名称</typeparam>
/// <param name="connectionStr">数据库字符串连接</param>
/// <param name="Id">主键值</param>
/// <returns>int</returns>
public virtual int Delete <T>(string connectionStr, object Id)
{
int result = 0;
try
{
EntityColumn column = new EntityColumnUtility().GetIdColumn <T>(Id);
if (column != null)
{
Type type = typeof(T);
string tableName = type.Name;
List <IDbDataParameter> dbParamList = new List <IDbDataParameter>();
StringBuilder cmdText = new StringBuilder();
cmdText.AppendFormat("delete from {0} where {1}={2}", tableName, column.ColumnName, DbPramStr + column.ColumnName);
IDbDataParameter[] dbParameters =
{
new AdoNetUtility(DbType).CreateDbParameter(column.ColumnName, column.ColumnValue)
};
result = Execute(connectionStr, cmdText.ToString(), CommandType.Text, dbParameters);
}
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <summary>
/// 执行分页数据查询操作,返回PageResponseData
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connectionStr"></param>
/// <param name="request"></param>
/// <returns></returns>
public override PageResponseData GetPageList <T>(string connectionStr, PageRequestData pageRequest)
{
PageResponseData responsResult = new PageResponseData();
try
{
#region 相关变量定义
Type type = typeof(T);
string tableName = type.Name;
int startNum = pageRequest.PageSize * (pageRequest.PageIndex - 1);
var searchEntity = pageRequest.SearchEntityObj;
#endregion
#region 分页查询处理
EntityColumn idColumn = new EntityColumnUtility().GetIdColumn <T>(null);
if (idColumn != null)
{
//查询记录结果数
responsResult.TotalCount = GetCount <T>(connectionStr, searchEntity);
if (responsResult.TotalCount > 0)
{
StringBuilder cmdText = new StringBuilder();
if (string.IsNullOrEmpty(searchEntity.ColumnSql))
{
searchEntity.ColumnSql = "*";
}
if (string.IsNullOrEmpty(searchEntity.SortColumn))
{
searchEntity.SortColumn = idColumn.ColumnName;
}
cmdText.AppendFormat("select {0} from {1} ", searchEntity.ColumnSql, tableName);
if (string.IsNullOrEmpty(searchEntity.WhereSql))
{
cmdText.AppendFormat("where {0} ", searchEntity.WhereSql);
}
cmdText.AppendFormat("order by {0} {1} ", searchEntity.SortColumn, searchEntity.SortMethod);
cmdText.AppendFormat("limit {0},{1} ", startNum, pageRequest.PageSize);
IDataReader dataReader;
if (searchEntity.WhereParam != null)
{
dataReader = SqlDbBase.ExecuteReaderWithParam(connectionStr, cmdText.ToString(), CommandType.Text, searchEntity.WhereParam);
}
else
{
dataReader = SqlDbBase.ExecuteReader(connectionStr, cmdText.ToString(), CommandType.Text, null);
}
responsResult.Data = Map <T>(dataReader);
}
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
return(responsResult);
}
/// <summary>
/// 执行编辑操作,返回影响行数
/// </summary>
/// <typeparam name="T">实体模型名称</typeparam>
/// <param name="connectionStr">数据库字符串连接</param>
/// <param name="t">实体模型实例化对象</param>
/// <returns>int</returns>
public virtual int Update <T>(string connectionStr, T t)
{
int result = 0;
try
{
#region 初始相关变量定义
var dbOperator = new AdoNetUtility(DbType);
EntityColumn idColumn = null;
List <EntityColumn> noneIdColumnList = null;
bool isHaveColumn = false;
bool isHaveId = false;
#endregion
#region 获取字段名称、值
List <EntityColumn> columnList = new EntityColumnUtility().GetColumnList <T>(t);
if (columnList != null && columnList.Count > 0)
{
var idList = (from p in columnList where p.IsPk == true select p).ToList <EntityColumn>();
if (idList != null && idList.Count > 0)
{
isHaveId = true;
idColumn = idList[0];
}
var noneIdList = (from p in columnList where p.IsPk == false select p).ToList <EntityColumn>();
if (noneIdList != null && noneIdList.Count > 0)
{
isHaveColumn = true;
noneIdColumnList = noneIdList;
}
}
#endregion
#region 拼接SQL及对数据库操作
if (isHaveColumn && isHaveId)
{
Type type = typeof(T);
string tableName = type.Name;
StringBuilder cmdText = new StringBuilder();
cmdText.AppendFormat("update {0} set ", tableName);
List <IDbDataParameter> dbParamList = new List <IDbDataParameter>();
for (int i = 0; i < noneIdColumnList.Count; i++)
{
EntityColumn column = noneIdColumnList[i];
cmdText.AppendFormat("{0}={1}", column.ColumnName, DbPramStr + column.ColumnName);
if (i != (noneIdColumnList.Count - 1))
{
cmdText.Append(",");
}
var dbParam = dbOperator.CreateDbParameter(column.ColumnName, column.ColumnValue);
dbParamList.Add(dbParam);
}
cmdText.AppendFormat(" where {0}={1}", idColumn.ColumnName, DbPramStr + idColumn.ColumnName);
var idDbParam = dbOperator.CreateDbParameter(idColumn.ColumnName, idColumn.ColumnValue);
dbParamList.Add(idDbParam);
result = Execute(connectionStr, cmdText.ToString(), CommandType.Text, dbParamList.ToArray());
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
/// <summary>
/// 执行添加操作,返回object(如果设置获取自动增长值,返回为自动增长值,否则返回值为影响行数)
/// </summary>
/// <typeparam name="T">实体模型名称</typeparam>
/// <param name="connectionStr">数据库字符串连接</param>
/// <param name="t">实体模型实例化对象</param>
/// <returns>object</returns>
public virtual object Insert <T>(string connectionStr, T t)
{
object result = null;
try
{
#region 初始相关变量定义
//var dbBase = new DbBase(dbType);
bool isHaveColumn = false;
bool isGetIdentityValue = false;
List <EntityColumn> noneIdentityColumns = null;
#endregion
#region 获取字段名称、值
List <EntityColumn> columnList = new EntityColumnUtility().GetColumnList <T>(t);
if (columnList != null && columnList.Count > 0)
{
var identityList = (from p in columnList where (p.IsIdentity == true && p.IsGetIdentityValue == true) select p).ToList <EntityColumn>();
if (identityList != null && identityList.Count > 0)
{
isGetIdentityValue = true;
}
var noneIdentityColumnList = (from p in columnList where (p.IsIdentity == false) select p).ToList <EntityColumn>();
if (noneIdentityColumnList != null && noneIdentityColumnList.Count > 0)
{
isHaveColumn = true;
noneIdentityColumns = noneIdentityColumnList;
}
}
#endregion
#region 拼接SQL及对数据库操作
if (isHaveColumn)
{
#region 模板及相关变量定义
Type type = typeof(T);
string tableName = type.Name;
string tmpColumnNames = "[[ColumnNames]]";
string tmpColumnValues = "[[ColumnValues]]";
List <IDbDataParameter> dbParamList = new List <IDbDataParameter>();
StringBuilder fieldStr = new StringBuilder();
StringBuilder paramStr = new StringBuilder();
StringBuilder sqlBuild = new StringBuilder();
sqlBuild.AppendFormat("insert into {0}({1}) values({2});", tableName, tmpColumnNames, tmpColumnValues);
#endregion
#region 对非自动增长字段操作
int i = 0;
foreach (EntityColumn column in noneIdentityColumns)
{
fieldStr.Append(column.ColumnName);
paramStr.Append(DbPramStr + column.ColumnName);
if (i != (noneIdentityColumns.Count - 1))
{
fieldStr.Append(",");
paramStr.Append(",");
}
var dbParam = new AdoNetUtility(DbType).CreateDbParameter(column.ColumnName, column.ColumnValue);
dbParamList.Add(dbParam);
i++;
}
sqlBuild.Replace(tmpColumnNames, fieldStr.ToString());
sqlBuild.Replace(tmpColumnValues, paramStr.ToString());
#endregion
#region 对要获取自动增长字段操作
if (isGetIdentityValue)
{
if ((int)DbType == (int)GcEnumDbType.MsSql)
{
sqlBuild.Append("select @@identity;");
}
else if ((int)DbType == (int)GcEnumDbType.MsSql)
{
sqlBuild.Append("select @@identity;");
}
result = ExecuteScalar(connectionStr, sqlBuild.ToString(), CommandType.Text, dbParamList.ToArray());
}
else
{
result = Execute(connectionStr, sqlBuild.ToString(), CommandType.Text, dbParamList.ToArray());
}
#endregion
}
#endregion
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
