C# SQL query with user input-CodePudding

I tried to make software that could be use as a database for libraries so I started to learn SQL I alredy did way to save a book into the database but I am stuck at searching in database I really dont know how to write query with user input I tried to google but cant find the answer I am stuck at this problem for a while now, please help me someone (I write my code normally in my native language but I rewrited it in english but if I forgot something to rewrite I am sorry I am in a bit of hurry when writing this).

try
        {
            using (connection = new SqlConnection(connectionString))
            {
                if(connection.State == ConnectionState.Closed)
                    connection.Open();
                using(DataTable dt = new DataTable("Search"))
                {
                    using(SqlCommand cmd = new SqlCommand("SELECT * FROM books WHERE name = @name OR name LIKE @name", connection))
                    {
                        cmd.Parameters.AddWithValue("@name", txtboxSearchName.Text);
                        cmd.Parameters.AddWithValue("author", string.Format("%{0}%", txtboxSearchAuthor.Text));
                        SqlDataAdapter adapter = new SqlDataAdapter();
                        adapter.Fill(dt);
                        dataGridView1.DataSource = dt;
                    }
                }
            }
        }
        catch (Exception)
        {
            MessageBox.Show("Something went wrong", "error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

CodePudding user response：

From What i understood you're trying to search for something in your database

your query looks is :

 SELECT * FROM books WHERE name = @name OR name LIKE @name

the ="" sign means that the name must equal the parameter @name
the "LIKE" operator is used in a WHERE clause to search for a specified pattern in a column

so if you're looking for books with a specific name you can use :

 SELECT * FROM books WHERE name = @name

if you're looking for books with names that are similar to @name you can use :

SELECT * FROM books WHERE name like @name

when using the like operator you should use the % to specify the similarities refer to this article so u can understand

i guess in your case the like is for author

here is your final fix :

    using(SqlCommand cmd = new SqlCommand("SELECT * FROM books WHERE name = @name OR name LIKE @author", connection))
                        {
                            cmd.Parameters.AddWithValue("@name", txtboxSearchName.Text);
// you were missin an @ in author parameter
                            cmd.Parameters.AddWithValue("@author", string.Format("%{0}%", txtboxSearchAuthor.Text));
                            SqlDataAdapter adapter = new SqlDataAdapter();
                            adapter.Fill(dt);
                            dataGridView1.DataSource = dt;
                        }

CodePudding user response：

You have almost completed the task; however, some issues should be corrected:

try {
  // Remove old DataSource if any
  if (dataGridView1.DataSource is IDisposable old)
    old.Dispose();

  using (connection = new SqlConnection(connectionString)) {
    // Let sql be readable
    // name = @name seems to be redundant: LIKE is broader in the context
    string sql = 
       @"SELECT * 
           FROM books 
          WHERE name LIKE @name";

    using(SqlCommand cmd = new SqlCommand(sql, connection)) {
      // Your command uses just one parameter - @name; no @author is used  
      cmd.Parameters.AddWithValue("@name", txtboxSearchName.Text);

      // SqlDataAdapter must know which command to use
      SqlDataAdapter adapter = new SqlDataAdapter(cmd);

      DataTable dt = new DataTable("Search");

      adapter.Fill(dt);
      dataGridView1.DataSource = dt;
    }
  }
}
catch (DbException e) { // Don't catch (= swallow) all the exception 
  // Message: let user know what went wrong - e.Message
  MessageBox.Show($"Something went wrong: {e.Message}", 
                   "Error", 
                    MessageBoxButtons.OK, 
                    MessageBoxIcon.Error);
}