/// <summary> /// 界面按钮权限 /// </summary> /// <param name="vm"></param> /// <returns></returns> public List <FormRoleMenuAuthoritiesVM> GetFormRoleMenuAuthorities(FormRoleMenuAuthoritiesVM vm) { StringBuilder sb = new StringBuilder(); try { string sql = @" select u.ID,u.Account,a.Name AuthorityName,m.FormName from Users u join UserRoles ur on u.ID=ur.UserID join RoleMenus rm on ur.RoleID=rm.RoleID join Menus m on rm.MenuID=m.ID join RoleMenuAuthorities rma on m.ID=rma.MenuID and rma.RoleID=ur.RoleID join Authorities a on rma.AuthorityID=a.ID where u.Account=@Account and m.FormName=@FormName and m.[Status]=1 "; List <SqlParameter> paramList = new List <SqlParameter>(); SqlParameter paramAccount = new SqlParameter("@Account", vm.Account); SqlParameter paramFormName = new SqlParameter("@FormName", vm.FormName); paramList.Add(paramAccount); paramList.Add(paramFormName); return(DataTableToList.DataSetToList <FormRoleMenuAuthoritiesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, paramList.ToArray()))); } catch (Exception ex) { return(null); } }
public List <MenusVM> GetAllMenus(MenusVM vm) { try { StringBuilder selectDataCommand = new StringBuilder(@" SELECT [ID] ,[ParentID] ,[FormName] ,[TabHeaderText] ,[SortCode] ,[Remark] FROM [RABC].[dbo].[Menus] where [Status]=1 "); List <SqlParameter> parameters = new List <SqlParameter>(); if (!string.IsNullOrEmpty(vm.FormName)) { selectDataCommand.Append(" and [FormName] like @FormName"); parameters.Add(new SqlParameter("@FormName", string.Format("%{0}%", vm.FormName))); } if (!string.IsNullOrEmpty(vm.TabHeaderText)) { selectDataCommand.Append(" and [TabHeaderText] like @TabHeaderText"); parameters.Add(new SqlParameter("@TabHeaderText", string.Format("%{0}%", vm.TabHeaderText))); } return(DataTableToList.DataSetToList <MenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), parameters.ToArray()))); } catch (Exception ex) { return(null); } }
public List <RoleMenuAuthoritiesVM> GetRoleMenuAuthorities(RoleMenuAuthoritiesVM vm) { StringBuilder sb = new StringBuilder(); try { StringBuilder sql = new StringBuilder(@" select rma.ID,m.TabHeaderText MenuName,r.Name RoleName ,a.Name AuthorityName from [dbo].[RoleMenuAuthorities] rma join [dbo].[Menus] m on rma.[MenuID]=m.ID join [dbo].[Roles] r on rma.RoleID=r.ID join [dbo].[Authorities] a on a.ID=rma.AuthorityID "); List <SqlParameter> paramList = new List <SqlParameter>(); StringBuilder where = new StringBuilder(" where 1=1 and m.[Status]=1 "); if (!string.IsNullOrEmpty(vm.MenuName)) { where.Append(" and m.TabHeaderText like @MenuName"); SqlParameter paramMenuName = new SqlParameter("@MenuName", string.Format("%{0}%", vm.MenuName)); paramList.Add(paramMenuName); } if (!string.IsNullOrEmpty(vm.RoleName)) { where.Append(" and r.Name like @RoleName"); SqlParameter paramRoleName = new SqlParameter("@RoleName", string.Format("%{0}%", vm.RoleName)); paramList.Add(paramRoleName); } sql.Append(where); return(DataTableToList.DataSetToList <RoleMenuAuthoritiesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql.ToString(), paramList.ToArray()))); } catch (Exception ex) { return(null); } }
//private static long SqlBulkCopyInsert() //{ // Stopwatch stopwatch = new Stopwatch(); // stopwatch.Start(); // DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, "select * from FROM [RABC].[dbo].[UserRoles] where ID<0").Tables[0]; // string passportKey; // for (int i = 0; i < dt.Columns.Count; i++) // { // DataRow dataRow = dt.NewRow(); // dataRow[""] = passportKey; // dataTable.Rows.Add(dataRow); // } // SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString); // sqlBulkCopy.DestinationTableName = "Passport"; // sqlBulkCopy.BatchSize = dataTable.Rows.Count; // SqlConnection sqlConnection = new SqlConnection(connectionString); // sqlConnection.Open(); // if (dataTable != null && dataTable.Rows.Count != 0) // { // sqlBulkCopy.WriteToServer(dataTable); // } // sqlBulkCopy.Close(); sqlConnection.Close(); // stopwatch.Stop(); // return stopwatch.ElapsedMilliseconds; //} public List <UserRolesVM> GetUserRoles(UserRolesVM vm) { try { // DataPageRequestInfo info = JsonHelper.DeserializeJsonToObject<DataPageRequestInfo>(strData); // StringBuilder where = new StringBuilder("where 1 = 1"); // var qm = JsonConvert.DeserializeAnonymousType(info.Conditions, new { Name = "" }); //List<SqlParameter> parameters = new List<SqlParameter>(); //if (!string.IsNullOrEmpty(qm.Name)) //{ // SqlParameter param = new SqlParameter("@Name", string.Format("%{0}%", qm.Name)); // where.Append(" and Name like @Name"); // parameters.Add(param); //} StringBuilder selectDataCommand = new StringBuilder(@" SELECT ur.ID ,[UserID] ,[RoleID],r.Name RoleName ,u.Account UserName FROM [RABC].[dbo].[UserRoles] ur join [dbo].[Users] u on ur.UserID=u.[ID] join [dbo].[Roles] r on r.ID=ur.RoleID "); List <SqlParameter> paramList = new List <SqlParameter>(); if (vm.UserID != 0) { selectDataCommand.Append(" where u.ID=@UserID"); SqlParameter paramID = new SqlParameter("@UserID", vm.UserID); paramList.Add(paramID); } // selectDataCommand.Append(where.ToString()); return(DataTableToList.DataSetToList <UserRolesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), paramList.ToArray()))); } catch (Exception ex) { return(null); } }
public List <RolesVM> GetUserNotHaveRoles(UserRolesVM vm) { try { //DataPageRequestInfo info = JsonHelper.DeserializeJsonToObject<DataPageRequestInfo>(strData); //StringBuilder where = new StringBuilder("where 1 = 1"); //var qm = JsonConvert.DeserializeAnonymousType(info.Conditions, new { Name = "" }); // RolesVM qm = JsonConvert.DeserializeObject<RolesVM>(info.Conditions); // List<SqlParameter> parameters = new List<SqlParameter>(); StringBuilder selectDataCommand = new StringBuilder(@" select [ID] ,[Name] ,[CreateTime],[Remark] from [dbo].[Roles] r where not exists ( select ur.ID from [dbo].[UserRoles] ur where ur.RoleID=r.ID and ur.UserID=@UserID ) "); SqlParameter paramUserID = new SqlParameter("@UserID", vm.UserID); return(DataTableToList.DataSetToList <RolesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), paramUserID))); } catch (Exception ex) { return(null); } }
public List <RolesVM> GetRoles(RolesVM vm) { try { // DataPageRequestInfo info = JsonHelper.DeserializeJsonToObject<DataPageRequestInfo>(strData); StringBuilder where = new StringBuilder("where 1 = 1"); //var qm = JsonConvert.DeserializeAnonymousType(info.Conditions, new { Name = "" }); // RolesVM qm = JsonConvert.DeserializeObject<RolesVM>(info.Conditions); List <SqlParameter> parameters = new List <SqlParameter>(); if (!string.IsNullOrEmpty(vm.Name)) { SqlParameter param = new SqlParameter("@Name", string.Format("%{0}%", vm.Name)); where.Append(" and Name like @Name"); parameters.Add(param); } StringBuilder selectDataCommand = new StringBuilder(" SELECT [ID],[Name] ,[CreateTime] ,[Remark] FROM [RABC].[dbo].[Roles]"); selectDataCommand.Append(where.ToString()); return(DataTableToList.DataSetToList <RolesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), parameters.ToArray()))); // retJson = JsonHelper.SerializeObject(list); } catch (Exception ex) { return(null); } }
public UserVM Login(UserVM vm) { string retJson = "error"; string sql = @" SELECT [ID] ,[Account] ,[Password] ,[Status] ,[CreateTime] ,[ModityTime] ,[TimeStamp] FROM [RABC].[dbo].[Users] where [Account]=@Account ; "; SqlParameter paramAccount = new SqlParameter("@Account", vm.Account); return(DataTableToList.DataSetToList <UserVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, paramAccount)).FirstOrDefault()); }
public List <MenusVM> GetMenus(RoleMenusVM vm) { try { // StringBuilder selectDataCommand = new StringBuilder(@" // SELECT m.[ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark] // FROM [RABC].[dbo].[Menus] m // where not exists // ( // select * from [dbo].[RoleMenus] rm where rm.MenuID=m.ID and rm.RoleID=@RoleID // ) //"); string sql = @" select * into #NoParents from ( SELECT m.[ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark],[Status] FROM [RABC].[dbo].[Menus] m where not exists ( select rm.ID from [dbo].[RoleMenus] rm where rm.MenuID=m.ID and rm.RoleID=@RoleID ) and m.[Status]=1 ) t select * into #parents from ( select [ID] ,[ParentID] ,[FormName] ,[TabHeaderText],[SortCode] ,[Remark],[Status] from Menus where ID in ( --查找所有有孩子的父节点 select ParentID from #NoParents where ParentID<>0 group by ParentID ) and [Status]=1 )t1 select * from #NoParents union select * from #parents drop table #NoParents drop table #parents "; SqlParameter paramRoleID = new SqlParameter("@RoleID", vm.RoleID); // selectDataCommand.Append(where.ToString()); return(DataTableToList.DataSetToList <MenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql, paramRoleID))); } catch (Exception ex) { return(null); } }
public List <UserMenusVM> GetUserMenus(string account) { string sql = string.Format(@" select distinct m.ID, m.[ParentID],m.[FormName] ,m.[TabHeaderText] ,m.SortCode from Users u join UserRoles ur on u.ID=ur.UserID join RoleMenus rm on ur.RoleID=rm.RoleID join Menus m on rm.MenuID=m.ID where u.Account='{0}' and m.[Status]=1 order by m.SortCode ", account); DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql); var list = DataTableToList.DataSetToList <UserMenusVM>(ds); return(list); }
public (int Count, List <UserVM> UsersList) GetUsers(UsersQM qm) { try { StringBuilder selectCountCommand = new StringBuilder(@" SELECT Count([ID]) FROM [RABC].[dbo].[Users] "); StringBuilder where = new StringBuilder("where 1 = 1"); List <SqlParameter> parameters = new List <SqlParameter>(); if (!string.IsNullOrEmpty(qm.Account)) { SqlParameter param = new SqlParameter("@Account", string.Format("%{0}%", qm.Account)); where.Append(" and Account like @Account"); parameters.Add(param); } selectCountCommand.Append(where.ToString()); StringBuilder selectDataCommand = new StringBuilder(string.Format(@" select * from ( select row_number() over (order by a.ID ) as rowNum, a.* from ( SELECT TOP ({0}) [ID],[Account] ,[Password] FROM [RABC].[dbo].[Users] {1} ) as a )c where c.rowNum>{2}", qm.Take + qm.Skip, where.ToString(), qm.Skip)); // DataPagingInfo dataPagingInfo = new DataPagingInfo(); var list = DataTableToList.DataSetToList <UserVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), parameters.ToArray())); // dataPagingInfo.DataSoure = JsonConvert.SerializeObject(list); // dataPagingInfo.SearchAllFlg = info.SearchAllFlg; // dataPagingInfo.PageCount = pageCount; int count = (int)SqlHelper.ExecuteScalar(SqlHelper.GetConnSting(), CommandType.Text, selectCountCommand.ToString(), parameters.ToArray());; // retJson = JsonHelper.SerializeObject(dataPagingInfo); return(count, list); } catch (Exception ex) { return(0, null); } }
public int AddRoleMenus(List <MenusVM> list) { StringBuilder sb = new StringBuilder(); int i = 0; using (SqlConnection con = new SqlConnection(SqlHelper.GetConnSting())) { con.Open(); SqlTransaction tran = con.BeginTransaction(); StringBuilder sqlSB = new StringBuilder(); try { //父节点单独拿出来处理 var parents = list.Where(p => p.ParentID == 0).ToList(); string existsParentIDS = "SELECT distinct [MenuID] FROM [RABC].[dbo].[RoleMenus] where RoleID= @RoleID"; var parentMenuIDS = DataTableToList.DataSetToList <RoleMenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, existsParentIDS, new SqlParameter("@RoleID", list.First().RoleID))).Select(p => p.MenuID).ToList(); var notEixtParent = parents.Where(p => !parentMenuIDS.Contains(p.ID)).ToList(); var dhildren = list.Where(p => p.ParentID != 0).ToList(); dhildren.AddRange(notEixtParent); SqlCommand cmd = new SqlCommand(); cmd.Connection = con; cmd.Transaction = tran; for (int j = 0; j < dhildren.Count; j++) { string insertCommand = String.Format(@"INSERT INTO [dbo].[RoleMenus] ([RoleID] ,[MenuID]) VALUES (@RoleID{0} ,@MenuID{0});", j); cmd.Parameters.AddWithValue(string.Format("@RoleID{0}", j), dhildren[j].RoleID); cmd.Parameters.AddWithValue(string.Format("@MenuID{0}", j), dhildren[j].ID); sqlSB.Append(insertCommand); } cmd.CommandText = sqlSB.ToString(); i = cmd.ExecuteNonQuery(); tran.Commit(); } catch (Exception ex) { i = 0; } } return(i); }
public List <FormRoleMenuAuthoritiesVM> GetParentMenus() { StringBuilder sb = new StringBuilder(); try { string sql = @" SELECT [ID] ,[ParentID] ,[FormName],[TabHeaderText] ,[SortCode] ,[Remark],[Status] FROM [RABC].[dbo].[Menus] where ParentID=0 and [Status]=1 "; return(DataTableToList.DataSetToList <FormRoleMenuAuthoritiesVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql))); } catch (Exception ex) { return(null); } }
public List <RoleMenusVM> GetRoleMenus(RoleMenusVM vm) { try { StringBuilder selectDataCommand = new StringBuilder(@" select rm.[ID],rm.[RoleID], rm.[MenuID],r.Name RoleName,m.TabHeaderText MenuName ,m.ParentID from [dbo].[RoleMenus] rm join [dbo].[Roles] r on rm.RoleID=r.ID join [dbo].[Menus] m on rm.MenuID =m.ID "); StringBuilder where = new StringBuilder(" where 1=1 and m.[Status]=1 "); List <SqlParameter> paramList = new List <SqlParameter>(); if (!string.IsNullOrEmpty(vm.RoleName)) { where.Append(" and r.Name like @RoleName"); SqlParameter paramRoleName = new SqlParameter("@RoleName", string.Format("%{0}%", vm.RoleName)); paramList.Add(paramRoleName); } if (!string.IsNullOrEmpty(vm.MenuName)) { where.Append(" and m.TabHeaderText like @MenuName"); SqlParameter paramMenuName = new SqlParameter("@MenuName", string.Format("%{0}%", vm.MenuName)); paramList.Add(paramMenuName); } if (vm.RoleID != 0) { where.Append(" and r.ID = @RoleID"); SqlParameter paramRoleID = new SqlParameter("@RoleID", vm.RoleID); paramList.Add(paramRoleID); } selectDataCommand.Append(where.ToString()); return(DataTableToList.DataSetToList <RoleMenusVM>(SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, selectDataCommand.ToString(), paramList.ToArray()))); } catch (Exception ex) { return(null); } }