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)
{
 autotext.Add(row["name"].ToString());
}

• 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.

 

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

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();
 Common.StockPurchase_Part.Fill(Common.StockPurchasePartDataset);

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

 Common.StockPurchasePartDataset.Tables[0].Rows[0].Delete();

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

Common.StockPurchase_Part.Update(Common.StockPurchasePartDataset.Tables[0]);

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";

Common.ProductTableView.Sort="product";
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.

 

 

How to Programtically add a listview control in C#

In C# users can add controls with its rich GUI interface, for advanced level programming needs it is good to do the task using some code.

Read more

How to Programtically add a listview control in C#

In C# users can add controls with its rich GUI interface, for advance level programming needs it is good to do the task using some code.

Read more

Inserting Data rows with Parameters and Stored procedure in C# and MS SQL Server

In C# you can do your SQL things in many ways, one of the safest and fastest method in execute SQL commands from SQL server itself. Suppose you want to new row to Product table,

1. First you need to setup SqlDataAdapter
2. Create Parameter Objects
3. Create SQL Procedure with Parameters

Lets do one by one

SqlDataAdapter

con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings
["MACCon"].ConnectionString;
 con.Open();
 ProductTableAdapter = new SqlDataAdapter("select * from productMaster", con);

We are now accessed the Product table with SqlDataAdapter and ready to create Parameter sets.

Setting up Parameters with values

ProductTableAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
 ProductTableAdapter.SelectCommand.CommandText = "CRUDS_PRODUCTMASTER";
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@Item", cmb_item.Text.ToString().ToUpper()));
 ProductTableAdapter.SelectCommand.Parameters.Add(new SqlParameter("@mfr", cmb_mfr.Text.ToString().ToUpper())); Common.ProductTableAdapter.SelectCommand.ExecuteScalar();

with the SqlParameter  we can pass values to the SQL Procedure that we are going to create in next step. The commandType of stored procedure indicate the it is StoredProcedure. Now all we need is a Procedure.

Design your  SQL Stored Procedure

Open you SQL Server Explorer or SQL Server Itself and expand Procedure node and drop the following line of code.

ALTER PROCEDURE dbo.CRUDS_PRODUCTMASTER
 (
 @item nchar(10),
 @mfr nchar(10),
 )
 AS
 Begin
 insert into productmaster  (item,mfr) values(@item,@mfr,);
 End

The parameters in C# and SQL should match otherwise it will cause errors.