Edit and Delete sql data in ASP.NET

Question

I am facing a bit of problem editing and deleting rows from SQL in ASP.NET MVC. The main problem I am facing is the fetch the QueryString value from url in my controller.

I can't attach Request.QueryString["Username"] for some reason. It is giving me Can not apply indexing with [] to an expression of type QueryString error.

Is there something I am doing it wrong or is there any way I can pass the QueryParameter through my controller to my Data access layer, where it will execute the desired query.

I am having the same problem with both Edit and Delete

This is my code looks like so far:

ShowUsers.cshtml (Edit and Delete actions are hooked up here)

@model IEnumerable<SecureMedi.Models.Users>

<section id="users">
    <table id="users-table" class="table table-bordered table-responsive">
        <thead>
            <tr>
                <th class="sort">Username</th>
                <th class="sort">Role</th>
                <th class="sort"><i class="md-icon dp18">mode_edit</i></th>
                <th class="sort"><i class="md-icon dp18">delete</i></th>
            </tr>
        </thead>
        <tbody class="list">
            @foreach (var item in Model) {
            <tr>
                <td class="username">@item.Username</td>
                <td class="role">@item.Role</td>
                <td class="actions">@Html.ActionLink("Edit", "EditUser", "Home", new { Username = @item.Username }, null)</td>
                <td class="actions">@Html.ActionLink("Delete", "DeleteUser", "Home", new { Username = @item.Username }, null)</td>
            </tr>
            }
        </tbody>
    </table>
</section>

EditUser.cshtml (For Editing) - Note: Here I would also like to display the Username value by default from the QueryString (Username=somename) in the form when the page loads. When going to EditUser action from ShowUsers page the url looks something like /Home/EditUser?Username=somename.

@model SecureMedi.Models.Users


<section id="edit-user">
    <h4 class="sub-title space-top space-bottom">Edit user</h4>
    <form id="edit-user-form" asp-controller="Home" asp-action="EditUser" method="post">
        <div class="form-group">
            <label asp-for="Username" class="form-control-label">Username</label>
            <input asp-for="Username" type="text" class="form-control" id="username" name="Username" required disabled value="" />
        </div><!-- / form-group -->
        <div class="form-group">
            <label asp-for="Role">Role</label>
            <select asp-for="Role" class="form-control selector" id="role" name="Role" required>
                <option value="" selected disabled>Select role</option>
                <option value="SecureMediUsers">SecureMediUsers</option>
                <option value="SecureMediModerators">SecureMediModerators</option>
                <option value="SecureMediAdministrators">SecureMediAdministrators</option>
            </select>
        </div><!-- / form-group -->
        <button type="submit" class="btn btn-primary">Save</button>
    </form><!-- / form -->
</section>

HomeController.cs

[HttpGet]
public IActionResult EditUser() {
    var model = new Users();
    return View(model);
}

[HttpPost]
public IActionResult EditUser(Users u) {
    if (!ModelState.IsValid) {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();
    ud.Edit(u);
    return RedirectToAction("ShowUsers");
}

[HttpPost]
public IActionResult DeleteUser(Users u) {
    if (!ModelState.IsValid) {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();
    ud.Delete(u);
    return RedirectToAction("ShowUsers");
}

UsersDAL.cs (Data access layer)

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using SecureMedi.Models;

namespace SecureMedi.DAL {
    public class UsersDAL {
        public void Edit(Users u) {
            string connectionstring = "MY_CONNECTION_STRING";
            string sql = String.Format("ALTER ROLE {0} DROP MEMBER {1}", u.Role, u.Username);
            string sql2 = String.Format("ALTER ROLE {0} ADD MEMBER {1}", u.Role, u.Username);
            SqlConnection conn = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlCommand cmd2 = new SqlCommand(sql2, conn);

            try {
                conn.Open();
                using(conn) {
                    cmd.Transaction = conn.BeginTransaction();
                    cmd.ExecuteNonQuery();
                    cmd2.Transaction = cmd.Transaction;
                    cmd2.ExecuteNonQuery();
                    cmd2.Transaction.Commit();
                }
            } finally {
                if (conn != null) {
                    conn.Close();
                }
            }

        }

        public void Delete(Users u) {
            string connectionstring = "MY_CONNECTION_STRING";
            string sql = String.Format("DROP USER {0}", u.Username);
            SqlConnection conn = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand(sql, conn);

            try {
                conn.Open();
                using(conn) {
                    cmd.ExecuteNonQuery();
                }
            } finally {
                if (conn != null) {
                    conn.Close();
                }
            }
        }
    }
}

Users.cs (Model)

using System.ComponentModel.DataAnnotations;

namespace SecureMedi.Models {
    public class Users {
        [Required]
        [StringLength(100)]
        public string Username {
            get;
            set;
        }

        [Required]
        [StringLength(100)]
        public string Role {
            get;
            set;
        }
    }
}

In summary, the problems I am trying to solve are:

1) Passing the Username query param from EditUser view and Role value from the form to my EditUser controller function.

2) I have hooked up the delete action like

<td class="actions">@Html.ActionLink("Delete", "DeleteUser", "Home", new { Username = @item.Username }, null)</td>

But I'm confused on to how to avoid the [HttpGet] for DeleteUser action. When I click on the anchor tag it is taking me to /Home/DeleteUser?Username=somename, but instead I would just like to execute the DeleteUser action without the view change.

Also, how do I pass Username query param to the controller action.

Answer 1

You Just Want to Delete the recor form database on just click on the delete link without confirmation user should be navigated to the same page .? these changes will do this trick for you ! tried

Update your Home Controller Like : i just remove the [httppost] from delete actionResult and change the parameter name and also write the controller name also in your return redirecttoaction

IMP NOTE : Update The Paramete Users To Username where ever u use it like in DAL Also Suggestion is to use the same one everywhere either it would be null

HomeController.cs

[HttpGet]
public IActionResult EditUser() {
    var model = new Users();
    return View(model);
}

[HttpPost]
public IActionResult EditUser(Users u) {
    if (!ModelState.IsValid) {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();
    ud.Edit(u);
    return RedirectToAction("ShowUsers", "ControllerName");
}

public IActionResult DeleteUser(Username u) {
    if (!ModelState.IsValid) {
        return View(u);
    }
    UsersDAL ud = new UsersDAL();
    ud.Delete(u);
    return RedirectToAction("ShowUsers", "ControllerName");
}

Answer 2

1) Change [HttpGet] EditUser to take UserName parameter: and get user by UserName to send this data to model:

[HttpGet]
    public IActionResult EditUser(string UserName) {
        var model = new Users(UserName);//probably, Or something like GetUser(UserName)
        return View(model);
    }

2) You can't call HTTPPost method by using actionLink. It will always use HttpGet. So there is two approaches that you can use - 1. Delete user in HttpGet and than redirect to previous page 2. Use JavaScript to call HttpPost, instead of send HttpGet by action link clicking.