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?

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. — dash
– dash, Commented Jan 2, 2013 at 16:41
Thanks for the try catch info. Also it seems the compayId advice might solve my problem. — Nibirue
– Nibirue, Commented Jan 2, 2013 at 16:45

Ilia G · Accepted Answer · 2013-01-02 16:41:30Z

5

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.

answered Jan 2, 2013 at 16:41

Ilia G

10.2k3 gold badges43 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nibirue Over a year ago

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

sgeddes · Accepted Answer · 2013-01-02 16:42:30Z

4

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

answered Jan 2, 2013 at 16:42

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related