How can i search SQL Server Database while typing in C# Winforms application?

Question

I am showing some sql table results in data grid view in winforms app. I normally use DbEntities but i had to use join in my query to get results from multiple results, so instead i used this code.

And i want to add a query and a textbox to search results while typing. How can i do that from what i already started?

        SqlConnection con = new SqlConnection("server=.; Initial 

        Catalog=winforms;Integrated Security=SSPI");

        DataTable dt = new DataTable();

        string sql = "SELECT Personel.ad, Personel.soyad, Personel.tc, Personel.dogum, Personel.isgiris, Birim.birimad AS [Birim], Sube.subead AS [Şube] FROM Personel JOIN Birim ON Birim.birimid = Personel.birimid JOIN Sube ON Sube.subeid = Personel.subeid";

        con.Open();
        SqlDataAdapter da = new SqlDataAdapter(sql, con);
        da.Fill(dt);
        dataGridView1.DataSource = dt;

Answer 1

You can use TextChanged event for textbox and send the text to the function as parameter. Then add the text to the WHERE clause on SQL query.

Be careful about user inputs for querying SQL. This is just an example, not very safe.

void textBox1_TextChanged(object sender, EventArgs e)
{
    CallSQL(textBox1.Text);
}

void CallSQL(string filterText)
{
...
...
 string sql = string.Format("SELECT ... WHERE Personel.Ad = {0}", filterText);
...
...
}

Answer 2

Found this answer to search in DataTable.

So for your solution you would need to implement

public static DataTable SearchInAllColums(this DataTable table, string keyword, StringComparison comparison)
{
    if(keyword.Equals(""))
    {
        return table;
    }
    DataRow[] filteredRows = table.Rows
       .Cast<DataRow>()
       .Where(r => r.ItemArray.Any(
       c => c.ToString().IndexOf(keyword, comparison) >= 0))
       .ToArray();

    if (filteredRows.Length == 0)
    {
        DataTable dtProcessesTemp = table.Clone();
        dtProcessesTemp.Clear();
        return dtProcessesTemp;
    }
    else
    {
        return filteredRows.CopyToDataTable();
    }
}

And then you could use it in your changeevent:

void textBox1_TextChanged(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection("server=.; Initial 

    Catalog=winforms;Integrated Security=SSPI");

    DataTable dt = new DataTable();

    string sql = "SELECT Personel.ad, Personel.soyad, Personel.tc, Personel.dogum, Personel.isgiris, Birim.birimad AS [Birim], Sube.subead AS [Şube] FROM Personel JOIN Birim ON Birim.birimid = Personel.birimid JOIN Sube ON Sube.subeid = Personel.subeid";

    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(sql, con);
    da.Fill(dt);

    dataTable.SearchInAllColums(textBox1.Text, StringComparison.OrdinalIgnoreCase);

    dataGridView1.DataSource = dataTable;
}

HOWEVER: Doing it like this will cause alot of traffic to your sql server. I would strongly suggest you to also implement some form of cache here for getting all searchable data. If that's an option.

Answer 3

just o add to the question and futures similiar problems:

Do not use SQL as "insta-search" on textchanged proprieties. Its bad for many reasons as people mentioned above. But first of all, you can have problems by locking up SQL tables for having people trying so search something and generating alot of traffic as consequence, and even for one user, its unsafe and unfriendly to resources.. Besides, you have a high traffic for SQL injection. There are some good programming practices to make it safer, i'll try to elucidate a little more.

Okay, you already know this piece of information. But how the Correct way to do it?

Use objects, DAL and cache info until its old enough and have to update it.

How:

First things first, you should create a DAL/DAO class that is used ONLY for sql querys and operations. Do some search on the subject and why is a bad idea to have your business rules and sql code scrambled togheter. Here some short text about it:About DAL/DAO

Then, create a class of objects. For example: PersonelInfo. Wich contains every attribute that you need, like: name,documents, etc.

Here some info about objects handling: Using Objects with C#

After having your object nice and done, use lists to store and pass it to datatable or directly to datagridview if you wich.

List<Objectname> listname = new List<Objectname>();

Then, use some loop to iterate through SQL data and fill your list.

Example:

while(dataReader.Read())
{
Object objectname = new Object();

objectname.attribute1 = dataReadet["columname"].ToString();
objectname.attribute2 = dataReadet["columname"].ToString();
objectname.attribute3 = dataReadet["columname"].ToString();
listname.Add(objectname);
}

at the end return your objectlist:

Return listname;

That way you have full organized object list that you can:

• Use as base for cache. That list contains all information that user needs to search for. Search the list with a foreach loop and return desired values to the user. Just remember to update it when it gets old.
• Use it as source for Datagridview or Datatable, its cheap.
• Use it with JSON, it can be sent by API or simple UDP/TCP connection

Hope this short comment bring you some light with data handling.

Note that not every data is safe to keep that way. Passwords and protected stuff should not be stored in memory or other ways that can be exploited.