DevExpress:下拉框绑定数据源 (ComboBoxEdit,LookUpEdit)
DevExpress:下拉框绑定数据源 (ComboBoxEdit,LookUpEdit)
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 }