public class Select
{
#region Class Members
private static string SELECT_PAGE_SQL = "SELECT TOP {0} * FROM ( SELECT ROW_NUMBER() OVER ({1}) AS ROW , {2} FROM {3} WHERE {4} ) AS DATALIST WHERE ROW > {5}";
private static string SELECT_SQL = @"SELECT {0} FROM {1} WHERE {2}";
/// <summary>
/// 数据操作类
/// </summary>
private DbHelper _DbHelper;
/// <summary>
/// 表名
/// </summary>
private string _TableName;
/// <summary>
/// 列名称/值 的集合
/// </summary>
private string _Columns = " * ";
private string _From;
private string _Where;
private string _OrderBy;
private object _Parameter;
private DynamicParameters _Parameters = new DynamicParameters();
/// <summary>
/// 页码
/// </summary>
private int _Page;
/// <summary>
/// 每页条数
/// </summary>
private int _Rows;
#endregion
#region Constructors
public Select(DbHelper dbHelper, string tableName, string colums)
{
_DbHelper = dbHelper;
_TableName = tableName;
_Columns = colums;
}
#endregion
#region Methods
public Select From(string from)
{
_From = from;
return this;
}
public Select Where(string where)
{
_Where = where;
return this;
}
public Select OrderBy(string orderBy)
{
_OrderBy = orderBy;
return this;
}
public Select Parameter(object parameter)
{
_Parameter = parameter;
return this;
}
/// <summary>
/// 条件中的参数
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public Select Parameters(params object[] parameters)
{
if (parameters != null)
{
for (var i = 0; i < parameters.Length; i++)
{
_Parameters.Add("@" + i, parameters[i]==null?0: parameters[i]);
}
}
return this;
}
public Select Paging(int page, int rows)
{
_Page = page;
_Rows = rows;
return this;
}
public IEnumerable<T> Query<T>()
{
var sql = string.Empty;
// 分页
if (_Rows > 0 && _Page > 0)
{
if (string.IsNullOrEmpty(_From))
_From = _TableName;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
if (!string.IsNullOrEmpty(_OrderBy))
_Where += " Order By" + _OrderBy;
// 分页
sql = string.Format(SELECT_PAGE_SQL, _Rows, _OrderBy, _Columns, _From, _Where, (_Page - 1) * _Rows);
}
else
{
if (string.IsNullOrEmpty(_From))
_From = _TableName;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
if (!string.IsNullOrEmpty(_OrderBy))
_Where += " Order By" + _OrderBy;
sql = string.Format(SELECT_SQL, _Columns, _From, _Where);
}
return _DbHelper.Query<T>(sql, _Parameter == null ? _Parameters : _Parameter);
}
#endregion
}
public class Select_T<T>
{
#region Class Members
private static string SELECT_PAGE_SQL = "SELECT TOP {0} * FROM ( SELECT ROW_NUMBER() OVER (Order By {1}) AS ROW , {2} FROM {3} WHERE {4} ) AS DATALIST WHERE ROW > {5}";
private static string SELECT_SQL = @"SELECT {0} FROM {1} WHERE {2}";
/// <summary>
/// 数据操作类
/// </summary>
private DbHelper _DbHelper;
/// <summary>
/// 表名
/// </summary>
private string _TableName;
/// <summary>
/// 列名称/值 的集合
/// </summary>
private string _Columns = " * ";
private string _Where;
private string _OrderBy;
private string _From;
private object _Parameter;
private DynamicParameters _Parameters = new DynamicParameters();
/// <summary>
/// 页码
/// </summary>
private int _Page;
/// <summary>
/// 每页条数
/// </summary>
private int _Rows;
#endregion
#region Constructors
public Select_T(DbHelper dbHelper, string tableName, string colums)
{
_DbHelper = dbHelper;
_TableName = tableName;
_Columns = colums;
}
#endregion
#region Methods
public Select_T<T> Where(string where)
{
_Where = where;
return this;
}
public Select_T<T> OrderBy(string orderBy)
{
_OrderBy = orderBy;
return this;
}
public Select_T<T> From(string from)
{
_From = from;
return this;
}
public Select_T<T> Page(int page, int rows)
{
_Page = page;
_Rows = rows;
return this;
}
/// <summary>
/// 条件中的参数
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public Select_T<T> Parameters(params object[] parameters)
{
if (parameters != null)
{
for (var i = 0; i < parameters.Length; i++)
{
_Parameters.Add("@" + i, parameters[i]==null?0: parameters[i]);
}
}
return this;
}
/// <summary>
/// 初始化参数集合
/// </summary>
/// <param name="parameters">所需参数集合</param>
/// <returns></returns>
public Select_T<T> Parameter(object parameter)
{
_Parameter = parameter;
return this;
}
public IEnumerable<T> Query()
{
var sql = string.Empty;
// 分页
if (_Rows > 0 && _Page > 0)
{
if (string.IsNullOrEmpty(_From))
_From = _TableName;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
if (string.IsNullOrEmpty(_OrderBy))
_OrderBy="ID DESC";
// 分页
sql = string.Format(SELECT_PAGE_SQL, _Rows, _OrderBy, _Columns, _From, _Where, (_Page - 1) * _Rows);
}
else
{
if (string.IsNullOrEmpty(_From))
_From = _TableName;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
if (!string.IsNullOrEmpty(_OrderBy))
_Where += " Order By " + _OrderBy;
sql = string.Format(SELECT_SQL, _Columns, _From, _Where);
}
return _DbHelper.Query<T>(sql, _Parameter == null ? _Parameters : _Parameter);
}
#endregion
}
{
#region Class Members
private static string SELECT_PAGE_SQL = "SELECT TOP {0} * FROM ( SELECT ROW_NUMBER() OVER ({1}) AS ROW , {2} FROM {3} WHERE {4} ) AS DATALIST WHERE ROW > {5}";
private static string SELECT_SQL = @"SELECT {0} FROM {1} WHERE {2}";
/// <summary>
/// 数据操作类
/// </summary>
private DbHelper _DbHelper;
/// <summary>
/// 表名
/// </summary>
private string _TableName;
/// <summary>
/// 列名称/值 的集合
/// </summary>
private string _Columns = " * ";
private string _From;
private string _Where;
private string _OrderBy;
private object _Parameter;
private DynamicParameters _Parameters = new DynamicParameters();
/// <summary>
/// 页码
/// </summary>
private int _Page;
/// <summary>
/// 每页条数
/// </summary>
private int _Rows;
#endregion
#region Constructors
public Select(DbHelper dbHelper, string tableName, string colums)
{
_DbHelper = dbHelper;
_TableName = tableName;
_Columns = colums;
}
#endregion
#region Methods
public Select From(string from)
{
_From = from;
return this;
}
public Select Where(string where)
{
_Where = where;
return this;
}
public Select OrderBy(string orderBy)
{
_OrderBy = orderBy;
return this;
}
public Select Parameter(object parameter)
{
_Parameter = parameter;
return this;
}
/// <summary>
/// 条件中的参数
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public Select Parameters(params object[] parameters)
{
if (parameters != null)
{
for (var i = 0; i < parameters.Length; i++)
{
_Parameters.Add("@" + i, parameters[i]==null?0: parameters[i]);
}
}
return this;
}
public Select Paging(int page, int rows)
{
_Page = page;
_Rows = rows;
return this;
}
public IEnumerable<T> Query<T>()
{
var sql = string.Empty;
// 分页
if (_Rows > 0 && _Page > 0)
{
if (string.IsNullOrEmpty(_From))
_From = _TableName;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
if (!string.IsNullOrEmpty(_OrderBy))
_Where += " Order By" + _OrderBy;
// 分页
sql = string.Format(SELECT_PAGE_SQL, _Rows, _OrderBy, _Columns, _From, _Where, (_Page - 1) * _Rows);
}
else
{
if (string.IsNullOrEmpty(_From))
_From = _TableName;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
if (!string.IsNullOrEmpty(_OrderBy))
_Where += " Order By" + _OrderBy;
sql = string.Format(SELECT_SQL, _Columns, _From, _Where);
}
return _DbHelper.Query<T>(sql, _Parameter == null ? _Parameters : _Parameter);
}
#endregion
}
public class Select_T<T>
{
#region Class Members
private static string SELECT_PAGE_SQL = "SELECT TOP {0} * FROM ( SELECT ROW_NUMBER() OVER (Order By {1}) AS ROW , {2} FROM {3} WHERE {4} ) AS DATALIST WHERE ROW > {5}";
private static string SELECT_SQL = @"SELECT {0} FROM {1} WHERE {2}";
/// <summary>
/// 数据操作类
/// </summary>
private DbHelper _DbHelper;
/// <summary>
/// 表名
/// </summary>
private string _TableName;
/// <summary>
/// 列名称/值 的集合
/// </summary>
private string _Columns = " * ";
private string _Where;
private string _OrderBy;
private string _From;
private object _Parameter;
private DynamicParameters _Parameters = new DynamicParameters();
/// <summary>
/// 页码
/// </summary>
private int _Page;
/// <summary>
/// 每页条数
/// </summary>
private int _Rows;
#endregion
#region Constructors
public Select_T(DbHelper dbHelper, string tableName, string colums)
{
_DbHelper = dbHelper;
_TableName = tableName;
_Columns = colums;
}
#endregion
#region Methods
public Select_T<T> Where(string where)
{
_Where = where;
return this;
}
public Select_T<T> OrderBy(string orderBy)
{
_OrderBy = orderBy;
return this;
}
public Select_T<T> From(string from)
{
_From = from;
return this;
}
public Select_T<T> Page(int page, int rows)
{
_Page = page;
_Rows = rows;
return this;
}
/// <summary>
/// 条件中的参数
/// </summary>
/// <param name="parameters"></param>
/// <returns></returns>
public Select_T<T> Parameters(params object[] parameters)
{
if (parameters != null)
{
for (var i = 0; i < parameters.Length; i++)
{
_Parameters.Add("@" + i, parameters[i]==null?0: parameters[i]);
}
}
return this;
}
/// <summary>
/// 初始化参数集合
/// </summary>
/// <param name="parameters">所需参数集合</param>
/// <returns></returns>
public Select_T<T> Parameter(object parameter)
{
_Parameter = parameter;
return this;
}
public IEnumerable<T> Query()
{
var sql = string.Empty;
// 分页
if (_Rows > 0 && _Page > 0)
{
if (string.IsNullOrEmpty(_From))
_From = _TableName;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
if (string.IsNullOrEmpty(_OrderBy))
_OrderBy="ID DESC";
// 分页
sql = string.Format(SELECT_PAGE_SQL, _Rows, _OrderBy, _Columns, _From, _Where, (_Page - 1) * _Rows);
}
else
{
if (string.IsNullOrEmpty(_From))
_From = _TableName;
if (string.IsNullOrEmpty(_Where))
_Where = " 1=1 ";
if (!string.IsNullOrEmpty(_OrderBy))
_Where += " Order By " + _OrderBy;
sql = string.Format(SELECT_SQL, _Columns, _From, _Where);
}
return _DbHelper.Query<T>(sql, _Parameter == null ? _Parameters : _Parameter);
}
#endregion
}