OA2021/DAL/MailPageService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Models;
using System.Data;

namespace DAL
{
    /// <summary>
    /// 主界面多表联查数据访问类
    /// </summary>
    public class MailPageService
    {
        /// <summary>
        /// 读取方法
        /// </summary>
        /// <param name="t">对象</param>
        /// <param name="type">对象区分</param>
        /// <param name="sql">sql语句</param>
        /// <param name="cmdType">文本类型</param>
        /// <param name="param">可变参数数组</param>
        /// <returns>返回集合</returns>
        public List<MailPage> excuteSql(string sql,params SqlParameter[] param)
        {
            List<MailPage> mpList = null;
            MailPage mp = null;

            using (SqlDataReader dr = SqlHelper.ExcuteReader(sql,CommandType.Text, param))
            {
                if (dr != null)
                {
                    mpList = new List<MailPage>();
                    while (dr.Read())
                    {
                        mp = new MailPage();

                        mp.ModelName = dr["ModelName"].ToString();
                        mp.ShowName = dr["ShowName"].ToString();
                        mp.PageUrl = dr["PageUrl"].ToString();
                        mp.Uid = Convert.ToInt32(dr["Uid"]);
                        mp.MName = dr["MName"].ToString();

                        mpList.Add(mp);
                    }
                }
            }
            return mpList;
        }

        /// <summary>
        /// 根据用户编号获取数据
        /// </summary>
        /// <param name="uid"></param>
        /// <returns></returns>
        public List<MailPage> GetAll(int uid)
        {
            string sql = "select sd2.Name as ModelName,sd1.ShowName,sd1.PageUrl,sd1.MName,sd1.Uid from "+
                            "(select sm.ShowName, sm.PageName as PageUrl,c.Uid,sd.[Name] as MName,Mid "+
                            "from SystemMenu sm "+
                            "join Competence c on sm.Id = c.Sid " +
                            "join SetData sd on sd.id = sm.Mnid " +
                            "where c.STable = 0 " +
                            "and sm.IsEnable = 1 " +
                            "and sm.IsDel = 0 " +
                            "and sm.IsShow = 1 " +
                            "and Uid = @Uid )  sd1 ,SetData sd2 " +
                            "where sd1.Mid = sd2.Id " +
                            "and sd2.STid = 5";
            return excuteSql(sql, new SqlParameter("@Uid", uid));
        }
    }
}