using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public abstract class Role
{
    public abstract void AddRole(string RoleName);
    public abstract void DeleteRole(string RoleID);
    public abstract void EditRole(string RoleID, string RoleName);
    public abstract void AddMemberToRole(string MemberID, string RoleID);
    public abstract void RemoveMember(string MemberID);
    public abstract void ChangeRole(string MemberID, string RoleID);
    public abstract bool IsInRole(string MemberID, string RoleID);
    public abstract DataTable GetRoles();
    public abstract DataTable GetRoleByID(string RoleID);
}

public class MyRoleManager : Role
{
    public override void AddRole(string RoleName)
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("INSERT INTO Role VALUES (NEWID, @roleName, GETDATE())", conn);
        
        cmd.Parameters.Add("@roleName", SqlDbType.NVarChar, 50).Value = RoleName;
        
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        conn.Dispose();
        cmd.Dispose();
    }

    public override void DeleteRole(string RoleID)
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("DELETE FROM Role WHERE RoleID = @roleID", conn);

        cmd.Parameters.Add("@roleID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(RoleID);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        conn.Dispose();
        cmd.Dispose();
    }

    public override void EditRole(string RoleID, string RoleName)
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("UPDATE Role SET RoleName = @roleName WHERE RoleID = @roleID", conn);

        cmd.Parameters.Add("@roleID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(RoleID);
        cmd.Parameters.Add("@roleName", SqlDbType.NVarChar, 50).Value = RoleName;

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        conn.Dispose();
        cmd.Dispose();
    }

    public override void AddMemberToRole(string MemberID, string RoleID)
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("INSERT INTO RoleMembers VALUES (@memberID, @roleID)", conn);

        cmd.Parameters.Add("@memberID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(MemberID);
        cmd.Parameters.Add("@roleID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(RoleID);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        conn.Dispose();
        cmd.Dispose();
    }

    public override void RemoveMember(string MemberID)
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("DELETE FROM RoleMembers WHERE MemberID = @memberID", conn);

        cmd.Parameters.Add("@memberID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(MemberID);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        conn.Dispose();
        cmd.Dispose();
    }

    public override bool IsInRole(string MemberID, string RoleID)
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("SELECT MemberID FROM RoleMembers WHERE RoleID = @roleID", conn);
        bool isInRole = false;

        cmd.Parameters.Add("@roleID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(RoleID);

        conn.Open();

        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        while (reader.Read())
        {
            if (reader.GetValue(0).ToString().ToUpper() == MemberID.ToUpper())
            {
                isInRole = true;
                break;
            }
        }

        reader = null;
        conn.Dispose();
        cmd.Dispose();

        return isInRole;
    }

    public override void ChangeRole(string MemberID, string RoleID)
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("UPDATE Role SET RoleID = @roleID WHERE MemberID = @memberID", conn);

        cmd.Parameters.Add("@memberID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(MemberID);
        cmd.Parameters.Add("@roleID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(RoleID);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();

        conn.Dispose();
        cmd.Dispose();
    }

    public override DataTable GetRoleByID(string RoleID)
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("SELECT * FROM Role WHERE RoleID = @roleID", conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataTable table = null;

        cmd.Parameters.Add("@roleID", SqlDbType.UniqueIdentifier, 16).Value = new Guid(RoleID);

        adapter.Fill(table);

        adapter = null;
        conn.Dispose();
        cmd.Dispose();

        return table;
    }

    public override DataTable GetRoles()
    {
        SqlConnection conn = new SqlConnection("initial catalog=TestDB; Integrated Security=SSPI");
        SqlCommand cmd = new SqlCommand("SELECT * FROM Role", conn);
        SqlDataAdapter adapter = new SqlDataAdapter(cmd);
        DataTable table = null;

        adapter.Fill(table);

        adapter = null;
        conn.Dispose();
        cmd.Dispose();

        return table;
    }
}