[SQL] Foreign Key 重要性

最近 [設備加工零件 Table] 三不五時會出現 Block，檢查 T-SQL 語法時，發現該段語法 Highlight 很奇怪

SELECT 
    .......................
FROM 派工單 AS s
    JOIN 派工單物料
    LEFT JOIN
        (            
            SELECT 
                Q.物料識別碼
            FROM 物料排版圖數量 AS Q
                JOIN 物料排版圖 AS M ON Q.物料排版圖數量ID = M.物料排版圖數量ID
                JOIN 排版圖 AS SP ON SP.排版圖圖號 = M.排版圖圖號
                JOIN 排版圖設備加工零件 AS GP ON GP.排版圖圖號 = SP.排版圖圖號
                JOIN 設備加工零件 AS GG ON GP.設備加工零件號碼 = GG.設備加工零件號碼
            WHERE Q.稽核 = 0
            GROUP BY Q.物料識別碼
        ) AS QQQ ON QQQ.物料識別碼 = w.物料識別碼
WHERE 
    .......................
ORDER BY
    .......................

詢問同事撰寫邏輯後才發現，原來是擔心 [物料排版圖]、[排版圖]、[排版圖設備加工零件] 和 [設備加工零件] 資料對應不起來，自動派工會出現問題，才乾脆把相關 Table 通通 JOIN 起來，聽完後直覺是，這不是 Foreign Key（以下簡稱 FK）該作的事情嘛。

檢查相關 Table 後才發現，[排版圖設備加工零件] 對應 [設備加工零件] 還真的沒有設定 FK，詳細了解原因後，才發現給現場操作員資料，會包含一些不需要 [設備加工零件]  的輔助資訊，兩者放在同一個 Table 內，所以才不能建立 FK，再檢查 [設備加工零件]  相關 Table 才發現，因為 [排版圖設備加工零件] 出錯，所以連帶 [物料排版圖數量] 相關資訊也掛掉不少。

多建立一個 Table 並修改程式來儲存輔助資訊，並把 [排版圖設備加工零件] 和 [設備加工零件] 的 FK 建立起來，避免再次發生悲劇，Orz

[C#] Excel 多個 sheet

利用 AdventureWorks2014 當成資料來源，要把個部門員工匯出至 Excel 內且一個部門要單獨在一個 Sheet 內。

參考

加入 Microsoft.Office.Interop.Excel 和 System.Configuration

專案和 Layout

Store Procedure

USE [AdventureWorks2014]
GO 

CREATE PROCEDURE dbo.GetDepartment
AS
BEGIN
    SELECT 
        DepartmentID AS DepNO ,
        Name AS DepName
    FROM [HumanResources].[Department]
    ORDER BY DepNO
END
GO

USE [AdventureWorks2014]
GO

CREATE PROCEDURE dbo.GetEmployee (@DepNO smallint)
AS
BEGIN
    SELECT
        D.DepartmentID AS DepNO ,
        D.Name AS DepName ,
        P.BusinessEntityID AS EmpNO,
        CONCAT(P.FirstName , ' - ' , P.LastName) AS EmpName ,
        E.jobTitle ,
        E.HireDate ,
        E.BirthDate
    FROM [HumanResources].[Employee] AS E
        JOIN [Person].[Person] AS P ON E.BusinessEntityID = P.BusinessEntityID
        JOIN [HumanResources].[EmployeeDepartmentHistory] AS H ON E.BusinessEntityID = H.BusinessEntityID
        JOIN [HumanResources].[Department] AS D ON H.DepartmentID = D.DepartmentID 
    WHERE H.EndDate IS NULL 
        AND D.DepartmentID = @DepNO
    ORDER BY DepNO , EmpNO
END
GO

App.config

把連線字串放在裡面

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
    <connectionStrings>
        <add name="default" connectionString="Data Source=InstanceName;Initial Catalog=AdventureWorks2014;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

C# Code

using System.Configuration;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace Data2ExcelSheet
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnExcel_Click(object sender, EventArgs e)
        {
            SaveFileDialog save = new SaveFileDialog();
            save.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            save.FileName = "Excel Export demo";
            save.Filter = "*.xlsx|*.xlsx";
            if (save.ShowDialog() != DialogResult.OK) return;

            DataTable Department = GetDepartment();
            Excel.Application xls = new Excel.Application();
            try
            {
                // 設定新增 WorkBook 時，會有幾個 sheet
                xls.SheetsInNewWorkbook = Department.Rows.Count;
                Excel.Workbook book = xls.Workbooks.Add();

                int index, DepNO;
                string DepName;
                Excel.Worksheet sheet;
                foreach (DataRow row in Department.Rows)
                {
                    DepNO = Convert.ToInt16(row["DepNO"]);
                    DepName = row["DepName"].ToString();

                    index = Department.Rows.IndexOf(row) + 1;
                    sheet = (Excel.Worksheet)xls.Worksheets[index];
                    sheet.Name = DepName;

                    Data2Sheet(sheet, GetEmployee(DepNO));
                }

                book.SaveAs(save.FileName);
                }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                xls.Quit();
            }
        }

        private void Data2Sheet(Excel.Worksheet sheet, DataTable dt)
        {
            int rowindex, colindex;
            foreach (DataRow row in dt.Rows)
            {
                rowindex = dt.Rows.IndexOf(row) + 1;

                foreach (DataColumn col in dt.Columns)
                {
                    colindex = dt.Columns.IndexOf(col) + 1;
                    sheet.Cells[rowindex, colindex] = row[col].ToString();
                }
            }   
        }

        private DataTable GetEmployee(int DepNO)
        {
            try
            {
                string ConnString = ConfigurationManager.ConnectionStrings["default"].ToString();

                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "GetEmployee";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@DepNO", DepNO);

                    DataTable dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    return dt;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private DataTable GetDepartment()
        {
            try
            {
                string ConnString = ConfigurationManager.ConnectionStrings["default"].ToString();
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "GetDepartment";
                    cmd.CommandType = CommandType.StoredProcedure;

                    DataTable dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    da.Fill(dt);
                    return dt;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}

匯出資料

• 延伸閱讀
• [C#] foreach 中的 index
• 參考資料
• 論壇討論

[C#] 擷取字串內容

論壇問題，要截取特定字串內指定目標的內容

完全不懂正則表示式，用 indexof() 搭配 substring() 來練習

namespace CatchTargetString
{
    class Program
    {
        static void Main(string[] args)
        {
            List<string> source = new List<strng>()
            {
                "yyhhhh=rrtrygfhgfhjgfj; vmjhdkadklajd=gjfxghytuytddxfhgf; 546444=jkdhghffkjdk;  kljjkk=jstrygfhgfhkjs;" ,
                "vmjhdkadklajd=gjfxghgfhgfhgf; 546444=jkjjkjdk; kljjkk=jskljkjs; yyhhhh=rrrgfjytyjjghjgfj;" ,
                "vmjhdkadklajd=gjfxtrytrytryhgf; 546444=jkjjdhgtytryk; yyhhhh=rrhhgjytuytgfj; kljjkk=jskyuytuytjs;"
            };

            string
                targetStart = "yyhhhh=",
                targetEnd = ";",
                data;
             int 
                indexStart, 
                indexEnd ,
                targetStartLength = targetStart.Length;

             List<string> result = new List<string>();

             foreach (string item in source)
             {
                 indexStart = item.IndexOf(targetStart);
                 if (indexStart == -1) continue;
                 indexEnd = item.IndexOf(targetEnd, indexStart);
                 if (indexEnd == -1) continue;

                 data = item.Substring(indexStart + targetStartLength, indexEnd - indexStart - targetStartLength);
                 result.Add(data);
             }

             dump(result);
         }

         public static void dump(List<string> source)
         {
             foreach (string item in source)
             {
                 Console.WriteLine(item);
             }
         }
    }
}

該找個時間學習正則表示式了，>.<

參考資料
• 論壇問題出處

[C#] Constructor 建構子

之前一直有個觀念是子類別建構式一定要指定對應的父類別建構子，要不然 VS 會報錯，最近才發現不是這麼一回事，寫個簡易範例記錄一下

ParentClass

namespace ConstructorDemo
{
    public class ParentClass
    {
        public ParentClass()
        {
            Console.WriteLine("Parent base Constructor");
        }

        public ParentClass (string Name)
        {
            Console.WriteLine("Hello {0} From ParentClass" , Name);
        }
     }
}

ChildClass

namespace ConstructorDemo
{
    public class ChildClass : ParentClass
    {
        public ChildClass()
        {
            Console.WriteLine("Child base Constructor");
        }
  
        // Demo1
        // 當 ChildClass 被 new 時，預設是會觸發 ParentClass 的 base Constructor
        public ChildClass(string Name)
        {
            Console.WriteLine("Hello {0} from ChildClass", Name);
        }

        // Demo2
        // 指定觸發 ParenClass Constructor
        public ChildClass(string Name) : base (Name)
        {
            Console.WriteLine("Hello {0} from ChildClass", Name);
        }
    }
}

測試

namespace ConstructorDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            ChildClass c1 = new ChildClass();

            Console.WriteLine("------------------------------");

            ChildClass c2 = new ChildClass("路人");
        }
    }
}

Demo1：沒有指定父類別建構子的情況

Demo2：指定父類別建構子

之前的觀念不知哪裡來的，Orz

延伸閱讀
• [C#] 呼叫父建構子

參考資料
• Zoom 遠距教學

[C#] 自訂控制項內的事件 2

這篇 [C#] 自訂控制項內的事件 練習時，只是很單純練習事件被觸發和了解事件的觸發順序，這篇以抓 TextBox 輸入後舊值當成練習

Layout：兩個控件 iTextBox 和 ListBox

iTextBox

using System;
using System.Windows.Forms;

namespace DataChangeEvent
{
    internal class iTextBox : TextBox
    {
        private string _oldValue;

        public iTextBox()
        {
            // 註冊 KeyPress Event
            KeyPress += iTextBox_KeyPress;
        }

        // Step3：宣告 DataChange Event 
        public event DataChangeEventHandler DataChange;

        // Step4：建立 OnDataChange() 來觸發 DataChange Event
        protected virtual void OnDataChange(DataChangeEventArgs e)
        {
            if (DataChange != null)
                DataChange(this, e);
        }

        private void iTextBox_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar != (char)Keys.Enter)
                return;

            // 值沒有變化情況下，就不需要觸發 DataChange Event
            if (_oldValue == Text)
                return;

            // 紀錄新舊值
            DataChangeEventArgs args = new DataChangeEventArgs(_oldValue, Text);

            // 紀錄先舊值後，把新值設定為舊值
            _oldValue = Text;

            // 呼叫 Method 來觸發是事件
            OnDataChange(args);

        }
    }

    // Step2：建立 DataChangeEventHandler Delegate
    public delegate void DataChangeEventHandler(object sender, DataChangeEventArgs e);

    // Step1：建立 DataChangeEventArgs EventArgs，並宣告兩個 Property
    public class DataChangeEventArgs : EventArgs
    {
        private string _oldValue;
        public string OldValue { get => _oldValue;}

        private string _newValue;
        public string NewValue { get => _newValue;}

        public DataChangeEventArgs(string oldValue, string newValue)
        {
            _oldValue = oldValue;
            _newValue = newValue;
        }
    }
}

DataChangeEvent

using System;
using System.Windows.Forms;

namespace DataChangeEvent
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            // 註冊 DataChange Event
            iTextBox1.DataChange += iTextBox1_DataChange;
        }

        void iTextBox1_DataChange(object sender, DataChangeEventArgs e)
        {
            string oldValue = string.IsNullOrWhiteSpace(e.OldValue) ? "空值" : e.OldValue;
            string info = $"觸發時間：{DateTime.Now} - OldValue：{oldValue} - NewValue：{e.NewValue}";
            listBox1.Items.Add(info);
        }
    }
}

延伸閱讀
• [C#] Event 練習
• [C#] 自訂控制項內的事件
  

[C#] PictureBox 隨滑鼠移動

論壇問題

PictureBox 要在滑鼠右上角隨著滑鼠移動且不影響滑鼠 Click 功能

練習在 MouseMove Event 中控制 PictureBox Location (Top、Left)

控件 Layout

C# Code

namespace PictureBoxWithMouse
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // 設定該 PictureBox 背景顏色為黑色
            pictureBox1.BackColor = Color.Black;
        }

        private void Form1_MouseMove(object sender, MouseEventArgs e)
        {
            // 設定 PictureBox 在滑鼠右上角
            pictureBox1.Left = e.X + pictureBox1.Width ;
            pictureBox1.Top = e.Y - pictureBox1.Height;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show("點擊該 Button");
        }
    }
}

原來滑鼠在某個控件上是不會觸發 Form 的 MouseMove Event

參考資料
• 論壇問題出處
• 王者歸來－C# 完全開發範例集, 3/e - 範例 568
  

[VS] Layout 工具列

WinForm 的 Layout 工具列可以從 ［VS => View => ToolBars => Layout 啟動 => Layout工具列］ 叫出，當選取多個控件時，可以透過它來設定控件位置和大小

參考資料
• 逐步解說：使用對齊線排列 Windows Form 上的控制項
  
• 如何：為所有 Windows Form 設定格線選項
  
• 如何：使用 Windows Form 中的對齊線和格線排列控制項
  
• 選項對話方塊、Windows Form 設計工具、一般