MVC常见的控制器，接口，数据层之间的操作

user_books_info 类

namespace CiWong.LearningLevel.Mapping
{
    public class user_books_info
    {
        /// <summary>
        /// 使用对象ID,个人版存放会员ID，班级版存放班级ID，校园版存放校园ID
        /// </summary>
        public long user_id { get; set; }

        /// <summary>
        /// 购买类型(1=个人版，2=班级版，3=学校版)
        /// </summary>
        public Int16 purchase_type { get; set; }

        /// <summary>
        /// 题册ID
        /// </summary>
        public long book_id { get; set; }

        /// <summary>
        /// 课本名称
        /// </summary>
        public string book_name { get; set; }

        /// <summary>
        /// 课本封面
        /// </summary>
        public string book_cover { get; set; }

        /// <summary>
        /// 使用对象名称
        /// </summary>
        public string user_name { get; set; }

        /// <summary>
        /// 使用类型(0=试用，1=已购买，2=赠送，3=校本课程)
        /// </summary>
        public int use_mode { get; set; }

        /// <summary>
        /// 添加时间
        /// </summary>
        public DateTime create_time { get; set; }

        /// <summary>
        /// 首次购买时间
        /// </summary>
        public DateTime bourght_time { get; set; }

        /// <summary>
        /// 最后一次续费时间
        /// </summary>
        public DateTime last_paid_time { get; set; }

        /// <summary>
        /// 过期时间
        /// </summary>
        public DateTime expire_date { get; set; }

    }
}

　　

1，数据操作层（List<>）

/// <summary>
        /// 获取我购买的书籍
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">条数</param>
        /// <returns></returns>
        public List<user_books_info> UserBookInfoGetByUserID(int userID, int pageIndex, int pageSize)
        {
            List<user_books_info> bookList = new List<user_books_info>();
            string sql = @"CALL learn_user_books_info_getbyuserid(@UserID,@pageIndex,@pageSize);";
            MySqlParameter[] para = new MySqlParameter[]                                    
            {  
                 new MySqlParameter("@UserID", userID),
                 new MySqlParameter("@pageIndex", pageIndex),
                 new MySqlParameter("@pageSize", pageSize)
             };
            using (var dr = MySqlHelper.ExecuteReader(DatabaseManager.Retrieve("wc_learninglevel_read"), sql, para))
            {
                while (dr.Read())
                {
                    var bookInfo = new user_books_info();
                    bookInfo.user_id = dr.GetInt32("user_id");
                    bookInfo.purchase_type = dr.GetInt16("purchase_type");
                    bookInfo.book_id = dr.GetInt64("book_id");
                    bookInfo.book_name = dr.GetString("book_name");
                    bookInfo.book_cover = dr.GetString("book_cover");
                    bookInfo.user_name = dr.GetString("user_name");
                    bookInfo.use_mode = dr.GetInt32("use_mode");
                    bookInfo.create_time = dr.GetDateTime("create_time");
                    bookInfo.bourght_time = dr.GetDateTime("bourght_time");
                    //bookInfo.last_paid_time = dr.GetDateTime("last_paid_time");
                    bookInfo.expire_date = dr.GetDateTime("expire_date");
                    bookList.Add(bookInfo);
                }
                dr.Close();
            }
            return bookList;
        }

　　

2，数据调用接口层(List<>)

  

        /// <summary>
        /// 获取我购买的书籍
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">条数</param>
        /// <returns></returns>
        List<user_books_info> UserBookInfoGetByUserID(int userID, int pageIndex, int pageSize);    

　　

3，控制器

   

ViewBag.UserLearns = this._Repository.UserBookInfoGetByUserID(userID, 1, 11);//购买的书籍

A, 数据操作层（IEnumerable<>）

   

        /// <summary>
        /// 获取文章列表 
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <param name="totalItem">总数据量</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="IsDelete">删除状态</param>
        /// <returns></returns>
        public IEnumerable<BlogContent> GetBlogContents(int userID, ref int totalItem, int pageIndex = 1, int pageSize = 20, bool IsFriend = false, bool IsManager = false)
        {
            List<BlogContent> _list = new List<BlogContent>();
            MySqlConnection conn = new MySqlConnection(ConnectionString);
            MySqlDataReader dr = null;
            try
            {
                conn.Open();
                totalItem = Convert.ToInt32(MySqlHelper.ExecuteScalar(conn, string.Format("SELECT COUNT(0) FROM BlogContent WHERE uid=@uid AND IsDelete=0 AND IsPublished=1 {0}", IsManager ? string.Empty : IsFriend ? "AND IsPublic<=1" : "AND IsPublic=0"),
                                            new MySqlParameter("@uid", userID)));
                if (totalItem > 0)
                {
                    string sql = @"SELECT Id,CateId,Title,Intro,Published,Created,
                                   ViewNums,CopNums,IsPublic,IsTop,IsPublished,
                                   Iscopied,OriginID,uid,OrignAccountId,OriginAccountName,Flowers FROM BlogContent
                                   WHERE uid=@uid AND IsDelete=0 AND IsPublished=1 {0}
                                   ORDER BY IsTop DESC,Id DESC LIMIT @Begin,@Size";
                    dr = MySqlHelper.ExecuteReader(conn, string.Format(sql, IsManager ? string.Empty : IsFriend ? "AND IsPublic<=1" : "AND IsPublic=0"),
                                new MySqlParameter("@uid", userID),
                                new MySqlParameter("@Begin", (pageIndex - 1) * pageSize),
                                new MySqlParameter("@Size", pageSize));
                    while (dr.Read())
                    {
                        _list.Add(new BlogContent()
                        {
                            Id = dr.GetInt64(0),
                            CateId = dr.GetInt64(1),
                            Title = dr.GetString(2),
                            Intro = dr.GetString(3),
                            Published = dr.GetDateTime(4),
                            Created = dr.GetDateTime(5),
                            ViewNums = dr.GetInt32(6),
                            CopNums = dr.GetInt32(7),
                            IsPublic = dr.GetByte(8),
                            IsTop = dr.GetBoolean(9),
                            IsPublished = dr.GetBoolean(10),
                            Iscopied = dr.GetBoolean(11),
                            OriginID = dr.GetInt64(12),
                            uid = dr.GetInt32(13),
                            OrignAccountId = dr.GetInt32(14),
                            OriginAccountName = dr.GetString(15),
                            Flower = dr.GetInt32(16)
                        });
                    }
                }
            }
            catch (MySqlException e)
            {
                throw e;
            }
            finally
            {
                if (null != dr) dr.Close();
                conn.Close();
            }
            return _list;
        }

B，数据操作接口层（IEnumerable<>）

    

        /// <summary>
        /// 获取文章列表
        /// </summary>
        /// <param name="userID">用户ID集合</param>
        /// <param name="totalItem">总数据量</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="IsDelete">删除状态</param>
        /// <returns></returns>
        IEnumerable<BlogContent> GetBlogContents(List<int> userIDs, ref int totalItem, int pageIndex = 1, int pageSize = 20, bool IsFriend = false, bool IsManager = false);

　　

C，控制器

     

var blogs1 = _blogManager.GetBlogContents(zone.UserID, ref totalItem, page, 10).ToList();