Tag Archives: .Net

Create aggregate function using lambda expressions on collectionView in C#


Hope you need this article for updating your skills. As we seen in the last article, we learned about collection view and how to create filters using them. If you miss just take a look at it. To I will help you learn som lambda expression

Lambda experession ?

Lambda expressions permit the creation of anonymous functions with a very concise syntax. I don’t want explain what lambda expression is , I will show you how to use it. For learn more about lambda please visit Richard Carr’s lackwaspBlog ,he had a detailed guide to lambda.

The following is an example of lambda expression.

(params) => expression

let’s jump into the regular salesinformation example. The sales Collection used to store all sales information such as Customer,Date,Amount,tax,discount etc. It may contain many of them, that is why we used collection or List.

We are going to use Agrgregate feature of collectionview object to create lambda function, for find total sale amount ,tax collected etc

 var total_saleAmount = Salescolletionview.Cast<SalesView>()
.Aggregate<SalesView, double>(0, (totalSale, s) => totalSales += s.Amount);

The SaleCollectionView class Hold information about Sales which is in the type of ViewClass. So first up all we can cast the view to SalesView and then invoke Aggregate. The Aggregate functionality of C#.Net let us create anonymous function using lambda expression.

At first we had initialise the output variable and access the Amount property of viewclass and then add it up.

In the similar manner you can operate on other properties of view class.

 var total_tax = Salescolletionview.Cast<SalesView>().
Aggregate<SalesView, double>(0, (tax, s) => tax+= s.Tax);

Linq – suming rows,create list of custom fields,create model class object list from data table


Querying data from a data table in .Net is easy with Linq. Linq is a query language which is applicable to all type of objects such as List,Tables etc in .Net . Infact you can simply convert the table data to object like List of generic type or cutom Model class object with Linq operation, in limited sense you can forget loops.

Sum table column

Let’ learn how to simply sum up a column in your data table using Linq.

var comms = (from p in public_members.payroll_posting.AsEnumerable() where p.Field<string>
("eid") == "1001" select p.Field<decimal>("amount")).Sum();

The above linq simply fetch amount from the data table for employee id “1001”. I also place an outer pareanthesis for summing up. The parenthesis come handy when you want to convert the values you selected to Arrays,List or perform aggragate functions like Sum,Count etct

Create List of rows with custom list

LINQ – CREATE LIST OF OBJECTS FROM A DATA TABLE
 var comm_list = (from p in posting.AsEnumerable() where  p.Field<string>("type") == 
"Commission" select new { amount = p.Field<decimal>("amount"), eno = p.Field<int>("pp_no"),
 edate = p.Field<DateTime>("post_date") }).ToList();

I have a table in which I store all employee payments under seperate types. I just want to create a list of commission paid to the employees as new list with custom column which is not exist in the data table .

The select new will create a model class like fields.

Create a list of objects from Linq query

Create list of Objects[Model class] with Linq

Following LinQ will help to build Model class object list from a data table, remember I used myown Model class which I not provided here. Use your own

 var payrolls = (from pp in payroll_entries.AsEnumerable()
                                  join emp in employees.AsEnumerable() on pp.Field<string>("eid") equals emp.Field<string>("eid")
                                  select new
                                  {
                                      p = new PayRollEntryModel()
                                      {
                                          VNO = pp.Field<int>("pe_no").ToString(),
                                          DATE = pp.Field<DateTime>("pe_date"),
                                          Employee = LedgeName(emp.Field<int>("lid")),
                                          EID = Convert.ToInt32(pp.Field<string>("eid")),
                                          CashAccount = LedgeName(pp.Field<int>("crledger")),
                                          Total = string.Format("{0:0.00}", pp.Field<decimal>("amount")),
                                          CrACID = Convert.ToInt32( pp.Field<int>("cash_ac")),
                                          Narration = pp.Field<string>("narration"),
                                          DrAcid = Convert.ToInt32( emp.Field<int>("lid")),                                         
                                          Allownaces=pp.Field<string>("allowances"),
                                          Commission=pp.Field<string>("comm"),
                                          Advance=pp.Field<string>("advance"),
                                          Basic=pp.Field<string>("bp"),
                                          Deductions=pp.Field<string>("deductions"),
                                          WDs= pp.Field<decimal>("wdays").ToString() ,
                                          WHs=  pp.Field<decimal>("whours").ToString(),                                       


                                      }
                                  }.p).ToList<PayRollEntryModel>();

At the 26th line of the above code I used .p because the linq only create a list of p which is a anonimous type. I want is a list of the Model class object which is PayRollEntryModel.

I think this will help somebody out there… leave comment and share your thoughts

All the examples I demonstrated here is part of my personal project and I should say these are working very well.

Advertisements

Change location of control at runtime in C#


Beginners of C# who were familiar with VB6 may try to move the control using left and top properties of the control. It works fine with the Visual Basic 6. In Visual Studio the concept is changed with Location.

A location has two coordinates, namely x and y, similar to Top and Left in VB6. A location is an object of class Point. So we can change the location of a control using a new point object.

In the following, I have a panel, which I want to move at the run time to another part of the window.

stockpanel.Location = new Point(250, 5);

That’s it.

How to use dataView rowfilter for searching rows in C#.Net


You can simply use SQL query statements to retrieve desired data from database server with C# and ADO.Net. But there are plenty of ways to search your localized version of data which is stored in dataSet and dataView.

We already learn how to use Find and Findrow methods for searching data in Visual Studio App. This post will show how to use row filter property of  DataView for sorting data rows.

  1. First up all you need to create Data adaptor, dataSet, and dataView.
  2. In our example, I have a date field in the database table, so that I can filter for specific date ranges as follows
 Common.AccountReportDV.RowFilter = "invoicedate>='" + dateTimePicker1.Value.ToShortDateString() + "' and invoicedate<='" + dateTimePicker2.Value.ToShortDateString() + "'";

Here common is a public static class where kept the Database functions. The above statement will be looking for data between the two dates.

You have to use Common.AccountReportDV and not Common.AccountReportDV.Table.rows  for retrieving the result as follows.

foreach (DataRowView row in Common.AccountReportDV)
 { 

console.WriteLine(row["<column name here>"]);

 }

DataRowView is a special class which helps to access data rows in a DataView object.

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.

 

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.