WINFORM权限系统开发系列教程（四）数据业务封装

数据访问DAL和实体类

UserInfomModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Xwy.WindowsFormsApp.Models
{
    public class UserInfoModel
    {
        public int UserId { get; set; }
        public string UserName { get; set; }
        public string UserPwd { get; set; }

    }
}

　　

RoleInfoModel.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Xwy.WindowsFormsApp.Models
{
    public class RoleInfoModel
    {
        public int RoleId { get; set; }
        public string RoleName { get; set; }



    }
}

　

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Xwy.WindowsFormsApp.Models
{
    public class MenuInfoModel
    {
        public int MenuId { get; set; }
        public string MenuName { get; set; }
        public int ParentId { get; set; }

        public string FrmName { get; set; }

        public string MKey { get; set; }


    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Xwy.WindowsFormsApp.Models
{
    public class MenuInfoAllModel:MenuInfoModel
    {
        //public int MenuId { get; set; }
        //public string MenuName { get; set; }
        //public int ParentId { get; set; }

        //public string FrmName { get; set; }

        //public string MKey { get; set; }

        public string ParentName { get; set; }


    }
}

　

UserDAL.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Xwy.WindowsFormsApp.Common;
using Xwy.WindowsFormsApp.DAL.Helpers;
using Xwy.WindowsFormsApp.Models;

namespace Xwy.WindowsFormsApp.DAL
{
    public class UserDAL
    {
        public int Login(UserInfoModel user)
        {
            string sql = "select UserId from UserInfos where UserName=@UserName and UserPwd=@UserPwd";
            SqlParameter[] paras =
            {
                new SqlParameter("@UserName",user.UserName),
                new SqlParameter("@UserPwd",user.UserPwd)
            };
            object oId = DBHelper.ExecuteScalar(sql,1,paras);
            if (oId != null && oId.ToString() != "")
            {
                return oId.GetInt();
            }
            else
            {
                return 0;
            }
        }
        
        public List<RoleInfoModel> GetUserRoles(int userId)
        {
            string sql = "select r.RoleId,RoleName from UserRoleInfos ur inner join RoleInfos r on r.RoleId=ur.RoleId where UserId=@UserId";
            SqlParameter paraId = new SqlParameter("@UserId", userId);
            SqlDataReader dr = DBHelper.ExecuteReader(sql, 1, paraId);
            List<RoleInfoModel> list = new List<RoleInfoModel>();
            while(dr.Read())
            {
                RoleInfoModel roleInfo = new RoleInfoModel();
                roleInfo.RoleId = dr["RoleId"].ToString().GetInt();
                roleInfo.RoleName = dr["RoleName"].ToString();
                list.Add(roleInfo);
            }
            dr.Close();//关闭阅读器
            return list;
        }
    
    }
}

MenuDAL.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Xwy.WindowsFormsApp.Common;
using Xwy.WindowsFormsApp.DAL.Helpers;
using Xwy.WindowsFormsApp.Models;

namespace Xwy.WindowsFormsApp.DAL
{
    public class MenuDAL
    {
        public List<MenuInfoAllModel> GetMenuList(int parentId,string mName)
        {
            List<MenuInfoAllModel> list = new List<MenuInfoAllModel>();

            string sql = "select m.MenuId,m.MenuName,m.ParentId,p.MenuName ParentName,m.FrmName,m.MKey from MenuInfos m left join MenuInfos p on m.ParentId=p.MenuId where 1=1";
            if (parentId > 0)
                sql += " and m.ParentId=@parentId";
            if (!string.IsNullOrEmpty(mName))
                sql += " and m.MenuName like @mName";
            SqlParameter[] paras =
           {
                new SqlParameter("@parentId",parentId),
                new SqlParameter("@mName",$"%{mName}%")
            };
            SqlDataReader dr = DBHelper.ExecuteReader(sql, 1, paras);
            while (dr.Read())
            {
                MenuInfoAllModel menu = new MenuInfoAllModel();
                menu.MenuId = dr["MenuId"].ToString().GetInt();
                menu.MenuName = dr["MenuName"].ToString();
                menu.ParentId = dr["ParentId"].ToString().GetInt();
                menu.ParentName = dr["ParentName"].ToString();
                menu.FrmName = dr["FrmName"].ToString();
                menu.MKey = dr["MKey"].ToString();
                list.Add(menu);
            }
            return list;
        }
        
        //获取父级菜单列表
        public DataTable GetParentList()
        {
            string sql = "select m.ParentId,p.MenuName ,count(1) count from MenuInfos m inner join MenuInfos p on m.ParentId=p.MenuId group by m.ParentId,p.MenuName";
            DataTable dt = DBHelper.GetDataTable(sql,1);
            return dt;
        
        }
        /// <summary>
        /// 获取用户角色菜单列表
        /// </summary>
        /// <param name="roleIds"></param>
        /// <returns></returns>
        public List<MenuInfoModel> GetUserMenuList(string roleIds)
        {
            string sql;
            if (roleIds.Split(',').Contains("1"))
            {
                sql = "select MenuId,MenuName,ParentId,FrmName,MKey from MenuInfos";
            }
            else
            {
                sql = "select rm.MenuId,MenuName,ParentId,FrmName,MKey from RoleMenuInfos rm inner join MenuInfos m on rm.MenuId=m.MenuId where rm.RoleId in (" + roleIds + ")";

            }
            SqlDataReader dr = DBHelper.ExecuteReader(sql, 1);
            List<MenuInfoModel> list = new List<MenuInfoModel>();
            while(dr.Read())
            {
                MenuInfoModel menuInfo = new MenuInfoModel();
                menuInfo.MenuId = dr["MenuId"].ToString().GetInt();
                menuInfo.MenuName = dr["MenuName"].ToString();
                menuInfo.ParentId= dr["ParentId"].ToString().GetInt();
                menuInfo.FrmName = dr["FrmName"].ToString();
                menuInfo.MKey = dr["MKey"].ToString();

                list.Add(menuInfo);
            }
            dr.Close();//关闭阅读器
            return list;
        }

        /// <summary>
        /// 删除菜单信息
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns></returns>
        public bool DeleteMenu(int menuId)
        {
            string sqlDelRoleMenu = "delete from RoleMenuInfos where RoleMenuInfos where MenuId=@menuId";
            string sqlDelMenu="delete from MenuInfos where MenuId=@menuId";
            SqlParameter[] paras = { new SqlParameter("@menuId", menuId) };
            List<CommandInfo> comList = new List<CommandInfo>();
            comList.Add(new CommandInfo
            {
                CommandText = sqlDelRoleMenu,
                IsProc = false,
                Paras = paras
            });
            comList.Add(new CommandInfo
            {
                CommandText = sqlDelMenu,
                IsProc = false,
                Paras = paras
            });
            return DBHelper.ExecuteTrans(comList);

        }

        /// <summary>
        /// 获取所有菜单数据（主要用于绑定下拉框）
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllMenu()
        {
            string sql = "select MenuId,MenuName from MenuInfos";
            return DBHelper.GetDataTable(sql,1);

        }


        public MenuInfoModel GetMenuInfoById(int menuId)
        {
            string sql = "select MenuId,MenuName,ParentId,FrmName,MKey from MenuInfos where MenuId=@menuId ";
            SqlParameter paraId = new SqlParameter("@menuId", menuId);
            SqlDataReader dr = DBHelper.ExecuteReader(sql, 1, paraId);
            MenuInfoModel menuInfo = default(MenuInfoModel);
            if (dr.Read())
            {
                menuInfo = new MenuInfoModel();
                menuInfo.MenuId = dr["MenuId"].ToString().GetInt();
                menuInfo.MenuName = dr["MenuName"].ToString();
                menuInfo.ParentId = dr["ParentId"].ToString().GetInt();
                menuInfo.FrmName = dr["FrmName"].ToString();
                menuInfo.MKey = dr["MKey"].ToString();
            }
            dr.Close();
            return menuInfo;
        }

        public bool ExistMenuName(string menuName)
        {
            string sql = "select count(1) from MenuInfos where MenuName=@menuName";
            SqlParameter paraName = new SqlParameter("@menuName", menuName);
            object oCount = DBHelper.ExecuteScalar(sql, 1, paraName);
            if (oCount != null && oCount.ToString() != "")
            {
                return oCount.GetInt() > 0;
            }
            else
            {
                return false;
            }
        }

        public bool AddMenuInfo(MenuInfoModel menuInfo)
        {
            string sql = "insert into MenuInfos(MenuName,ParentId,FrmName,MKey) values(@menuName,@parentId,@frmName,@mKey)";

            SqlParameter[] paras = 
           { 
                new SqlParameter("@menuName", menuInfo.MenuName),
                new SqlParameter("@parentId", menuInfo.ParentId),
                new SqlParameter("@frmName", menuInfo.FrmName),
                new SqlParameter("@mKey", menuInfo.MKey)
            };
            return DBHelper.ExecuteNonQuery(sql, 1, paras) > 0;
        }
        public bool UpdateMenuInfo(MenuInfoModel menuInfo)
        {
            string sql = "update  MenuInfos set MenuName=@menuName,ParentId=@parentId,FrmName=@frmName,MKey=@mKey where MenuId=@menuId ";

            SqlParameter[] paras =
           {
                new SqlParameter("@menuName", menuInfo.MenuName),
                new SqlParameter("@parentId", menuInfo.ParentId),
                new SqlParameter("@frmName", menuInfo.FrmName),
                new SqlParameter("@mKey", menuInfo.MKey),
                new SqlParameter("@menuId", menuInfo.MenuId),
            };
            return DBHelper.ExecuteNonQuery(sql, 1, paras) > 0;
        }
    }
}

 RoleDAL.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Xwy.WindowsFormsApp.Common;
using Xwy.WindowsFormsApp.DAL.Helpers;
using Xwy.WindowsFormsApp.Models;

namespace Xwy.WindowsFormsApp.DAL
{
    public class MenuDAL
    {
        public List<MenuInfoAllModel> GetMenuList(int parentId,string mName)
        {
            List<MenuInfoAllModel> list = new List<MenuInfoAllModel>();

            string sql = "select m.MenuId,m.MenuName,m.ParentId,p.MenuName ParentName,m.FrmName,m.MKey from MenuInfos m left join MenuInfos p on m.ParentId=p.MenuId where 1=1";
            if (parentId > 0)
                sql += " and m.ParentId=@parentId";
            if (!string.IsNullOrEmpty(mName))
                sql += " and m.MenuName like @mName";
            SqlParameter[] paras =
           {
                new SqlParameter("@parentId",parentId),
                new SqlParameter("@mName",$"%{mName}%")
            };
            SqlDataReader dr = DBHelper.ExecuteReader(sql, 1, paras);
            while (dr.Read())
            {
                MenuInfoAllModel menu = new MenuInfoAllModel();
                menu.MenuId = dr["MenuId"].ToString().GetInt();
                menu.MenuName = dr["MenuName"].ToString();
                menu.ParentId = dr["ParentId"].ToString().GetInt();
                menu.ParentName = dr["ParentName"].ToString();
                menu.FrmName = dr["FrmName"].ToString();
                menu.MKey = dr["MKey"].ToString();
                list.Add(menu);
            }
            return list;
        }
        
        //获取父级菜单列表
        public DataTable GetParentList()
        {
            string sql = "select m.ParentId,p.MenuName ,count(1) count from MenuInfos m inner join MenuInfos p on m.ParentId=p.MenuId group by m.ParentId,p.MenuName";
            DataTable dt = DBHelper.GetDataTable(sql,1);
            return dt;
        
        }
        /// <summary>
        /// 获取用户角色菜单列表
        /// </summary>
        /// <param name="roleIds"></param>
        /// <returns></returns>
        public List<MenuInfoModel> GetUserMenuList(string roleIds)
        {
            string sql;
            if (roleIds==""||roleIds.Split(',').Contains("1"))
            {
                sql = "select MenuId,MenuName,ParentId,FrmName,MKey from MenuInfos";
            }
            else
            {
                sql = "select rm.MenuId,MenuName,ParentId,FrmName,MKey from RoleMenuInfos rm inner join MenuInfos m on rm.MenuId=m.MenuId where rm.RoleId in (" + roleIds + ")";

            }
            SqlDataReader dr = DBHelper.ExecuteReader(sql, 1);
            List<MenuInfoModel> list = new List<MenuInfoModel>();
            while(dr.Read())
            {
                MenuInfoModel menuInfo = new MenuInfoModel();
                menuInfo.MenuId = dr["MenuId"].ToString().GetInt();
                menuInfo.MenuName = dr["MenuName"].ToString();
                menuInfo.ParentId= dr["ParentId"].ToString().GetInt();
                menuInfo.FrmName = dr["FrmName"].ToString();
                menuInfo.MKey = dr["MKey"].ToString();

                list.Add(menuInfo);
            }
            dr.Close();//关闭阅读器
            return list;
        }

        /// <summary>
        /// 删除菜单信息
        /// </summary>
        /// <param name="menuId"></param>
        /// <returns></returns>
        public bool DeleteMenu(int menuId)
        {
            string sqlDelRoleMenu = "delete from RoleMenuInfos where RoleMenuInfos where MenuId=@menuId";
            string sqlDelMenu="delete from MenuInfos where MenuId=@menuId";
            SqlParameter[] paras = { new SqlParameter("@menuId", menuId) };
            List<CommandInfo> comList = new List<CommandInfo>();
            comList.Add(new CommandInfo
            {
                CommandText = sqlDelRoleMenu,
                IsProc = false,
                Paras = paras
            });
            comList.Add(new CommandInfo
            {
                CommandText = sqlDelMenu,
                IsProc = false,
                Paras = paras
            });
            return DBHelper.ExecuteTrans(comList);

        }

        /// <summary>
        /// 获取所有菜单数据（主要用于绑定下拉框）
        /// </summary>
        /// <returns></returns>
        public DataTable GetAllMenu()
        {
            string sql = "select MenuId,MenuName from MenuInfos";
            return DBHelper.GetDataTable(sql,1);

        }


        public DataTable GetAllTvMenus()
        {
            string sql = "select MenuId,MenuName,ParentId from MenuInfos";
            return DBHelper.GetDataTable(sql, 1);

        }


        public MenuInfoModel GetMenuInfoById(int menuId)
        {
            string sql = "select MenuId,MenuName,ParentId,FrmName,MKey from MenuInfos where MenuId=@menuId ";
            SqlParameter paraId = new SqlParameter("@menuId", menuId);
            SqlDataReader dr = DBHelper.ExecuteReader(sql, 1, paraId);
            MenuInfoModel menuInfo = default(MenuInfoModel);
            if (dr.Read())
            {
                menuInfo = new MenuInfoModel();
                menuInfo.MenuId = dr["MenuId"].ToString().GetInt();
                menuInfo.MenuName = dr["MenuName"].ToString();
                menuInfo.ParentId = dr["ParentId"].ToString().GetInt();
                menuInfo.FrmName = dr["FrmName"].ToString();
                menuInfo.MKey = dr["MKey"].ToString();
            }
            dr.Close();
            return menuInfo;
        }

        public bool ExistMenuName(string menuName)
        {
            string sql = "select count(1) from MenuInfos where MenuName=@menuName";
            SqlParameter paraName = new SqlParameter("@menuName", menuName);
            object oCount = DBHelper.ExecuteScalar(sql, 1, paraName);
            if (oCount != null && oCount.ToString() != "")
            {
                return oCount.GetInt() > 0;
            }
            else
            {
                return false;
            }
        }

        public bool AddMenuInfo(MenuInfoModel menuInfo)
        {
            string sql = "insert into MenuInfos(MenuName,ParentId,FrmName,MKey) values(@menuName,@parentId,@frmName,@mKey)";

            SqlParameter[] paras = 
           { 
                new SqlParameter("@menuName", menuInfo.MenuName),
                new SqlParameter("@parentId", menuInfo.ParentId),
                new SqlParameter("@frmName", menuInfo.FrmName),
                new SqlParameter("@mKey", menuInfo.MKey)
            };
            return DBHelper.ExecuteNonQuery(sql, 1, paras) > 0;
        }
        public bool UpdateMenuInfo(MenuInfoModel menuInfo)
        {
            string sql = "update  MenuInfos set MenuName=@menuName,ParentId=@parentId,FrmName=@frmName,MKey=@mKey where MenuId=@menuId ";

            SqlParameter[] paras =
           {
                new SqlParameter("@menuName", menuInfo.MenuName),
                new SqlParameter("@parentId", menuInfo.ParentId),
                new SqlParameter("@frmName", menuInfo.FrmName),
                new SqlParameter("@mKey", menuInfo.MKey),
                new SqlParameter("@menuId", menuInfo.MenuId),
            };
            return DBHelper.ExecuteNonQuery(sql, 1, paras) > 0;
        }
    }
}