随笔- 75 文章- 5 评论- 6 阅读- 57551

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，控制器

1	`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);