[C#] ComboBox 多欄位 - TSQL

在 T-SQL 中先行把 ComboxBox DisplayMember 的資料整理好，藉此來作到多欄位顯示功能

利用該 Store Procedure 來產生日期資料

CREATE PROCEDURE dbo.GetComboBoxData
AS
    BEGIN
       WITH CTE AS 
       (
           SELECT 
               YEAR(GetDate()) AS [Year] , 0 AS DelCount
           UNION ALL
           SELECT 
               [Year] - 1 , DelCount + 1
           FROM CTE
           WHERE DelCount + 1 <= 10
       )
       SELECT
           CAST([Year] AS varchar(4)) + 
           '( 民國 ' + RIGHT(REPLICATE('0',3)  + CAST([Year] - 1911 AS varchar(3)),3) + ' 年)' AS Display ,
           [Year] AS value
       FROM CTE
    END

C# Winform 中的測試 Code

using System.Data.SqlClient;

namespace ComboBoxMultiCols
{
    public partial class MultiCols_TSQL : Form
    {
        public MultiCols_TSQL()
        {
            InitializeComponent();
        }

        private void MultiCols_TSQL_Load(object sender, EventArgs e)
        {
            comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
            SetComboDaataSource();
        }

        private void SetComboDaataSource()
        {

            string ConnString = @"Data Source=InstanceName;Initial Catalog=DBName;Integrated Security=True";
            using(SqlConnection conn = new SqlConnection(ConnString))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "GetComboBoxData";

                    if (conn.State == ConnectionState.Closed) conn.Open();

                    DataTable dt = new DataTable();
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    da.Fill(dt);

                    comboBox1.DataSource = dt;
                    comboBox1.DisplayMember = dt.Columns[0].ToString();
                    comboBox1.ValueMember = dt.Columns[1].ToString();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }
    }
}

延伸閱讀
• [C#] ComboBox 多欄位 - Format

參考資料
• 論壇討論