[EF] DataGridView 和導覽屬性 - Partial Class

在 stackoverflow 討論中，有利用 Partial Class 來取出導覽屬性，就動手試看看囉

直接在 DataPropertyName 上設定 CustName 啦

Order Class

namespace DataGridView4PartialClass
{
    using System;
    using System.Collections.Generic;
    
    public partial class Order
    {
        public int OrderID { get; set; }
        public System.DateTime OrderDate { get; set; }
        public int CustID { get; set; }
    
        public virtual Customer Customer { get; set; }
    }
}

Partial Class

namespace DataGridView4PartialClass
{
    public partial class Order
    {
        public string CustName
        {
            get
            {
                if (this.Customer != null)
                {
                    return this.Customer.CustName;
                }
                else
                {
                    return string.Empty;
                }
            }
        }
    }
}

C# Code

using System.Data.Entity;

namespace DataGridView4PartialClass
{
    public partial class Form1 : Form
    {
        EFDbContext context;
        BindingSource bsOrder;

        public Form1()
        {
            InitializeComponent();
            context = new EFDbContext();
            bsOrder = new BindingSource();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            context.Order.Include(c => c.Customer).Load();
            bsOrder.DataSource = context.Order.Local.ToBindingList();

            dgvOrders.AutoGenerateColumns = false;
            dgvOrders.DataSource = bsOrder;
        }
    }
}

執行顯示結果

在該篇文章內看到 DataGridView 要顯示導覽屬性的 6 種方式

1. 使用 DataGridViewComboBoxColumn
2. 使用 Partial Class
3. 使用 LINQ
4. 使用 DataGridView 的 CellFormatting Event
5. 覆寫並透過 ToString()
6. 自訂 TypeDescriptor

想筆記 Partial Class、CellFormatting 和自訂 TypeDescriptor，前面兩者已經完成，自訂 TypeDescriptor，則是因為對 OOP 沒有這麼熟悉，看完一頭霧水，只能含淚跳過，^^''

[EF] DataGridView 和導覽屬性 - Reflection

該篇筆記是延伸 [EF] DataGridView 和導覽屬性 - CellFormatting Event，該篇在 CellFormatting Event 內 Hard Code，完全沒有彈性，所以在這篇改用 Reflection 來達到相同需求，本篇也同樣是 DataGridView: How to Bind Nested Objects 的練習筆記

C# Code

using System.Data.Entity;
using System.Reflection;

namespace DataGridView4CellFormatting
{
    public partial class Form2 : Form
    {
        EFDbContext context;
        BindingSource bsOrder;

        public Form2()
        {
            InitializeComponent();
            context = new EFDbContext();
            bsOrder = new BindingSource();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            context.Order.Include(c => c.Customer).Load();
            bsOrder.DataSource = context.Order.Local.ToBindingList();

            dgvOrders.AutoGenerateColumns = false;
            dgvOrders.DataSource = bsOrder;
        }

        private readonly char DataPropertyNameSign = '.' ;
        private void dgvOrders_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            object obj = dgvOrders.Rows[e.RowIndex].DataBoundItem;
            string DataPropertyName = dgvOrders.Columns[e.ColumnIndex].DataPropertyName;

            if (obj != null &&
                DataPropertyName.Contains(DataPropertyNameSign))
            {
                e.Value = BindNavigationProperty(obj, DataPropertyName);
            }
        }

        /// <summary>
        /// 註解以該範例來說明註記，方便事後回憶
        /// </summary>
        /// <param name="Property">Order Class</param>
        /// <param name="DataPropertyName">Customer.CustName</param>
        /// <returns>CustName 資料</returns>
        private string BindNavigationProperty(object Property, string DataPropertyName)
        {
            if (DataPropertyName.Contains(DataPropertyNameSign) == false)
            {
                throw new Exception($"{nameof(DataPropertyName)} 資料不符合導覽屬性規則");
            }

            // Customer.CustName
            var DataPropertyInfo = ParseDataPropertyName(DataPropertyName);
            // Customer
            string NavigatrionPropertName = DataPropertyInfo.NavigationPropertyName;
            // CustName
            string PropertyName = DataPropertyInfo.PropertyName;

            // 取回 Order.Customer 相關資料
            var obj = GetPropertyValue(Property, NavigatrionPropertName);
            // 取回 Customer.CustName 資料
            return GetPropertyValue(obj, PropertyName).ToString();
        }

        /// <summary>
        /// 取得 object Property Value
        /// </summary>
        /// <param name="obj">欲取值的 object </param>
        /// <param name="propertyName">屬性</param>
        /// <returns>屬性值</returns>
        private object GetPropertyValue(object obj, string propertyName)
        {
            Type t = obj.GetType();

            // BindingFlags.IgnoreCase 表示不區分大小寫，Flag 只要重新設定就要全部都設，只設單一個會沒有效果
            PropertyInfo info = t.GetProperty(propertyName, BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
            if (info == null)
            {
                throw new Exception($"Property：{nameof(propertyName)} 不存在，無法取值");
            }

            object oValue = info.GetValue(obj, null);
            if (oValue == null)
            {
                throw new Exception($"無法取得 Property：{nameof(propertyName)} 的值");
            }

            return oValue;
        }

        /// <summary>
        /// 解析 DataPropertyName
        /// </summary>
        /// <param name="DataPropertyName">DataPropertyName</param>
        /// <returns>ClassName 和 PropertyName </returns>
        private (string NavigationPropertyName, string PropertyName) ParseDataPropertyName(string DataPropertyName)
        {
            if (string.IsNullOrEmpty(DataPropertyName))
            {
                throw new ArgumentNullException(nameof(DataPropertyName));
            }

            string[] result = DataPropertyName.Split(DataPropertyNameSign);

            int ArraryLength = 2;
            if (result.Length != ArraryLength)
            {
                string ErrorMessage = $"{nameof(DataPropertyName)} 參數格式必須為 ClassName.PropertyName，傳入值為 {DataPropertyName}";
                throw new ArgumentException(ErrorMessage);
            }

            return (result[0], result[1]);
        }
    }
}

• 重點1：BindingFlags.IgnoreCase [Highlight 76 77 行]

練習時設定 DataPropertyName 時，手殘輸入造成 DataPropertyName 和 Class.Property 大小寫差異，找不到 Property 而拋出 null exception，所以特別去指定忽略大小寫，BindingFlags 一旦有重新設定，就要把相關 Flag 也一併補上，要不然會沒有作用喔

• 重點2：SQL Profile 側錄 TSQL 語法

腦海裡的無聊想法，想說都用 Load() 取出 Customer 相關資料，會不會在 CellFormatting 時又回到 DB 一筆一筆抓 CustName，經由 SQL Profile 確定後，確定沒有該情況

• 參考資料
• Stackoverflow
• DataGridView: How to Bind Nested Objects
• GitHub Source Code

[EF] DataGridView 和導覽屬性 - CellFormatting Event

要在 DataGridView 上設定 DataPropertyName 屬性就可以顯示導覽屬性相關資料，為該文章 - DataGridView: How to Bind Nested Objects 的練習筆記

該圖示在 DataPropertyName 上設定 Customer.CustName

但執行時，客戶名稱顯示空白，沒有資料出現

在 CellFormatting Event 內進行變化

Order Class

namespace DataGridView4CellFormatting
{
    using System;
    using System.Collections.Generic;
    
    public partial class Order
    {
        public int OrderID { get; set; }
        public System.DateTime OrderDate { get; set; }
        public int CustID { get; set; }
    
        public virtual Customer Customer { get; set; }
    }
}

Customer Class

namespace DataGridView4CellFormatting
{
    using System;
    using System.Collections.Generic;
    
    public partial class Customer
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Customer()
        {
            this.Order = new HashSet<Order>();
        }
    
        public int CustID { get; set; }
        public string CustName { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Order> Order { get; set; }
    }
}

C# Code

using System.Data.Entity;

namespace DataGridView4CellFormatting
{
    public partial class Form1 : Form
    {
        EFDbContext context;
        BindingSource bsOrder;

        public Form1()
        {
            InitializeComponent();
            context = new EFDbContext();
            bsOrder = new BindingSource();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            context.Order.Include(c => c.Customer).Load();
            bsOrder.DataSource = context.Order.Local.ToBindingList();

            dgvOrders.AutoGenerateColumns = false;
            dgvOrders.DataSource = bsOrder;
        }

        private void dgvOrders_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            var row = dgvOrders.Rows[e.RowIndex];

            if (e.RowIndex >= 0 &&
                e.ColumnIndex == 3 &&
                row != null)
            {
                var order = (Order)row.DataBoundItem;
                if (order != null && order.Customer != null)
                {
                    e.Value = order.Customer.CustName;
                }
            }
        }
    }
}

執行結果

• 參考資料
• Stackoverflow
• DataGridView: How to Bind Nested Objects
• GitHub Source Code

[EF] 簡單 CRUD 練習

看完該課程 - CRUD C#.Net with Entity Framework 後的簡易練習筆記

C# Code

using System.Data.Entity;
using SimpleCRUD.Models;

namespace SimpleCRUD
{
    public partial class Form1 : Form
    {
        CRUDContext context = new CRUDContext();
        Employee employ = new Employee();

        public Form1()
        {
            InitializeComponent();
            DataControlVisible();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            ControlClear();
            DgvSource();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            // DBContext 物件會佔用資源，因此必須特別注意其生命週期，
            // 通常都會利用 using 確保會釋放佔用資源，在這是 Form 結束時，
            // 手動進行釋放，要不然就只能等待 GC 進行資源回收

            // https://docs.microsoft.com/zh-tw/ef/ef6/fundamentals/databinding/winforms
            // 根據該文章釋放 Context 是在 Closing Event 中手動進行

            context.Dispose();
        }

        private void ControlClear()
        {
            txtName.Text = string.Empty;
            cboDepartment.Text = "資訊";
            dtHireDate.Value = DateTime.Today;
        }

        private void ControlBinding()
        {
            txtName.Text = employ.Name;
            cboDepartment.Text = employ.Department.Trim();
            dtHireDate.Value = employ.HireDate;
        }

        private void DataControlVisible()
        {
            var state = context.Entry(employ).State;
            if (state == EntityState.Added || state == EntityState.Modified)
            {
                BtnAdd.Visible = false;
                BtnModify.Visible = false;
                BtnDelete.Visible = false;
                BtnSave.Visible = true;
                BtnRevert.Visible = true;
            }
            else
            {
                BtnAdd.Visible = true;
                BtnModify.Visible = true;
                BtnDelete.Visible = true;
                BtnSave.Visible = false;
                BtnRevert.Visible = false;
            }
        }

        private void DgvSource()
        {
            DgvSearch.DataSource = context.Employee.ToList();
        }

        private void BtnAdd_Click(object sender, EventArgs e)
        {
            ControlClear();

            employ = new Employee
            {
                Name = txtName.Text,
                Department = cboDepartment.Text,
                HireDate = dtHireDate.Value
            };

            // 下述兩種方式都可以透過 SaveChange() 把資料存進 DB 內
            // 方法一：把資料新增進資料集
            context.Employee.Add(employ);
            // 方法二：把資料狀態設為 EntityState.Added
            // context.Entry(employ).State = EntityState.Added;

            DataControlVisible();
        }

        private void BtnModify_Click(object sender, EventArgs e)
        {
            context.Entry(employ).State = EntityState.Modified;
            employ.Name = "取消測試";
            ControlBinding();
            DataControlVisible();
        }

        private void BtnSave_Click(object sender, EventArgs e)
        {
            employ.Name = txtName.Text;
            employ.Department = cboDepartment.Text;
            employ.HireDate = dtHireDate.Value;

            context.SaveChanges();
            DgvSource();
            DataControlVisible();
        }

        private void BtnRevert_Click(object sender, EventArgs e)
        {
            // 只針對該筆資料，重新讀取
            context.Entry(employ).Reload();
            ControlBinding();
            DataControlVisible();
        }

        private void BtnDelete_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("是否刪除該筆資料", "刪除", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.Cancel)
            {
                return;
            }

            context.Employee.Remove(employ);
            context.SaveChanges();
            employ = null;
            ControlClear();
            DgvSource();
        }

        private void DgvSearch_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (DgvSearch.CurrentRow == null)
            {
                return;
            }
            
            int ID = Convert.ToInt32(DgvSearch.CurrentRow.Cells["ID"].Value);
            employ = context.Employee.Single(s => s.ID == ID);
            ControlBinding();
        }
    }
}

[EF] 資料載入

練習一下要符合 Master-Detail 架構下，移動 Master 資料時，要帶出 Detail 相關資料，會同時使用到 Eager Loading 和 Lazy Loading

C# Code

using System.Data.Entity;

namespace WinFormsWithLoading
{
    public partial class Form1 : Form
    {
        EFDemoEntities _context;
        public Form1()
        {
            InitializeComponent();
            dataGridView1.AutoGenerateColumns = false;
            _context = new EFDemoEntities();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            _context.Order.Include(o => o.Customer).Load();
            // SQL Profile 就會側錄到下面的 TSQL 語法
            //SELECT
            //    [Extent1].[OrderID] AS[OrderID], 
            //    [Extent1].[OrderDate] AS[OrderDate], 
            //    [Extent1].[CustID] AS[CustID], 
            //    [Extent2].[CustID] AS[CustID1], 
            //    [Extent2].[CustName]
            //        AS[CustName]
            //FROM[dbo].[Order]
            //        AS[Extent1]
            //INNER JOIN[dbo].[Customer] AS[Extent2] ON[Extent1].[CustID] = [Extent2].[CustID]

            bsOrder.DataSource = _context.Order.Local.ToBindingList();
            bsOrder.PositionChanged += BsOrder_PositionChanged;
            bsOrder.MoveLast();

            txtOrderID.DataBindings.Add("Text", bsOrder, "OrderID");
            txtOrderDate.DataBindings.Add("Text", bsOrder, "OrderDate");
            txtCustID.DataBindings.Add("Text", bsOrder, "CustID");
            txtCustName.DataBindings.Add("Text", bsOrder, "Customer.CustName");

            bindingNavigator1.BindingSource = bsOrder;

        }

        private void BsOrder_PositionChanged(object sender, EventArgs e)
        {
            if (bsOrder.Current == null) return;
            Order order = (bsOrder.Current as Order);

            // 清除現有 Local Entity
            _context.OrderDetail.Local.ToList().ForEach(x => _context.Entry(x).State = EntityState.Deleted);

            // MSDN 文章寫法
            _context.Entry(order).Collection(o => o.OrderDetail).Load();

            // VFP 慣例寫法
            //_context.OrderDetail.Where(w => w.OrderID == order.OrderID).Load();

            dataGridView1.DataSource = null;
            dataGridView1.DataSource = _context.OrderDetail.Local.ToBindingList();

        }
    }
}

[EF] 透過導覽屬性來取出相關資料

在 Winform 上透過 TextBox.DataBinding 搭配導覽屬性，直接呈現相關資料

專案內容

C# 相關語法

using System.Data.Entity;
using WinFormsWithNavigation.Models;

namespace WinFormsWithNavigation
{
    public partial class Form1 : Form
    {
        private NavigationContext _context;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            _context = new NavigationContext();
            _context.Order.Load();

            bsOrder.DataSource = _context.Order.Local.ToBindingList();
            txtOrderID.DataBindings.Add("Text", bsOrder, "OrderID");
            txtOrderDate.DataBindings.Add("Text", bsOrder, "OrderDate");
            txtCustID.DataBindings.Add("Text", bsOrder, "CustID");
            txtCustName.DataBindings.Add("Text", bsOrder, "Customer.CustName");
            bindingNavigator1.BindingSource = bsOrder;
        }
    }
}

執行結果

CPU 壓蓋生鏽

在網路上是常常看見有人 PO 主機板上螺絲氧化相片，第一次看見 CPU 壓蓋生鏽的，還是自家公司內 PC，特地拍下來記錄一下

[SSMS] 指定範本參數的值

閱讀 SQL Server 書籍時，發現自己常用內建範本，每次範本叫出來就是直接修改，原來有較便利修正範本參數的方式

SSMS 上的圖示，或是按 Ctrl + Shift + M 就可以把 [指定範本參數的值] 視窗叫出來

[指定範本參數的值]  視窗叫出來後，可以更改的的範本參數就會變成綠色背景，直接在視窗內修改參數值就行

查資料才發現，原來該功能在 SQL Server 2008 就有，SQL Server 2012 甚至有 [程式碼片段] 可以使用了

參考資料
• SQL Server 2008 SSMS 使用小技巧(4)- 使用範本總管產生指令
• SQL Server 2012_SSMS 2012 管理工具 - 小技巧：在「查詢編輯器」上，使用「程式碼片段(Snippets)」

[Win10] 複製路徑

看 Udmey 課程時講師介紹的 Windows 10 功能 - 複製路徑，在資料夾或檔案上，按 shift 後並點選滑鼠右鍵，就可以看見 [複製路徑] 選項，覺得實用

參考資料
• Win10中复制文件/文件夹路径（地址）的方法技巧

[VFP] 報表多控件設定

以往設計報表時，是還蠻常設定到 Print When，但因為條件通常都不一樣，所以一個一個設定也沒有覺得奇怪，但這次使用者需求，幾乎是全部控件都要設定相同的 Print When，所幸 VFP 報表本身可以多選控件後，只要設定一次 Print When，就可以通通套用