public static Nullable <int> GetCount(string where, int loginId)
{
StringBuilder sql = new StringBuilder();
sql.Append("select count(*) from ViewPolicy ");
//if (where != string.Empty)
// sql += "where 1=1 " + where;
string ids = LoginDataLimitServices.GetLimit(loginId);
if (string.IsNullOrEmpty(ids))
{
sql.AppendFormat("where policy_makerId = {0} ", loginId);
}
else
{
sql.AppendFormat("where policy_makerId in ({0}) ", ids + "," + loginId);
}
if (string.IsNullOrEmpty(where))
{
sql.Append(where);
}
object result = DBHelper.ExecuteScalar(sql.ToString());
if (result == null)
{
return(null);
}
else
{
return(Convert.ToInt32(result));
}
}
/// <summary>
/// 获取记录总数
/// </summary>
/// <param name="where"></param>
/// <returns></returns>
public static int GetTotalCount(string where, int userID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT COUNT(*) FROM To_Collecting ");
string ids = LoginDataLimitServices.GetLimit(userID);
if (string.IsNullOrEmpty(ids))
{
sqlBuilder.AppendFormat(" WHERE markerID = {0}", userID);
}
else
{
sqlBuilder.AppendFormat(" WHERE markerID in ({0},{1})", ids, userID);
}
if (!string.IsNullOrEmpty(where))
{
sqlBuilder.Append(where);
}
using (SqlConnection conn = new SqlConnection(DBHelper.connectionString))
{
conn.Open();
SqlCommand sqlCmd = new SqlCommand(sqlBuilder.ToString(), conn);
object result = sqlCmd.ExecuteScalar();
return(result == null ? 0 : int.Parse(result.ToString()));
}
}
public static DataTable GetListByPage(int?id, int loginId, string orderby, string strWhere, int startIndex, int endIndex)
{
StringBuilder sql = new StringBuilder();
sql.Append("SELECT * FROM ( ");
sql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
sql.Append("order by p." + orderby);
}
else
{
sql.Append("order by p.id desc");
}
sql.Append(")AS Row,");
sql.Append("p.* from ViewPolicy as p ");
StringBuilder where = new StringBuilder();
string ids = LoginDataLimitServices.GetLimit(loginId);
if (string.IsNullOrEmpty(ids))
{
where.AppendFormat("where policy_makerId = {0} ", loginId);
}
else
{
where.AppendFormat("where policy_makerId in ({0}) ", ids + "," + loginId);
}
if (id != null)
{
where.AppendFormat("and p.id={0}", id);
}
sql.Append(where);
if (!string.IsNullOrEmpty(strWhere))
{
sql.Append(strWhere);
}
sql.Append(" ) TT");
sql.AppendFormat(" WHERE TT.Row between {0} and {1} ", startIndex, endIndex);
if (!string.IsNullOrEmpty(orderby.Trim()))
{
sql.Append("order by " + orderby + " ");
}
else
{
sql.Append("order by id desc ");
}
DataTable dt = DBHelper.GetDataSet(sql.ToString());
return(dt);
}