public List <Project> GetPageList(int start, int end)
{
string sql = "select * from(select *,row_number()over(order by id) as num from Projects) as t where t.num between @start and @end";
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
};
DataTable dt = DbHelper.GetDataTable(sql, ps);
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 List <Like> GetPageListWhereTo(int start, int end, Dictionary <string, object> where)
{
StringBuilder sql = new StringBuilder("select * from(select *,row_number()over(order by id) as num from Likes) as t where t.num between @start and @end");
List <SqlParameter> pList = new List <SqlParameter>()
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
};
if (where != null && where.Count() > 0)
{
foreach (var item in where)
{
sql.Append($" and {item.Key}=@{item.Value}");
pList.Add(new SqlParameter("@" + item.Key, item.Value));
}
}
DataTable dt = DbHelper.GetDataTable(sql.ToString(), pList.ToArray());
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
List <Model.Like> models = new List <Model.Like>();
for (int i = 0; i < dt.Rows.Count; ++i)
{
Model.Like model = new Model.Like();
model.UserId = Convert.ToInt32(dt.Rows[0]["Id"]);
model.ProjectId = Convert.ToInt32(dt.Rows[0]["ClassifyName"]);
models.Add(model);
}
return(models);
}
public Project GetModel(int id)
{
string sql = "select * from Projects where Id=@id";
SqlParameter p = new SqlParameter("@id", id);
DataTable dt = DbHelper.GetDataTable(sql, p);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
Model.Project model = new Model.Project();
model.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
model.ClassifyId = Convert.ToInt32(dt.Rows[0]["ClassifyId"].ToString());
model.ProjectName = dt.Rows[0]["ProjectName"].ToString();
model.State = (ProjectState)Enum.Parse(typeof(ProjectState), dt.Rows[0]["State"].ToString());
model.CurrentMoney = Convert.ToDecimal(dt.Rows[0]["CurrentMoney"].ToString());
model.Goal = Convert.ToDecimal(dt.Rows[0]["Goal"].ToString());
model.Deadline = DateTime.Parse(dt.Rows[0]["Deadline"].ToString());
model.LikeCount = Convert.ToInt32(dt.Rows[0]["LikeCount"].ToString());
model.Content = dt.Rows[0]["Content"].ToString();
model.CoverImg = dt.Rows[0]["CoverImg"].ToString();
model.PublishState = (PublishState)Enum.Parse(typeof(ProjectState), dt.Rows[0]["PublishState"].ToString());
model.OwnerId = Convert.ToInt32(dt.Rows[0]["OwnerId"] == DBNull.Value?"0": dt.Rows[0]["OwnerId"].ToString());
model.PublishState = (PublishState)Enum.Parse(typeof(ProjectState), dt.Rows[0]["PublishState"] == DBNull.Value ? "2" : dt.Rows[0]["PublishState"].ToString());
model.OwnerId = Convert.ToInt32(dt.Rows[0]["OwnerId"] == DBNull.Value ? "0" : dt.Rows[0]["OwnerId"].ToString());
model.Return = dt.Rows[0]["Return"].ToString();
model.ReturnMiddle = dt.Rows[0]["ReturnMiddle"].ToString();
model.ReturnMax = dt.Rows[0]["ReturnMax"].ToString();
model.ReturnTime = Convert.ToInt32(dt.Rows[0]["ReturnTime"].ToString());
model.Freight = Convert.ToDecimal(dt.Rows[0]["Freight"].ToString());
return(model);
}
public List <Model.Admin> GetPageList(int start, int end)
{
string sql = "select * from(select *,row_number()over(order by id) as num from Admins) as t where t.num between @start and @end";
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
};
DataTable dt = DbHelper.GetDataTable(sql, ps);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
List <Model.Admin> models = new List <Model.Admin>();
for (int i = 0; i < dt.Rows.Count; ++i)
{
Model.Admin model = new Model.Admin();
model.Id = Convert.ToInt32(dt.Rows[i]["id"]);
model.AdminName = dt.Rows[i]["AdminName"].ToString();
model.Pwd = dt.Rows[i]["Pwd"].ToString();
models.Add(model);
}
return(models);
}
public List <Model.User> GetPageList(int start, int end)
{
string sql = "select * from(select *,row_number()over(order by id) as num from Users) as t where t.num between @start and @end";
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
};
DataTable dt = DbHelper.GetDataTable(sql, ps);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
List <Model.User> models = new List <Model.User>();
for (int i = 0; i < dt.Rows.Count; ++i)
{
Model.User model = new Model.User();
model.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
model.UserName = dt.Rows[i]["UserName"].ToString();
model.Sex = char.Parse(dt.Rows[i]["Sex"].ToString());
model.Address = dt.Rows[i]["Address"].ToString();
model.Description = dt.Rows[i]["Description"].ToString();
model.Nickname = dt.Rows[i]["Nickname"].ToString();
model.Url = dt.Rows[i]["Url"].ToString();
model.UserPic = dt.Rows[i]["UserPic"].ToString();
model.Pwd = dt.Rows[i]["Pwd"].ToString();
model.Phone = dt.Rows[i]["Phone"].ToString();
models.Add(model);
}
return(models);
}
public List <LaunchInfo> GetPageList(int start, int end)
{
string sql = "select * from(select *,row_number()over(order by id) as num from LaunchInfo) as t where t.num between @start and @end";
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
};
DataTable dt = DbHelper.GetDataTable(sql, ps);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
List <Model.LaunchInfo> models = new List <Model.LaunchInfo>();
for (int i = 0; i < dt.Rows.Count; ++i)
{
models.Add(new LaunchInfo()
{
UserId = Convert.ToInt32(dt.Rows[i]["Id"].ToString()),
TrueName = dt.Rows[i]["TrueName"].ToString(),
PhoneNumber = dt.Rows[i]["PhoneNumber"].ToString(),
BankName = dt.Rows[i]["BankName"].ToString(),
Branch = dt.Rows[i]["Branch"].ToString(),
BankCard = dt.Rows[i]["BankCard"].ToString(),
Address = dt.Rows[i]["Address"].ToString(),
ProjectId = Convert.ToInt32(dt.Rows[i]["ProjectId"] == DBNull.Value?"0": dt.Rows[i]["ProjectId"]),
});
}
return(models);
}
public List <Like> GetPageList(int start, int end)
{
string sql = "select * from(select *,row_number()over(order by id) as num from Likes) as t where t.num between @start and @end";
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
};
DataTable dt = DbHelper.GetDataTable(sql, ps);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
List <Model.Like> models = new List <Model.Like>();
for (int i = 0; i < dt.Rows.Count; ++i)
{
Model.Like model = new Model.Like();
model.UserId = Convert.ToInt32(dt.Rows[0]["Id"]);
model.ProjectId = Convert.ToInt32(dt.Rows[0]["ClassifyName"]);
models.Add(model);
}
return(models);
}
public List <SupportProject> GetPageList(int start, int end)
{
string sql = "select * from(select *,row_number()over(order by id) as num from SupportProjects) as t where t.num between @start and @end";
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
};
DataTable dt = DbHelper.GetDataTable(sql, ps);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
List <Model.SupportProject> models = new List <Model.SupportProject>();
for (int i = 0; i < dt.Rows.Count; ++i)
{
Model.SupportProject model = new Model.SupportProject();
model.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
model.UserId = Convert.ToInt32(dt.Rows[i]["UserId"]);
model.ProjectId = Convert.ToInt32(dt.Rows[i]["ProjectId"]);
model.Money = Convert.ToDecimal(dt.Rows[i]["Money"]);
models.Add(model);
}
return(models);
}
public Like GetModel(int id)
{
string sql = "select * from Likes where Id=@id";
SqlParameter p = new SqlParameter("@id", id);
DataTable dt = DbHelper.GetDataTable(sql, p);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
Model.Like model = new Model.Like();
model.UserId = Convert.ToInt32(dt.Rows[0]["Id"]);
model.ProjectId = Convert.ToInt32(dt.Rows[0]["ClassifyName"]);
return(model);
}
public Classify GetModel(int id)
{
string sql = "select * from Classifys where Id=@id";
SqlParameter p = new SqlParameter("@id", id);
DataTable dt = DbHelper.GetDataTable(sql, p);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
Model.Classify model = new Model.Classify();
model.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
model.ClassifyName = dt.Rows[0]["ClassifyName"].ToString();
return(model);
}
public Model.Admin GetModel(string adminName)
{
string sql = "select * from Admins where AdminName=@adminName";
SqlParameter p = new SqlParameter("@adminName", adminName);
DataTable dt = DbHelper.GetDataTable(sql, p);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
Model.Admin model = new Model.Admin();
model.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
model.AdminName = dt.Rows[0]["AdminName"].ToString();
model.Pwd = dt.Rows[0]["Pwd"].ToString();
return(model);
}
public Comment GetModel(int id)
{
string sql = "select * from Comments where Id=@id";
SqlParameter p = new SqlParameter("@id", id);
DataTable dt = DbHelper.GetDataTable(sql, p);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
Model.Comment model = new Model.Comment();
model.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
model.UserId = Convert.ToInt32(dt.Rows[0]["UserId"]);
model.ProjectId = Convert.ToInt32(dt.Rows[0]["ProjectId"]);
model.Content = dt.Rows[0]["Content"].ToString();
return(model);
}
public SupportProject GetModel(int id)
{
string sql = "select * from SupportProjects where Id=@id";
SqlParameter p = new SqlParameter("@id", id);
DataTable dt = DbHelper.GetDataTable(sql, p);
if (dt != null && dt.Rows.Count < 1)
{
return(null);
}
Model.SupportProject model = new Model.SupportProject();
model.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
model.UserId = Convert.ToInt32(dt.Rows[0]["UserId"]);
model.ProjectId = Convert.ToInt32(dt.Rows[0]["ProjectId"]);
model.Money = Convert.ToDecimal(dt.Rows[0]["Money"]);
return(model);
}
public List <Project> GetPageList(int start, int end, string keyword)
{
keyword = keyword == null ? "" : keyword;
string sql = $"select * from(select *,row_number()over(order by id) as num from Projects where PublishState={Convert.ToInt32(PublishState.Approved)} and State={Convert.ToInt32(ProjectState.InProgress)} or State={Convert.ToInt32(ProjectState.Finish)}) as t where t.num between @start and @end and ProjectName like '%'+@keyword+'%' order by id desc ";
SqlParameter[] ps = new SqlParameter[]
{
new SqlParameter("@start", start),
new SqlParameter("@end", end),
new SqlParameter("@keyword", keyword),
};
DataTable dt = DbHelper.GetDataTable(sql, ps);
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(string.IsNullOrWhiteSpace(dt.Rows[i]["CurrentMoney"].ToString()) ? "0" : 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();
model.PublishState = (PublishState)Enum.Parse(typeof(ProjectState), string.IsNullOrWhiteSpace(dt.Rows[i]["PublishState"].ToString()) ? "0" : dt.Rows[0]["PublishState"].ToString());
model.OwnerId = Convert.ToInt32(string.IsNullOrWhiteSpace(dt.Rows[i]["OwnerId"].ToString()) ? "0" : dt.Rows[i]["OwnerId"].ToString());
model.PublishState = (PublishState)Enum.Parse(typeof(ProjectState), string.IsNullOrWhiteSpace(dt.Rows[i]["PublishState"].ToString()) ? "0" : dt.Rows[i]["PublishState"].ToString());
model.OwnerId = Convert.ToInt32(string.IsNullOrWhiteSpace(dt.Rows[i]["OwnerId"].ToString()) ? "0" : dt.Rows[i]["OwnerId"].ToString());
models.Add(model);
}
return(models);
}
/// <summary>
/// 获得所有分类
/// </summary>
/// <returns></returns>
public List <Classify> GetAllList()
{
string sql = "select * from Classifys";
DataTable dt = DbHelper.GetDataTable(sql);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
List <Model.Classify> models = new List <Model.Classify>();
for (int i = 0; i < dt.Rows.Count; ++i)
{
Model.Classify model = new Model.Classify();
model.Id = Convert.ToInt32(dt.Rows[i]["Id"].ToString());
model.ClassifyName = dt.Rows[i]["ClassifyName"].ToString();
models.Add(model);
}
return(models);
}
public LaunchInfo GetModel(int id)
{
string sql = "select * from LaunchInfo where Id=@id";
SqlParameter p = new SqlParameter("@id", id);
DataTable dt = DbHelper.GetDataTable(sql, p);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
Model.LaunchInfo model = new Model.LaunchInfo();
model.UserId = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
model.TrueName = dt.Rows[0]["TrueName"].ToString();
model.PhoneNumber = dt.Rows[0]["PhoneNumber"].ToString();
model.BankName = dt.Rows[0]["BankName"].ToString();
model.Branch = dt.Rows[0]["Branch"].ToString();
model.BankCard = dt.Rows[0]["BankCard"].ToString();
model.Address = dt.Rows[0]["Address"].ToString();
model.ProjectId = Convert.ToInt32(dt.Rows[0]["ProjectId"] == DBNull.Value?"0": dt.Rows[0]["ProjectId"]);
return(model);
}
public Model.User GetModel(string username)
{
string sql = "select * from Users where Username=@username";
SqlParameter p = new SqlParameter("@username", username);
DataTable dt = DbHelper.GetDataTable(sql, p);
if (dt == null || dt.Rows.Count <= 0)
{
return null;
}
Model.User model = new Model.User();
model.Id = Convert.ToInt32(dt.Rows[0]["Id"].ToString());
model.UserName = dt.Rows[0]["UserName"].ToString();
model.Sex = char.Parse(dt.Rows[0]["Sex"].ToString());
model.Address = dt.Rows[0]["Address"].ToString();
model.Description = dt.Rows[0]["Description"].ToString();
model.Nickname = dt.Rows[0]["Nickname"].ToString();
model.Url = dt.Rows[0]["Url"].ToString();
model.UserPic = dt.Rows[0]["UserPic"].ToString();
model.Pwd = dt.Rows[0]["Pwd"].ToString();
model.Phone = dt.Rows[0]["Phone"].ToString();
return model;
}
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 where 1=1 ");
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}");
}
}
}
sql.Append(") as t where t.num between @start and @end");
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();
model.PublishState = (PublishState)Enum.Parse(typeof(ProjectState), string.IsNullOrWhiteSpace(dt.Rows[0]["PublishState"].ToString()) ? "0" : dt.Rows[0]["PublishState"].ToString());
model.OwnerId = Convert.ToInt32(dt.Rows[0]["OwnerId"] == DBNull.Value ? 0 : dt.Rows[0]["OwnerId"]);
models.Add(model);
}
return(models);
}
