Implement Search from database as alphabets are entered

Question

I have written a code that searches specific string from database table.what i want to implement is search like when we search friends on facebook means it returns results matching to the input given even if it is partial for example if i want to search ANDREW as soon as i enter single character results should start to appear if i enter "an" like andy andrew and....

here is my code for textbox text changed method

    table = new DataTable();
    table.Columns.Add("Name");
    table.Columns.Add("Type");
    table.Columns.Add("Status");
    table.Columns.Add("Date Created");
    table.Columns.Add("Action");

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = textBoxSearch.Text;
    cmd.CommandText = "SELECT id,uName,uType,uStatus,uDate from users WHERE uName=@username ";

    SqlDataReader dr = cmd.ExecuteReader();
    if (dr.HasRows == true)
    {
        while (dr.Read())
        {
            MessageBox.Show(dr["uName"].ToString() + dr["uType"].ToString() + dr["uStatus"].ToString());
            row = table.NewRow();
            row["Name"] = dr["uName"].ToString();
            row["Type"] = dr["uType"].ToString();
            row["Status"] = dr["uStatus"].ToString();
            row["Date Created"] = dr["uDate"].ToString();
            // row["Action"] = new Button();
            table.Rows.Add(row);
            UsersView.DataSource = table;
        }//End While for entering peresent amount of data                   
    }//End If to check wether or not users exist
    dr.Close();//Close Datareader
}

score 1 · Answer 1 · edited May 23 '17 at 12:11

1

To call back to the database, the CommandText becomes

... WHERE uName LIKE @username + '%'

However, I wouldn't call back to the database every time: I'd use an auto complete control. There are several autocomplete controls for Winforms out there. And this SO answer too.

edited May 23 '17 at 12:11

Community

1
1

answered Nov 07 '10 at 19:34

gbn

422,506
82
585
676

It would give "an%" based on your example – gbn Nov 07 '10 at 20:52
and i have implemted auto complete text box but how can i do it in case of data grid view?? as there 3 values name type date created etc?? – Afnan Bashir Nov 07 '10 at 21:21

score 1 · Accepted Answer · answered Nov 07 '10 at 21:37

As you're using C#, why not go for something more LINQ-y?

Set up your LINQ Database Context file and use the following code to achieve what you're trying to (quickly typing this, sorry if there's any errors):

table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Type");
table.Columns.Add("Status");
table.Columns.Add("Date Created");
table.Columns.Add("Action");

var db = new DbDataContext();
var users = (from u in db.users
             where u.Contains(textBoxSearch.Text)
             select u).ToList();
foreach(var user in users)
{
     MessageBox.Show(user.uName + user.uType + user.uStatus);
            row = table.NewRow();
            row["Name"] = user.uName;
            row["Type"] = duser.uType;
            row["Status"] = user.uStatus;
            row["Date Created"] = user.uDate.ToShortDateString();
            table.Rows.Add(row);
}

UsersView.DataSource = table;

there us an error DbDataContext not in context .I dont know how to USE linq — Afnan Bashir, Nov 08 '10 at 18:54
Have you set up the dbml file ready? DbDataContext would refer to the name of that dbml file. If you need to know the absolute basics, Use this video: http://www.asp.net/linq/videos/how-do-i-linq-to-sql-overview, or if you want to learn it really in-depth, I'd recommend either a book or the whole series on LINQ here: http://www.asp.net/linq/videos — David Archer, Nov 08 '10 at 19:49

Implement Search from database as alphabets are entered

2 Answers2