来自:http://www.cnblogs.com/fengmazi/archive/2011/11/18/2254760.html
首先感谢我以前的组长--志远,他教会了我很多很多东西,他的责任心,他的为人处事让我深深敬佩,
在我们所有项目中,有一个功能是必不可少的,那就是查询,不同的需求,需要不同的查询条件,查询条件的设计也是千奇百怪,有时我就想,有没有一种通用的查询控件呢,这样,我们就不需要千篇一律写相同的代码,充分发挥代码的可重用性。
一个查询功能有三个必要条件,一个标题,一个输入控件,一个查询按钮,复杂的我就不在列举了,只有想不到的,没有做不到的。
如果要实现一个通用的查询功能,有几个前提必须明白
1)什么是通用,在我的理解是,可以适合不同的需求。
2)输入控件得包含基本的数据类型,如整形,浮点类型,日期,复制框,下拉列表。。。。。当然也有人说,他只输入一个文本框就行了,什么东西都可以输入,如果你这样想,下面的内容无需再看,这种牛人地球人都无法阻止他了,我更伤不起。
3)多语言的支持,此文不会涉及到,在本人项目中已实现些了功能,我想大家会有很好的办法实现
4)安全,而无需自己添加安全机制,如防SQL注入。
先上效果图,如果有不足之处,还需大家提点,小人不才,不是做得非常完美。
环境配置
开发工具:VS2010,.NET Framework4.0,DXperience-10.2.5
1.先从创建项目开始:创建解决方案(Com),创建类库项目(QueryLibrary),接着创建Window窗口应用程序(ControlQueryText)此项目用于测试
2.在库类Control添加用户控件ControlQuery.cs,界面布局如下图
3.下面开始编写代码了,定义输入框所支持的数据类型,使用枚举类型
1
2
3
4
5
6
7
8
9
10
11
|
public
enum
ControlType
{
TextEdit=0,
//普通文本框
SpinEdit=1,
//数字类型输入框
ComboBox=2,
//下拉列表
DateTime=3,
//日期类型
ButtonEdit=4,
//XX类型,文本框边有个按钮,点击它弹出一个自定义窗口,在自定义窗口输入或选择要输入的条件
GridLookUpEdit= 5,
//XX类型,点击弹出GridControl列表,在里面选择一行或多行数据作为输入条件
MemoExEdit= 6,
//XX类型,是一个多行文本控件,可以输入多个值,以“,”,“;”,“空格”分界
PopEdit = 7
//XX类型,输入自定义DataTable,GridControl,Control
}
|
4.定义一个条件的数据结构DataSources,从左到右依次为:连接符(and 或者 or)左括号,提示符,判断符(=,<,>,<>,like,in等),右括号
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
public
delegate
object
DelegateEditMethod();
public
delegate
void
DelegateReturnControl(
object
control);
public
struct
DataSources
{
public
string
description {
get
;
set
; }
//条件显示名
public
object
value {
get
;
set
; }
//条件值
public
ControlType controlType {
get
;
set
; }
//控件类型
public
bool
defaultSelect {
get
;
set
; }
//是否默显示此条件
public
string
defaultOperator {
get
;
set
; }
//默认连接符
public
object
defaultValue {
get
;
set
; }
//默认值
public
IList<KeyValuePair <
string
,
object
>> selectDataSource {
get
;
set
; }
//选择项的数据源,如下拉列表之类的
public
string
[] group {
get
;
set
; }
//分组 个人感觉这个没有多少意义
public
bool
fixedLine {
get
;
set
; }
//指定不能删除的条件
public
DelegateEditMethod editMethod;
//委托方法
public
DelegateReturnControl returnControl;
//委托控件
}
|
4.定义查询结果集类FilterItem
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
public
class
FilterItem
{
/// <summary>
/// 判斷符 And 或 Or
/// </summary>
public
string
Logical {
get
;
set
; }
/// <summary>
/// 左括號
/// </summary>
public
string
LeftParentheses {
get
;
set
; }
/// <summary>
/// 字段
/// </summary>
public
string
Column {
get
;
set
; }
/// <summary>
/// 連接符 如: =
/// </summary>
public
string
Operator {
get
;
set
; }
/// <summary>
/// 值
/// </summary>
public
string
Value {
get
;
set
; }
/// <summary>
/// 右括號
/// </summary>
public
string
RigthParentheses {
get
;
set
; }
/// <summary>
/// 所属组
/// </summary>
public
string
[] group {
get
;
set
; }
/// <summary>
/// 得到参数名
/// </summary>
public
string
ParameterName {
get
;
set
; }
}
|
5,定义FilterItem集合,实现IEnumerable,IEnumerator ,并提供一般基础方法如 Add,RemoveAt,Clear,得到sql脚本,以Parameter形式的sql脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
|
public
class
Filter:IEnumerable,IEnumerator
{
private
int
index=-1;
private
List<FilterItem> Filters;
public
int
Length
{
get
{
if
(Filters ==
null
)
{
return
0;
}
return
Filters.Count;
}
}
/// <summary>
/// 是否出错 false :出错 , true :未出错
/// </summary>
public
bool
isError {
get
;
set
; }
/// <summary>
/// 出错描述语言
/// </summary>
public
string
Error {
get
;
set
; }
public
FilterItem
this
[
int
index]
{
get
{
if
(Filters==
null
|| index < 0 || index > Filters.Count)
{
return
null
;
}
else
{
return
Filters[index];
}
}
set
{
if
(!(index < 0 || index <= Filters.Count) && Filters!=
null
)
{
Filters[index] = value;
}
}
}
public
FilterItem
this
[
string
columnsName]
{
get
{
if
(Filters ==
null
|| columnsName ==
null
|| columnsName==
""
)
{
return
null
;
}
else
{
return
Filters.Find(
delegate
(FilterItem filter)
{
return
(filter.Column == columnsName);
});
}
}
set
{
if
(Filters !=
null
&& columnsName !=
null
&& columnsName !=
""
)
{
FilterItem filteritem=Filters.Find(
delegate
(FilterItem filter)
{
return
(filter.Column == columnsName);
});
filteritem = value;
}
}
}
public
IEnumerator GetEnumerator()
{
return
(IEnumerator)
this
;
}
public
object
Current
{
get
{
return
Filters[index]; }
}
public
bool
MoveNext()
{
index++;
return
index >= Filters.Count ?
false
:
true
;
}
public
void
Reset()
{
index = -1;
}
#region 一般方法
public
void
Add(FilterItem filterItem)
{
if
(Filters ==
null
)
{
Filters =
new
List<FilterItem>();
}
Filters.Add(filterItem);
}
public
void
RemoveAt(
int
index)
{
if
(Filters !=
null
)
{
Filters.RemoveAt(index);
}
}
public
void
RemoveAt(FilterItem filterItem)
{
if
(Filters !=
null
&& filterItem!=
null
)
{
Filters.Remove(filterItem);
}
}
public
void
Clear()
{
if
(Filters !=
null
)
{
Filters.Clear();
}
}
public
List<FilterItem> FindGroup(
string
group)
{
List<FilterItem> groupFilterItem=Filters.FindAll(
delegate
(FilterItem filter)
{
for
(
int
i = 0; i < filter.group.Length; i++)
{
if
(filter.group[i] == group)
return
true
;
}
return
false
;
});
return
groupFilterItem;
}
#endregion
/// <summary>
/// 得到查询字符串
/// </summary>
/// <returns></returns>
public
override
string
ToString()
{
StringBuilder str =
new
StringBuilder();
if
(Filters ==
null
)
return
""
;
foreach
(FilterItem item
in
Filters)
{
str.Append(
" "
+ item.Logical);
str.Append(
" "
+item.LeftParentheses);
str.Append(
" "
+ item.Column);
str.Append(
" "
+ item.Operator);
str.Append(
" "
+ item.Value);
str.Append(
" "
+item.RigthParentheses);
}
return
str.ToString();
}
/// <summary>
/// 得到分组的查询字符串
/// </summary>
/// <param name="group">组名</param>
/// <returns></returns>
public
string
ToGroupString(
string
group)
{
StringBuilder str =
new
StringBuilder();
if
(Filters ==
null
)
return
""
;
List<FilterItem> groupFilterItem = FindGroup(group);
foreach
(FilterItem item
in
groupFilterItem)
{
str.Append(
" "
+ item.Logical);
str.Append(
" "
+ item.LeftParentheses);
str.Append(
" "
+ item.Column);
str.Append(
" "
+ item.Operator);
str.Append(
" "
+ item.Value);
str.Append(
" "
+ item.RigthParentheses);
}
return
str.ToString();
}
/// <summary>
/// 得到参数查询字符串 如 and Id=@Id 如果有 . 换为 _
/// </summary>
/// <returns></returns>
public
string
ToParameterString()
{
StringBuilder str =
new
StringBuilder();
if
(Filters ==
null
)
return
""
;
foreach
(FilterItem item
in
Filters)
{
str.Append(
" "
+ item.Logical);
str.Append(
" "
+ item.LeftParentheses);
str.Append(
" "
+ item.Column);
str.Append(
" "
+ item.Operator);
str.Append(
" @"
+ item.ParameterName);
str.Append(
" "
+ item.RigthParentheses);
}
return
str.ToString();
}
}
|
6.定义自定义查询事件,在调用控件方触发
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
public
event
btnSearchHandle SearchClick;
public
delegate
void
btnSearchHandle(
object
sender, SearchEventArgs e);
public
class
SearchEventArgs : EventArgs
{
private
Filter filter;
public
SearchEventArgs(Filter _filter)
{
this
.filter = _filter;
}
public
Filter Filter
{
get
{
return
filter; }
}
/// <summary>
/// 此方法还需要改进 返回值应该是 Filter 类,而不是 List<FilterItem> 泛型
/// </summary>
/// <param name="group"></param>
/// <returns></returns>
public
List<FilterItem> GetGroup(
string
group)
{
return
filter.FindGroup(group);
}
}
|
7拼接查询字符串
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
#region 拼接查询字符串
private
Filter GetItemControl()
{
Control c = mainPanelControl;
string
strlogical =
""
;
string
strleftParentheses=
""
;
string
strColumn =
""
;
string
strOperator =
""
;
string
strValue =
""
;
string
strrigthParentheses =
""
;
Filter filter =
new
Filter();
filter.isError=
true
;
filter.Error =
""
;
for
(
int
i = c.Controls.Count - 1; i >-1; i--)
{
if
(c.Controls[i]
is
DevExpress.XtraEditors.PanelControl)
{
if
(c.Controls[i].BackColor == System.Drawing.Color.Red)
c.Controls[i].BackColor = c.Controls[i].Parent.BackColor;
Control.ControlCollection controlCollection = c.Controls[i].Controls;
ComboBoxEdit txtLogical = controlCollection[2]
as
ComboBoxEdit;
ComboBoxEdit txtLeftParentheses = controlCollection[3]
as
ComboBoxEdit;
ComboBoxEdit txtRightParentheses = controlCollection[4]
as
ComboBoxEdit;
ImageComboBoxEdit txtColumn0 = controlCollection[0]
as
ImageComboBoxEdit;
ComboBoxEdit txtOperator = controlCollection[1]
as
ComboBoxEdit;
TextEdit txtValue0 = controlCollection[6]
as
TextEdit;
if
(txtColumn0.EditValue ==
null
||
string
.IsNullOrEmpty(txtColumn0.EditValue.ToString()))
{
continue
;
}
if
(txtLogical.EditValue ==
null
||
string
.IsNullOrEmpty(txtLogical.EditValue.ToString()) ||
txtOperator.EditValue ==
null
||
string
.IsNullOrEmpty(txtOperator.EditValue.ToString()))
{
//filter.Clear();
//filter.isError = false;
//filter.Error += "Column \"" + ((DevExpress.XtraEditors.Controls.ImageComboBoxItem)txtColumn0.SelectedItem).Description + "\" no value," + System.Environment.NewLine;
//c.Controls[i].BackColor = System.Drawing.Color.Red;
//txtValue0.EditValue == null || string.IsNullOrEmpty(txtValue0.EditValue.ToString()
continue
;
}
if
(txtValue0 ==
null
)
{
if
(controlCollection[6]
is
PopEditColumns)
{
PopEditColumns aa = controlCollection[6]
as
PopEditColumns;
if
(aa.GetSelectRow(
false
) ==
null
)
{
continue
;
}
}
else
{
continue
;
}
}
else
if
(txtValue0.EditValue ==
null
)
{
continue
;
}
strlogical = txtLogical.EditValue.ToString();
strleftParentheses = txtLeftParentheses.EditValue.ToString();
strColumn = txtColumn0.EditValue.ToString();
strOperator = txtOperator.EditValue.ToString();
if
(txtValue0!=
null
)
strValue = txtValue0.EditValue.ToString().Replace(
"'"
,
"''"
);
strrigthParentheses = txtRightParentheses.EditValue.ToString();
FilterItem filterItem =
new
FilterItem();
filterItem.Logical = strlogical;
filterItem.LeftParentheses = strleftParentheses;
filterItem.Column = strColumn;
filterItem.Operator = strOperator;
filterItem.ParameterName =
"ParameterName_"
+ i.ToString();
if
(controlCollection[6]
is
PopEditColumns)
{
StringBuilder str =
new
StringBuilder();
PopEditColumns controlColumns = controlCollection[6]
as
PopEditColumns;
List<DataRow> dr = controlColumns.GetSelectRow(
false
);
if
(dr ==
null
|| dr.Count == 0)
continue
;
string
[] keyname = filterItem.Column.Split(
'/'
);
foreach
(DataRow item
in
dr)
{
str.Append(
" or "
);
bool
temp =
true
;
for
(
int
j = 0; j < keyname.Length; j++)
{
if
(String.IsNullOrEmpty(keyname[j]))
continue
;
string
[] columns = keyname[j].Split(
':'
);
if
(item[columns[0]] ==
null
)
continue
;
if
(temp)
{
str.Append(columns[1] +
"='"
+ item[columns[0]].ToString() +
"'"
);
temp =
false
;
}
else
{
str.Append(
" and "
+ columns[1] +
"='"
+ item[columns[0]].ToString() +
"'"
);
}
}
}
str = str.Remove(0, 4).Insert(0,
" ("
).Insert(str.Length,
") "
);
filterItem.Column =
""
;
filterItem.Operator =
""
;
filterItem.Value = str.ToString();
}
else
{
if
(txtValue0
is
DevExpress.XtraEditors.SpinEdit)
{
filterItem.Value = strValue;
}
else
if
(txtValue0
is
DevExpress.XtraEditors.DateEdit)
{
DateTime valuedate =
new
DateTime();
DateTime.TryParse(strValue,
out
valuedate);
filterItem.Value = strValue;
if
(filterItem.Operator ==
"<"
|| filterItem.Operator ==
"<="
)
{
filterItem.Value =
"convert(datetime,'"
+ valuedate.ToString(
"yyyy-MM-dd 23:59:59.999"
) +
"')"
;
}
else
if
(filterItem.Operator ==
">"
|| filterItem.Operator ==
">="
)
{
filterItem.Value =
"convert(datetime,'"
+ valuedate.ToString(
"yyyy-MM-dd 00:00:00.000"
) +
"')"
;
}
else
if
(filterItem.Operator ==
"="
)
{
filterItem.Operator =
" between "
;
filterItem.Value =
"convert(datetime,'"
+ valuedate.ToString(
"yyyy-MM-dd 00:00:00.000"
) +
"')"
+
" and "
+
"convert(datetime,'"
+ valuedate.ToString(
"yyyy-MM-dd 23:59:59.999"
) +
"')"
;
}
else
{
filterItem.Value =
"convert(datetime,'"
+ valuedate +
"')"
;
}
}
else
if
(txtValue0
is
DevExpress.XtraEditors.MemoExEdit)
{
if
(strOperator ==
"in"
)
{
//分隔符 ",",";" "换行"
//string[] split = strValue.Split(new string[] { ",", ";", System.Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
strValue = strValue.Replace(
","
,
"','"
).Replace(
";"
,
"','"
).Replace(System.Environment.NewLine,
"','"
);
filterItem.Value =
"('"
+ strValue +
"')"
;
}
else
filterItem.Value =
" '"
+ strValue +
"'"
;
}
else
{
if
(strOperator ==
"in"
)
filterItem.Value =
"("
+ strValue +
")"
;
else
filterItem.Value =
" '"
+ strValue +
"'"
;
}
}
filterItem.RigthParentheses = strrigthParentheses;
filter.Add(filterItem);
}
}
return
filter;
}
#endregion
|
主要方法在这了,并附上源码,里面包含测试用例,下载源码