CODEWITHSUNDEEP
c#sqlsqldatareader
Nibirue
Nibirue

Reputation: 411

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

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?

Upvotes: 0

Views: 4632

Answers (2)

sgeddes
sgeddes

Reputation: 62841

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

Upvotes: 4

Ilia G
Ilia G

Reputation: 10211

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.

Upvotes: 5

Related Questions