public List<LeaveMessage> Pages(int pageIndex, int pageSize, string conditions, out int total)
{
total = 0;
var sql = @"";
//var sql = @"SELECT * FROM leavemessage ORDER BY CreateTime DESC";
var sql_total = @"Select Count(*) From leavemessage;";
MySqlParameter[] paraList = new MySqlParameter[]
{
new MySqlParameter("@PageIndex", --pageIndex * pageSize),
new MySqlParameter("@PageSize", pageSize)
};
var list = _DataBase.QueryList<LeaveMessage>(sql, paraList.ToList());
total = _DataBase.QueryTotal(sql_total, null); //查询总数
return list;
}
/// <summary>
/// 登录
/// </summary>
/// <param name="userName"></param>
/// <param name="passWord"></param>
/// <param name="user"></param>
/// <param name="msg"></param>
/// <returns>登录成功返回true和User信息，失败返回false和null</returns>
public bool LogOn(string userName, string passWord, out User user, out string msg)
{
int result_u = 0;
user = null;
msg = string.Empty;
string sql_u = @"Select COUNT(*) Count From user Where UserName = @UserName;";
string sql_up = @"Select * From user Where UserName = @UserName And PassWord = @PassWord;";
MySqlParameter[] para_u = new MySqlParameter[] { new MySqlParameter("@UserName", userName) };
MySqlParameter[] para_up = new MySqlParameter[] { new MySqlParameter("@UserName", userName), new MySqlParameter("@PassWord", passWord) };
//验证用户是否存在
using (var conn = DataBase.GetOpenConn4MySql())
{
MySqlDataReader dr = DataBase.ExecuteQueryReader(sql_u, conn, para_u);
while (dr.Read())
{
result_u = dr["Count"] != null ? Convert.ToInt32(dr["Count"]) : 0;
}
dr.Close();
if (result_u <= 0)
{
msg = "用户名不存在";
return false;
}
}
//验证用户名和密码是否正确
DataSet ds = new DataSet();
MySqlDataAdapter da = DataBase.ExecuteQuery4MySql(sql_up, para_up.ToList());
if (da != null)
{
da.Fill(ds);
}
if (ds.Tables[0] != null && ds.Tables[0].Rows.Count > 0)
{
user = FillData.FillDataToEntity<User>(new User(), ds.Tables[0]);
return true;
}
else
{
msg = "密码输入错误";
return false;
}
}
/// <summary>
/// 查看文章详细内容
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public ArticleDBModel GetArticleById(string id)
{
var sql = @"Select a.*,b.NickName,b.Sex,b.UserName From article a LEFT JOIN user b ON a.UserId = b.Id Where 1=1 And a.Id = @Id;UPDATE article SET ViewCount=ViewCount+1 WHERE Id = @Id;";
MySqlParameter[] para = new MySqlParameter[] { new MySqlParameter("@Id", id) };
return _DataBase.QueryModel<ArticleDBModel>(sql, para.ToList());
}
/// <summary>
/// 查询用户的文章列表
/// </summary>
/// <param name="userName"></param>
/// <param name="total"></param>
/// <returns></returns>
public List<Article> GetUserArticles(string userId, out int total)
{
string sql = @"Select * From article Where UserId = @UserId Order By CreateTime Desc;";
string sql_total = @"Select Count(*) From article Where UserId = @UserId";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@UserId", userId),
};
var list = _DataBase.QueryList<Article>(sql, para.ToList());
var totallist = _DataBase.QueryTotal(sql_total, para.ToList());
total = totallist;
return list;
}
/// <summary>
/// 分页查询(无条件)
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="conditions"></param>
/// <param name="total"></param>
/// <returns>没有数据的返回null</returns>
public List<Article> Pages(int pageIndex, int pageSize, string userId, out int total)
{
if (pageIndex <= 1)
pageIndex = 1;
total = 0;
var sql = @"SELECT
a.*, b.NickName,
b.Sex,
b.UserName
FROM
article a
LEFT JOIN USER b ON a.UserId = b.Id
WHERE
UserId = @UserId
ORDER BY
IsTop DESC,
CreateTime DESC
LIMIT @PageIndex,
@PageSize;";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@PageIndex", --pageIndex*10),
new MySqlParameter("@PageSize", pageSize)
};
var list = _DataBase.QueryList<Article>(sql, para.ToList());
var sql_total = @"SELECT
a.*, b.NickName,
b.Sex,
b.UserName
FROM
article a
LEFT JOIN USER b ON a.UserId = b.Id
WHERE
UserId = @UserId";
DataSet ds = _DataBase.GetPage4MySql(new Article(), pageIndex, pageSize);
DataTable pages = ds.Tables[0];
total = _DataBase.QueryTotal(sql_total, para.ToList());
return list;
}
public List<LeaveMsgDBModel> LeaveMsgPages(int pageIndex, int pageSize, string conditions, out int total)
{
var sql = @"SELECT
a.Id,
a.ParentId,
a.Content,
a.UserId,
CASE WHEN c.UserName is NULL THEN '游客'
ELSE c.NickName END as NickName,
b.Id PId,
b.ParentId PParentId,
b.Content PContent,
b.UserId PUserId,
CASE WHEN d.UserName is NULL THEN '游客'
ELSE d.NickName END as PNickName,
a.CreateTime,
CASE WHEN b.CreateTime is NULL THEN a.CreateTime
ELSE b.CreateTime END as PCreateTime
FROM
leavemessage a
LEFT JOIN leavemessage b ON a.ParentId = b.Id
LEFT JOIN `user` c ON a.UserId = c.Id
LEFT JOIN `user` d ON b.UserId = d.Id
ORDER BY
a.CreateTime DESC LIMIT @PageIndex,@PageSize;";
var sql_total = @"Select Count(*) From leavemessage;";
MySqlParameter[] paraList = new MySqlParameter[]
{
new MySqlParameter("@PageIndex", --pageIndex * pageSize),
new MySqlParameter("@PageSize", pageSize)
};
var list = _DataBase.QueryList<LeaveMsgDBModel>(sql, paraList.ToList());
total = _DataBase.QueryTotal(sql_total, null); //查询总数
return list;
}
/// <summary>
/// 文章的评论列表
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="articleId"></param>
/// <param name="total"></param>
/// <returns></returns>
public List<CommentDBModel> CommentPages(int pageIndex, int pageSize, string articleId, out int total)
{
total = 0;
var sql = @"SELECT
a.ArticleId,
a.Id,
a.ParentId,
a.Content,
a.UserId,
CASE WHEN c.UserName is NULL THEN '游客'
ELSE c.NickName END as NickName,
b.Id PId,
b.ParentId PParentId,
b.Content PContent,
b.UserId PUserId,
CASE WHEN d.UserName is NULL THEN '游客'
ELSE d.NickName END as PNickName,
a.CreateTime,
CASE WHEN b.CreateTime is NULL THEN a.CreateTime
ELSE b.CreateTime END as PCreateTime
FROM
`comment` a
LEFT JOIN `comment` b ON a.ParentId = b.Id
LEFT JOIN `user` c ON a.UserId = c.Id
LEFT JOIN `user` d ON b.UserId = d.Id
WHERE a.ArticleId = @ArticleId
ORDER BY
a.CreateTime DESC LIMIT @PageIndex,@PageSize";
var sql_total = @"Select Count(*) From comment WHERE ArticleId = @ArticleId;";
MySqlParameter[] paraList = new MySqlParameter[]
{
new MySqlParameter("@ArticleId", articleId),
new MySqlParameter("@PageIndex", --pageIndex * pageSize),
new MySqlParameter("@PageSize", pageSize)
};
var list = _DataBase.QueryList<CommentDBModel>(sql, paraList.ToList());
total = _DataBase.QueryTotal(sql_total, paraList.ToList()); //查询总数
return list;
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="id"></param>
/// <param name="passWord"></param>
/// <returns></returns>
public bool EditPwd(string id, string oldPwd, string newPwd, out string msg)
{
msg = "修改失败！";
var sql_oldpwd = @"Select * From user Where Id=@Id And PassWord=@OldPwd";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@Id", id),
new MySqlParameter("@OldPwd", oldPwd)
};
var result = _DataBase.QueryModel<User>(sql_oldpwd, para.ToList());
if (string.IsNullOrEmpty(result.Id))
{
msg = "原密码输入错误！";
return false;
}
var sql = @"Update user Set PassWord=@PassWord Where Id=@Id";
MySqlParameter[] paras = new MySqlParameter[]
{
new MySqlParameter("@PassWord", newPwd),
new MySqlParameter("@Id", id)
};
if (DataBase.ExecuteNonQuery4MySql(sql, paras.ToList()) > 0)
{
msg = "修改成功！";
return true;
}
return false;
}
/// <summary>
/// 检查名称是否存在
/// </summary>
/// <param name="keyWord"></param>
/// <returns></returns>
public bool Exsit(string keyWord)
{
string sql = @"Select * From user Where UserName = @UserName;";
MySqlParameter[] para = new MySqlParameter[] { new MySqlParameter("@UserName", keyWord) };
List<User> userList = _DataBase.QueryList<User>(sql, para.ToList());
if (userList != null && userList.Count > 0)
{
return true;
}
return false;
}
/// <summary>
/// 更新用户昵称和性别信息
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Update(User model)
{
var sql = @"Update user Set NickName = @NickName, Sex = @Sex Where Id = @Id";
MySqlParameter[] para = new MySqlParameter[]
{
new MySqlParameter("@NickName", model.NickName),
new MySqlParameter("@Sex", model.Sex),
new MySqlParameter("@Id", model.Id)
};
if (DataBase.ExecuteNonQuery4MySql(sql, para.ToList()) > 0)
{
return 1;
}
return 0;
}