How to use SqlDataReader to retrieve information from database, c#

Question

I need to access a variable in my SQL database, along with a username which is already implemented properly. I query the database using this statement:

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";

Then connect to the database and access the username:

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();
 }
}

In the if statement where I set reader["username"] equal to Username, I output Username using debug and the value is correct. What i have in comments relating to CompanyId is what I want to do, but was unable. Doing so doesn't cause errors, but it does ignore the entire statement (even the Username variable which works otherwise). based on my query string, how can I access the variable companyId?

• dash: Your sql query doesn't actually select the column value companyId in the Select list. Also note you don't need the try catch block as using will close the connection as soon as the code leaves the scope of the block, regardless of success or failure.
• Nibirue: Thanks for the try catch info. Also it seems the compayId advice might solve my problem.

Answer 1

In your _getUserByUsernameQuery you are only selecting the username field. Make sure that fields that you want to read from reader[...] are present in your select statement.

• Nibirue: This solved my problem; accepting yours as it was the first response.

Answer 2

Looks like you need to add company id to your select statement to be able to retrieve it:

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";