How to Search data from Database in C#

Finding data is an indispensable feature when you build an application. To search data from a database in a C# Windows Forms application, you typically need to follow these steps:

How to Search data from Database in C#

Opening your form designer, then drag and drop the Label, TextBox, Button and DataGridView controls from the Visual Studio toolbox into your form designer. You can design a simple UI allows you to search data in sql database based on the search value you enter from TextBox.

Opening your app.config file, then add your connection string as shown below.

<connectionStrings>
  <add name="cn" connectionString="data source=.;initial catalog=northwind;user id=sa;password=123@qaz;" providerName="System.Data.SqlClient"/>
</connectionStrings>

You should modify the username and password login to sql server based on your sql account.

I will use the Northwind database to play my demo, if you don't have the Northwind database in your SQL Server yet. You can view the article on how to download and install the Northwind database into SQL Server.

Using the app.config file to store the connection string makes it easy to change the connection string without modifying your source code.

And don't forget to add a reference to the System.Configuration.dll library to your project. This library helps you to read your connection string from the app.config file.

Adding a click event handler to the Search button allows you to search data from sql database, then display data to DataGridView.

private void btnSearch_Click(object sender, EventArgs e)
{
    try
    {
        using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["cn"].ConnectionString))
        {
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            // Create a DataTable to hold the results
            using (DataTable dt = new DataTable("Customer"))
            {
                using (SqlCommand cmd = new SqlCommand("select *from customers where customerid=@customerid or contactname like @contactname", cn))
                {
                    // Add parameter for the search term
                    cmd.Parameters.AddWithValue("customerid", txtSearch.Text);
                    cmd.Parameters.AddWithValue("contactname", string.Format("%{0}%", txtSearch.Text));
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    // Fill the DataTable with the results of the query
                    adapter.Fill(dt);
                    // Bind the DataTable to the DataGridView to display the results
                    dataGridView.DataSource = dt;
                    lblTotal.Text = $"Total records: {dataGridView.RowCount}";
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

In this example:

• txtSearch is a TextBox control where the user enters the search term.
• dataGridView is a DataGridView control where the search results will be displayed.
• The btnSearch_Click event handler executes the search when the user clicks a button.

You should use try catch to catch errors if something goes wrong, then write your code in this try catch block.

You can use the SqlConnection class to make a connection to your database, and don't forget to check your sql connection status. If the connection is closed you need to open it.

You can create a new DataTable to help you store the data returned from the sql query.

Next, use the SqlCommand class to execute your SQL query. You can easily add query parameters to SqlCommand using the AddWithValue method. It represents a Transact-SQL statement or stored procedure to execute against a SQL Server database.

Finally, Use the SqlDataAdapter class to read the data and fill in your DataTable object, and then bind it to your DataGridView. The SqlDataAdapter, serves as a bridge between a DataSet and SQL Server for retrieving and saving data.

Adding a key press event handler to the TextBox control allows you to enter value, then perform click the search button.

private void txtSearch_KeyPress(object sender, KeyPressEventArgs e)
{
    if (e.KeyChar == (char)13)//enter
        btnSearch.PerformClick();
}

VIDEO TUTORIAL