|
As you may know already, the DataTable class provides its own mechanism for selecting its records and storing them in an array of DataRows. This is done using the Select() method. Here is an example: using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;
public class MusicStore : Form
{
DataSet dsMusicStore;
DataTable tblStoreItem;
private ColumnHeader colItemCode;
private ColumnHeader colCategory;
private ColumnHeader colItemName;
private ColumnHeader colUnitPrice;
ListView lvwStoreItems;
public MusicStore()
{
InitializeComponent();
}
void InitializeComponent()
{
lvwStoreItems = new ListView();
lvwStoreItems.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
lvwStoreItems.FullRowSelect = true;
lvwStoreItems.GridLines = true;
lvwStoreItems.Location = new Point(12, 12);
lvwStoreItems.Size = new System.Drawing.Size(530, 192);
lvwStoreItems.View = View.Details;
colItemCode = new ColumnHeader();
colItemCode.Text = "Item #";
colItemCode.Width = 50;
lvwStoreItems.Columns.Add(colItemCode);
colCategory = new ColumnHeader();
colCategory.Text = "Category";
colCategory.Width = 105;
lvwStoreItems.Columns.Add(colCategory);
colItemName = new ColumnHeader();
colItemName.Text = "Item Name/Description";
colItemName.Width = 310;
lvwStoreItems.Columns.Add(colItemName);
colUnitPrice = new ColumnHeader();
colUnitPrice.Text = "Unit Price";
colUnitPrice.TextAlign = HorizontalAlignment.Right;
lvwStoreItems.Columns.Add(colUnitPrice);
tblStoreItem = new DataTable("StoreItem");
tblStoreItem.Columns.Add("ItemNumber", Type.GetType("System.UInt32"));
tblStoreItem.Columns.Add("Category", Type.GetType("System.String"));
tblStoreItem.Columns.Add("ItemName", Type.GetType("System.String"));
tblStoreItem.Columns.Add("UnitPrice", Type.GetType("System.Decimal"));
dsMusicStore = new DataSet("StoreItems");
dsMusicStore.Tables.Add(tblStoreItem);
Text = "Music Store";
MaximizeBox = false;
Size = new System.Drawing.Size(562, 243);
Controls.Add(lvwStoreItems);
Load += new EventHandler(MusicStoreLoad);
}
private void MusicStoreLoad(object sender, EventArgs e)
{
Directory.CreateDirectory(@"C:\Music Store");
DataRow rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 925173;
rowStoreItem["Category"] = "Acoustic Guitars";
rowStoreItem["ItemName"] = "Yamaha F335 Acoustic Guitar";
rowStoreItem["UnitPrice"] = 129.85;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 274930;
rowStoreItem["Category"] = "Drums";
rowStoreItem["ItemName"] = "Simmons SD5X Electronic Drum Set";
rowStoreItem["UnitPrice"] = 395.85;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 927974;
rowStoreItem["ItemName"] = "TASCAM DP-24 24-Track Digital Portastudio";
rowStoreItem["UnitPrice"] = 595.55;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 402884;
rowStoreItem["Category"] = "Synthesizers";
rowStoreItem["ItemName"] = "Roland JUNO-Gi Synthesizer";
rowStoreItem["UnitPrice"] = 795.85;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 402884;
rowStoreItem["ItemName"] = "ART SLA1 Studio Power Amplifier";
rowStoreItem["UnitPrice"] = 265.55;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 974951;
rowStoreItem["Category"] = "Microphone Cables";
rowStoreItem["ItemName"] = "Live Wire Advantage Standard EXM Series Microphone Cable";
rowStoreItem["UnitPrice"] = 14.95;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 208148;
rowStoreItem["Category"] = "Electric Bass";
rowStoreItem["ItemName"] = "Fender Modern Player Jazz Electric Bass Guitar";
rowStoreItem["UnitPrice"] = 495.95;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 802840;
rowStoreItem["Category"] = "Acoustic Guitars";
rowStoreItem["ItemName"] = "Taylor Big Baby Taylor Acoustic Guitar";
rowStoreItem["UnitPrice"] = 449.95;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 724051;
rowStoreItem["Category"] = "Drums";
rowStoreItem["ItemName"] = "Gretsch Drums Energy 5-Piece Drum Set";
rowStoreItem["UnitPrice"] = 695.65;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 524037;
rowStoreItem["Category"] = "Electric Bass";
rowStoreItem["ItemName"] = "Fender Deluxe P Bass Special 4-String Bass";
rowStoreItem["UnitPrice"] = 699.95;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem["ItemNumber"] = 274703;
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["Category"] = "Synthesizers";
rowStoreItem["ItemName"] = "Access Virus TI v2 Keyboard";
rowStoreItem["UnitPrice"] = 2992.25;
tblStoreItem.Rows.Add(rowStoreItem);
rowStoreItem = tblStoreItem.NewRow();
rowStoreItem["ItemNumber"] = 408408;
rowStoreItem["Category"] = "Speakers";
rowStoreItem["ItemName"] = "Behringer EUROLIVE B210D Active PA Speaker System";
rowStoreItem["UnitPrice"] = 195.95;
tblStoreItem.Rows.Add(rowStoreItem);
dsMusicStore.WriteXml(@"C:\Music Store\StoreItems.xml");
DataRow[] rows = dsMusicStore.Tables[0].Select();
foreach (var item in rows)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
[STAThread]
public static int Main()
{
System.Windows.Forms.Application.Run(new MusicStore());
return 0;
}
}
One of the members of the DataTableExtensions class is AsEnumerable. The syntax of the DataTableExtensions.AsEnumerable() method is: public static EnumerableRowCollection<DataRow> AsEnumerable(this DataTable source); As you can see, this method takes no argument. It is called by a DataTable object and it returns a set of DataRow records as an IEnumerable collection. Once you have gotten that collection, you can use it in LINQ. Here is an example: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace DataSetLINQ { public partial class Exercise : Form { public Exercise() { InitializeComponent(); } private void Exercise_Load(object sender, EventArgs e) { dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); IEnumerable
The DataTable class supports query conditions both in the LINQ and the IEnumerable interface. When formulating the where condition, to locate a DataColumn column, apply its name as a string to the value holder, then compare its value to the desired criterion. Here is an example: using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace DataSetLINQ
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
IEnumerable<DataRow> storeItems = from recStoreItems
in dsStoreItems.Tables[0].AsEnumerable()
where recStoreItems["Category"].ToString() == "Acoustic Guitars"
select recStoreItems;
foreach (var item in storeItems)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
}
}
Remember the rules based on the type of value and use the appropriate operator: ==, <, <=, >, >=, and != . Here is an example: using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace DataSetLINQ
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
IEnumerable<DataRow> storeItems = from recStoreItems
in dsStoreItems.Tables[0].AsEnumerable()
where double.Parse(recStoreItems["UnitPrice"].ToString()) <= 500
select recStoreItems;
foreach (var item in storeItems)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
}
}
This would produce:
Remember that the Enumerable class supports the condition through the Where() method. To use it, first include your select statement in parentheses and create your condition outside. Here is an example: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace DataSetLINQ { public partial class Exercise : Form { public Exercise() { InitializeComponent(); } private void Exercise_Load(object sender, EventArgs e) { dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); IEnumerable<DataRow> storeItems = (from recStoreItems in dsStoreItems.Tables[0].AsEnumerable() select recStoreItems).Where(name => name["ItemName"].ToString().Contains("Guitar")); foreach (var item in storeItems) { ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString()); lviCollection.SubItems.Add(item["Category"].ToString()); lviCollection.SubItems.Add(item["ItemName"].ToString()); lviCollection.SubItems.Add(item["UnitPrice"].ToString()); lvwStoreItems.Items.Add(lviCollection); } } } } To negate a condition, you can precede the where expression by the ! operator.
You can arrange the records of a data table using either LINQ or the Enumerable class. Once you have gotten the records using the AsEnumerable() method, you can apply the orderdy operator to your select statement. Here is an example: using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace DataSetLINQ
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
IEnumerable<DataRow> storeItems = from recStoreItems
in dsStoreItems.Tables[0].AsEnumerable()
orderby recStoreItems["ItemName"].ToString()
select recStoreItems;
foreach (var item in storeItems)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
}
}
This would produce:
To arrange the records using the Enumerable class, create your select statement and call the OrderBy() method on it. Here is an example: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace DataSetLINQ { public partial class Exercise : Form { public Exercise() { InitializeComponent(); } private void Exercise_Load(object sender, EventArgs e) { dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); IEnumerable<DataRow> storeItems = (from recStoreItems in dsStoreItems.Tables[0].AsEnumerable() select recStoreItems).OrderBy(item => item["UnitPrice"]); foreach (var item in storeItems) { ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString()); lviCollection.SubItems.Add(item["Category"].ToString()); lviCollection.SubItems.Add(item["ItemName"].ToString()); lviCollection.SubItems.Add(item["UnitPrice"].ToString()); lvwStoreItems.Items.Add(lviCollection); } } } } To arrange the result in reverse order, call the OrderByDescending() method.
A logical conjunction is created in a where statement as an addition of two conditions combined using the && operator. Here is an example: using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace DataSetLINQ
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
IEnumerable<DataRow> storeItems = from recStoreItems
in dsStoreItems.Tables[0].AsEnumerable()
where (recStoreItems["Category"].ToString() == "Electric Bass") && (double.Parse(recStoreItems["UnitPrice"].ToString()) <= 500)
select recStoreItems;
foreach (var item in storeItems)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
}
}
This would produce:
Remember that, to negate a conjunction, precede it with a ! operator. A logical disjunction is done using the || operator. Here is an example: using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace DataSetLINQ
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
IEnumerable<DataRow> storeItems = from recStoreItems
in dsStoreItems.Tables[0].AsEnumerable()
where (recStoreItems["Category"].ToString() == "Acoustic Guitars") || (recStoreItems["Category"].ToString() == "Electric Guitars")
select recStoreItems;
foreach (var item in storeItems)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
}
}
This would produce:
The LINQ provides the let keyword that allows you to select records from a data table. The simplest way to use the let operator is to produce all the records from the data table. To do this, declare a variable using let in your query, assign the ValueHolder to the variable, and select that let variable. Outside the query, access each table column using the period operator applied to the foreach variable. Here is an example: private void MusicStoreLoad(object sender, EventArgs e)
{
DataSet dsStoreItems = new DataSet("StoreItems");
dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
IEnumerable<DataRow> storeItems = from recStoreItems
in dsStoreItems.Tables[0].AsEnumerable()
let acousticGuitars = recStoreItems
select recStoreItems;
foreach (var item in storeItems)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
This would produce:
In reality, the let operator allows you to declare a local variable in your query and use that variable any way you want. For example, you can assign a table column to the let variable, then select that variable and access it outside as the returned value of the query. Here is an example: using System; using System.IO; using System.Data; using System.Linq; using System.Drawing; using System.Windows.Forms; using System.Collections.Generic; public class MusicStore : Form { private ListBox lbxMusicStore; public MusicStore() { InitializeComponent(); } void InitializeComponent() { lbxMusicStore = new ListBox(); lbxMusicStore.Location = new Point(12, 12); lbxMusicStore.Size = new System.Drawing.Size(305, 170); Text = "Music Store"; MaximizeBox = false; Size = new System.Drawing.Size(340, 214); Controls.Add(lbxMusicStore); Load += new EventHandler(MusicStoreLoad); } private void MusicStoreLoad(object sender, EventArgs e) { DataSet dsStoreItems = new DataSet("StoreItems"); dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); var storeItems = from recStoreItems in dsStoreItems.Tables[0].AsEnumerable() let name = recStoreItems["ItemName"] select name; foreach (var item in storeItems) lbxMusicStore.Items.Add(item); } [STAThread] public static int Main() { System.Windows.Forms.Application.Run(new MusicStore()); return 0; } } This would produce:
In the same way, you can create an expression made of constant values, some columns of the table, or a combination of columns and constains. When you exit the query, you can select that let variable. Here is an example: using System; using System.IO; using System.Data; using System.Linq; using System.Drawing; using System.Windows.Forms; using System.Collections.Generic; public class MusicStore : Form { private ListBox lbxMusicStore; public MusicStore() { InitializeComponent(); } void InitializeComponent() { lbxMusicStore = new ListBox(); lbxMusicStore.Location = new Point(12, 12); lbxMusicStore.Size = new System.Drawing.Size(335, 170); Text = "Music Store"; MaximizeBox = false; Size = new System.Drawing.Size(365, 214); Controls.Add(lbxMusicStore); Load += new EventHandler(MusicStoreLoad); } private void MusicStoreLoad(object sender, EventArgs e) { DataSet dsStoreItems = new DataSet("StoreItems"); dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); var storeItems = from recStoreItems in dsStoreItems.Tables[0].AsEnumerable() let description = string.Concat(recStoreItems["ItemName"], ", ", recStoreItems["UnitPrice"]) select description; foreach (var item in storeItems) lbxMusicStore.Items.Add(item); } [STAThread] public static int Main() { System.Windows.Forms.Application.Run(new MusicStore()); return 0; } } This would produce:
In the body of the query, you can declare many variables, each with its own let keyword. Still inside the query, you can use the variable as you see fit. At the end, select only one of the variables and use it outside of the query. If necessary, you can add a where condition to your query, before the let section(s).
As opposed to the let operator that can produce only one variable from a query, the LINQ provides the new keyword that allows you to create as many names as you want in your query and access those names outside the query. To do this, create a select new {} section at the end your query. Inside the curly brackets, define each name and assign a value to it. The name can be any, as lonog as it follows the rules of names in C#. Separate each expression (the name and its assignment) with commas. As seen for the let operator, a value can be a constant or the value of a table column accessed from the ValueHolder. Outside the query, you can access each new name from he foreach variable. Here is an example: using System; using System.IO; using System.Data; using System.Linq; using System.Drawing; using System.Windows.Forms; using System.Collections.Generic; public class MusicStore : Form { DataSet dsMusicStore; DataTable tblStoreItem; private ColumnHeader colItemCode; private ColumnHeader colCategory; private ColumnHeader colItemName; private ColumnHeader colUnitPrice; ListView lvwStoreItems; public MusicStore() { InitializeComponent(); } void InitializeComponent() { lvwStoreItems = new ListView(); lvwStoreItems.Anchor = AnchorStyles.Left | AnchorStyles.Top | AnchorStyles.Right | AnchorStyles.Bottom; lvwStoreItems.FullRowSelect = true; lvwStoreItems.GridLines = true; lvwStoreItems.Location = new Point(12, 12); lvwStoreItems.Size = new System.Drawing.Size(530, 192); lvwStoreItems.View = View.Details; colItemCode = new ColumnHeader(); colItemCode.Text = "Item #"; colItemCode.Width = 50; lvwStoreItems.Columns.Add(colItemCode); colCategory = new ColumnHeader(); colCategory.Text = "Category"; colCategory.Width = 105; lvwStoreItems.Columns.Add(colCategory); colItemName = new ColumnHeader(); colItemName.Text = "Item Name/Description"; colItemName.Width = 310; lvwStoreItems.Columns.Add(colItemName); colUnitPrice = new ColumnHeader(); colUnitPrice.Text = "Unit Price"; colUnitPrice.TextAlign = HorizontalAlignment.Right; lvwStoreItems.Columns.Add(colUnitPrice); tblStoreItem = new DataTable("StoreItem"); tblStoreItem.Columns.Add("ItemNumber", Type.GetType("System.UInt32")); tblStoreItem.Columns.Add("Category", Type.GetType("System.String")); tblStoreItem.Columns.Add("ItemName", Type.GetType("System.String")); tblStoreItem.Columns.Add("UnitPrice", Type.GetType("System.Decimal")); dsMusicStore = new DataSet("StoreItems"); dsMusicStore.Tables.Add(tblStoreItem); Text = "Music Store"; MaximizeBox = false; Size = new System.Drawing.Size(562, 244); Controls.Add(lvwStoreItems); Load += new EventHandler(MusicStoreLoad); } private void MusicStoreLoad(object sender, EventArgs e) { DataSet dsStoreItems = new DataSet("StoreItems"); dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); var storeItems = from recStoreItems in dsStoreItems.Tables[0].AsEnumerable() select new { Code = recStoreItems["ItemNumber"], Type = recStoreItems["Category"], Name = recStoreItems["ItemName"], Price = recStoreItems["UnitPrice"], }; foreach (var item in storeItems) { ListViewItem lviCollection = new ListViewItem(item.Code.ToString()); lviCollection.SubItems.Add(item.Type.ToString()); lviCollection.SubItems.Add(item.Name.ToString()); lviCollection.SubItems.Add(item.Price.ToString()); lvwStoreItems.Items.Add(lviCollection); } } [STAThread] public static int Main() { System.Windows.Forms.Application.Run(new MusicStore()); return 0; } } Remember that you can combine the values of columns and assign such an expression to a new name.
The LINQ provides a rich collection of methods to perform various types of operations, including calculations on numeric values. For example, if your table has a column with numbers, you can find the minimum, the maximum, the sum, or the average of the number. To get the value, create a a query that produces the values of the number-based column and call the Average() method on the returned value. Here are examples: using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.IO; namespace DataSetLINQ { public partial class Exercise : Form { public Exercise() { InitializeComponent(); } private void Exercise_Load(object sender, EventArgs e) { DataSet dsStoreItems = new DataSet("StoreItems"); dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); DataRow[] rows = dsStoreItems.Tables[0].Select(); foreach (var item in rows) { ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString()); lviCollection.SubItems.Add(item["Category"].ToString()); lviCollection.SubItems.Add(item["ItemName"].ToString()); lviCollection.SubItems.Add(item["UnitPrice"].ToString()); lvwStoreItems.Items.Add(lviCollection); } IEnumerable<double> prices = from storeItems in dsStoreItems.Tables[0].AsEnumerable() let price = double.Parse(storeItems["UnitPrice"].ToString()) select price; txtMinimum.Text = prices.Min().ToString(); txtMaximum.Text = prices.Max().ToString(); double avgPrice = (from storeItems in dsStoreItems.Tables[0].AsEnumerable() let price = double.Parse(storeItems["UnitPrice"].ToString()) select price).Average(); txtAverage.Text = avgPrice.ToString(); } } }
One way you can present the records of a table is to display them in groups. In LINQ, this is done using the group ... by operation. To do this, type the group keyword followed the ValueHolder and by. Then specify the column by which the results should be grouped. To present the results, start with a foreach loop that is based on the from variable. Nest a foreach loop that is based on the first foreach variable. Here is an example: using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Drawing;
using System.Windows.Forms;
using System.Collections.Generic;
public class MusicStore : Form
{
private ColumnHeader colItemCode;
private ColumnHeader colCategory;
private ColumnHeader colItemName;
private ColumnHeader colUnitPrice;
ListView lvwStoreItems;
public MusicStore()
{
InitializeComponent();
}
void InitializeComponent()
{
lvwStoreItems = new ListView();
lvwStoreItems.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
lvwStoreItems.FullRowSelect = true;
lvwStoreItems.GridLines = true;
lvwStoreItems.Location = new Point(12, 12);
lvwStoreItems.Size = new System.Drawing.Size(530, 192);
lvwStoreItems.View = View.Details;
colItemCode = new ColumnHeader();
colItemCode.Text = "Item #";
colItemCode.Width = 50;
lvwStoreItems.Columns.Add(colItemCode);
colCategory = new ColumnHeader();
colCategory.Text = "Category";
colCategory.Width = 105;
lvwStoreItems.Columns.Add(colCategory);
colItemName = new ColumnHeader();
colItemName.Text = "Item Name/Description";
colItemName.Width = 310;
lvwStoreItems.Columns.Add(colItemName);
colUnitPrice = new ColumnHeader();
colUnitPrice.Text = "Unit Price";
colUnitPrice.TextAlign = HorizontalAlignment.Right;
lvwStoreItems.Columns.Add(colUnitPrice);
Text = "Music Store";
MaximizeBox = false;
Size = new System.Drawing.Size(562, 244);
Controls.Add(lvwStoreItems);
Load += new EventHandler(MusicStoreLoad);
}
private void MusicStoreLoad(object sender, EventArgs e)
{
DataSet dsStoreItems = new DataSet("StoreItems");
dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
var storeItems = from recStoreItems
in dsStoreItems.Tables[0].AsEnumerable()
group recStoreItems by recStoreItems["Category"];
foreach (var items in storeItems)
{
foreach (var item in items)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
}
[STAThread]
public static int Main()
{
System.Windows.Forms.Application.Run(new MusicStore());
return 0;
}
}
To support grouping, the Enumerable class is equipped with the GroupBy() method that is overloaded with various versions. This method takes one argument as a function. Here is an example of calling it: private void MusicStoreLoad(object sender, EventArgs e)
{
DataSet dsStoreItems = new DataSet("StoreItems");
dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml");
var storeItems = dsStoreItems.Tables[0].AsEnumerable().GroupBy(cat => cat["Category"]);
foreach (var items in storeItems)
{
foreach (var item in items)
{
ListViewItem lviCollection = new ListViewItem(item["ItemNumber"].ToString());
lviCollection.SubItems.Add(item["Category"].ToString());
lviCollection.SubItems.Add(item["ItemName"].ToString());
lviCollection.SubItems.Add(item["UnitPrice"].ToString());
lvwStoreItems.Items.Add(lviCollection);
}
}
}
The GroupBy() method is equipped with a property named Key that you can use to get a list of the values in the column that is used for grouping. Here is an example: private void MusicStoreLoad(object sender, EventArgs e) { DataSet dsStoreItems = new DataSet("StoreItems"); dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); var storeItems = dsStoreItems.Tables[0].AsEnumerable().GroupBy(cat => cat["Category"]); ListBox lbxStoreItems = new ListBox(); lbxStoreItems.Location = new Point(12, 250); Controls.Add(lbxStoreItems); foreach (var item in storeItems) lbxStoreItems.Items.Add(item.Key); } In reality, the GroupBy() method takes two arguments. The first argument is used to specify the column by which the records should be grouped. The second argument is used to list the values of the column in each category. Here is an example of calling the method with both arguments: private void MusicStoreLoad(object sender, EventArgs e) { DataSet dsStoreItems = new DataSet("StoreItems"); dsStoreItems.ReadXml(@"C:\Music Store\StoreItems.xml"); var storeItems = dsStoreItems.Tables[0].AsEnumerable().GroupBy(cat => cat["Category"], name => name["ItemName"]); foreach (var items in storeItems) { foreach (var item in items) { ListViewItem lviCollection = new ListViewItem(items.Key.ToString()); lviCollection.SubItems.Add(""); lviCollection.SubItems.Add(item.ToString()); lviCollection.SubItems.Add(""); lvwStoreItems.Items.Add(lviCollection); } } }
Joining records is the fundamental aspect of relational databases. It is done by getting records from one set related to those records through a common colmun. There are various types of joins.The most common type is called inner join. To support joins, the LINQ provides the join operator.ch others in another set.
|
|
||||||||||||||||||||||||||||||||||||||||||||||||
|
Employee # | First Name | Last Name | Title |
92735 | Jeffrey | Leucart | General Manager |
29268 | Catherine | Rawley | Administrative Assistant |
73948 | Allison | Garlow | Rental Associate |
40508 | David | Stillson | Technician |
24793 | Michelle | Taylor | Accounts Manager |
20480 | Peter | Futterman | Rental Associate |
72084 | Georgia | Rosen | Customer Service Representative |
38240 | Karen | Blackney | Rental Associate |
Tag # | Make | Model | Doors | Passengers | Condition | Category | Availability |
2AM9952 | Ford | Fiesta SE | 4 | 5 | Driveable | Economy | Available |
6AD8274 | Mazda | CX-9 | 4 | 5 | Excellent | Mini Van | Available |
8AG3584 | Toyota | Sienna LE FWD | 4 | 8 | Excellent | Passenger Van | Available |
KER204 | Ford | Focus SF | 4 | 5 | Excellent | Compact | Being Serviced |
3AD9283 | Kia | Rio EX | 4 | 5 | Excellent | Economy | Rented |
8AE9294 | Lincoln | MKT 3.5L | 4 | 5 | Excellent | Full Size | Available |
KLT840 | Ford | E-350 XL | 3 | 15 | Driveable | Passenger Van | Available |
8AL8033 | Toyota | Corolla LE | 4 | 5 | Excellent | Compact | Available |
4AF9284 | Toyota | Tacoma | 2 | 2 | Needs Repair | Pickup Truck | Available |
ADG279 | GMC | Acadia SLE | 4 | 5 | Excellent | SUV | Rented |
1AD8049 | Dodge | Charger SXT | 4 | 5 | Excellent | Standard | Being Serviced |
9MD3974 | Toyota | Sienna LE FWD | 4 | 8 | Driveable | Passenger Van | Rented |
5AJ9274 | BMW | 528i | 4 | 5 | Excellent | Full Size | Available |
GTH295 | Kia | Rio5 | 4 | 5 | Excellent | Economy | Available |
8AT2408 | Mazda | Miata MX-5 | 2 | 2 | Excellent | Compact | Available |
6AP2486 | Fiat | 500 | 2 | 4 | Excellent | Economy | Available |
2AL9485 | Chrysler | 200 | 2 | 2 | Excellent | Compact | Available |
DFP924 | Toyota | Sienna LE FWD | 4 | 8 | Driveable | Passenger Van | Available |
2MD8382 | Toyota | RAV4 I4 4X4 | 4 | 5 | Excellent | SUV | Available |
8AR9374 | Honda | Accord LX | 4 | 5 | Excellent | Standard | Rented |
5MD2084 | Chevrolet | Equinox LS | 4 | 5 | Driveable | Mini Van | Available |
BND927 | Ford | Fiesta SE | 4 | 5 | Driveable | Economy | Available |
6AP2749 | Toyota | Corolla LE | 4 | 5 | Excellent | Compact | Rented |
8AL7394 | Ford | F-250 SD Reg Cab 4X4 | 2 | 2 | Excellent | Pickup Truck | Available |
4MD2840 | Chevrolet | 2500 LS | 3 | 15 | Excellent | Passenger Van | Rented |
G249580 | Nissan | Sentra SR | 4 | 5 | Excellent | Compact | Available |
3AK7397 | Chrysler | 200 | 2 | 2 | Excellent | Compact | Available |
VGT927 | Toyota | Tundra Dbl Cab 4X4 | 2 | 5 | Excellent | Pickup Truck | Available |
2AT9274 | Ford | Focus SF | 4 | 5 | Excellent | Compact | Available |
6AH8429 | Lincoln | MKT 3.5L | 4 | 5 | Needs Repair | Full Size | Available |
8MD9284 | Ford | Escape SE I4 | 4 | 5 | Excellent | Mini Van | Available |
PLD937 | Chevrolet | Imapala LT | 4 | 5 | Excellent | Compact | Being Serviced |
5AK2974 | Fiat | 500 | 2 | 4 | Excellent | Economy | Available |
1MD9284 | Ford | Escape SE I4 | 4 | 5 | Excellent | Mini Van | Being Serviced |
SDG624 | Chevrolet | Volt | 4 | 5 | Excellent | Standard | Available |
2AR9274 | Kia | Rio SX | 4 | 5 | Excellent | Economy | Available |
JWJ814 | Cadillac | CTS-V | 4 | 5 | Excellent | Full Size | Available |
7MD9794 | Ford | Focus SF | 4 | 5 | Excellent | Compact | Rented |
UQW118 | Chevrolet | 2500 LS | 3 | 15 | Needs Repair | Passenger Van | Available |
2MD9247 | Toyota | RAV4 I4 4X4 | 4 | 5 | Excellent | SUV | Available |
Receipt # | RPPB | First Name | Last Name | Address | City | State | ZIP Code | Tag # | Car Condition | Tank Level | Mileage Start | Start Date | Rate Applied | Order Status |
100001 | 20480 | Marcel | Buhler | 6800 Haxell Crt | Alexandria | VA | 22314 | 8AG3584 | Excellent | Empty | 12728 | 7/14/2014 | 69.95 | Vehicle With Customer |
100002 | 24793 | Joan | Altman | 3725 South Dakota Ave NW | Washington | DC | 20012 | KER204 | Good | 3/4 Full | 24715 | 7/18/2014 | 62.95 | Vehicle With Customer |
100003 | 38240 | Thomas | Filder | 4905 Herrenden St | Arlington | VA | 22204 | 8AL8033 | Excellent | Full | 6064 | 7/18/2014 | 34.95 | Vehicle With Customer |
Receipt # to Open | Tank Level | Mileage End | End Date | Total Days | Rate Applied | Sub-Total | Tax Rate | Tax Amount | Order Total | Order Status |
100001 | Half Tank | 13022 | 7/19/2014 | 5 | 69.95 | 349.75 | 7.75% | 27.11 | 376.86 | Rental Order Complete |
100003 | Full | 6229 | 7/21/2014 | 3 | 34.95 | 104.85 | 7.75% | 8.13 | 112.98 | Rental Order Complete |
Receipt # | RPPB | First Name | Last Name | Address | City | State | ZIP Code | Tag # | Car Condition | Tank Level | Mileage Start | Start Date | Rate Applied | Order Status |
100004 | 73948 | Gregory | Strangeman | 5530 Irving St | College Park | MD | 20740 | 2AT9274 | Excellent | 1/2 Tank | 8206 | 7/21/2014 | 28.95 | Vehicle With Customer |
Receipt # to Open | Tank Level | Mileage End | End Date | Total Days | Rate Applied | Sub-Total | Tax Rate | Tax Amount | Order Total | Order Status |
100002 | Full | 25694 | 7/22/2014 | 4 | 62.95 | 251.8 | 7.75% | 19.39 | 271.19 | Rental Order Complete |
Receipt # | RPPB | First Name | Last Name | Address | City | State | ZIP Code | Tag # | Car Condition | Tank Level | Mileage Start | Start Date | Rate Applied | Order Status |
100005 | 38240 | Michelle | Russell | 10070 Weatherwood Drv | Rockville | MD | 20853 | 8AE9294 | Excellent | Full | 3659 | 7/22/2014 | 38.95 | Vehicle With Customer |
Receipt # to Open | Tank Level | Mileage End | End Date | Total Days | Rate Applied | Sub-Total | Tax Rate | Tax Amount | Order Total | Order Status |
100005 | Full | 3806 | 7/23/2014 | 1 | 38.95 | 38.95 | 7.75% | 3.00 | 41.95 | Rental Order Complete |
100004 | 3/4 Full | 8412 | 7/25/2014 | 2 | 28.95 | 57.9 | 7.75% | 4.46 | 62.36 | Rental Order Complete |