public DataSet ExecuteDataSet(string sqlClause, params SqlParameter[] commandParameters)
{
return(DbHelperSql.Query(this.ConnectionString, sqlClause, commandParameters));
}
/// <summary>
///
/// </summary>
/// <param name="sqlClause"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public object GetSingle(string sqlClause, params SqlParameter[] parameters)
{
return(DbHelperSql.GetSingle(ConnectionString, sqlClause, parameters));
}
public void Execute(string sqlClause)
{
DbHelperSql.ExecuteSql(this.ConnectionString, sqlClause);
}
/// <summary>
/// 分页数据获取
/// </summary>
/// <param name="whereClause"></param>
/// <param name="fields"></param>
/// <param name="orderBy"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public IPageOfItems <T> GetPaged(string whereClause, string fields, string orderBy, int pageIndex, int pageSize)
{
whereClause = whereClause.Replace("'", "''");
var pageOfItems = new PageOfItems <T>
{
PageIndex = pageIndex,
PageSize = pageSize
};
//var sqlClause =
// string.Format("EXEC [dbo].[up_GetPaged] '{0}','{1}' ,'{2}','{3}','{4}',{5},{6}",
// TableName, fields, whereClause, orderBy, PrimaryKeyName, pageIndex, pageSize);
var parameters = new[] {
new SqlParameter("@TableName", SqlDbType.NVarChar, 255),
new SqlParameter("@Fields", SqlDbType.NVarChar, 255),
new SqlParameter("@WhereClause", SqlDbType.NVarChar, 2000),
new SqlParameter("@OrderBy", SqlDbType.NVarChar, 255),
new SqlParameter("@PrimaryKey", SqlDbType.NVarChar, 255),
new SqlParameter("@PageIndex", SqlDbType.Int, 4),
new SqlParameter("@PageSize", SqlDbType.Int, 4),
};
parameters[0].Value = TableName;
parameters[1].Value = string.IsNullOrEmpty(fields) ? "*" : fields;
parameters[2].Value = whereClause ?? string.Empty;
parameters[3].Value = orderBy ?? string.Empty;
parameters[4].Value = PrimaryKeyName;
parameters[5].Value = pageIndex;
parameters[6].Value = pageSize;
using (var reader = DbHelperSql.RunProcedure(ConnectionString, "up_GetPaged", parameters))
{
var list = FormatModel(reader, pageSize).ToReadOnlyCollection();
if (reader.Tables.Count >= 2 && reader.Tables[1].Rows.Count > 0)
{
var row = reader.Tables[1].Rows[0];
if (row["TotalRowCount"] == DBNull.Value)
{
pageOfItems.TotalItemCount = 0;
}
else
{
pageOfItems.TotalItemCount = (Int32)row["TotalRowCount"];
}
}
pageOfItems.AddRange(list);
}
return(pageOfItems);
//using (var reader = ExecuteDataSet(sqlClause))
//{
// var list = FormatModel(reader, pageSize).ToReadOnlyCollection();
// if (reader.Tables.Count >= 2 && reader.Tables[1].Rows.Count > 0)
// {
// var row = reader.Tables[1].Rows[0];
// if (row["TotalRowCount"] == DBNull.Value) pageOfItems.TotalItemCount = 0; else pageOfItems.TotalItemCount = (Int32)row["TotalRowCount"];
// }
// pageOfItems.AddRange(list);
//}
//return pageOfItems;
}