public void AddRelation(Role right, Role group)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[2];
parameters[0] = new System.Data.SqlClient.SqlParameter("@rightId", System.Data.SqlDbType.BigInt);
parameters[0].set_Value((int) right.Id);
parameters[1] = new System.Data.SqlClient.SqlParameter("@groupId", System.Data.SqlDbType.BigInt);
parameters[1].set_Value((int) group.Id);
DALSql.ExecuteNonQuery("insert sn.RightGroups (RightId, GroupId) values (@rightId, @groupId)", parameters);
}
public void AddRelation(Role right, User user)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[2];
parameters[0] = new System.Data.SqlClient.SqlParameter("@rightId", System.Data.SqlDbType.BigInt);
parameters[0].set_Value((int) right.Id);
parameters[1] = new System.Data.SqlClient.SqlParameter("@userId", System.Data.SqlDbType.BigInt);
parameters[1].set_Value((long) user.Id);
DALSql.ExecuteNonQuery("insert sn.UserRights ( UserId, RightId ) values ( @userId, @rightId )", parameters);
}
public void DeleteRelation(Role right, Role group)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[2];
parameters[0] = new System.Data.SqlClient.SqlParameter("@rightId", System.Data.SqlDbType.BigInt);
parameters[0].set_Value((int) right.Id);
parameters[1] = new System.Data.SqlClient.SqlParameter("@groupId", System.Data.SqlDbType.BigInt);
parameters[1].set_Value((int) group.Id);
DALSql.ExecuteNonQuery("delete sn.RightGroups where RightId = @rightId and GroupId = @groupId", parameters);
}
public void Delete(Role owid)
{
if (owid.TypeRight == RightTypesEnum.ГруппаПрав)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.BigInt) };
parameters[0].set_Value((long) owid.Id);
DALSql.ExecuteNonQuery("delete sn.RightGroups where RightId = @id or GroupId = @id\r\n delete sn.UserRights where RightId = @id\r\n delete sn.Rights where id = @id", parameters);
}
}
public void DeleteRelation(Role right, User user)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[2];
parameters[0] = new System.Data.SqlClient.SqlParameter("@rightId", System.Data.SqlDbType.BigInt);
parameters[0].set_Value((int) right.Id);
parameters[1] = new System.Data.SqlClient.SqlParameter("@userId", System.Data.SqlDbType.BigInt);
parameters[1].set_Value((long) user.Id);
DALSql.ExecuteNonQuery("delete sn.UserRights where UserId = @userId and RightId = @rightId", parameters);
}
public void Load(System.Data.DataRow row, Role owid)
{
owid.Id = row.IsNull("id") ? ((int) 0) : ((int) ((int) row.get_Item("id")));
owid.Right = row.IsNull("name") ? string.Empty : ((string) ((string) row.get_Item("name")));
owid.TypeRight = row.IsNull("name") ? RightTypesEnum.Пусто : ((RightTypesEnum) ((int) row.get_Item("TypeId")));
}
public Role FindRoleByName(string name)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar) };
parameters[0].set_Value(name);
new BindingListView<Role>();
Role owid = new Role();
this.Load(DALSql.ExecuteDataTable(this.SelectQuery + " where [name] = @name", parameters).Rows.get_Item(0), owid);
return owid;
}
public void AddRelation(Role child)
{
Mappers.RoleMapper.AddRelation(child, this);
}
public BindingListView<Role> FindIncludeRolesBy(Role role)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.BigInt) };
parameters[0].set_Value((int) role.Id);
string sql = this.SelectQuery + " inner join sn.RightGroups rg on rg.RightId = t.id\r\n where\trg.GroupId = @id";
BindingListView<Role> view = new BindingListView<Role>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
Role owid = new Role();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public BindingListView<Role> FindIncludeRolesBy(User user)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar) };
parameters[0].set_Value(user.Login);
string sql = this.RoleSelectQuery + " \t\tinner join sys.database_role_members rm on t.id = rm.role_principal_id\r\n\t\t inner join sys.database_principals pr on rm.member_principal_id = pr.principal_id\r\n where pr.type='S' and t.typeRole in ('РОЛЬ') and pr.[name] = @name";
BindingListView<Role> view = new BindingListView<Role>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
Role owid = new Role();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public BindingListView<Role> FindIncludeRightsBy(User user)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@userId", System.Data.SqlDbType.BigInt) };
parameters[0].set_Value((long) user.Id);
string sql = this.SelectQuery + "\tinner join sn.UserRights ur on ur.RightId = t.id\r\n\tinner join sn.Users u on ur.UserId = u.id\r\nwhere u.id = @userId and t.TypeId not in (select id from sn.RightTypes where [name] like 'Роль%')";
BindingListView<Role> view = new BindingListView<Role>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
Role owid = new Role();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public BindingListView<Role> FindExcludeRolesBy(Role role)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.BigInt) };
parameters[0].set_Value((int) role.Id);
string sql = this.SelectQuery + " where t.id <> @id\r\n and t.TypeId in (1, 2)\r\n and t.id not in\t(select rg.RightId\r\n\t\t\t\t\t from sn.RightGroups rg\r\n where rg.GroupId = @id)";
BindingListView<Role> view = new BindingListView<Role>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
Role owid = new Role();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public BindingListView<Role> FindExcludeRightsBy(User user)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@userId", System.Data.SqlDbType.BigInt) };
parameters[0].set_Value((long) user.Id);
string sql = "select r.id, r.name, r.TypeId\r\n\tinto #t\r\n\tfrom sn.Rights r\r\n\t\tinner join sn.UserRights ur on ur.RightId = r.id\r\n\t\tinner join sn.Users u on ur.UserId = u.id\r\n\twhere u.id = @userId\r\n\tunion \r\n\tselect rr.id, rr.name, rr.TypeId\r\n\t\tfrom sn.UserRights ur\r\n\t\tinner join sn.Users u on ur.UserId = u.id\r\n\t\tinner join sn.Rights r on ur.RightId = r.id\r\n\t\tinner join sn.RightGroups rg on r.id = rg.GroupId\r\n\t\tinner join sn.Rights rr on rg.RightId = rr.id\r\n\twhere r.TypeId = 2 and u.id = @userId\r\n\tunion \r\n\tselect rrr.id, rrr.name, rrr.TypeId\r\n\tfrom sn.UserRights ur\r\n\t\tinner join sn.Users u on ur.UserId = u.id\r\n\t\tinner join sn.Rights r on ur.RightId = r.id\r\n\t\tinner join sn.RightGroups rg on r.id = rg.GroupId\r\n\t\tinner join sn.Rights rr on rg.RightId = rr.id\r\n\t\tinner join sn.RightGroups rrg on rr.id = rrg.GroupId\r\n\t\tinner join sn.Rights rrr on rrg.RightId = rrr.id\r\n\twhere r.TypeId = 2 and rr.TypeId = 2 and u.id = @userId\r\n" + this.SelectQuery + "\r\n\twhere t.id not in (select tt.id from #t tt) and t.TypeId not in (select id from sn.RightTypes where [name] like 'Роль%')";
BindingListView<Role> view = new BindingListView<Role>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
Role owid = new Role();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
private void toolStripButton1_Click(object sender, System.EventArgs e)
{
Role role = new Role();
if (new AdminRoleAddForm(role).ShowDialog() == System.Windows.Forms.DialogResult.Yes)
{
this.UpdateView();
this.bsRoles.set_Position(this.bsRoles.Find("Right", role.Right));
}
}
public BindingListView<Role> GetRoles(RightTypesEnum rType)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@type", System.Data.SqlDbType.BigInt) };
parameters[0].set_Value((long) rType);
BindingListView<Role> view = new BindingListView<Role>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(this.SelectQuery + " where TypeId = @type", parameters).Rows)
{
Role owid = new Role();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public AdminRoleAddForm(Role role)
: this()
{
this.m_Role = role;
}
public void Insert(Role owid)
{
if (owid.TypeRight == RightTypesEnum.ГруппаПрав)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar) };
parameters[0].set_Value(owid.Right);
DALSql.ExecuteNonQuery("exec [sn].[InsertRightGroup] @name", parameters);
}
}
public void DeleteRelation(Role child)
{
Mappers.RoleMapper.DeleteRelation(child, this);
}
public AdminRoleRelationsForm(Role role)
: this()
{
this.m_Role = role;
}
