Retrieving data from database in WPF Desktop application

Question

I am new to c# and I had just learned how to input data into datatable of the database.mdf within my WPF Application.

This is done via the following code

SqlConnection sqlCon = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;");
try
{
    if (sqlCon.State == ConnectionState.Closed)
        sqlCon.Open();
    String query = "SELECT COUNT(1) FROM tbl WHERE Username=@Username and Password=@Password";
    SqlCommand sqlCmd = new SqlCommand(query, sqlCon);
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.Parameters.AddWithValue("@Username", txtbxUsername.Text);
    sqlCmd.Parameters.AddWithValue("@Password", pswbxPassword.Password);
    int count = Convert.ToInt32(sqlCmd.ExecuteScalar());
    if (count == 1)
    {
        MessageBox.Show("Login successfully!");

        DetailPage newpage = new DetailPage();
        newpage.Show();
        this.Close();
    }
    else
    {
        MessageBox.Show("Username or password is incorrect");
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    sqlCon.Close();
}

Currently after logging in via the login page, there's a detail page where I want to display details like firstname, lastname, age which are saved in my database previously. May I ask what are the SqlCommands I could use to retrieve all these parameters data from the database?

Coskun Ozogul · Accepted Answer · 2018-08-23 06:44:53Z

0

In your place, I would add a User class in my application

public class user
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string MailAddress{ get; set; }
}


//You can keep your Logged user in a static class


public static class PublicParameters
{ 
  public static User CurrentUser;

  //Define only one connection string in your application.
  public static string ConnectionString= @"Data Source=   (LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated 
 Security=True;"
}




    void Login()
    {
        using (SqlConnection sqlConn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand sqlComm = new SqlCommand("SELECT COUNT(1) FROM tbl WHERE Username=@Username AND Password=@Password", sqlConn))
            {
                if (sqlComm.Connection.State == System.Data.ConnectionState.Closed)
                    sqlComm.Connection.Open();
                SqlDataReader sqlRd = sqlComm.ExecuteReader();
                sqlComm.Parameters.AddWithValue("@Username", txtbxUsername.Text);
                sqlComm.Parameters.AddWithValue("@Password", pswbxPassword.Password);

                //Your username column must be unique
                while (sqlRd.Read())
                {
                    PublicParameters.CurrentUser = new Controllers.User();
                    PublicParameters.CurrentUser.FirstName = sqlRd["FirstName"].ToString();
                    PublicParameters.CurrentUser.LastName = sqlRd["LastName"].ToString();
                    PublicParameters.CurrentUser.MailAddress = sqlRd["MailAddress"].ToString();
                    //And other properties to assign
                }

                if(PublicParameters.CurrentUser != null)
                {
                    MessageBox.Show("Login successfully!");
                    //Yo have your logged user 
                }
                else
                {
                    MessageBox.Show("Username or password is incorrect");
                }
            }
        }
    }

edited Aug 23, 2018 at 6:44

answered Aug 21, 2018 at 7:03

Coskun Ozogul

2,5172 gold badges25 silver badges37 bronze badges

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

7 Comments

geekuser Over a year ago

Thank you, but may I know where did you define CurrentUser?

Mikitori Over a year ago

He didn't post the full code, but I guess Controllers is his namespace and CurrentUser is just an instance of a user (the class defined at the top).

Coskun Ozogul Over a year ago

In your place, I would add a Public static class in my project. In this class (for exemple a classs named PublicParameters), I would define a public static User object in this class. And you can call this object in any class in which you reference your Public Parameters class by using PublicParameters;

geekuser Over a year ago

Thanks, I try doing this however they gave me an Error exception at the line SqlDataReader sqlRd = sqlCmdExecute.Reader Additional information: Incorrect syntax near the keyword 'FROM' Is there anything wrong with this?

Coskun Ozogul Over a year ago

Yes, my select command was not complete ! sorry :) SELECT COUNT(1) FROM TABLE ... will be better :)

|

Collectives™ on Stack Overflow

Retrieving data from database in WPF Desktop application

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related