Hi,
I have finally managed to assign multiples roles to single Users based on the database table design.
Transaction on Role Insert needed to be performed due to insert on two tables simultaneously.
Here is what i did:
<div class="input-group mb-3">
<asp:TextBox ID="txtUserId_update" runat="server" placeholder="User Email" class="form-control"
ReadOnly="true" Visible="true"></asp:TextBox>
</div>
<div class="input-group mb-3">
<asp:TextBox ID="txtRoleId_update" runat="server" placeholder="User Email" class="form-control"
ReadOnly="true" Visible="true"></asp:TextBox>
</div>
<div class="input-group mb-3">
<label class="text-info small">
User FirstName:</label>
<asp:TextBox ID="txtUserFirstName_update" runat="server" placeholder="" class="form-control"
ReadOnly="true"></asp:TextBox>
<div class="input-group-append">
<span class="fas fa-pencil-alt"></span>
</div>
</div>
<div class="input-group mb-3">
<label class="text-info small">
UserEmail:</label>
<asp:TextBox ID="txtUserEmail_update" runat="server" placeholder="" class="form-control"
ReadOnly="true"></asp:TextBox>
<div class="input-group-append">
<span class="fas fa-pencil-alt"></span>
</div>
</div>
<div class="input-group mb-3">
<label class="text-info small">
Current RoleName:</label>
<div class="input-group mb-3">
<asp:DropDownList ID="ddCurrentRoleName" runat="server" CssClass="form-control">
</asp:DropDownList>
</div>
</div>
<div class="input-group mb-3">
<label class="text-info small">
New Role:</label>
<div class="input-group mb-3">
<asp:DropDownList ID="ddRoleName" runat="server" CssClass="form-control">
</asp:DropDownList>
</div>
</div>
<asp:Button ID="btnUpdateExistingRoles" runat="server" OnClick="btnUpdateExistingRoles_Click"
CausesValidation="false" Text="Update Existing Role" CssClass="btn btn-info btn-sm" />
<asp:Button ID="btnAddNewRoles" runat="server" OnClick="btnAddNewRoles_Click" CausesValidation="false"
Text="Add New Role" CssClass="btn btn-warning btn-sm" />
<asp:Button ID="btnNoUpdate" runat="server" Text="Close" CssClass="btn btn-danger btn-sm" />
<asp:Button runat="server" ID="updateRowUser" Style="display: none" />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" class="table table-bordered table-striped"
Width="100%" ClientIDMode="Static" DataKeyNames="UserId" DataSourceID="SqlDataSource1">
<Columns>
<asp:TemplateField HeaderText="UserId" Visible="true">
<HeaderTemplate>
<asp:Label ID="LabelUserId" runat="server" Text="UserId" />
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="LblUserId" runat="server" Text='<%# Eval("UserId") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="RoleId" Visible="true">
<HeaderTemplate>
<asp:Label ID="LabelRoleId" runat="server" Text="RoleId" />
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="LblRoleId" runat="server" Text='<%# Eval("RoleId") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="UserFirstName">
<HeaderTemplate>
<asp:Label ID="LabelUserFirstName" runat="server" Text="FirstName" />
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="LblUserFirstName" runat="server" Text='<%# Eval("UserFirstName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:HyperLinkField HeaderText="UserEmail" DataTextField="UserEmail" DataNavigateUrlFormatString="mailto:{0}"
DataNavigateUrlFields="UserEmail" />
<asp:TemplateField HeaderText="RoleName">
<HeaderTemplate>
<asp:Label ID="LabelRoleName" runat="server" Text="RoleName" />
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="LblRoleName" runat="server" Text='<%# Eval("RoleName") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Update">
<ItemTemplate>
<asp:Button ID="updateRowUser" runat="server" OnClick="takeData" CssClass="btn btn-info btn-sm"
Text="Update" OnClientClick="ShowModalPopup()"></asp:Button>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Left" Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:TemplateField>
<asp:TemplateField HeaderText="Delete">
<ItemTemplate>
<asp:Button ID="btnDelete" runat="server" CssClass="btn btn-danger btn-sm" Text="Delete"
OnClientClick="return DeleteConfirm(this);" OnClick="btnDelete_Click"></asp:Button>
</ItemTemplate>
<HeaderStyle HorizontalAlign="Left" Wrap="False" />
<ItemStyle HorizontalAlign="Left" Wrap="False" />
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionQuiz %>"
SelectCommand="SELECT EXAMUser.UserId, EXAMUser.RoleId, EXAMUser.UserFirstName, EXAMUser.UserEmail, EXAMRoles.RoleName FROM EXAMUser INNER JOIN EXAMUserInRoles ON EXAMUser.UserId = EXAMUserInRoles.UserId INNER JOIN EXAMRoles ON (EXAMUserInRoles.RoleId = EXAMRoles.RoleId)">
<SelectParameters>
<asp:SessionParameter Name="UserEmail" SessionField="UserEmail" />
</SelectParameters>
</asp:SqlDataSource>
private String conUpdate = ConfigurationManager.ConnectionStrings["ConnectionQuiz"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddRoleName.Items.Clear();
ddRoleName.Items.Add(new System.Web.UI.WebControls.ListItem("AddNewRole", ""));
ddRoleName.AppendDataBoundItems = true;
DataTable rolename = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["ConnectionQuiz"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
SqlDataAdapter adapter = new SqlDataAdapter("SELECT RoleId, RoleName FROM EXAMRoles", con);
adapter.Fill(rolename);
ddRoleName.DataSource = rolename;
ddRoleName.DataTextField = "RoleName";
ddRoleName.DataValueField = "RoleId";
ddRoleName.DataBind();
}
}
if (!IsPostBack)
{
ddCurrentRoleName.Items.Clear();
ddCurrentRoleName.Items.Add(new System.Web.UI.WebControls.ListItem("SelectNewRole", "-1"));
ddCurrentRoleName.AppendDataBoundItems = true;
DataTable dennerolename = new DataTable();
string constr = ConfigurationManager.ConnectionStrings["ConnectionQuiz"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
SqlDataAdapter adapter = new SqlDataAdapter("SELECT RoleId, RoleName FROM EXAMRoles", con);
adapter.Fill(dennerolename);
ddCurrentRoleName.DataSource = dennerolename;
ddCurrentRoleName.DataTextField = "RoleName";
ddCurrentRoleName.DataValueField = "RoleId";
ddCurrentRoleName.DataBind();
}
}
}
protected void btnAddNewRoles_Click(object sender, EventArgs e)
{
SqlCommand cmd;
SqlConnection con;
con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionQuiz"].ConnectionString);
string query1 = "INSERT INTO EXAMUserInRoles(UserId, RoleId) VALUES(@UserId, @RoleId)";
if (!ddRoleName.SelectedItem.Text.Equals("AddNewRole"))
{
if (txtRoleId_update.Text != ddRoleName.DataValueField)
{
try
{
cmd = new SqlCommand(query1);
cmd.Connection = con;
cmd.Parameters.AddWithValue("@UserId", txtUserId_update.Text);
cmd.Parameters.AddWithValue("@RoleId", ddRoleName.SelectedValue);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
// Refresh the data
GridView1.EditIndex = -1;
GridView1.DataBind();
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "Swal.fire('New Role Added..', 'New Role added successfully', 'success');", true);
}
catch (Exception ex)
{
throw ex;
}
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "Swal.fire('The User already has this role..', 'Insert unsuccessfully', 'error');", true);
}
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "Swal.fire('AddNewRole is not a role..', 'Select a role', 'error');", true);
}
}
protected void btnUpdateExistingRoles_Click(object sender, EventArgs e)
{
SqlTransaction transaction;
SqlCommand mycomm;
SqlConnection myconn;
myconn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionQuiz"].ConnectionString);
myconn.Open();
transaction = myconn.BeginTransaction();
try
{
if (ddCurrentRoleName.SelectedValue != "-1")
{
mycomm = new SqlCommand("UPDATE EXAMUser SET RoleId = @RoleId WHERE UserId = @UserId", myconn, transaction);
mycomm.Parameters.AddWithValue("@UserId", txtUserId_update.Text);
mycomm.Parameters.AddWithValue("@RoleId", ddCurrentRoleName.SelectedValue);
mycomm.ExecuteNonQuery();
mycomm = new SqlCommand("UPDATE EXAMUserInRoles SET RoleId = @RoleId WHERE UserId = @UserId", myconn, transaction);
mycomm.Parameters.AddWithValue("@UserId", txtUserId_update.Text);
mycomm.Parameters.AddWithValue("@RoleId", ddCurrentRoleName.SelectedValue);
mycomm.ExecuteNonQuery();
transaction.Commit();
// Refresh the data
GridView1.EditIndex = -1;
GridView1.DataBind();
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "Swal.fire('Role Updated..', 'Role Updated Successfully', 'success');", true);
}
else
{
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "Swal.fire('Please select a valid role.', 'Role Updated failed', 'error');", true);
}
}
catch (SqlException sqlError)
{
transaction.Rollback();
}
myconn.Close();
}
protected void takeData(object sender, EventArgs e)
{
Button btnSend = sender as Button;
GridViewRow row = btnSend.NamingContainer as GridViewRow;
int rowId = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value.ToString());
string strUser = "SELECT EXAMUser.UserId, EXAMUser.RoleId, EXAMUser.UserFirstName, EXAMUser.UserEmail, EXAMRoles.RoleName " +
"FROM EXAMUser INNER JOIN EXAMUserInRoles ON EXAMUser.UserId = EXAMUserInRoles.UserId " +
"INNER JOIN EXAMRoles ON (EXAMUserInRoles.RoleId = EXAMRoles.RoleId) WHERE EXAMUser.UserId ='" + rowId + "'";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(strUser, conUpdate);
da.Fill(ds, "EXAMUser");
if (ds.Tables[0].Rows.Count > 0)
{
txtUserFirstName_update.Text = ds.Tables[0].Rows[0]["UserFirstName"].ToString();
txtUserEmail_update.Text = ds.Tables[0].Rows[0]["UserEmail"].ToString();
txtUserId_update.Text = ds.Tables[0].Rows[0]["UserId"].ToString();
txtRoleId_update.Text = ds.Tables[0].Rows[0]["RoleId"].ToString();
}
}
//delete event
protected void btnDelete_Click(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionQuiz"].ConnectionString;
SqlConnection con = new SqlConnection(connStr);
SqlCommand cmd;
Button btnSend = sender as Button;
GridViewRow row = btnSend.NamingContainer as GridViewRow;
int rowId = Convert.ToInt32(GridView1.DataKeys[row.RowIndex].Value.ToString());
string userName = GridView1.DataKeys[row.RowIndex].Value.ToString();
try
{
con.Open();
cmd = new SqlCommand("DELETE FROM EXAMUser WHERE UserId ='" + rowId + "'", con);
cmd.ExecuteNonQuery();
cmd.Dispose();
Membership.DeleteUser(userName);
GridView1.DataBind();
catch (SqlException sqlEx)
{
if (sqlEx.Number == 547)
{
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "youCannotDeleteRecord();", true);
//ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "Swal.fire('Delete failed..', 'There are student enrolled in it', 'success');", true);
}
}
finally
{
con.Close();
}
}
protected void GridView1_DeleteRecord(object sender, GridViewDeleteEventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionQuiz"].ConnectionString;
SqlConnection con = new SqlConnection(connStr);
SqlCommand cmd;
GridViewRow row = (GridViewRow)GridView1.Rows[e.RowIndex];
int rowID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Value.ToString());
try
{
con.Open();
cmd = new SqlCommand("DELETE FROM EXAMUser WHERE UserId ='" + rowID + "'", con);
cmd.ExecuteNonQuery();
con.Close();
cmd.Dispose();
GridView1.DataBind();
}
catch (SqlException)
{
ScriptManager.RegisterStartupScript(this, GetType(), "Popup", "youCannotDeleteRecord();", true);
}
string userName = GridView1.DataKeys[e.RowIndex].Value.ToString();
Membership.DeleteUser(userName);
GridView1.DataBind();
}