C# How do I store values from sql database to an object

Question

I have a login page whenever the authentication is correct or equal to 1 I want to pass that to the next window, but my problem is I cannot store the values into the class object that I made.

The idea is, if the username and password is correct then I want to store their values into an object so that I can pass it to the next window and reuse it. A tip about separating them would also be good. sorry I'm dumb, just started C# two weeks ago.

// logging-in process codes
private void LoggingIn()
{
    SqlConnection sqlCon = new SqlConnection(@"Data Source=localhost\SQLEXPRESS; Initial Catalog=SalesDB; Integrated Security=true;"); // this is the connection to the database

    try
    {
        if (sqlCon.State == ConnectionState.Closed)
        {
            sqlCon.Open();
        }

        String query = "SELECT COUNT(1) FROM users_tbl WHERE Username = @Username AND Password = @Password";
        SqlCommand sqlCmd = new SqlCommand(query, sqlCon);
        sqlCmd.CommandType = CommandType.Text;

        sqlCmd.Parameters.AddWithValue("@Username", txtUsername.Text);
        sqlCmd.Parameters.AddWithValue("@Password", txtPassword.Password);

        int count = Convert.ToInt32(sqlCmd.ExecuteScalar()); //if true the value will be converted to "1" in integer.
        LoginInfo userInfo = new LoginInfo();

        using (SqlDataReader oReader = sqlCmd.ExecuteReader())
        {
            while (oReader.Read())
            {
                userInfo.UserName = oReader["Username"].ToString();
                userInfo.PassWord = oReader["Password"].ToString();
                userInfo.Role = oReader["Role"].ToString();
                userInfo.FirstName = oReader["First_Name"].ToString();
                userInfo.LastName = oReader["Last_Name"].ToString();
            }
        }

        if (count == 1)
        {
            MainWindow dashboard = new MainWindow();
            dashboard.Show();
            this.Close();
        }
        else
        {
            MessageBox.Show("Username or Password is incorrect. ");
            txtUsername.Focus();
            txtUsername.Clear();
            txtPassword.Clear();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        sqlCon.Close();
    }
}

// login button code
private void btnSubmit_Click(object sender, RoutedEventArgs e)
{
    LoggingIn();
}

// my LoginInfo Class
namespace TestApp
{
    public class LoginInfo
    {
        private string userName;

        public string UserName
        {
            get { return userName; }
            set { userName = value; }
        }

        private string passWord;

        public string PassWord
        {
            get { return passWord; }
            set { passWord = value; }
        }

        private string role;

        public string Role
        {
            get { return role; }
            set { role = value; }
        }


        private string firstName;

        public string FirstName
        {
            get { return firstName; }
            set { firstName = value; }
        }


        private string lastName;

        public string LastName
        {
            get { return lastName; }
            set { lastName = value; }
        }
    }
}

SELECT COUNT(1) FROM users_tbl where Username=@Username AND Password=@Password this will return the number of rows that match your where clause, not individual columns. Did you mean to use select top 1 *? — Arian Motamedi
– Arian Motamedi, Commented Feb 3, 2018 at 16:38
No, I want to get the values of a certain data where the user name and password is equal to what I have typed in my login page. I see, how should I change it? And I want to change the part that I am using that "Select Count(1)", I want to make it if that username and password exists in the database then I must get its values and store it in a object then pass it to the new window() and get logged-in — Ivan Apungan
– Ivan Apungan, Commented Feb 3, 2018 at 16:43
As I said, your where clause takes care of finding the row that matches the username and password. Obviously if no such row exists, nothing is returned. If more than one exists, multiple rows are returned. You can start with SELECT * FROM users_tbl where Username=@Username AND Password=@Password and go from there. If you need both the count and the data as separate queries, you'll need two different sql commands. — Arian Motamedi
– Arian Motamedi, Commented Feb 3, 2018 at 17:20
You should never store passwords in plaintext. Passwords should be one-way hashed and salted so that nobody can recover the plaintext password. — mason
– mason, Commented Feb 3, 2018 at 20:33

Troy Turley · Accepted Answer · 2018-02-03 17:27:29Z

I would recommend against managing your own user accounts. There are many good choices that will do that for you. If you want them to be stored locally I would recommend looking into Microsoft Identity. Visual Studio 2017 will set it up for you if you choose it when you create the new project If you choose to manage the user accounts yourself you will want to encrypt them. The most accepted method is to store a one-way hash in the database. That way, if a hacker breaks into your database they can't read the passwords. https://learn.microsoft.com/en-us/aspnet/core/security/authentication/identity?tabs=visual-studio%2Caspnetcore2x

Also, if you are accessing the database you might check into Microsoft Entity Framework. It will effectively map your database to your objects.

There are a lot of good learning resources available for free. Youtube would be a good place to start. When performing a search be sure to include the version of the .Net Framework you're using so you will get relevant results. Good luck! (I would have just commented this rather than an answer, but I haven't earned that privilege)

Sabbir Hassan · Accepted Answer · 2018-02-03 18:12:18Z

As far as I understood you basically want a global object that you can access to from any class. I will not go into you sql query or other code above.

First you create a Public Static Class and give them some static properties.

public static class UserInfoObject
    {
        public static int CountFromYourQuery { get; set; } = 0; //I would initiate the value to 0
        public static string UserName { get; set; } = ""; //added this one so that you understand the structure or you realise that you can add more properties this way
    }

Now you can call this class and its properties from anywhere within your project (of course). But you have to initialise before you call them.

private UserInfoObject UIO = new UserInfoObject(); // this initialised your class with initial value.

Now you can insert value to its properties

if (count == 1)
        {
           UIO.CountFromYourQuery = YourCountVariable;

            MainWindow dashboard = new MainWindow();
            dashboard.Show();
            this.Close();
        }
        else
        {
        ...
        }

Sorry I just realised now that you actually did all of it but your class doesnt have static property thats why you can not access them like global ones.

Still i hope my example above will help you to understand whats happening here. Also You can write {get;set;} in short hand unless you are processing anything inside your getter or setter.

PS: I am not that expert in C# either so my code practise might not look professional. Also, you should think about memory consumption/resource blocking while you write your code especially static variables which will remain in the memory for a long time.

Hope that will help :)

SSD · Accepted Answer · 2018-02-03 18:20:01Z

I dont see how you are not getting sql exception while reading data for SqlReader object.

See following code of yours:

int count = Convert.ToInt32(sqlCmd.ExecuteScalar()); //if true the value will be converted to "1" in integer.
LoginInfo userInfo = new LoginInfo();

using (SqlDataReader oReader = sqlCmd.ExecuteReader())
{
     while (oReader.Read())
      {
          userInfo.UserName = oReader["Username"].ToString();
          userInfo.PassWord = oReader["Password"].ToString();
          userInfo.Role = oReader["Role"].ToString();
          userInfo.FirstName = oReader["First_Name"].ToString();
          userInfo.LastName = oReader["Last_Name"].ToString();
      }
}

What is wrong on above code?

Before the using{} clause of SqlDataReader there should be new sqlCmd
Assigning new sql command to fetch data for user from database
Instead of while you need if(oReader.Read())

Add this before using{SqlDataReader

query = "SELECT [username], [password], First_Name, Last_Name FROM users_tbl WHERE Username = @Username AND Password = @Password";
sqlCmd = new SqlCommand(query, sqlCon);
sqlCmd.CommandType = CommandType.Text;
 sqlCmd.Parameters.AddWithValue("@Username", txtUsername.Text);
sqlCmd.Parameters.AddWithValue("@Password", txtPassword.Password);

Above code will get data required for SqlDataReader and then it will read it.

You can also combine both Sql queries as single call to database. Just fetch user details and set a boolean flag to true if sqlReader reads anything. Then check that boolean varable instead of checking if(count==1). Leaving it for you to try

Collectives™ on Stack Overflow

C# How do I store values from sql database to an object

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related