How to create Autocomplete Text box with SQL Data in C#.Net

As we know , can facilitate Autocomplete feature to Text box, DatagridView  controls in C#.Net.  We can also fetch auto text from data tables too, by using dataview  object.

  • To do this we need to fill the dataset and dataview objects then add the rows to string collection as follows.
GroupTableAdapter = new SqlDataAdapter("select name from AccountGroups", con);
 GroupTableDataset = new DataSet();
 GroupTableAdapter.Fill(GroupTableDataset, "AccountGroups");
 GroupTableView = new DataView(GroupTableDataset.Tables[0]);
  • Adding the rows to the string collection
AutoCompleteStringCollection autotext = new AutoCompleteStringCollection();
foreach (DataRowView row in Common.GroupTableView)
  • Attach the collection to the control
txt_gname.AutoCompleteMode = AutoCompleteMode.Suggest;
txt_gname.AutoCompleteSource = AutoCompleteSource.CustomSource;
txt_gname.AutoCompleteCustomSource = autotext;

drop all three section of code in load or initialize event and it will work.



Autocomplete text to a single datagridview column in C#

We already discussed the matter how to suggest AutoComplte Text  for datagridview column in C#. This post is the extension to that post, which will explain , limit the searching capability for single column.

Task: Need Autocomplete suggest list of Text as I type for the PARTY column only[DataGridView].


Drop the following code in the  dataGridView1_EditingControlShowing event.

TextBox auto = e.Control as TextBox;
 if (dataGridView1.Columns[dataGridView1.CurrentCell.ColumnIndex].HeaderText == "PARTY")
 AutoCompleteStringCollection autotxt = new AutoCompleteStringCollection();

foreach (DataRowView row in Common.AccountRegistrationTableView)
 auto.AutoCompleteMode = AutoCompleteMode.Suggest;
 auto.AutoCompleteSource = AutoCompleteSource.CustomSource;
 auto.AutoCompleteCustomSource = autotxt;
 auto.AutoCompleteMode = AutoCompleteMode.None;
 auto.AutoCompleteSource = AutoCompleteSource.None;
 auto.AutoCompleteCustomSource = null;

  • Firstly  capture the control using TextBox auto = e.Control as TextBox;
  • Secondly  populate names from the database and add to AutocompletStringCollection.
  • Thirdly  attach the collection to custom source of the control.
  • The else part simply prevent from displaying autocomplete for every columns in the datagridview.



Set Printer as default in C#

As we learned how to list installed printers , also it is possible set printer as default at run time with some tricky C# code. This can be achieved by using winspool.drv functionality.

Firstly incorporate the Winspool functionality to C# by using DllImport.

  1. Add using System.Runtime.InteropServices
  2. Add a public class Myprinter and implement SetDefaultPrinter method (bottom of the namespace )


public static class myPrinters
 [DllImport("winspool.drv", CharSet = CharSet.Auto, SetLastError = true)]
 public static extern bool SetDefaultPrinter(string Name);


Now you can simply set a printer as default using the following code.

myPrinters.SetDefaultPrinter(<yourprinter name>);


That is all for today

List all available printers in C#.Net

.Net frame work allows you to enjoy rich set of printing functionalities.  Lets’s learn how to list all printers in a system in C#

Using the PinterSetting class you can access a list of installed printers on your system and can be added to any control you want.

The Printer class belong to the System.Drawing.Printing package, so you need to reference the class by including using System.Drawing.Printing;, at the start of the program module/form.

PrinterSettings setting = new PrinterSettings();
 foreach (string prntr in PrinterSettings.InstalledPrinters)


The above mentioned code will add all installed printer names to a list box.


How to delete rows using simple sql statement in C#

As we have seen how to delete rows in a dataset  in C#.Net, there are simple ways to do the same using SQL command object. All we need is an sql statement and an Object to do the job.;Common.cmd = new SqlCommand("delete from purchasepart where billno=" + b1, Common.con);


cmd object configure the delete statement and call the ExecuteNonQuery() , and will delete those rows you are specified. Keep in mind that the connection should kept open while the ExecuteNonQuery(); method invoked.

Delete a rows using command builder and dataset in C#

The following example simply illustrate how you can delete rows using dataset and command builder in C#. As a C# programmer you may know how important a dataset is.

Here in our example, we first create a database adapter pointing to some table rows and then build a dataset which is the local representation of database table.

Common.StockPurchase_Part = new SqlDataAdapter("select * from purchasepart where billno=" + bno, Common.con);
 Common.StockPurchasePartDataset = new DataSet();

Now lets create a command builder and configure the delete command of the adapter.

Common.cmdbuilder = new System.Data.SqlClient.SqlCommandBuilder(Common.StockPurchase_Part);
 Common.StockPurchase_Part.DeleteCommand = Common.cmdbuilder.GetDeleteCommand();

Now we  need to mark the rows we want to delete, using the Rows collection. In my example , I have only one rows, which is stored in the 0th position. So my code will look like


Now all I need is just call the Update method of the adapter and it simply works.


Hope you got the technique.

Find rows with Find() in c#

Database programming is easy with Visual Studio.Net projects, it offers a variety of methods to visualize the programming logic. Here in our example, we demonstrate how you can extract specific rows from a TableView, which is the local representation of the SQL Table/Database Table View.

First I need a table view, like

 ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);
 ProductTableAdapter.Fill(ProductTableDataset, "ProductMaster");
 ProductTableView = new DataView(ProductTableDataset.Tables[0]);

Now I am going to find some rows for a specific product in the table view using the Find() method.

taekproduct="Citrizine Tab";

int i = Common.ProductTableView.Find(takeproduct);

First I need to specify the Sort column which is the same column with the value I want to search. Second the product itself.

The Find() returns the row number instead of the row itself which can be used to access the row you want. If it’s 1 you can make sure you succeeded to find the row with takeproduct value otherwise the value will be -1.

Then I extract the tax rate from the table.

 if (i != -1)
 { takecgst = takesgst = takeigst = 0;
 double.TryParse( Common.ProductTableView[i]["cgst"].ToString(),out cgstrate);
 double.TryParse(Common.ProductTableView[i]["sgst"].ToString(), out sgstrate);
 double.TryParse(Common.ProductTableView[i]["igst"].ToString(), out igstrate);}


that’s all I got today.