AutoCompleteCustomSource in C#

Sample use of AutoCompleteCustomSource with data obtained from values in a column of a database table.



We know how to define an AutoCompleteCustomSource with a particular list of names or values but if we want the list to come from a column then  we need to load the data from the database into a dataset and then we need to assign the value of the column to the respective textbox.

This is a sample application which basically retrieves all the login ids from the table named Emp which basically contains empid, ename, empadd, loginid, word and from the windows forms we have 2 textboxes whereby we are setting the textbox1 auto complete source to a custom source and at run time we are getting the data from the database. Kindly look at the coding part which has been done.

The Definition of my table is:

create table Emp
(
EmpId int identity(100,1)not null,
EName varchar(20) not null,
EAdd varchar(20) not null,
LoginId varchar(20) not null,
word varchar(20) not null
)

Add some records in the table with all the fields.

My Form Design is

Image1.gif

The Coding part for the form is:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsApplication3
{
    public partial class Form1 : Form
    {
        SqlConnection con;
        SqlDataAdapter da;
        SqlCommand cmd;
        SqlDataReader dr;
        DataSet ds = new DataSet();
        public Form1()
        {
            InitializeComponent();
        }
 
        private void button1_Click(object sender, EventArgs e)
        {
            con = new SqlConnection("Data Source=localhost;Initial Catalog=AdventureWorks;User Id=sa;word=faculty");
            cmd = new SqlCommand("Select * from Emp where LoginId=@login and word=@", con);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadData();
            string[] login = new string[ds.Tables[0].Rows.Count - 1];
            textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
            for (int i = 0; i < ds.Tables[0].Rows.Count - 1; i++)
            {
                login[i] = ds.Tables[0].Rows[i]["LoginId"].ToString();
            }
            foreach (string name in login)
            {
                textBox1.AutoCompleteCustomSource.Add(name);
            }
        }
        private void LoadData()
        {
            con = new SqlConnection("Data Source=localhost;Initial Catalog=AdventureWorks;User Id=sa;word=faculty");
            da = new SqlDataAdapter("Select * from Emp", con);
            da.Fill(ds, "Emp");
        }
    }
}

Test your application; use any login ids and initially you'll get all the login ids in your database.