DevExpress:下拉框綁定資料來源 (ComboBoxEdit,LookUpEdit)

 

        #region 下拉框 comboBoxEdit 和 LookUpEditor        //  設定下拉框   傳入參數(需要設定的下拉框控制項，查詢的語句，預設第一行的內容)        public static void SetComboList(DevExpress.XtraEditors.ComboBoxEdit ComboList, string QuerySQL, string FirstRowText = "",bool bSelectFirstItem=false)        {            DataTable dtList=null;            try            {                dtList = sqlHelper.ExecuteDataTable(Common.ConnString, CommandType.Text, QuerySQL, null);            }            catch (Exception ex)            {                frm0201.DisplayMsg("資料訪問異常:", ex.Message.ToString());                return;            }            int intCount = (dtList!=null) ? dtList.Rows.Count : 0;            ComboList.Properties.TextEditStyle = TextEditStyles.DisableTextEditor; // 設定 comboBox的文本值不能被編輯            ComboList.Properties.Items.Clear();            if (!string.IsNullOrEmpty(FirstRowText)) ComboList.Properties.Items.Add(FirstRowText);            if (intCount > 0)            {                for (int i = 0; i < intCount; i++)                {                    ComboList.Properties.Items.Add(dtList.Rows[i][0].ToString());                }            }            if (bSelectFirstItem) ComboList.SelectedIndex = 0; // 設定選中第1項        }        //設定下拉框   loe 為下拉框名, sSql 為下拉框的資料來源        public static void SetLookUpList(ref DevExpress.XtraEditors.LookUpEdit loeSource, string strSQL, int ValueCol = 0, int NameCol = 1,DataTable dtSource=null,bool bSelectFirstItem=false,string sFirstRowText="")        {            DataTable dt =null;            try            {                if (!string.IsNullOrEmpty(strSQL))                {                    dt = sqlHelper.ExecuteDataTable(Common.ConnString, CommandType.Text, strSQL, null);                }                else if (dtSource != null)                {                    dt = dtSource.Copy();                }            }            catch (Exception ex)            {                frm0201.DisplayMsg("資料訪問異常:", ex.Message.ToString());                return;            }            loeSource.Properties.Columns.Clear();            loeSource.Properties.DataSource = null;            if (dt!=null && dt.Rows.Count > 0 && dt.Columns.Count > 1) // 一般為2列: id列 和 value 列            {                loeSource.Properties.Columns.Add(new LookUpColumnInfo(dt.Columns[ValueCol].ColumnName.ToString()));                loeSource.Properties.Columns[ValueCol].Visible = false; // 隱藏id列                loeSource.Properties.Columns.Add(new LookUpColumnInfo(dt.Columns[NameCol].ColumnName.ToString()));                if (!string.IsNullOrEmpty(sFirstRowText))                {                    DataRow dr = dt.NewRow();                    dr[dt.Columns[ValueCol]] = -1; // 這一項的 ID 值為 -1                    dr[dt.Columns[NameCol]] = sFirstRowText;                    dt.Rows.InsertAt(dr, 0);// 從索引為0的位置插入                }                loeSource.Properties.DataSource = dt;                loeSource.Properties.ValueMember = dt.Columns[ValueCol].ColumnName; // 對應ID                loeSource.Properties.DisplayMember = dt.Columns[NameCol].ColumnName; // 顯示內容                if (bSelectFirstItem) // 是否選中第一項                {                    int iID=0;                    if(Int32.TryParse(dt.Rows[0][0].ToString(),out iID))                    {                        loeSource.EditValue = iID;                    }                }            }            loeSource.Properties.ShowHeader = false;            loeSource.Properties.ShowFooter = false;            loeSource.Properties.NullText = "";        }        // 得到下拉框 的內容項,以Keys.Tab拼成一個字串         public static string GetComboList(string sQuerySQL)        {            try            {                DataTable dtList = sqlHelper.ExecuteDataTable(Common.ConnString, CommandType.Text, sQuerySQL, null);                if (dtList!=null && dtList.Rows.Count > 0 && dtList.Columns.Count == 1) // 有資料且只有一列                {                    string sResult = string.Empty;                    for (int i = 0; i < dtList.Rows.Count; i++)                    {                        sResult += dtList.Rows[i][0].ToString() + Convert.ToChar(Keys.Tab);                    }                    if (sResult.Length > 1)                    {                        sResult = sResult.Remove(sResult.Length - 1);                    }                    return sResult;                }            }            catch (Exception ex)            {                XtraMessageBox.Show(ex.Message.ToString());            }            return "";        }        #endregion