Windows Forms: How to Search data in Database using C#

Finding data is an indispensable feature when you build an application. In this article I will show you how to search data in sql server.

Opening your form designer, then drag 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.

In this tutorial, 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 watch the video I uploaded 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();
            using (DataTable dt = new DataTable("Customer"))
            {
                using (SqlCommand cmd = new SqlCommand("select *from customers where customerid=@customerid or contactname like @contactname", cn))
                {
                    cmd.Parameters.AddWithValue("customerid", txtSearch.Text);
                    cmd.Parameters.AddWithValue("contactname", string.Format("%{0}%", txtSearch.Text));
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(dt);
                    dataGridView.DataSource = dt;
                    lblTotal.Text = $"Total records: {dataGridView.RowCount}";
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

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