use the Identity as filtermechanism in my Controller

Question

I have a Controller that grabs all the items from a Mysql database and puts them into my models. This method is called in my Index() so it can display all the items in a gridview.

I want to be able to sign in on the webpage with a 'company number' that is used in the Login.cshtml. The company number also needs to be used in my Contoller ( I think ). It will be used in the SQL Query so the company only sees their own items. but how can I make it so the Controller is able to reach the information?

public List<ContainerInfo> TablesColumnDisplay()
{
  //string on = the connectionstring
  using SqlCommand SqlComm = new SqlCommand($"SELECT * FROM TheItemList"); //I think this needs WHERE company_number = '{Identity.Companynumber}'
  using (SqlDataAdapter sda = new SqlDataAdapter())
    {
      {
        SqlComm.Connection = SqlConn;
        SqlConn.Open();
        sda.SelectCommand = SqlComm;

        SqlDataReader sdr = SqlComm.ExecuteReader();
        while (sdr.Read())
        {
          ItemList item = new ItemList();
          //The entire sdr reader that puts all the items into the models
        }
}

Or am I horribly wrong and do I have to make a deperate method for it? I'm a bit lost on how to attack this problem.

Edit I am using the standard scaffolded Identity Area where i've changed some model items. in my Login.cshtml is a standard form which is asking for company_number, password and if a remember me is true or not.

public async Task<IActionResult> OnPostAsync(string returnUrl = null)
        {
            returnUrl = returnUrl ?? Url.Content("~/");

            if (ModelState.IsValid)
            {
                // This doesn't count login failures towards account lockout
                // To enable password failures to trigger account lockout, set lockoutOnFailure: true
                var result = await _signInManager.PasswordSignInAsync(Input.company_number.ToString(), Input.Password, Input.RememberMe, lockoutOnFailure: false);
                if (result.Succeeded)
                {
                    _logger.LogInformation("User logged in.");
                    return LocalRedirect(returnUrl);
                }
                if (result.RequiresTwoFactor)
                {
                    return RedirectToPage("./LoginWith2fa", new { ReturnUrl = returnUrl, RememberMe = Input.RememberMe });
                }
                if (result.IsLockedOut)
                {
                    _logger.LogWarning("User account locked out.");
                    return RedirectToPage("./Lockout");
                }
                else
                {
                    ModelState.AddModelError(string.Empty, "Invalid login attempt.");
                    return Page();
                }
            }

            // If we got this far, something failed, redisplay form
            return Page();
        }

Is this an action method, why not pass the company number as a query parameter? — Karney., Mar 11 '21 at 09:26
The DropdownColumns is a List. it generates all information from an SQL query and I pass this through the Index() method via ViewData. ```public IActionResult Index() { ViewData["DropDown1"] = DropdownMenu(); var containers = _context.B0.OrderByDescending(x => x.Rowid); return View(containers); }``` I am currently using the standard scaffolded Razorpage Login.cshtml and login.cshtml.cs where I changed Emailed to 'company_number — Brian van den Berg, Mar 11 '21 at 11:52
I think such a long question can sum up in this a few words "*How to get_company number of current logged in user?*" — Ergis, Mar 11 '21 at 11:57
How to get the company_number from a logged in user to my Controller and use it in my TablesColumnDisplay() method. that is is what should probably happen. — Brian van den Berg, Mar 11 '21 at 12:04

score 0 · Accepted Answer · answered Mar 12 '21 at 13:28

I fixed the issue by looking through stackoveflow.

Note that I have to use 'System.Security.Claims' in order to access the Logged user. You need a variable which contains the currently logged in user. this can be found with User.FindFirst(ClaimTypes.Name).Value; Reference here

You can then use that variable in your SQL query by using the $ sign at the start. allowing you to use variables inside of the query.

using System.Security.Claims;

//This gives me the value of the first ClaimTypes.Name(which in my case: is 123456)
int Username = Convert.ToInt32(User.FindFirst(ClaimTypes.Name).Value);
public List<ContainerInfo> TablesColumnDisplay()
{
  //string conn = the connectionstring
  using SqlCommand SqlComm = new SqlCommand($"SELECT * FROM TheItemList WHERE company_number = {Username}"); 
  using (SqlDataAdapter sda = new SqlDataAdapter())
    {
      {
        SqlComm.Connection = SqlConn;
        SqlConn.Open();
        sda.SelectCommand = SqlComm;

        SqlDataReader sdr = SqlComm.ExecuteReader();
        while (sdr.Read())
        {
          ItemList item = new ItemList();
          //The entire sdr reader that puts all the items into the models
        }
}

use the Identity as filtermechanism in my Controller

1 Answers1