/// <summary>
/// 返回Object
/// </summary>
/// <param name="strSql">T-SQL语句</param>
/// <param name="dbParameter">DbCommand参数</param>
/// <returns></returns>
public object FindObject(string strSql, DbParameter[] dbParameter)
{
using (var conn = Connection)
{
return(SqlHelper.ExecuteScalar(conn, CommandType.Text, strSql, DatabaseCommon.DbParameterToSqlParameter(dbParameter)));
}
}
/// <summary>
/// 更新一条数据
/// </summary>
/// <param name="entity">实体对象</param>
/// <returns></returns>
public int Update <T>(T entity) where T : class
{
int res = 0;
if (SqlTransaction == null)
{
string sql = DatabaseCommon.UpdateSql <T>(entity).ToString();
DbParameter[] parameter = DatabaseCommon.GetParameter <T>(entity);
using (var conn = Connection)
{
res = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, DatabaseCommon.DbParameterToSqlParameter(parameter));
}
}
else
{
string sql = DatabaseCommon.UpdateSql <T>(entity).ToString();
DbParameter[] parameter = DatabaseCommon.GetParameter <T>(entity);
res = SqlHelper.ExecuteNonQuery(SqlTransaction, CommandType.Text, sql, DatabaseCommon.DbParameterToSqlParameter(parameter));
}
return(res);
}
/// <summary>
/// 根据T-SQL获取分页数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strSql">T-SQL语句</param>
/// <param name="dbParameter">DbCommand参数</param>
/// <param name="orderField">排序字段，多个用英文逗号隔开，类似：Id Asc,Name Desc</param>
/// <param name="isAsc">是否升序</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">索引</param>
/// <param name="total">总记录</param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
{
StringBuilder sb = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
string orderBy = "";
//表名
string table = EntityAttributeHelper.GetEntityTable <T>();
if (!string.IsNullOrEmpty(orderField))
{
if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
{
orderBy = "Order By " + orderField;
}
else
{
orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
else
{
orderBy = "Order By (Select 0)";
}
sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
using (var dbConnection = Connection)
{
IEnumerable <T> data = new List <T>();
string selectCountSql = "Select Count(*) From " + table + " WHERE 1 = 1";
total = (int)SqlHelper.ExecuteScalar(dbConnection, CommandType.Text, selectCountSql);
DataSet dataSet = SqlHelper.ExecuteDataset(dbConnection, CommandType.Text, sb.ToString(), DatabaseCommon.DbParameterToSqlParameter(dbParameter));
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].IsExistRows())
{
DataTable dataTable = dataSet.Tables[0];
data = dataTable.DataTableToList <T>();
}
return(data);
}
}
/// <summary>
/// 根据T-SQL语句获取一条数据，返回对象集合
/// </summary>
/// <param name="strSql">T-SQL语句</param>
/// <param name="dbParameter">DbCommand参数</param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(string strSql, DbParameter[] dbParameter) where T : class, new()
{
List <T> res = new List <T>();
using (var conn = Connection)
{
DataSet data = SqlHelper.ExecuteDataset(conn, CommandType.Text, strSql, DatabaseCommon.DbParameterToSqlParameter(dbParameter));
if (data.Tables.Count > 0 && data.Tables[0].IsExistRows())
{
DataTable table = data.Tables[0];
res = table.DataTableToList <T>();
}
}
return(res);
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procName">存储过程名称</param>
/// <param name="dbParameter">DbCommand参数</param>
/// <returns></returns>
public int ExecuteByProc(string procName, DbParameter[] dbParameter)
{
using (var connection = Connection)
{
return(SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, procName, DatabaseCommon.DbParameterToSqlParameter(dbParameter)));
}
}
/// <summary>
/// 执行T-SQL
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="dbParameter">DbCommand参数</param>
/// <returns></returns>
public int ExecuteBySql(string strSql, params DbParameter[] dbParameter)
{
using (var connection = Connection)
{
return(SqlHelper.ExecuteNonQuery(connection, CommandType.Text, strSql, DatabaseCommon.DbParameterToSqlParameter(dbParameter)));
}
}
/// <summary>
/// 获取分页DataTable
/// </summary>
/// <param name="strSql">T-SQL语句</param>
/// <param name="dbParameter">DbCommand参数</param>
/// <param name="orderField">排序字段</param>
/// <param name="isAsc">是否升序</param>
/// <param name="pageSize">每页条数</param>
/// <param name="pageIndex">索引</param>
/// <param name="total">总记录</param>
/// <returns></returns>
public DataTable FindTable(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total)
{
using (var dbConnection = Connection)
{
StringBuilder sb = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
string orderBy = "";
if (!string.IsNullOrEmpty(orderField))
{
if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
{
orderBy = "Order By " + orderField;
}
else
{
orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
else
{
orderBy = "Order By (Select 0)";
}
sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
total = Convert.ToInt32(SqlHelper.ExecuteScalar(dbConnection, CommandType.Text, "Select Count(1) From (" + strSql + ") As t", dbParameter));
DataSet dataSet = SqlHelper.ExecuteDataset(dbConnection, CommandType.Text, sb.ToString(), DatabaseCommon.DbParameterToSqlParameter(dbParameter));
DataTable table = new DataTable();
if (dataSet.Tables.Count > 0)
{
if (dataSet.Tables[0].IsExistRows())
{
table = dataSet.Tables[0];
}
}
return(table);
}
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="strSql">T-SQL语句</param>
/// <param name="dbParameter">DbCommand参数</param>
/// <returns></returns>
public DataTable FindTable(string strSql, DbParameter[] dbParameter)
{
DataTable res = new DataTable();
using (var conn = Connection)
{
DataSet data = SqlHelper.ExecuteDataset(conn, CommandType.Text, strSql, DatabaseCommon.DbParameterToSqlParameter(dbParameter));
if (data.Tables[0].IsExistRows())
{
res = data.Tables[0];
}
}
return(res);
}
/// <summary>
/// 执行sql语句，获取一个DataTable
/// </summary>
/// <param name="strSql"></param>
/// <param name="dbParameter"></param>
/// <returns></returns>
public DataTable FindTable(string strSql, DbParameter[] dbParameter)
{
using (var dbConnection = Connection)
{
var dataReader = SqlHelper.ExecuteReader(dbConnection as SqlConnection, CommandType.Text, strSql, DatabaseCommon.DbParameterToSqlParameter(dbParameter));
return(ConvertExtension.IDataReaderToDataTable(dataReader));
}
}
/// <summary>
/// 分页查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strSql"></param>
/// <param name="dbParameter"></param>
/// <param name="orderField"></param>
/// <param name="isAsc"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="total"></param>
/// <returns></returns>
public IEnumerable <T> FindList <T>(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
{
using (var dbConnection = Dbcontext.Database.Connection)
{
StringBuilder sb = new StringBuilder();
if (pageIndex == 0)
{
pageIndex = 1;
}
int num = (pageIndex - 1) * pageSize;
int num1 = (pageIndex) * pageSize;
string orderBy = "";
if (!string.IsNullOrEmpty(orderField))
{
if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
{
orderBy = "Order By " + orderField;
}
else
{
orderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
}
}
else
{
orderBy = "Order By (Select 0)";
}
sb.Append("Select * From (Select ROW_NUMBER() Over (" + orderBy + ")");
sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
total = SqlHelper.ExecuteNonQuery(dbConnection as SqlConnection, CommandType.Text, "Select Count(1) From (" + strSql + ") As t", DatabaseCommon.DbParameterToSqlParameter(dbParameter));
//total = Convert.ToInt32(new DbHelper(dbConnection).ExecuteScalar(CommandType.Text, "Select Count(1) From (" + strSql + ") As t", dbParameter));
SqlDataReader dataReader = SqlHelper.ExecuteReader(dbConnection as SqlConnection, CommandType.Text, sb.ToString(), DatabaseCommon.DbParameterToSqlParameter(dbParameter));//new DbHelper(dbConnection).ExecuteReader(CommandType.Text, sb.ToString(), dbParameter);
return(ConvertExtension.IDataReaderToList <T>(dataReader));
}
}
