Hii Akram.19,
Please refer below sample.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
Namespaces
C#
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data.SqlClient
Imports System.Configuration
Code
C#
private void Form1_Load(object sender, EventArgs e)
{
this.PopulateDataGridView();
this.PopulateComboBox(cbCountry, "SELECT DISTINCT Country FROM Customers", "Country", "Country");
this.PopulateComboBox(cbCity, "SELECT DISTINCT City FROM Customers", "City", "City");
this.PopulateComboBox(cbCustomerID, "SELECT DISTINCT CustomerID FROM Customers", "CustomerID", "CustomerID");
this.PopulateComboBox(cbCompanyName, "SELECT DISTINCT CompanyName FROM Customers", "CompanyName", "CompanyName");
this.PopulateComboBox(cbContactName, "SELECT DISTINCT ContactName FROM Customers", "ContactName", "ContactName");
this.PopulateComboBox(cbContactTitle, "SELECT DISTINCT ContactTitle FROM Customers", "ContactTitle", "ContactTitle");
this.PopulateComboBox(cbAddress, "SELECT DISTINCT Address FROM Customers", "Address", "Address");
this.PopulateComboBox(cbPostalCode, "SELECT DISTINCT PostalCode FROM Customers", "PostalCode", "PostalCode");
}
private void PopulateComboBox(ComboBox comboBox, string query, string displayMember, string valueMember)
{
string constr = @"Data Source=.;Database=Northwind;Uid=sa;Pwd=pass@123";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
//Fill the DataTable with records from Table.
DataTable dt = new DataTable();
sda.Fill(dt);
//Insert the Default Item to DataTable.
DataRow row = dt.NewRow();
dt.Rows.InsertAt(row, 0);
//Assign DataTable as DataSource.
comboBox.DataSource = dt;
comboBox.DisplayMember = displayMember;
comboBox.ValueMember = valueMember;
}
}
}
}
private void PopulateDataGridView()
{
string query = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, Country, City, PostalCode FROM Customers";
query += " WHERE (Country = @Country OR @Country IS NULL) AND (City = @City OR @City IS NULL) AND (ContactName = @ContactName OR @ContactName IS NULL)";
query += " AND (CompanyName = @CompanyName OR @CompanyName IS NULL) AND (CustomerID = @CustomerID OR @CustomerID IS NULL) AND (ContactTitle = @ContactTitle OR @ContactTitle IS NULL)";
query += " AND (Address = @Address OR @Address IS NULL) AND (PostalCode = @PostalCode OR @PostalCode IS NULL)";
string constr = @"Data Source=192.168.0.100\SQL2019;Database=Northwind;Uid=sa;Pwd=pass@123";
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query, con))
{
cmd.CommandType = CommandType.Text;
if (cbCountry.SelectedValue != null && cbCity.SelectedValue != null && cbContactName.SelectedValue != null
&& cbCompanyName.SelectedValue != null && cbCustomerID.SelectedValue != null && cbContactTitle.SelectedValue != null
&& cbAddress.SelectedValue != null && cbPostalCode.SelectedValue != null)
{
cmd.Parameters.AddWithValue("@Country", cbCountry.SelectedValue);
cmd.Parameters.AddWithValue("@City", cbCity.SelectedValue);
cmd.Parameters.AddWithValue("@CustomerID", cbCustomerID.SelectedValue);
cmd.Parameters.AddWithValue("@CompanyName", cbCompanyName.SelectedValue);
cmd.Parameters.AddWithValue("@ContactName", cbContactName.SelectedValue);
cmd.Parameters.AddWithValue("@ContactTitle", cbContactTitle.SelectedValue);
cmd.Parameters.AddWithValue("@Address", cbAddress.SelectedValue);
cmd.Parameters.AddWithValue("@PostalCode", cbPostalCode.SelectedValue);
}
else
{
cmd.Parameters.AddWithValue("@Country", DBNull.Value);
cmd.Parameters.AddWithValue("@City", DBNull.Value);
cmd.Parameters.AddWithValue("@CustomerID", DBNull.Value);
cmd.Parameters.AddWithValue("@CompanyName", DBNull.Value);
cmd.Parameters.AddWithValue("@ContactName", DBNull.Value);
cmd.Parameters.AddWithValue("@ContactTitle", DBNull.Value);
cmd.Parameters.AddWithValue("@Address", DBNull.Value);
cmd.Parameters.AddWithValue("@PostalCode", DBNull.Value);
}
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
sda.Fill(dt);
dgvCustomers.DataSource = dt;
}
}
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
this.PopulateDataGridView();
}
VB.Net
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
Me.PopulateDataGridView()
Me.PopulateComboBox(cbCountry, "SELECT DISTINCT Country FROM Customers", "Country", "Country")
Me.PopulateComboBox(cbCity, "SELECT DISTINCT City FROM Customers", "City", "City")
Me.PopulateComboBox(cbCustomerID, "SELECT DISTINCT CustomerID FROM Customers", "CustomerID", "CustomerID")
Me.PopulateComboBox(cbCompanyName, "SELECT DISTINCT CompanyName FROM Customers", "CompanyName", "CompanyName")
Me.PopulateComboBox(cbContactName, "SELECT DISTINCT ContactName FROM Customers", "ContactName", "ContactName")
Me.PopulateComboBox(cbContactTitle, "SELECT DISTINCT ContactTitle FROM Customers", "ContactTitle", "ContactTitle")
Me.PopulateComboBox(cbAddress, "SELECT DISTINCT Address FROM Customers", "Address", "Address")
Me.PopulateComboBox(cbPostalCode, "SELECT DISTINCT PostalCode FROM Customers", "PostalCode", "PostalCode")
End Sub
Private Sub PopulateComboBox(ByVal comboBox As ComboBox, ByVal query As String, ByVal displayMember As String, ByVal valueMember As String)
Dim constr As String = "Data Source=.;Database=Northwind;Uid=sa;Pwd=pass@123"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query, con)
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
Dim row As DataRow = dt.NewRow()
dt.Rows.InsertAt(row, 0)
comboBox.DataSource = dt
comboBox.DisplayMember = displayMember
comboBox.ValueMember = valueMember
End Using
End Using
End Using
End Sub
Private Sub PopulateDataGridView()
Dim query As String = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, Country, City, PostalCode FROM Customers"
query += " WHERE (Country = @Country OR @Country IS NULL) AND (City = @City OR @City IS NULL) AND (ContactName = @ContactName OR @ContactName IS NULL)"
query += " AND (CompanyName = @CompanyName OR @CompanyName IS NULL) AND (CustomerID = @CustomerID OR @CustomerID IS NULL) AND (ContactTitle = @ContactTitle OR @ContactTitle IS NULL)"
query += " AND (Address = @Address OR @Address IS NULL) AND (PostalCode = @PostalCode OR @PostalCode IS NULL)"
Dim constr As String = "Data Source=192.168.0.100\SQL2019;Database=Northwind;Uid=sa;Pwd=pass@123"
Using con As SqlConnection = New SqlConnection(constr)
Using cmd As SqlCommand = New SqlCommand(query, con)
cmd.CommandType = CommandType.Text
If cbCountry.SelectedValue IsNot Nothing AndAlso cbCity.SelectedValue IsNot Nothing AndAlso cbContactName.SelectedValue IsNot Nothing AndAlso cbCompanyName.SelectedValue IsNot Nothing AndAlso cbCustomerID.SelectedValue IsNot Nothing AndAlso cbContactTitle.SelectedValue IsNot Nothing AndAlso cbAddress.SelectedValue IsNot Nothing AndAlso cbPostalCode.SelectedValue IsNot Nothing Then
cmd.Parameters.AddWithValue("@Country", cbCountry.SelectedValue)
cmd.Parameters.AddWithValue("@City", cbCity.SelectedValue)
cmd.Parameters.AddWithValue("@CustomerID", cbCustomerID.SelectedValue)
cmd.Parameters.AddWithValue("@CompanyName", cbCompanyName.SelectedValue)
cmd.Parameters.AddWithValue("@ContactName", cbContactName.SelectedValue)
cmd.Parameters.AddWithValue("@ContactTitle", cbContactTitle.SelectedValue)
cmd.Parameters.AddWithValue("@Address", cbAddress.SelectedValue)
cmd.Parameters.AddWithValue("@PostalCode", cbPostalCode.SelectedValue)
Else
cmd.Parameters.AddWithValue("@Country", DBNull.Value)
cmd.Parameters.AddWithValue("@City", DBNull.Value)
cmd.Parameters.AddWithValue("@CustomerID", DBNull.Value)
cmd.Parameters.AddWithValue("@CompanyName", DBNull.Value)
cmd.Parameters.AddWithValue("@ContactName", DBNull.Value)
cmd.Parameters.AddWithValue("@ContactTitle", DBNull.Value)
cmd.Parameters.AddWithValue("@Address", DBNull.Value)
cmd.Parameters.AddWithValue("@PostalCode", DBNull.Value)
End If
Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt As DataTable = New DataTable()
sda.Fill(dt)
dgvCustomers.DataSource = dt
End Using
End Using
End Using
End Sub
Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs)
Me.PopulateDataGridView()
End Sub
Screenshot