C# MySqlParameter.ToList方法代码示例

        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 [email protected] And [email protected]";
            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 [email protected] Where [email protected]";
            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;
 }