Click here to Skip to main content
15,916,371 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Dear All,

I have made a form to search in c#. In this form i search a NTN (National Tax Number) through a textbox in data grid view but problem is this i can search one NTN through this textbox. Whereas I have hundred of NTNs to search. I want to search multiple values through this textbox. I made the same in VB.NET where i separate each NTN with qomma. How can i do the same in C#.

What I have tried:

private void searchbtn_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(cs);
            string query = "select * from atlit where ntn like @ntn";
            SqlDataAdapter sda = new SqlDataAdapter(query, con);
            sda.SelectCommand.Parameters.AddWithValue("@ntn", searchtxtbox.Text.Trim());
            DataTable data = new DataTable();
            sda.Fill(data);
            if(data.Rows.Count > 0)
            {
                dataGridView1.DataSource = data;
                MessageBox.Show("This Search is in ATL List");
            }
            else
            {
                MessageBox.Show("No Record Found!!!!!");
                dataGridView1.DataSource= data;
            }

            lbl_recordfound.Text = "Total Records found: " + data.Rows.Count.ToString("#,###");
            lbl_recordfound.Enabled = true;
Posted
Updated 22-Nov-22 0:58am

You can use the SQL IN operator, see: SQL IN Operator[^]

For an example see this answer from one of our highly esteemed CodeProject members: How to search multiple values in one textbox[^]
 
Share this answer
 
v2
The answer linked from solution 1 mentions that concatenating the values directly into the query leaves you vulnerable to SQL Injection[^]; unfortunately, it doesn't show you the correct way to avoid that.

You could use a library such as Dapper[^] to achieve this. Or you could write your own code, which isn't too complicated:
C#
DataTable data = new DataTable();

using (SqlConnection con = new SqlConnection(cs))
using (SqlDataAdapter sda = new SqlDataAdapter(null, con))
{
    var commandText = new System.Text.StringBuilder("SELECT * FROM atlit WHERE 1 = 0");
    foreach (string ntn in searchtxtbox.Text.Split(','))
    {
        if (string.IsNullOrWhiteSpace(ntn)) continue;
        
        string parameterName = "@ntn" + sda.SelectCommand.Parameters.Count;
        sda.SelectCommand.Parameters.AddWithValue(parameterName, ntn.Trim());
        commandText.Append(" OR ntn Like ").Append(parameterName);
    }
    
    sda.SelectCommand.CommandText = commandText.ToString();
    sda.Fill(data);
}
This will build a query that looks something like:
SQL
SELECT * FROM atlit WHERE 1 = 0 OR ntn Like @ntn0 OR ntn Like @ntn1
Each comma-separated value from the textbox will be appended to the command as a parameter. If no values are entered, then no data will be returned.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900