1 DevExpress:下拉框绑定数据源 (ComboBoxEdit,LookUpEdit)
2
3
4 // 设置下拉框 传入参数(需要设置的下拉框控件，查询的语句，默认第一行的内容)
5 public static void SetComboList(DevExpress.XtraEditors.ComboBoxEdit ComboList, string QuerySQL, string FirstRowText = "",bool bSelectFirstItem=false)
6 {
7 DataTable dtList=null;
8 try
9 {
10 dtList = sqlHelper.ExecuteDataTable(Common.ConnString, CommandType.Text, QuerySQL, null);
11 }
12 catch (Exception ex)
13 {
14 frm0201.DisplayMsg("数据访问异常:", ex.Message.ToString());
15 return;
16 }
17
18 int intCount = (dtList!=null) ? dtList.Rows.Count : 0;
19 ComboList.Properties.TextEditStyle = TextEditStyles.DisableTextEditor; // 设置 comboBox的文本值不能被编辑
20 ComboList.Properties.Items.Clear();
21 if (!string.IsNullOrEmpty(FirstRowText)) ComboList.Properties.Items.Add(FirstRowText);
22
23 if (intCount > 0)
24 {
25 for (int i = 0; i < intCount; i++)
26 {
27 ComboList.Properties.Items.Add(dtList.Rows[i][0].ToString());
28 }
29 }
30 if (bSelectFirstItem) ComboList.SelectedIndex = 0; // 设置选中第1项
31 }
32
33 //设置下拉框 loe 为下拉框名, sSql 为下拉框的数据来源
34 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="")
35 {
36 DataTable dt =null;
37
38 try
39 {
40 if (!string.IsNullOrEmpty(strSQL))
41 {
42 dt = sqlHelper.ExecuteDataTable(Common.ConnString, CommandType.Text, strSQL, null);
43 }
44 else if (dtSource != null)
45 {
46 dt = dtSource.Copy();
47 }
48 }
49 catch (Exception ex)
50 {
51 frm0201.DisplayMsg("数据访问异常:", ex.Message.ToString());
52 return;
53 }
54
55 loeSource.Properties.Columns.Clear();
56 loeSource.Properties.DataSource = null;
57
58 if (dt!=null && dt.Rows.Count > 0 && dt.Columns.Count > 1) // 一般为2列: id列 和 value 列
59 {
60 loeSource.Properties.Columns.Add(new LookUpColumnInfo(dt.Columns[ValueCol].ColumnName.ToString()));
61 loeSource.Properties.Columns[ValueCol].Visible = false; // 隐藏id列
62 loeSource.Properties.Columns.Add(new LookUpColumnInfo(dt.Columns[NameCol].ColumnName.ToString()));
63
64 if (!string.IsNullOrEmpty(sFirstRowText))
65 {
66 DataRow dr = dt.NewRow();
67 dr[dt.Columns[ValueCol]] = -1; // 这一项的 ID 值为 -1
68 dr[dt.Columns[NameCol]] = sFirstRowText;
69 dt.Rows.InsertAt(dr, 0);// 从索引为0的位置插入
70 }
71
72 loeSource.Properties.DataSource = dt;
73 loeSource.Properties.ValueMember = dt.Columns[ValueCol].ColumnName; // 对应ID
74 loeSource.Properties.DisplayMember = dt.Columns[NameCol].ColumnName; // 显示内容
75
76 if (bSelectFirstItem) // 是否选中第一项
77 {
78 int iID=0;
79 if(Int32.TryParse(dt.Rows[0][0].ToString(),out iID))
80 {
81 loeSource.EditValue = iID;
82 }
83 }
84 }
85
86 loeSource.Properties.ShowHeader = false;
87 loeSource.Properties.ShowFooter = false;
88 loeSource.Properties.NullText = "";
89 }
90
91 // 得到下拉框 的内容项,以Keys.Tab拼成一个字符串
92 public static string GetComboList(string sQuerySQL)
93 {
94 try
95 {
96 DataTable dtList = sqlHelper.ExecuteDataTable(Common.ConnString, CommandType.Text, sQuerySQL, null);
97 if (dtList!=null && dtList.Rows.Count > 0 && dtList.Columns.Count == 1) // 有数据且只有一列
98 {
99 string sResult = string.Empty;
100 for (int i = 0; i < dtList.Rows.Count; i++)
101 {
102 sResult += dtList.Rows[i][0].ToString() + Convert.ToChar(Keys.Tab);
103 }
104
105 if (sResult.Length > 1)
106 {
107 sResult = sResult.Remove(sResult.Length - 1);
108 }
109 return sResult;
110 }
111 }
112 catch (Exception ex)
113 {
114 XtraMessageBox.Show(ex.Message.ToString());
115 }
116
117 return "";
118 }