A Vendor Balances application that uses LINQ
The next three topics of this chapter present a simple application that uses a LINQ query to display vendor and invoice information on a form. This will help you see how you can use a query from within a C# application.
The user interface for the application
Figure 11-14 shows the user interface for the Vendor Balances application. As you can see, this interface consists of a single form that lists the balance due for each vendor that has a balance due. This list is sorted by balance due in descending sequence.
The list in this form is displayed in a ListView control. If you aren't familiar with this control, you may want to refer to Visual Studio help to find out how it works. For the purposes of this application, though, you just need to set the View property of this control to Details, and you need to define the column headings as described in this figure. In addition, you need to know how to load data into the control as shown in the next figure.
The classes used by the application
Figure 11-15 also summarizes the classes used by this application. As you can see, the Invoice class represents a single invoice in the Invoices table, and the Vendor class represents a single vendor in the Vendors table. Then, the InvoiceDB class contains a single method named GetInvoices that retrieves all the invoices from the Invoices table and returns them as a List<Invoice> object. Similarly, the VendorDB class contains a single method named GetVendors that retrieves all the vendors from the Vendors table and returns them as a List<Vendor> object. Finally, the PayablesDB class contains a method named GetConnection that returns a connection to the Payables database.
All of these classes are stored in a class library named PayablesData. Because you saw classes like these in the previous section of this book, I won't show you the code for these classes here. Instead, I'll just present the code for the form so you can see the query that's used by this application.
The Vendor Balances form
Classes used by the application
Description
Figure 11-14 A Vendor Balances application that uses LINQ
The code for the form
Figure 11-15 shows the code for the Vendor Balances form. All of this code is placed within the Load event handler for the form so the list is displayed when the form is loaded. To start, this code declares the variables that will store the lists of vendors and invoices. Then, it uses the methods of the InvoiceDB and VendorDB classes to load data into these lists. The next statement defines the query expression. Because this expression is similar to others you've seen in this chapter, you shouldn't have any trouble understanding how it works. So I'll just summarize it for you.
First, notice that the query expression joins data from the invoice and vendor lists. That's necessary because the vendor name will be displayed on the form along with the balance due. Second, notice that the invoices are grouped by vendor using the Name property of each vendor. Then, within the where clause, the Sum method is used to calculate the balance due for each vendor so the elements that are returned are restricted to vendors who have a balance. The Sum method is also used in the orderby clause to sort the list by the balance due so that the largest balances are displayed first. And it's used in the select clause to include the balance due in the query results along with the vendor name.
To load data into the ListView control, this code uses a foreach statement that loops through the query results. But first, this code checks that at least one element was returned by the query. If not, it displays a message indicating that all invoices are paid in full. Otherwise, it declares a variable named i that will be used as an index for the items that are added to the ListView control.
For each element in the query results, the foreach loop starts by adding the Name property to the Items collection of the ListView control. That causes the name to be displayed in the first column of the control. Then, the next statement adds the Due property as a subitem of the item that was just added. That causes this value to be displayed in the column following the vendor name column. Notice that this statement refers to the item by its index. Then, the last statement in the loop increments the index variable.
The code for the Vendor Balances form
public partial class Form1 : Form
{
private void Form1_Load(object sender, EventArgs e)
List<Invoice> invoiceList = null;
List<Vendor> vendorList = null;
try
invoiceList = InvoiceDB.GetInvoices();
vendorList = VendorDB.GetVendors();
var vendorsDue =
from invoice in invoiceList
join vendor in vendorList
on invoice.VendorID equals vendor.VendorID
group invoice by vendor.Name into invoiceGroup
where invoiceGroup.Sum(i => i.BalanceDue) > 0
orderby invoiceGroup.Sum(i => i.BalanceDue)
descending
select new
Name = invoiceGroup.Key,
Due = invoiceGroup.Sum(i => i.BalanceDue)
};
if (vendorsDue.Count() > 0)
int i = 0;
foreach (var vendor in vendorsDue)
lvVendorsDue.Items.Add(vendor.Name);
lvVendorsDue.Items[i].SubItems.Add(
vendor.Due.ToString("c"));
i += 1;
}
else
MessageBox.Show("All invoices are paid in full.",
"No Balances Due");
this.Close();
catch (Exception ex)
MessageBox.Show(ex.Message, ex.GetType().ToString());
Figure 11-15 The code for the Vendor Balances form