Search multiple values through one text box in C#

Question

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 22-Nov-22 0:19am

Ghulam Sarwar72

Updated 22-Nov-22 0:58am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RickZeeland · Answer 1 · 2022-11-22T00:33:00

Solution 1

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[^]

Posted 22-Nov-22 0:33am

RickZeeland

Updated 22-Nov-22 0:38am

v2

Richard Deeming · Answer 2 · 2022-11-22T00:58:00

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.