public void AddRelation(OldRole roleChild, User user)
{
if (roleChild.TypeRole == "Роль приложения")
{
DALSql.ExecuteNonQuery(string.Concat((string[]) new string[] { "insert sn.UserRights ( UserId, RightId ) values ( ", ((long) user.Id).ToString(), ", ", ((long) roleChild.Id).ToString(), ")" }), null);
}
else
{
DALSql.ExecuteNonQuery(string.Concat((string[]) new string[] { "exec sys.sp_addrolemember [", roleChild.Name, "],[", user.Login, "]" }), null);
}
}
public void DeleteRelation(OldRole roleChild, User user)
{
if (roleChild.TypeRole == "Роль приложения")
{
DALSql.ExecuteNonQuery("delete sn.UserRights where RightId = " + ((long) roleChild.Id).ToString() + " and UserId = " + ((long) user.Id).ToString(), null);
}
else
{
DALSql.ExecuteNonQuery(string.Concat((string[]) new string[] { "exec sys.sp_droprolemember [", roleChild.Name, "],[", user.Login, "]" }), null);
}
}
public void DeleteRelation(OldRole roleChild, OldRole roleParent)
{
DALSql.ExecuteNonQuery(string.Concat((string[]) new string[] { "exec sys.sp_droprolemember [", roleChild.Name, "],[", roleParent.Name, "]" }), null);
}
public void Delete(OldRole owid)
{
DALSql.ExecuteNonQuery("drop role [" + owid.Name + "]", null);
}
public void Load(System.Data.DataRow row, OldRole owid)
{
owid.Id = (long) ((long) row.get_Item("id"));
owid.Name = (string) ((string) row.get_Item("name"));
owid.PrintName = (string) ((string) row.get_Item("printName"));
owid.TypeRole = (string) ((string) row.get_Item("typeRole"));
}
public BindingListView<OldRole> GetRoles(string typeRole)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[] { new System.Data.SqlClient.SqlParameter("@typeRole", System.Data.SqlDbType.VarChar) };
parameters[0].set_Value(typeRole);
BindingListView<OldRole> view = new BindingListView<OldRole>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(this.SelectQuery + " where typeRole = @typeRole", parameters).Rows)
{
OldRole owid = new OldRole();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public void Insert(OldRole owid)
{
DALSql.ExecuteDataTable("create role [" + owid.Name + "]", null);
}
public BindingListView<OldRole> 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.SelectQuery + " \r\n inner 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\r\n union\r\n " + this.SelectQueryAR + "\r\n inner join sn.UserRights ur on tar.id = ur.RightId\r\n inner join sn.Users u on ur.UserId = u.id and u.login = @name\r\n\t where tar.TypeId = 4";
BindingListView<OldRole> view = new BindingListView<OldRole>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
OldRole owid = new OldRole();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public OldRole 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<OldRole>();
OldRole owid = new OldRole();
this.Load(DALSql.ExecuteDataTable(this.SelectQuery + " where /*typeRole in ('РОЛЬ','ПРАВО') and*/ [name] = @name", parameters).Rows.get_Item(0), owid);
return owid;
}
public BindingListView<OldRole> FindIncludeRoleRightsBy(User user, OldRole r)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[2];
parameters[0] = new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar);
parameters[0].set_Value(user.Login);
parameters[1] = new System.Data.SqlClient.SqlParameter("@role", System.Data.SqlDbType.VarChar);
parameters[1].set_Value((long) r.Id);
string sql = this.SelectQuery + " inner join sys.database_role_members rm on t.id = rm.role_principal_id\r\n inner join sn.RolesView rv on rv.id = rm.member_principal_id\r\n inner join sys.database_role_members rm2 on rv.id = rm2.role_principal_id /*and rv.typeRole in ('РОЛЬ')*/ and rv.id = @role\r\n inner join sys.database_principals pr on rm2.member_principal_id = pr.principal_id and pr.type = 'S' and pr.name = @name\r\n ";
BindingListView<OldRole> view = new BindingListView<OldRole>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
OldRole owid = new OldRole();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public BindingListView<OldRole> FindIncludeRolesBy(OldRole 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((long) role.Id);
string sql = this.SelectQuery + " inner join sys.database_role_members rm on t.id = rm.role_principal_id \r\n where\trm.member_principal_id = @id";
BindingListView<OldRole> view = new BindingListView<OldRole>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
OldRole owid = new OldRole();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public BindingListView<OldRole> FindExcludeRolesBy(OldRole 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((long) role.Id);
string sql = this.SelectQuery + " where\t/*t.typeRole in ('РОЛЬ','ПРАВО')\r\n\t\t and*/ t.id <> @id\r\n and not t.id in\t(select role_principal_id\r\n\t\t\t\t\t from\tsys.database_role_members \r\n\t\t\t\t\t where\tmember_principal_id = @id)";
BindingListView<OldRole> view = new BindingListView<OldRole>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
OldRole owid = new OldRole();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public BindingListView<OldRole> FindExcludeRightsCurrentUser()
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[0];
string sql = this.SelectField + " from sn.GetExcludeUserRoles t ";
BindingListView<OldRole> view = new BindingListView<OldRole>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
OldRole owid = new OldRole();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public BindingListView<OldRole> FindExcludeRightsBy(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.SelectQuery + " where\t/*t.typeRole in ('РОЛЬ','ПРАВО') \r\n and*/ not t.id in (\tselect\tt.id\r\n\t from\tsn.RolesView t\r\n inner 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 inner join sys.database_role_members rm2 on pr.principal_id = rm2.role_principal_id\r\n\t\t inner join sys.database_principals pr2 on rm2.member_principal_id = pr2.principal_id\r\n inner join sys.database_role_members rm3 on pr2.principal_id = rm3.role_principal_id\r\n\t\t inner join sys.database_principals pr3 on rm3.member_principal_id = pr3.principal_id\r\n inner join sys.database_role_members rm4 on pr3.principal_id = rm4.role_principal_id\r\n\t \t inner join sys.database_principals pr4 on rm4.member_principal_id = pr4.principal_id\r\n where pr4.type='S' \r\n /*and t.typeRole in ('РОЛЬ','ПРАВО') */\r\n and pr4.[name] = @name \r\n union\r\n select\tt.id\r\n\t from\tsn.RolesView t\r\n inner 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 inner join sys.database_role_members rm2 on pr.principal_id = rm2.role_principal_id\r\n\t\t inner join sys.database_principals pr2 on rm2.member_principal_id = pr2.principal_id\r\n inner join sys.database_role_members rm3 on pr2.principal_id = rm3.role_principal_id\r\n\t\t inner join sys.database_principals pr3 on rm3.member_principal_id = pr3.principal_id\r\n where pr3.type='S' \r\n /*and t.typeRole in ('РОЛЬ','ПРАВО') */\r\n and pr3.[name] = @name\r\n union \r\n select\tt.id\r\n\t from\tsn.RolesView t\r\n inner 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 inner join sys.database_role_members rm2 on pr.principal_id = rm2.role_principal_id\r\n\t\t inner join sys.database_principals pr2 on rm2.member_principal_id = pr2.principal_id\r\n where pr2.type='S' \r\n /*and t.typeRole in ('РОЛЬ','ПРАВО') */\r\n and pr2.[name] = @name\r\n union\r\n select\tt.id\r\n\t from\tsn.RolesView t\r\n inner 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' \r\n /*and t.typeRole in ('РОЛЬ','ПРАВО') */\r\n and pr.[name] = @name) ";
BindingListView<OldRole> view = new BindingListView<OldRole>();
foreach (System.Data.DataRow row in DALSql.ExecuteDataTable(sql, parameters).Rows)
{
OldRole owid = new OldRole();
this.Load(row, owid);
view.Add(owid);
}
return view;
}
public void DeleteRelation(OldRole child)
{
Mappers.OldRoleMapper.DeleteRelation(child, this);
}
public void AddRelation(OldRole child)
{
Mappers.OldRoleMapper.AddRelation(child, this);
}