如何使用SqlDataReader从数据库中检索信息，c#

Question

我需要访问我的SQL数据库中的变量，以及已经正确实现的用户名。我使用以下语句查询数据库:

private const string _getUserByUsernameQuery = @"
SELECT
    [User].[username]
FROM
    [User] WITH (NOLOCK) 
    INNER JOIN [Company] WITH (NOLOCK)
    ON [User].[companyId] = [Company].[id]
WHERE
    [User].[username] = @username 
    AND [User].[password] = @password";

然后连接数据库并访问用户名:

using (SqlConnection connection = new SqlConnection(SQLConfiguration.ConnectionString))
{
   SqlCommand command = new SqlCommand(_getUserByUsernameQuery, connection);
   command.Parameters.AddWithValue("@username", username);
   command.Parameters.AddWithValue("@password", password);
   try
   {
      connection.Open();
      using (SqlDataReader reader = command.ExecuteReader())
      {
         if (reader.Read())
         {
            Username = Convert.ToString(reader["username"]);
            //CompanyId = Convert.ToString(reader["companyId"]);
            lblUsername = Username;
            //lblCompanyId = CompanyId;
            Debug.WriteLine("Testing2::");
            Debug.WriteLine(lblUsername);
            //Debug.WriteLine(lblCompanyId);
         }
      }
   }
   catch (Exception)
   {
      if(connection.State == System.Data.ConnectionState.Open)
      connection.Close();
   }
}

在if语句中，我设置reader["username"]等于username，我使用debug输出username并且值是正确的。我在有关CompanyId的评论中所写的是我想做的，但无法做到。这样做不会导致错误，但它会忽略整个语句(甚至忽略Username变量)。根据我的查询字符串，我如何访问变量companyId?

Answer 1

在您的_getUserByUsernameQuery中，您只选择username字段。请确保要从reader[...]中读取的字段存在于select语句中。

Answer 2

看起来您需要在select语句中添加公司id以便能够检索它:

private const string _getUserByUsernameQuery = @"
SELECT
    [User].[username], [User].[companyId]
FROM
    [User] WITH (NOLOCK) 
    INNER JOIN [Company] WITH (NOLOCK)
    ON [User].[companyId] = [Company].[id]
WHERE
    [User].[username] = @username 
    AND [User].[password] = @password";