none
How to get single value of a sql query into a string RRS feed

  • Question

  • I am trying to get results from a sql server query: 

       using (SqlConnection myConnection = new SqlConnection(ConnStr))
                {
                    string oString = "Select Psw from Employees where Email = @E-Mail";
                    SqlCommand oCmd = new SqlCommand(oString, myConnection);
                    oCmd.Parameters.AddWithValue("@E-Mail", Email.Text);
                    myConnection.Open();
                    using (SqlDataReader oReader = oCmd.ExecuteReader())
                    {
                        while (oReader.Read())
                        {
                            
                        }
    
                        myConnection.Close();
                    }
                }

    the thing is, "Email" is PK, so I know I will get only one result - one column and one row - a single value

    Is this possible to get this single value strait into a string, without using  SqlDataReader? Because I know for sure I will get only one value so there is no need.

    Sunday, November 3, 2019 3:59 PM

Answers

  • Karen was just showing you a way to do this with a method to call.
     
    Here's your original code, that I changed to call the ExecuteScalar() method and set a psw variable:

     

    string psw = ""; using (SqlConnection myConnection = new SqlConnection(ConnStr)) { string oString = "Select Psw from Employees where Email = @E-Mail"; SqlCommand oCmd = new SqlCommand(oString, myConnection); oCmd.Parameters.AddWithValue("@E-Mail", Email.Text); myConnection.Open();

    psw = oCmd.ExecuteScalar().ToString(); myConnection.Close(); }


     
    Here's a different version of Karen's method, and how you would call it:
     
    string psw = GetSingleValue(Email.Text);
     
    public string GetSingleValue(string identifier)
    {
        using (var cn = new SqlConnection { ConnectionString = ConStr })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.Parameters.AddWithValue("@E-Mail", identifier);
                cmd.CommandText = "Select Psw from Employees where Email = @E-Mail";
                cn.Open();
                return cmd.ExecuteScalar().ToString();
            }
        }
    }


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP, Moderator Tuesday, November 5, 2019 5:42 AM fixed a typo, bolded changes
    • Marked as answer by avivgood Tuesday, November 5, 2019 7:48 PM
    Tuesday, November 5, 2019 2:17 AM
    Moderator
  • What is the purpose of "int identifier" ? 
    • Edited by avivgood Sunday, November 3, 2019 4:48 PM
    • Marked as answer by avivgood Tuesday, November 5, 2019 7:48 PM
    Sunday, November 3, 2019 4:47 PM

All replies

  • Hello,

    Here is an example.

    public string GetSingleValue(int identifier)
    {
        using (var cn = new SqlConnection { ConnectionString = ConStr })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.Parameters.AddWithValue("@E-Mail", identifier);
                cmd.CommandText = "Select Psw from Employees where Email = @E-Mail";
    
                cn.Open();
                return cmd.ExecuteScalar().ToString();
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, November 3, 2019 4:20 PM
    Moderator
  • What is the purpose of "int identifier" ? 
    • Edited by avivgood Sunday, November 3, 2019 4:48 PM
    • Marked as answer by avivgood Tuesday, November 5, 2019 7:48 PM
    Sunday, November 3, 2019 4:47 PM
  • What is the purpose of "int identifier" ? 
    You simply replace it with a string rather than an int.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Sunday, November 3, 2019 6:45 PM
    Moderator
  • And what it does? what value should I put in?
    Monday, November 4, 2019 6:36 PM
  • Karen was just showing you a way to do this with a method to call.
     
    Here's your original code, that I changed to call the ExecuteScalar() method and set a psw variable:

     

    string psw = ""; using (SqlConnection myConnection = new SqlConnection(ConnStr)) { string oString = "Select Psw from Employees where Email = @E-Mail"; SqlCommand oCmd = new SqlCommand(oString, myConnection); oCmd.Parameters.AddWithValue("@E-Mail", Email.Text); myConnection.Open();

    psw = oCmd.ExecuteScalar().ToString(); myConnection.Close(); }


     
    Here's a different version of Karen's method, and how you would call it:
     
    string psw = GetSingleValue(Email.Text);
     
    public string GetSingleValue(string identifier)
    {
        using (var cn = new SqlConnection { ConnectionString = ConStr })
        {
            using (var cmd = new SqlCommand { Connection = cn })
            {
                cmd.Parameters.AddWithValue("@E-Mail", identifier);
                cmd.CommandText = "Select Psw from Employees where Email = @E-Mail";
                cn.Open();
                return cmd.ExecuteScalar().ToString();
            }
        }
    }


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP, Moderator Tuesday, November 5, 2019 5:42 AM fixed a typo, bolded changes
    • Marked as answer by avivgood Tuesday, November 5, 2019 7:48 PM
    Tuesday, November 5, 2019 2:17 AM
    Moderator