test/Member/CoreEntity/DAL/RoleDAL.cs

using Common.Model;
using CoreEntity.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ZcPeng.PublicLibrary;
using Common.Wechat;
using System.Data.SqlClient;
using PublicLibrary.Model;

namespace CoreEntity.DAL
{
    public class RoleDAL
    {
        public static IList<Role> GetRoles()
        {
            IList<Role> roleList = new List<Role>(0);

            DataTable result;

            string direct = " desc ";

            result = DataAccess.GetDataTable( Config.TablePrefix+"Role", "Id", "Id,RoleName,RoleRemark", "", "", "Id" + direct, 1, 100,out var msg);
            if (result != null && result.Rows.Count > 0)
            {
                // 把DataTable转换为IList<Role>  
                roleList = ModelConvertHelper<Role>.ConvertToModel(result);
            }
            return roleList;
        }

        public static int SaveRoles(IList<Role> roles)
        {
            string commandText = "INSERT INTO " + Config.TablePrefix + "Role (rolename,roleremark,isdelete)"
                + " VALUES ";
            int number = 0;
            //准备参数
            List<List<Object>> parameters = new List<List<Object>>();
            foreach (Role role in roles)
            {
                commandText = commandText + "(@rolename" + number + ",@roleremark" + number + ",@isdelete" + number + ")";

                parameters.Add(new List<Object>() { "rolename" + number, role.RoleName });
                parameters.Add(new List<Object>() { "roleremark" + number, role.RoleRemark });
                parameters.Add(new List<Object>() { "isdelete" + number, role.IsDelete });
                number = number + 1;
                if (number == roles.Count)
                {
                    commandText = commandText + ",";
                }
            }
            string result;

            List<SqlParameter> parameters1 = DataAccess.ToParameters(parameters);
            int success = DataAccess.ExecuteCommand(commandText, parameters1, out result);
            return success;
        }

        public static int SaveRolesRelatePermission(int permissionId,int[] rolesIds)
        {
            var param = new List<SqlParameter>();
            param.Add(new SqlParameter("PermissionId", permissionId));
            var sql = "update mem_RoleRelatePermission set IsDelete =1 where PermissionId=@PermissionId;";
            var index = 0;
            foreach (var item in rolesIds)
            {
                index++;
                param.Add(new SqlParameter($"{index}", item));
                sql += $" if (exists(select * from mem_RoleRelatePermission where PermissionId = @PermissionId and RoleId = @{index} and IsDelete = 1)) " +
                    " begin " +
                      $" update mem_RoleRelatePermission set IsDelete = 0 where PermissionId = @PermissionId and RoleId =  @{index} and IsDelete = 1 " +
                    " end " +
                    " else " +
                    " begin " +
                    $" insert into mem_RoleRelatePermission (RoleId, PermissionId) values (@{index},@PermissionId) end ";
            }
            int success = DataAccess.ExecuteCommand(sql, param, out _);

            return success;
        }

        public static int DeleteRoleRelate(int PermissionId)
        {

            string commandText = "DELETE FROM  mem_RoleRelatePermission WHERE PermissionId = @PermissionId";

            //准备参数
            List<List<Object>> parameters = new List<List<Object>>();
            parameters.Add(new List<Object>() { "PermissionId", PermissionId });

            string result = "";
            List<SqlParameter> parameters1 = DataAccess.ToParameters(parameters);
            int success = DataAccess.ExecuteCommand(commandText, parameters1, out result);
            return success;
        }

        public static IList<Permission> GetPermissions(Int64 roleId,string filter = " and A.AuthType ='U'")
        {
            IList<Permission> permsList = new List<Permission>(0);

            DataTable dt = new DataTable();
            string result = string.Empty;
            var sortDirection = 1;
            var pageIndex = 1;
            var pageSize = 100;

            string direct = " desc ";
            if (sortDirection == 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText = "select * from (" +
                "select A.*, row_number() over" +
                "(  order by A.Sort " + direct + " )  as rownum" +
                "  from " + Config.TablePrefix + "Permission A" +
                " where  A.Id in (select B.PermissionId from mem_RoleRelatePermission B" +
                " where  B.roleid = @roleid and B.permissionId = A.Id and isdelete <> 1 )" +
                filter +
                " and A.isdelete = 0 )AAA" +
                " where AAA.rownum>" + start + " and AAA.rownum<" + end ;


            List<List<Object>> parameters1 = new List<List<Object>>();
            parameters1.Add(new List<Object>() { "roleid", roleId });
            bool result1 = DataAccess.GetValues(commandText, ref dt, DataAccess.ToParameters(parameters1).ToArray(), out result);
            
            if (result1 && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<Role>  
                permsList = ModelConvertHelper<Permission>.ConvertToModel(dt);
            }
            return permsList;
        }

        public static IList<Permission> GetTopPermissions()
        {
            IList<Permission> permsList = new List<Permission>(0);

            DataTable dt = new DataTable();
            string result = string.Empty;
            var sortDirection = 1;
            var pageIndex = 1;
            var pageSize = Config.MaxPageSize;

            string direct = " desc ";
            if (sortDirection != 1)
                direct = " asc";
            int start = (pageIndex - 1) * pageSize;
            int end = (start + 1 + pageSize);
            string commandText = "select * from (" +
                "select A.*, row_number() over" +
                "(  order by A.Id " + direct + " )  as rownum" +
                "  from " + Config.TablePrefix + "Permission A " +
                " where A.ParentId = @ParentId " +
                ") AAA" +
                " where AAA.rownum>" + start + " and AAA.rownum<" + end +
                " ";


            List<List<Object>> parameters1 = new List<List<Object>>();
            parameters1.Add(new List<Object>() { "ParentId", 0 });
            bool result1 = DataAccess.GetValues(commandText, ref dt, DataAccess.ToParameters(parameters1).ToArray(), out result);

            if (result1 && dt.Rows.Count > 0)
            {
                // 把DataTable转换为IList<Role>  
                permsList = ModelConvertHelper<Permission>.ConvertToModel(dt);
            }
            return permsList;
        }

    }
}