[C#] ComboBox DropDownList (續)

在這篇 [C#] ComboBox DropDownList 提到 Item 和 DataBinding 資料，字元長度要一樣，DropDownList 才會正確顯示資料，實務上是把 Item 資料放在 DB Table (以下稱呼 ComboBoxData Table) 內，因為會有各式各樣的 ComboBox Item 資料放在這，所以 ComboBoxData Table 資料欄位長度一定和 Data Table 欄位長度不一樣，因此兩者必須互相搭配才成正確顯示 DropDownList 資料

簡易範例：利用自訂 ComboBox 控件來正確顯示 DropDownList 資料

方案內容

WinForm Layout

在 AdventureWorks2016 內，建立 DemoData 和 ComboBoxData 兩個 Table 並新增對應資料

USE [AdventureWorks2016]
GO

-- 建立 DemoData Table
CREATE TABLE [dbo].[DemoData](
    [PKCol] [char](10) NOT NULL,
    [ComboBoxCol] [char](10) NOT NULL,
    CONSTRAINT [PK_DemoData] PRIMARY KEY CLUSTERED 
    (
        [PKCol] ASC
    )
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DemoData] ADD  CONSTRAINT [DF_DemoData_ComboBoxData]  DEFAULT ('') FOR [ComboBoxCol]
GO

-- 新增 DemoData 資料
INSERT INTO DemoData (PKCol , ComboBoxCol)
SELECT 'Demo1' , 'A'
UNION ALL
SELECT 'Demo2' , 'B'
UNION ALL
SELECT 'Demo3' , 'O'
UNION ALL
SELECT 'Demo4' , 'AB'

-- 建立 ComboBoxData Table
CREATE TABLE [dbo].[ComboBoxData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Kind] [char](10) NOT NULL,
    [Value] [char](100) NOT NULL,
    [Display] [char](100) NOT NULL,
    CONSTRAINT [PK_ComboBoxData] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ComboBoxData] ADD  CONSTRAINT [DF_ComboBoxData_Kind]  DEFAULT ('') FOR [Kind]
GO
ALTER TABLE [dbo].[ComboBoxData] ADD  CONSTRAINT [DF_ComboBoxData_Value]  DEFAULT ('') FOR [Value]
GO
ALTER TABLE [dbo].[ComboBoxData] ADD  CONSTRAINT [DF_ComboBoxData_Display]  DEFAULT ('') FOR [Display]
GO

-- 新增 ComboBoxData 資料
INSERT INTO ComboBoxData (Kind , [Value] , Display)
SELECT '血型' , 'A' , 'A'
UNION ALL
SELECT '血型' , 'B' , 'B'
UNION ALL
SELECT '血型' , 'O' , 'O'
UNION ALL
SELECT '血型' , 'AB' , 'AB'

建立自訂 ComboBox 控件：iComboBox

// 引用前請先把 System.Windows.Forms 加入參考
using System.Windows.Forms;

namespace iControl
{
    public class iComboBox : ComboBox
    {
        public iComboBox()
        {
            DropDownStyle = ComboBoxStyle.DropDownList;
        }

        /// <summary>
        /// 覆寫 Text 屬性，確保 DataBinding 資料，都會去除尾端空白
        /// </summary>
        public override string Text
        {
            get
            {
                return base.Text.Trim();
            }
            set
            {
                string data = value;
                if (data != null) data = data.Trim();
                base.Text = data;
            }
        }
    }
}

撰寫 WinForm 測試環境

using System.Data.SqlClient;

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

        private BindingSource bs = new BindingSource();
        private string ConnectonStrng = @"Data Source=.\SQL2016;Initial Catalog=AdventureWorks2016;Integrated Security=True;";

        private void Form1_Load(object sender, EventArgs e)
        {
            bs.DataSource = GetBindingSourceData();
            bindingNavigator1.BindingSource = bs;

            iComboBox1.DataSource = GetComboBoxData("血型");
            iComboBox1.ValueMember = "Value";
            iComboBox1.DisplayMember = "Display";

            textBox1.DataBindings.Add("Text", bs, "PKCol", true);
            iComboBox1.DataBindings.Add("Text", bs, "ComboBoxCol", true);
        }

        private DataTable GetBindingSourceData()
        {
            using (SqlConnection conn = new SqlConnection(ConnectonStrng))
            {
                try
                {
                    string TSQL = string.Empty;
                    TSQL += "SELECT    ";
                    TSQL += " PKCol    ";
                    TSQL += " ,ComboBoxCol ";
                    TSQL += "FROM DemoData  ";
                    SqlCommand cmd = new SqlCommand(TSQL, conn);

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

        private DataTable GetComboBoxData(string Kind)
        {
            using (SqlConnection conn = new SqlConnection(ConnectonStrng))
            {
                try
                {
                    string TSQL = string.Empty;
                    TSQL += "SELECT         ";
                    TSQL += " Kind        ";
                    TSQL += " , RTRIM([Value]) AS [Value]   ";
                    TSQL += " , RTRIM([Display]) AS Display ";
                    TSQL += "FROM ComboBoxData      ";
                    TSQL += "WHERE Kind = @Kind      ";

                    SqlCommand cmd = new SqlCommand(TSQL, conn);

                    cmd.Parameters.Add("@Kind", SqlDbType.Char, 10).Value = Kind;

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

執行效果