/// <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)); } }