Home  • Programming • C#.NET

DataTable and LINQ

Example 1
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 DataTableLinq
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            dataGridView1.DataSource = GetTable();
        }

        private void button1_Click(object sender, EventArgs e)
        {


           
            DataTable dt = GetTable();

            var table = from t in dt.AsEnumerable()
                                         where t.Field<String>("Drug") == "Indocin"
                                         select t;

            DataTable n=table.CopyToDataTable<DataRow>();

            dataGridView2.DataSource = null;
            dataGridView2.DataSource = n;

            var name=(from c in table
                      where c.Field<int>("Dosage")==25
                      select c.Field<String>("Drug")
                          ).SingleOrDefault<String>();

            textBox1.Text = name;

        }

        static DataTable GetTable()
        {
            // Here we create a DataTable with four columns.
            DataTable table = new DataTable();
            table.Columns.Add("Dosage", typeof(int));
            table.Columns.Add("Drug", typeof(string));
            table.Columns.Add("Patient", typeof(string));
            table.Columns.Add("Date", typeof(DateTime));

            // Here we add five DataRows.
            table.Rows.Add(25, "Indocin", "David", DateTime.Now);
            table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
            table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
            table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
            table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
            return table;
        }
    }
}

Example1
DataClasses1DataContext db = new DataClasses1DataContext();

            var query = from c in db.Customers
                        where c.City == "London"
                        select c.City;

            foreach (var q in query)
            {
                Console.WriteLine(q);
            }
Example 3
public void SimpleQuery5()
{
    DataClasses1DataContext dc = new DataClasses1DataContext();

    var q =
        from a in dc.GetTable<Order>()
        where a.CustomerID.StartsWith("A")
        orderby a.OrderDate ascending
        select a;

    dataGridView1.DataSource = q;
}
Example 4
public void SimpleQuery3()
{
    DataClasses1DataContext dc = new DataClasses1DataContext();

    var q =
        from a in dc.GetTable<Order>()
        where a.CustomerID.StartsWith("A")
        select a;

    dataGridView1.DataSource = q;
}
Example 5
public void GetCustomerOrder()
{
    DataClasses1DataContext dc = new DataClasses1DataContext();

    var q= (from orders in dc.GetTable<Order>()
            from orderDetails in dc.GetTable<Order_Detail>()
            from prods in dc.GetTable<Product>()
            where ((orderDetails.OrderID == orders.OrderID) &&
                 (prods.ProductID == orderDetails.ProductID) &&
                 (orders.EmployeeID == 1))
            orderby orders.ShipCountry
            select new CustomerOrderResult
            {
                CustomerID = orders.CustomerID,
                CustomerContactName = orders.Customer.ContactName,
                CustomerCountry = orders.Customer.Country,
                OrderDate = orders.OrderDate,
                EmployeeID = orders.Employee.EmployeeID,
                EmployeeFirstName = orders.Employee.FirstName,
                EmployeeLastName = orders.Employee.LastName,
                ProductName = prods.ProductName
            }).ToList<CustomerOrderResult>();

    dataGridView1.DataSource = q;
}


public class CustomerOrderResult
{
    public System.String CustomerID
        {get;set;}
    public System.String CustomerContactName
        {get;set;}
    public System.String CustomerCountry
        {get;set;}
    public System.Nullable<System.DateTime> OrderDate
        {get;set;}
    public System.Int32 EmployeeID
        {get;set;}
    public System.String EmployeeFirstName
        {get;set;}
    public System.String EmployeeLastName
        {get;set;}
    public System.String ProductName
        {get;set;}
}
Example 6
public void GetCustomerOrder2()
{
    DataClasses1DataContext dc = new DataClasses1DataContext();

    var query = (from orders in dc.GetTable<Order>()
             from orderDetails in dc.GetTable<Order_Detail>()
             from prods in dc.GetTable<Product>()
             where ((orderDetails.OrderID == orders.OrderID)
                  && (prods.ProductID == orderDetails.ProductID)
                  && (orders.EmployeeID == 1))
             orderby orders.ShipCountry
             select new CustomerOrderResult
             {
                 CustomerID = orders.CustomerID,
                 CustomerContactName = orders.Customer.ContactName,
                 CustomerCountry = orders.Customer.Country,
                 OrderDate = orders.OrderDate,
                 EmployeeID = orders.Employee.EmployeeID,
                 EmployeeFirstName = orders.Employee.FirstName,
                 EmployeeLastName = orders.Employee.LastName,
                 ProductName = prods.ProductName
             }).ToList<CustomerOrderResult>();

    var matches = (from c in query
                  where c.CustomerID == "RICAR"
                  select c).ToList<CustomerOrderResult>();

    dataGridView1.DataSource = matches;
}

Comments 2


https://msdn.microsoft.com/en-us/library/bb669073(v=vs.110).aspx
 var uoms = (
            from u in um.AsEnumerable()           
            select new {id=u.Field<int>("id"),abbr=u.Field<String>("abbr")}).ToList();

            cmbUOM.DataSource = uoms;
            cmbUOM.DisplayMember = "abbr";
            cmbUOM.ValueMember = "id";
Copyright © 2024. Powered by Intellect Software Ltd