C# - EX11

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 Ex10
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        //목록보기

        private void LoadProducts()
        {
            //LINQ Code -> Object Collection
            NorthwindDataContext db = new NorthwindDataContext();
            var products = from p in db.Products
                           orderby p.ProductID descending
                           //select p; // SELECT * FROM Product; 와 같다
                           select new
                           {
                               ProductID = p.ProductID,
                               ProductName = p.ProductName,
                               //SupplierID = p.SupplierID,
                               SupplierID = p.Suppliers.CompanyName,  //JOIN
                               CatogoryID = p.Categories.CategoryName, //JOIN
                               //CatogoryID = p.CategoryID,
                               Price = p.UnitPrice,
                               Quantity = p.QuantityPerUnit,
                               Stock = p.UnitsInStock
                           };//SELECT productid, productname, ... FROM Pruduct
            dataGridView1.DataSource = products;
        }
        private void button1_Click(object sender, EventArgs e)
        {
            LoadProducts();
        }
        //추가
        private void button2_Click(object sender, EventArgs e)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            Products product = new Products
            {
                ProductName = "덴마크 요구르트",
                SupplierID = 1,
                CategoryID = 1,
                UnitPrice = 1000
            };
            db.Products.InsertOnSubmit(product);
            db.SubmitChanges();

            LoadProducts();

        }
        //변경 UPDATE
        private void button3_Click(object sender, EventArgs e)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var products = from p in db.Products
                           where p.ProductID > 77
                           orderby p.ProductID descending
                           select p;

            if (products.Count() > 0)
            {
                Products product = products.First();
                product.UnitPrice += 100;
                db.SubmitChanges();
                LoadProducts();
            }
            else
                MessageBox.Show("수정대상 없음");
        }
        //삭제
        private void button4_Click(object sender, EventArgs e)
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var products = from p in db.Products
                           where p.ProductID > 77
                           orderby p.ProductID descending
                           select p;
            if (products.Count() > 0)
            {
                //메모리상의 변경
                db.Products.DeleteOnSubmit(products.First());
                //변경내용을 DB에 적용 (Query 수행)
                db.SubmitChanges();
                LoadProducts();
            }
        }
    }
}