private static Role PrepareRules(string rolename)
{
SqlConnection sqlConnection = DBUtil.CreateConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Connection.Open();
cmd.CommandText = @"
SELECT PermissionId, GrantId FROM PermissionRules
WHERE RoleName = @RoleName
ORDER BY PermissionId, GrantId
";
//USE PARAMETERS FOR SECURITY
cmd.Parameters.Add(new SqlParameter("@RoleName", rolename.Trim()));
SqlDataReader reader = cmd.ExecuteReader();
Role role = new Role(rolename);
while (reader.Read())
{
int permissionId = reader.GetInt32(0);
int grantId = reader.GetInt32(1);
role.Rules[permissionId].Grants[grantId] = true;
}
cmd.Connection.Close();
return role;
}
public static bool EditRole(Role role)
{
bool transactionStatus = false;
SqlConnection sqlConnection = DBUtil.CreateConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Connection.Open();
cmd.Transaction = sqlConnection.BeginTransaction("EditRole");
try
{
cmd.CommandText = RemoveRulesSqlScript(role.Name);
cmd.CommandText += CreateRoleSqlScript(role);
cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
transactionStatus = true;
Refresh();
}
catch (Exception)
{
cmd.Transaction.Rollback();
}
return transactionStatus;
}
private static string CreateRoleSqlScript(Role role)
{
string commandText = string.Empty;
const string InsertQueryTemplate = @"
INSERT INTO PermissionRules (RoleName, PermissionId, GrantId)
VALUES ('{0}', {1}, {2});
";
foreach (KeyValuePair<int, Rule> rule in role.Rules)
{
foreach (KeyValuePair<int, Grant> grant in Grant.Grants)
{
if (rule.Value.HasGrants[grant.Value.Id] && rule.Value.Grants[grant.Value.Id])
{
commandText += string.Format(InsertQueryTemplate,
role.Name,
rule.Value.PermissionId,
grant.Value.Id
);
}
}
}
return commandText;
}
public static bool CreateRole(Role role)
{
bool transactionStatus = false;
SqlConnection sqlConnection = DBUtil.CreateConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.Connection.Open();
cmd.Transaction = sqlConnection.BeginTransaction("CreateRole");
try
{
System.Web.Security.Roles.CreateRole(role.Name);
cmd.CommandText = CreateRoleSqlScript(role);
cmd.ExecuteNonQuery();
cmd.Transaction.Commit();
transactionStatus = true;
Refresh();
}
catch
{
System.Web.Security.Roles.DeleteRole(role.Name);
cmd.Transaction.Rollback();
}
finally
{
sqlConnection.Close();
}
return transactionStatus;
}