c# - Passing ADFS authenticated user's UPN to SQL string for connection

Question

I'm trying to pull information from the MS SQL database based on the UPN of the logged in user. I guess it would seem somewhat trivial for some.

In controller I get user's UPN:

var identity = (ClaimsIdentity)User.Identity;
loggedInUser.UserHandle = identity.Claims.Where(c => c.Type == ClaimTypes.Upn).First().Value;
//get userHandle from UPN
int index = loggedInUser.UserHandle.LastIndexOf("@");
        if (index > 0)
            loggedInUser.UserHandle = loggedInUser.UserHandle.Substring(0, index);

Below this I try to get the data:

String sql = "Select * from [MyDatabase].[dbo].[TableName] where UserHandle = @userHandle";
using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add(new SqlParameter("userHandle", loggedInUser.UserHandle));

            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                user.FirstName = reader["FirstName"].ToString();
                user.LastName = reader["LastName"].ToString();
            }
            conn.Close();
        }
 return View(loggedInUser);

I can see the correct UserHandle in View using @Model.UserHandle. But it returns null in Controller so I can't get the firstName and LastName from the table.

What am I doing wrong? How can I get the value of UPN to be used in my sql string so I can get data from the table?

标签： c#sql-servermodel-view-controller

Answer 1

将其留在这里以防万一。

我需要传递“用户”而不是“登录用户”：

return View(user);