object GetSalesByProduct(NORTHWNDDataSet ds)
{
var products = ds.Products;
var details = ds.Order_Details;
var categories = ds.Categories;
var salesByProduct =
from p in products.AsLive()
join c in categories.AsLive()
on p.CategoryID equals c.CategoryID
join d in details.AsLive()
on p.ProductID equals d.ProductID
into sales
let productSales = new ProductInfo()
{
ProductName = p.ProductName,
CategoryName = c.CategoryName,
TotalSales = sales.Sum(
d => d.UnitPrice * d.Quantity *
(decimal)(1f - d.Discount))
}
orderby productSales.TotalSales descending
select productSales;
return(salesByProduct);
}
object GetSalesByCategory(NORTHWNDDataSet ds)
{
var products = ds.Products;
var details = ds.Order_Details;
var categories = ds.Categories;
var salesByCategory =
(from p in products.AsLive()
join c in categories.AsLive()
on p.CategoryID equals c.CategoryID
join d in details.AsLive()
on p.ProductID equals d.ProductID
let detail = new
{
CategoryName = c.CategoryName,
SaleAmount = d.UnitPrice * d.Quantity
* (decimal)(1f - d.Discount)
}
group detail
by detail.CategoryName into categorySales
let total = categorySales.Sum(x => x.SaleAmount)
orderby total descending
select new
{
CategoryName = categorySales.Key,
TotalSales = total
}).AsDynamic();
return salesByCategory;
}
object GetSalesByCategory(NORTHWNDDataSet ds)
{
var products = ds.Products;
var details = ds.Order_Details;
var categories = ds.Categories;
var salesByCategory =
(from p in products.AsLive()
join c in categories.AsLive()
on p.CategoryID equals c.CategoryID
join d in details.AsLive()
on p.ProductID equals d.ProductID
let detail = new
{
CategoryName = c.CategoryName,
SaleAmount = d.UnitPrice * d.Quantity
* (decimal)(1f - d.Discount)
}
group detail
by detail.CategoryName into categorySales
let total = categorySales.Sum(x => x.SaleAmount)
orderby total descending
select new
{
CategoryName = categorySales.Key,
TotalSales = total
}).AsDynamic();
return(salesByCategory);
}
object GetSalesByProduct(NORTHWNDDataSet ds)
{
var products = ds.Products;
var details = ds.Order_Details;
var categories = ds.Categories;
var salesByProduct =
from p in products.AsLive()
join c in categories.AsLive()
on p.CategoryID equals c.CategoryID
join d in details.AsLive()
on p.ProductID equals d.ProductID
into sales
let productSales = new ProductInfo()
{
ProductName = p.ProductName,
CategoryName = c.CategoryName,
TotalSales = sales.Sum(
d => d.UnitPrice * d.Quantity *
(decimal)(1f - d.Discount))
}
orderby productSales.TotalSales descending
select productSales;
return salesByProduct;
}
public override global::System.Data.DataSet Clone()
{
NORTHWNDDataSet cln = ((NORTHWNDDataSet)(base.Clone()));
cln.InitVars();
cln.SchemaSerializationMode = this.SchemaSerializationMode;
return(cln);
}
NORTHWNDDataSet GetData()
{
NORTHWNDDataSet ds = new NORTHWNDDataSet();
new NORTHWNDDataSetTableAdapters.ProductsTableAdapter()
.Fill(ds.Products);
new NORTHWNDDataSetTableAdapters.CategoriesTableAdapter()
.Fill(ds.Categories);
return(ds);
}
NORTHWNDDataSet GetData()
{
var ds = new NORTHWNDDataSet();
new NORTHWNDDataSetTableAdapters.ProductsTableAdapter()
.Fill(ds.Products);
new NORTHWNDDataSetTableAdapters.Order_DetailsTableAdapter()
.Fill(ds.Order_Details);
new NORTHWNDDataSetTableAdapters.CategoriesTableAdapter()
.Fill(ds.Categories);
return ds;
}
NORTHWNDDataSet GetData()
{
var ds = new NORTHWNDDataSet();
new NORTHWNDDataSetTableAdapters.ProductsTableAdapter()
.Fill(ds.Products);
new NORTHWNDDataSetTableAdapters.Order_DetailsTableAdapter()
.Fill(ds.Order_Details);
new NORTHWNDDataSetTableAdapters.CategoriesTableAdapter()
.Fill(ds.Categories);
return(ds);
}
public static global::System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(global::System.Xml.Schema.XmlSchemaSet xs)
{
NORTHWNDDataSet ds = new NORTHWNDDataSet();
global::System.Xml.Schema.XmlSchemaComplexType type = new global::System.Xml.Schema.XmlSchemaComplexType();
global::System.Xml.Schema.XmlSchemaSequence sequence = new global::System.Xml.Schema.XmlSchemaSequence();
global::System.Xml.Schema.XmlSchemaAny any = new global::System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
global::System.Xml.Schema.XmlSchema dsSchema = ds.GetSchemaSerializable();
if (xs.Contains(dsSchema.TargetNamespace))
{
global::System.IO.MemoryStream s1 = new global::System.IO.MemoryStream();
global::System.IO.MemoryStream s2 = new global::System.IO.MemoryStream();
try {
global::System.Xml.Schema.XmlSchema schema = null;
dsSchema.Write(s1);
for (global::System.Collections.IEnumerator schemas = xs.Schemas(dsSchema.TargetNamespace).GetEnumerator(); schemas.MoveNext();)
{
schema = ((global::System.Xml.Schema.XmlSchema)(schemas.Current));
s2.SetLength(0);
schema.Write(s2);
if ((s1.Length == s2.Length))
{
s1.Position = 0;
s2.Position = 0;
for (; ((s1.Position != s1.Length) &&
(s1.ReadByte() == s2.ReadByte()));)
{
;
}
if ((s1.Position == s1.Length))
{
return(type);
}
}
}
}
finally {
if ((s1 != null))
{
s1.Close();
}
if ((s2 != null))
{
s2.Close();
}
}
}
xs.Add(dsSchema);
return(type);
}
public Form1()
{
InitializeComponent();
// get the data
NORTHWNDDataSet ds = GetData();
// create a live views to update order details
this.dataGridView1.DataSource = GetOrderDetails(ds);
// create live views that provide up-to-date order information
this.dataGridView2.DataSource = GetSalesByCategory(ds);
this.dataGridView3.DataSource = GetSalesByProduct(ds);
}
static void Main(string[] args)
{
NORTHWNDDataSetTableAdapters.EmployeesTableAdapter ta = new NORTHWNDDataSetTableAdapters.EmployeesTableAdapter();
NORTHWNDDataSet ds = new NORTHWNDDataSet();
NORTHWNDDataSet.EmployeesDataTable dt = new NORTHWNDDataSet.EmployeesDataTable();
ta.Fill(dt);
var q1 = from emp in dt
where emp.FirstName.StartsWith("M")
select new { nom = emp.LastName };
foreach (var s in q1)
{
Console.WriteLine(s.nom);
}
SalarieDataContext db = new SalarieDataContext();
var q2 = from emp in db.Employees
where emp.FirstName.StartsWith("M")
select emp;
foreach (var s in q2)
{
Console.WriteLine(s.FirstName);
}
var q3 = from emp in db.Employees
select new XElement("Employe",
new XElement("Nom", emp.LastName),
new XElement("Prénom", emp.FirstName),
new XElement("DateNaissance", emp.BirthDate));
XElement racine = new XElement("Employes", q3);
Console.WriteLine(racine.ToString());
var q4 = from emp in racine.Elements("Employe")
where emp.Element("Prénom").Value.StartsWith("N")
select emp.Element("Nom").Value;
foreach (var s in q4)
{
Console.WriteLine(s);
}
Console.ReadKey();
}
object GetOrderDetails(NORTHWNDDataSet ds)
{
var products = ds.Products;
var details = ds.Order_Details;
var categories = ds.Categories;
var orderDetails =
(from d in details.AsLive().AsUpdatable()
join p in products.AsLive()
on d.ProductID equals p.ProductID
join c in categories.AsLive()
on p.CategoryID equals c.CategoryID
select new
{
c.CategoryName,
p.ProductName,
d.UnitPrice,
d.Quantity,
d.Discount
}).AsDynamic();
return(orderDetails);
}
object GetOrderDetails(NORTHWNDDataSet ds)
{
var products = ds.Products;
var details = ds.Order_Details;
var categories = ds.Categories;
var orderDetails =
(from d in details.AsLive().AsUpdatable()
join p in products.AsLive()
on d.ProductID equals p.ProductID
join c in categories.AsLive()
on p.CategoryID equals c.CategoryID
select new
{
c.CategoryName,
p.ProductName,
d.UnitPrice,
d.Quantity,
d.Discount
}).AsDynamic();
return orderDetails;
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.components = new System.ComponentModel.Container();
this.resetButton = new System.Windows.Forms.Button();
this.submitButton = new System.Windows.Forms.Button();
this.dataAdapter = new System.Data.SqlClient.SqlDataAdapter();
this.nORTHWNDDataSetBindingSource = new System.Windows.Forms.BindingSource(this.components);
this.nORTHWNDDataSet = new WinForms_SQL2.NORTHWNDDataSet();
this.customersBindingSource = new System.Windows.Forms.BindingSource(this.components);
this.customersTableAdapter = new WinForms_SQL2.NORTHWNDDataSetTableAdapters.CustomersTableAdapter();
this.tableAdapterManager = new WinForms_SQL2.NORTHWNDDataSetTableAdapters.TableAdapterManager();
this.customersDataGridView = new System.Windows.Forms.DataGridView();
this.dataGridViewTextBoxColumn1 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn2 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn3 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn4 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn5 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn6 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn7 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn8 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn9 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn10 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.dataGridViewTextBoxColumn11 = new System.Windows.Forms.DataGridViewTextBoxColumn();
this.Button_SubmitQuery = new System.Windows.Forms.Button();
this.textBox1 = new System.Windows.Forms.TextBox();
this.Button_About = new System.Windows.Forms.Button();
((System.ComponentModel.ISupportInitialize)(this.nORTHWNDDataSetBindingSource)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.nORTHWNDDataSet)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.customersBindingSource)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.customersDataGridView)).BeginInit();
this.SuspendLayout();
//
// resetButton
//
this.resetButton.FlatAppearance.BorderColor = System.Drawing.Color.Black;
this.resetButton.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.resetButton.Location = new System.Drawing.Point(13, 13);
this.resetButton.Name = "resetButton";
this.resetButton.Size = new System.Drawing.Size(75, 23);
this.resetButton.TabIndex = 1;
this.resetButton.Text = "Reset";
this.resetButton.UseVisualStyleBackColor = true;
this.resetButton.Click += new System.EventHandler(this.resetButton_Click);
//
// submitButton
//
this.submitButton.FlatAppearance.BorderColor = System.Drawing.Color.Black;
this.submitButton.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.submitButton.Location = new System.Drawing.Point(94, 13);
this.submitButton.Name = "submitButton";
this.submitButton.Size = new System.Drawing.Size(148, 23);
this.submitButton.TabIndex = 2;
this.submitButton.Text = "Submit Changes";
this.submitButton.UseVisualStyleBackColor = true;
this.submitButton.Click += new System.EventHandler(this.submitButton_Click);
//
// nORTHWNDDataSetBindingSource
//
this.nORTHWNDDataSetBindingSource.DataSource = this.nORTHWNDDataSet;
this.nORTHWNDDataSetBindingSource.Position = 0;
//
// nORTHWNDDataSet
//
this.nORTHWNDDataSet.DataSetName = "NORTHWNDDataSet";
this.nORTHWNDDataSet.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
//
// customersBindingSource
//
this.customersBindingSource.DataMember = "Customers";
this.customersBindingSource.DataSource = this.nORTHWNDDataSet;
//
// customersTableAdapter
//
this.customersTableAdapter.ClearBeforeFill = true;
//
// tableAdapterManager
//
this.tableAdapterManager.BackupDataSetBeforeUpdate = false;
this.tableAdapterManager.CustomersTableAdapter = this.customersTableAdapter;
this.tableAdapterManager.SuppliersTableAdapter = null;
this.tableAdapterManager.UpdateOrder = WinForms_SQL2.NORTHWNDDataSetTableAdapters.TableAdapterManager.UpdateOrderOption.InsertUpdateDelete;
//
// customersDataGridView
//
this.customersDataGridView.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Bottom)
| System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.customersDataGridView.AutoGenerateColumns = false;
this.customersDataGridView.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
this.customersDataGridView.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
this.dataGridViewTextBoxColumn1,
this.dataGridViewTextBoxColumn2,
this.dataGridViewTextBoxColumn3,
this.dataGridViewTextBoxColumn4,
this.dataGridViewTextBoxColumn5,
this.dataGridViewTextBoxColumn6,
this.dataGridViewTextBoxColumn7,
this.dataGridViewTextBoxColumn8,
this.dataGridViewTextBoxColumn9,
this.dataGridViewTextBoxColumn10,
this.dataGridViewTextBoxColumn11
});
this.customersDataGridView.DataSource = this.customersBindingSource;
this.customersDataGridView.Location = new System.Drawing.Point(13, 75);
this.customersDataGridView.Name = "customersDataGridView";
this.customersDataGridView.Size = new System.Drawing.Size(516, 276);
this.customersDataGridView.TabIndex = 2;
//
// dataGridViewTextBoxColumn1
//
this.dataGridViewTextBoxColumn1.DataPropertyName = "CustomerID";
this.dataGridViewTextBoxColumn1.HeaderText = "CustomerID";
this.dataGridViewTextBoxColumn1.Name = "dataGridViewTextBoxColumn1";
//
// dataGridViewTextBoxColumn2
//
this.dataGridViewTextBoxColumn2.DataPropertyName = "CompanyName";
this.dataGridViewTextBoxColumn2.HeaderText = "CompanyName";
this.dataGridViewTextBoxColumn2.Name = "dataGridViewTextBoxColumn2";
//
// dataGridViewTextBoxColumn3
//
this.dataGridViewTextBoxColumn3.DataPropertyName = "ContactName";
this.dataGridViewTextBoxColumn3.HeaderText = "ContactName";
this.dataGridViewTextBoxColumn3.Name = "dataGridViewTextBoxColumn3";
//
// dataGridViewTextBoxColumn4
//
this.dataGridViewTextBoxColumn4.DataPropertyName = "ContactTitle";
this.dataGridViewTextBoxColumn4.HeaderText = "ContactTitle";
this.dataGridViewTextBoxColumn4.Name = "dataGridViewTextBoxColumn4";
//
// dataGridViewTextBoxColumn5
//
this.dataGridViewTextBoxColumn5.DataPropertyName = "Address";
this.dataGridViewTextBoxColumn5.HeaderText = "Address";
this.dataGridViewTextBoxColumn5.Name = "dataGridViewTextBoxColumn5";
//
// dataGridViewTextBoxColumn6
//
this.dataGridViewTextBoxColumn6.DataPropertyName = "City";
this.dataGridViewTextBoxColumn6.HeaderText = "City";
this.dataGridViewTextBoxColumn6.Name = "dataGridViewTextBoxColumn6";
//
// dataGridViewTextBoxColumn7
//
this.dataGridViewTextBoxColumn7.DataPropertyName = "Region";
this.dataGridViewTextBoxColumn7.HeaderText = "Region";
this.dataGridViewTextBoxColumn7.Name = "dataGridViewTextBoxColumn7";
//
// dataGridViewTextBoxColumn8
//
this.dataGridViewTextBoxColumn8.DataPropertyName = "PostalCode";
this.dataGridViewTextBoxColumn8.HeaderText = "PostalCode";
this.dataGridViewTextBoxColumn8.Name = "dataGridViewTextBoxColumn8";
//
// dataGridViewTextBoxColumn9
//
this.dataGridViewTextBoxColumn9.DataPropertyName = "Country";
this.dataGridViewTextBoxColumn9.HeaderText = "Country";
this.dataGridViewTextBoxColumn9.Name = "dataGridViewTextBoxColumn9";
//
// dataGridViewTextBoxColumn10
//
this.dataGridViewTextBoxColumn10.DataPropertyName = "Phone";
this.dataGridViewTextBoxColumn10.HeaderText = "Phone";
this.dataGridViewTextBoxColumn10.Name = "dataGridViewTextBoxColumn10";
//
// dataGridViewTextBoxColumn11
//
this.dataGridViewTextBoxColumn11.DataPropertyName = "Fax";
this.dataGridViewTextBoxColumn11.HeaderText = "Fax";
this.dataGridViewTextBoxColumn11.Name = "dataGridViewTextBoxColumn11";
//
// Button_SubmitQuery
//
this.Button_SubmitQuery.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.Button_SubmitQuery.FlatAppearance.BorderColor = System.Drawing.Color.Black;
this.Button_SubmitQuery.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.Button_SubmitQuery.Location = new System.Drawing.Point(352, 13);
this.Button_SubmitQuery.Name = "Button_SubmitQuery";
this.Button_SubmitQuery.Size = new System.Drawing.Size(106, 23);
this.Button_SubmitQuery.TabIndex = 3;
this.Button_SubmitQuery.Text = "Submit Query";
this.Button_SubmitQuery.UseVisualStyleBackColor = true;
this.Button_SubmitQuery.Click += new System.EventHandler(this.Button_SubmitQuery_Click);
//
// textBox1
//
this.textBox1.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
| System.Windows.Forms.AnchorStyles.Right)));
this.textBox1.Location = new System.Drawing.Point(13, 42);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(445, 20);
this.textBox1.TabIndex = 4;
this.textBox1.Text = "SELECT * FROM CUSTOMERS WHERE City=\'London\'";
//
// Button_About
//
this.Button_About.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Right)));
this.Button_About.FlatAppearance.BorderColor = System.Drawing.Color.Black;
this.Button_About.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.Button_About.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Button_About.Location = new System.Drawing.Point(508, 13);
this.Button_About.Name = "Button_About";
this.Button_About.Size = new System.Drawing.Size(21, 23);
this.Button_About.TabIndex = 5;
this.Button_About.Text = "?";
this.Button_About.UseVisualStyleBackColor = true;
this.Button_About.Click += new System.EventHandler(this.Button_About_Click);
//
// Form1
//
this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
this.ClientSize = new System.Drawing.Size(541, 363);
this.Controls.Add(this.Button_About);
this.Controls.Add(this.textBox1);
this.Controls.Add(this.Button_SubmitQuery);
this.Controls.Add(this.customersDataGridView);
this.Controls.Add(this.submitButton);
this.Controls.Add(this.resetButton);
this.MinimumSize = new System.Drawing.Size(453, 104);
this.Name = "Form1";
this.Text = "Windows Forms SQL Example";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ComponentModel.ISupportInitialize)(this.nORTHWNDDataSetBindingSource)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.nORTHWNDDataSet)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.customersBindingSource)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.customersDataGridView)).EndInit();
this.ResumeLayout(false);
this.PerformLayout();
}
