/// <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)));
}
