Ejemplo n.º 1
0
        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));
            }
        }
Ejemplo n.º 2
0
        /// <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()));
            }
        }
Ejemplo n.º 3
0
        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);
        }