How can I write a command to select a data from a database table when it matches the input provided on my textBox1.text?

Question

I am working on a windows app that will display the Synonym to a TextBox2 if the input on TextBox1 matches any of the data in the Word column of my database table below. My code below only output Joyful when I enter any of the words in Word column Please how can I correct & achieve that?

See my table and code below:

**Id**  **Word**    **Synonym**
1   Happy   Joyful
2   Sad     Unhappy
3.  Good    Fine


string constr = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=…\Database1.mdf;Integrated Security=True";
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT Synonym FROM Ant&Syn WHERE Id = 1"))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        sdr.Read();
                        textBox2.Text = sdr["Synonym"].ToString();
                    }
                }
                con.Close();

_WHERE Id = 1_ this will retrieve always the same record. You need to change this condition to use the string present in the Textbox. Search how to use the WHERE statement in sql — Steve, May 09 '21 at 15:10
@Steve, Thank you for spotting that. I will read more on how to use the WHERE statement in sql — Trust Awal, May 09 '21 at 15:41
@OlivierRogier, Thank you for referring me to that link. The post is helpful. However, I tried this: SELECT Synonym FROM Ant&Syn WHERE Word = '" + TextBox1 + "'" did not work. Do you have a better suggestion for me. I am new here. still learning — Trust Awal, May 09 '21 at 15:44
The accepted answer contains an error as well your original code. You are calling Read but you don't care what is the result of that Read. If there is no matching record then there is nothing to read and the call return false. You shouldn't blindly try to extract values from a reader when Read return false. — Steve, May 09 '21 at 17:49

score 0 · Accepted Answer · answered May 09 '21 at 15:50

0

How about using parameterized query as below?

string matchingWord = TextBox1.text;
using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand("SELECT Synonym FROM Ant&Syn WHERE Word = @word"))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@word", matchingWord)
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        sdr.Read();
                        textBox2.Text = sdr["Synonym"].ToString();
                    }
                }
                con.Close();
            }

answered May 09 '21 at 15:50

Prashant

26
5

Thank you very much. My program seems to be working as expected now after following your suggestion above. – Trust Awal May 09 '21 at 16:16
Using `AddWithValue` is not recommended: [AddWithValue sql injection safe?](https://stackoverflow.com/questions/21263536/addwithvalue-sql-injection-safe-why/21263886) and [SqlCommand Parameters Add vs. AddWithValue](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) – May 09 '21 at 16:23
Wow! Thanks for sharing this side information about the use of AddWithValue. Very useful. A lot to learn and discover in the world of programming – Trust Awal May 09 '21 at 16:55

score 0 · Answer 2 · 2021-05-09T17:29:14.023

Using a SQL parameter

It execute the query to filter on the word to search and returns all available synonyms 0..* using the generic List class to store values that will be joined for a one line display in the text box (empty string if none found):

using System.Collections.Generic;

var synonyms = new List<string>();

using ( var connection = new SqlConnection(connectionString) )
  try
  {
    connection.Open();
    using ( var command = connection.CreateCommand() )
    {
      command.CommandText = "SELECT Synonym FROM [Ant&Syn] WHERE Word = ?";
      command.Parameters.Add("@word", SqlDbType.Text).Value = TextBox1.text;
      using ( var reader = command.ExecuteReader() )
        while ( reader.Read() )
          synonyms.Add(reader[0].ToString());
    }
  }
  finally
  {
    connection.Close();
  }

TextBox2.Text = string.Join(", ", synonyms);

We can manage multiple results with a multi-line textbox, a listbox, a context menu or anything else.

What are good ways to prevent SQL injection?

AddWithValue sql injection safe? Why?

SqlCommand Parameters Add vs. AddWithValue

Output

Fine

Improvement - Refactoring

We can create a method and use the yield keyword to return a IEnumerable:

IEnumerable<string> GetSynonyms(string word)
{
  using ( var connection = new SqlConnection(connectionString) )
    try
    {
      connection.Open();
      using ( var command = connection.CreateCommand() )
      {
        command.CommandText = "SELECT Synonym FROM [Ant&Syn] WHERE Word = ?";
        command.Parameters.Add("@word", SqlDbType.Text).Value = word;
        using ( var reader = command.ExecuteReader() )
          while ( reader.Read() )
            yield return reader[0].ToString();
      }
    }
    finally
    {
      connection.Close();
    }
}

var synonyms = GetSynonyms(TextBox1.text);

Improvement - Case insensitive comparison

We can use a flag to set the option:

IEnumerable<string> GetSynonyms(string word, bool caseInsensitive)
{
  using ( var connection = new SqlConnection(connectionString) )
    try
    {
      connection.Open();
      using ( var command = connection.CreateCommand() )
      {
        string checkColumn = caseInsensitive ? "lower(Word)" : "Word";
        string checkMatch = caseInsensitive ? word.ToLower() : word;
        command.CommandText = "SELECT Synonym FROM [Ant&Syn] WHERE " + checkColumn + " = ?";
        command.Parameters.Add("@word", DbType.String).Value = checkMatch;
        using ( var reader = command.ExecuteReader() )
          while ( reader.Read() )
            yield return reader[0].ToString();
      }
    }
    finally
    {
      connection.Close();
    }
}

Thank you very much too. I have saved it for later testing. I hope it works with LocalDB in Visual Studio 2019? — Trust Awal, May 09 '21 at 16:19
Like I mentioned earlier, am really new & still learning. So please be patient with me. I tried your code and I am having the following two errors below: First error is: "The name 'Builder' does not exist in the current context". Secondly error is: "The type or namespace name 'List<>' could not be found (are you missing a using directive or an assembly reference?)" — Trust Awal, May 09 '21 at 16:53

How can I write a command to select a data from a database table when it matches the input provided on my textBox1.text?

2 Answers2