/// <summary> /// 根据条件分页获取 /// </summary> /// <param name="start">开始行</param> /// <param name="end">结束行</param> /// <param name="wheres">筛选条件</param> /// <param name="orderBys">排序值</param> /// <returns></returns> public List <Project> GetPageListWhereToAndOrderBy(int start, int end, Dictionary <string, object> wheres, List <string> orderBys) { StringBuilder sql = new StringBuilder("select * from(select *,row_number()over(order by id) as num from Projects) as t where t.num between @start and @end"); List <SqlParameter> paramList = new List <SqlParameter>() { new SqlParameter("@start", start), new SqlParameter("@end", end), }; if (wheres != null && wheres.Count() > 0) { foreach (var item in wheres) { if (string.IsNullOrWhiteSpace(item.Key)) { throw new Exception("查询条件不能为空"); } sql.Append($" and {item.Key}=@{item.Key}"); paramList.Add(new SqlParameter("@" + item.Key, item.Value)); } } if (orderBys != null && orderBys.Count() > 0) { sql.Append(" order by "); foreach (var item in orderBys) { if (string.IsNullOrWhiteSpace(item)) { throw new Exception("查询条件不能为空"); } if (orderBys.First() == item) { sql.Append($"{item}"); } else { sql.Append($",{item}"); } } } DataTable dt = DbHelper.GetDataTable(sql.ToString(), paramList.ToArray()); if (dt == null && dt.Rows.Count < 1) { return(null); } List <Model.Project> models = new List <Model.Project>(); for (int i = 0; i < dt.Rows.Count; ++i) { Model.Project model = new Model.Project(); model.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString()); model.ClassifyId = Convert.ToInt32(dt.Rows[i]["ClassifyId"].ToString()); model.ProjectName = dt.Rows[i]["ProjectName"].ToString(); model.State = (ProjectState)Enum.Parse(typeof(ProjectState), dt.Rows[i]["State"].ToString()); model.CurrentMoney = Convert.ToDecimal(dt.Rows[i]["CurrentMoney"].ToString()); model.Goal = Convert.ToDecimal(dt.Rows[i]["Goal"].ToString()); model.Deadline = DateTime.Parse(dt.Rows[i]["Deadline"].ToString()); model.LikeCount = Convert.ToInt32(dt.Rows[i]["LikeCount"].ToString()); model.Content = dt.Rows[i]["Content"].ToString(); model.CoverImg = dt.Rows[i]["CoverImg"].ToString(); models.Add(model); } return(models); }
public SqlDataReader queryBySort(string sort) { string cmdText = "select * from book where b_sort='" + sort + "'"; return(DbHelper.ExecuteReader(cmdText)); }
//根据关键字查询,所有字段都进行匹配 public DataTable queryByAll(string all) { string cmdText = "select * from book where b_id like '%" + all + "%' or b_name like '%" + all + "%' or b_author like '%" + all + "%' or b_publisher like '%" + all + "%' or b_pubtime like '%" + all + "%' or b_pritime like '%" + all + "%' or b_edition like '%" + all + "%' or b_impression like '%" + all + "%' or b_pages like '%" + all + "%' or b_words like '%" + all + "%' or b_format like '%" + all + "%' or b_paper like '%" + all + "%' or b_packaging like '%" + all + "%' or b_isbn like '%" + all + "%' or b_price like '%" + all + "%' or b_sort like '%" + all + "%' or b_inventory like '%" + all + "%'"; return(DbHelper.ExecuteDataTable(cmdText)); }
public SqlDataReader querybyPager(string paper) { string cmdText = "select * from book where b_paper='" + paper + "'"; return(DbHelper.ExecuteReader(cmdText)); }
public SqlDataReader queryByIsbn(string isbn) { string cmdText = "select * from book where b_isbn='" + isbn + "'"; return(DbHelper.ExecuteReader(cmdText)); }
public SqlDataReader queryByEdition(string edition) { string cmdText = "select * from book where b_edition='" + edition + "'"; return(DbHelper.ExecuteReader(cmdText)); }
public SqlDataReader queryByWords(string words) { string cmdText = "select * from book where b_words='" + words + "'"; return(DbHelper.ExecuteReader(cmdText)); }
/// <summary> /// 删除一个集合 /// </summary> public bool DeleteRange(string p_strWhere) { return(DbHelper.ExecuteSql(Conn, DeleteRangeSQL(p_strWhere))); }
/// <summary> /// 删除全部 /// </summary> public bool DeleteAll() { return(DbHelper.ExecuteSql(Conn, DeleteAllSQL())); }
/// <summary> /// 修改全部数据 /// </summary> public bool UpdateAll(DEV_STANDARD_VMAS model) { return(DbHelper.ExecuteSql(Conn, UpdateAllSQL(model))); }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(int intID) { return(DbHelper.ExecuteSql(Conn, DeleteSQL(intID))); }
/// <summary> /// 修改一个集合 /// </summary> public bool UpdateRange(DEV_STANDARD_VMAS model, string p_strWhere) { return(DbHelper.ExecuteSql(Conn, UpdateRangeSQL(model, p_strWhere))); }
/// <summary> /// 修改一条数据 /// </summary> public bool Update(DEV_STANDARD_VMAS model, int intID) { return(DbHelper.ExecuteSql(Conn, UpdateSQL(model, intID))); }
/// <summary> /// 添加一条数据 /// </summary> public bool Insert(DEV_STANDARD_VMAS model) { return(DbHelper.ExecuteSql(Conn, InsertSQL(model))); }
public SqlDataReader queryByName(string name) { string cmdText = "select * from book where b_name='" + name + "'"; return(DbHelper.ExecuteReader(cmdText)); }
/// <summary> /// 得到对象集合 /// </summary> public DEV_STANDARD_VMAS[] GetModelList(string p_strWhere, string p_strOrder, int p_intPageNumber, int p_intPageSize) { int m_intPageNumber = p_intPageNumber; int m_intPageCount = GetPageCount(p_strWhere, p_intPageSize); if ((m_intPageNumber < 1) || (m_intPageNumber == 0)) { m_intPageNumber = 1; } if (m_intPageNumber == -1) { m_intPageNumber = m_intPageCount; } if (m_intPageNumber > m_intPageCount) { m_intPageNumber = m_intPageCount; } string strSql = ""; strSql += "select * from DEV_STANDARD_VMAS"; if (p_strWhere.Trim().Length > 0) { strSql += " where " + p_strWhere; } if (p_strOrder.Trim().Length > 0) { strSql += " order by " + p_strOrder; } DataTable dtTemp = new DataTable(); if (p_intPageSize > 0) { DbHelper.GetTable(Conn, strSql, ref dtTemp, (m_intPageNumber - 1) * p_intPageSize, p_intPageSize); } else { DbHelper.GetTable(Conn, strSql, ref dtTemp); } DEV_STANDARD_VMAS[] arrModel = new DEV_STANDARD_VMAS[dtTemp.Rows.Count]; for (int N = 0; N < dtTemp.Rows.Count; N++) { arrModel[N] = new DEV_STANDARD_VMAS(); arrModel[N].ID = dtTemp.Rows[N]["ID"] == DBNull.Value ? 0 : Convert.ToInt32(dtTemp.Rows[N]["ID"]); arrModel[N].SEARCH_CONDITION = dtTemp.Rows[N]["SEARCH_CONDITION"] == DBNull.Value ? "" : dtTemp.Rows[N]["SEARCH_CONDITION"].ToString(); arrModel[N].ZXMC = dtTemp.Rows[N]["ZXMC"] == DBNull.Value ? "" : dtTemp.Rows[N]["ZXMC"].ToString(); arrModel[N].COXZ = dtTemp.Rows[N]["COXZ"] == DBNull.Value ? "" : dtTemp.Rows[N]["COXZ"].ToString(); arrModel[N].HCXZ = dtTemp.Rows[N]["HCXZ"] == DBNull.Value ? "" : dtTemp.Rows[N]["HCXZ"].ToString(); arrModel[N].NOXZ = dtTemp.Rows[N]["NOXZ"] == DBNull.Value ? "" : dtTemp.Rows[N]["NOXZ"].ToString(); arrModel[N].HC_NOXZ = dtTemp.Rows[N]["HC_NOXZ"] == DBNull.Value ? "" : dtTemp.Rows[N]["HC_NOXZ"].ToString(); arrModel[N].PDFS = dtTemp.Rows[N]["PDFS"] == DBNull.Value ? "" : dtTemp.Rows[N]["PDFS"].ToString(); arrModel[N].IS_SHOW = dtTemp.Rows[N]["IS_SHOW"] == DBNull.Value ? 0 : Convert.ToInt32(dtTemp.Rows[N]["IS_SHOW"]); arrModel[N].AREA_NAME = dtTemp.Rows[N]["AREA_NAME"] == DBNull.Value ? "" : dtTemp.Rows[N]["AREA_NAME"].ToString(); arrModel[N].UTYPE = dtTemp.Rows[N]["UTYPE"] == DBNull.Value ? 0 : Convert.ToInt32(dtTemp.Rows[N]["UTYPE"]); } dtTemp.Dispose(); return(arrModel); }
public SqlDataReader queryByAuthor(string author) { string cmdText = "select * from book where b_author='" + author + "'"; return(DbHelper.ExecuteReader(cmdText)); }
public int Delete(int id) { string sql = "delete from users where id=@id"; SqlParameter p = new SqlParameter("@id", id); return DbHelper.ExecuteNotQuery(sql, p); }
public SqlDataReader queryByImpression(string impression) { string cmdText = "select * from book where b_impression='" + impression + "'"; return(DbHelper.ExecuteReader(cmdText)); }
public int GetModelCount() { string sql = "select count(*) from Users"; return (int)DbHelper.ExecuteScalar(sql); }
public SqlDataReader queryByFormat(string format) { string cmdText = "select * from book where b_format='" + format + "'"; return(DbHelper.ExecuteReader(cmdText)); }
public int InsertData(int UserId, int ProjectId, Decimal Money) { string sql = $"insert into SupportProjects([UserId],[ProjectId],[Money]) values('{UserId}','{ProjectId}','{ Money}')"; return(DbHelper.Update(sql)); }
public SqlDataReader queryByPackaging(string packaging) { string cmdText = "select * from book where b_packaging='" + packaging + "'"; return(DbHelper.ExecuteReader(cmdText)); }
public int GetModelCount() { string sql = "select count(*) from SupportProjects"; return((int)DbHelper.ExecuteScalar(sql)); }
public SqlDataReader queryByPrice(string price) { string cmdText = "select * from book where b_price='" + price + "'"; return(DbHelper.ExecuteReader(cmdText)); }
//更新一本书 //0:更新失败 //1:更新成功 //2:连接出错,更新失败 //3:图片为空,更新失败 //4:ID为空,更新失败 // public int update(Model.Book book) { string id = book.Id; string name = book.Name; string author = book.Author; string publisher = book.Publisher; string pubtime = book.Pubtime; string pritime = book.Pritime; string edition = book.Edition; string impression = book.Impression; string pages = book.Pages; string words = book.Words; string format = book.Format; string paper = book.Paper; string packaging = book.Packaging; string isbn = book.Isbn; byte[] picture = book.Picture; string price = book.Price; string sort = book.Sort; string inventory = book.Inventory; //判断连接 if (DbHelper.checkConnection() == false) { return(2); } if (picture == null) { return(3); } if (id == null || id == "") { return(4); } string cmdText = "update book set b_name='" + name + "',b_author='" + author + "',b_publisher='" + publisher + "',b_pubtime='" + pubtime + "',b_pritime='" + pritime + "',b_edition='" + edition + "',b_impression='" + impression + "',b_pages='" + pages + "',b_words='" + words + "',b_format='" + format + "',b_paper='" + paper + "',b_packaging='" + packaging + "',b_isbn='" + isbn + "',b_picture=@picture,b_price='" + price + "',b_sort='" + sort + "',b_inventory='" + inventory + "' where b_id='" + id + "'"; SqlParameter[] parameters = { new SqlParameter("@picture", SqlDbType.Binary) }; parameters[0].Value = picture; int i = DbHelper.ExecuteNonQuery(cmdText, parameters); if (i == 1) { return(1); } else { Console.WriteLine("更新失败"); return(0); } }
public SqlDataReader queryByInventory(string inventory) { string cmdText = "select * from book where b_inventory='" + inventory + "'"; return(DbHelper.ExecuteReader(cmdText)); }
//根据id查询,以下多个函数均类似 public SqlDataReader queryById(string id) { string cmdText = "select * from book where b_id='" + id + "'"; return(DbHelper.ExecuteReader(cmdText)); }
//得到最新添加的10条记录 public DataTable getLast10Records() { string str = "select top 10 * from book order by b_id desc"; return(DbHelper.ExecuteDataTable(str)); }
/// <summary> /// 删除一条数据 /// </summary> public bool Delete(string strMODEL_NAME) { return(DbHelper.ExecuteSql(Conn, DeleteSQL(strMODEL_NAME))); }