用户输入查询与拼音首字母的结合，提高用户的操作体验
我们在界面设计的时候，不管是Web的还是Winform的程序，为了方便用户对各种数据进行操作，提高用户的操作体验，都是一个永恒不变的话题，需要尽可能地提高。本文抛砖引玉，介绍本人在Web和Winform中使用拼音简码以及智能提示的具体例子，对这个话题进行探讨。
在下面的Web界面中，我们可以通过拼音首字母或者部分中文内容，来模糊搜索（类似Google搜索的智能提示），一个方便用户搜索，第二个有效利用Ajax技术来提高用户的体验。
用户可以输入中文，一样有智能提示。
在Winform界面中，同样也可以做到智能提示，由于Winform中的响应速度比较快，我们可以根据输入的首字母或者部分中文快速更新列表内容即可，如下图所示。
其实以上两个，都是需要一个Sql函数，就是把中文转换为首字母的函数，以便能够快速搜索内容，下面我列出SqlServer和Oracle的转换首字母的函数。以飨读者。
SqlServer的汉字转拼音码的函数:
代码
--
--
Definition for user-defined function f_GetPy :
--
GO
create
function
[
dbo
]
.f_GetPy(
@str
nvarchar
(
4000
))
returns
nvarchar
(
4000
)
as
begin
declare
@strlen
int
,
@re
nvarchar
(
4000
)
declare
@t
table
(chr
nchar
(
1
) collate Chinese_PRC_CI_AS,letter
nchar
(
1
))
insert
into
@t
(chr,letter)
select
'
吖
'
,
'
A
'
union
all
select
'
八
'
,
'
B
'
union
all
select
'
嚓
'
,
'
C
'
union
all
select
'
咑
'
,
'
D
'
union
all
select
'
妸
'
,
'
E
'
union
all
select
'
发
'
,
'
F
'
union
all
select
'
旮
'
,
'
G
'
union
all
select
'
铪
'
,
'
H
'
union
all
select
'
丌
'
,
'
J
'
union
all
select
'
咔
'
,
'
K
'
union
all
select
'
垃
'
,
'
L
'
union
all
select
'
嘸
'
,
'
M
'
union
all
select
'
拏
'
,
'
N
'
union
all
select
'
噢
'
,
'
O
'
union
all
select
'
妑
'
,
'
P
'
union
all
select
'
七
'
,
'
Q
'
union
all
select
'
呥
'
,
'
R
'
union
all
select
'
仨
'
,
'
S
'
union
all
select
'
他
'
,
'
T
'
union
all
select
'
屲
'
,
'
W
'
union
all
select
'
夕
'
,
'
X
'
union
all
select
'
丫
'
,
'
Y
'
union
all
select
'
帀
'
,
'
Z
'
select
@strlen
=
len
(
@str
),
@re
=
'
'
while
@strlen
>
0
begin
select
top
1
@re
=
letter
+
@re
,
@strlen
=
@strlen
-
1
from
@t
a
where
chr
<=
substring
(
@str
,
@strlen
,
1
)
order
by
chr
desc
if
@@rowcount
=
0
select
@re
=
substring
(
@str
,
@strlen
,
1
)
+
@re
,
@strlen
=
@strlen
-
1
end
return
(
@re
)
end
--
Definition for user-defined function f_GetPy :
--
GO
create
function
[
dbo
]
.f_GetPy(
@str
nvarchar
(
4000
))
returns
nvarchar
(
4000
)
as
begin
declare
@strlen
int
,
@re
nvarchar
(
4000
)
declare
@t
table
(chr
nchar
(
1
) collate Chinese_PRC_CI_AS,letter
nchar
(
1
))
insert
into
@t
(chr,letter)
select
'
吖
'
,
'
A
'
union
all
select
'
八
'
,
'
B
'
union
all
select
'
嚓
'
,
'
C
'
union
all
select
'
咑
'
,
'
D
'
union
all
select
'
妸
'
,
'
E
'
union
all
select
'
发
'
,
'
F
'
union
all
select
'
旮
'
,
'
G
'
union
all
select
'
铪
'
,
'
H
'
union
all
select
'
丌
'
,
'
J
'
union
all
select
'
咔
'
,
'
K
'
union
all
select
'
垃
'
,
'
L
'
union
all
select
'
嘸
'
,
'
M
'
union
all
select
'
拏
'
,
'
N
'
union
all
select
'
噢
'
,
'
O
'
union
all
select
'
妑
'
,
'
P
'
union
all
select
'
七
'
,
'
Q
'
union
all
select
'
呥
'
,
'
R
'
union
all
select
'
仨
'
,
'
S
'
union
all
select
'
他
'
,
'
T
'
union
all
select
'
屲
'
,
'
W
'
union
all
select
'
夕
'
,
'
X
'
union
all
select
'
丫
'
,
'
Y
'
union
all
select
'
帀
'
,
'
Z
'
select
@strlen
=
len
(
@str
),
@re
=
'
'
while
@strlen
>
0
begin
select
top
1
@re
=
letter
+
@re
,
@strlen
=
@strlen
-
1
from
@t
a
where
chr
<=
substring
(
@str
,
@strlen
,
1
)
order
by
chr
desc
if
@@rowcount
=
0
select
@re
=
substring
(
@str
,
@strlen
,
1
)
+
@re
,
@strlen
=
@strlen
-
1
end
return
(
@re
)
end
Oracle的汉字转拼音首字母的函数：
代码
CREATE
OR
REPLACE
FUNCTION
F_PINYIN(P_NAME
IN
VARCHAR2
)
RETURN
VARCHAR2
AS
V_COMPARE
VARCHAR2
(
100
);
V_RETURN
VARCHAR2
(
4000
);
FUNCTION
F_NLSSORT(P_WORD
IN
VARCHAR2
)
RETURN
VARCHAR2
AS
BEGIN
RETURN
NLSSORT(P_WORD,
'
NLS_SORT=SCHINESE_PINYIN_M
'
);
END
;
BEGIN
FOR
I
IN
1
..NVL(LENGTH(P_NAME),
0
) LOOP
V_COMPARE :
=
F_NLSSORT(SUBSTR(P_NAME, I,
1
));
IF
V_COMPARE
>=
F_NLSSORT(
'
吖
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
驁
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
A
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
八
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
簿
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
B
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
嚓
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
錯
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
C
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
咑
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
鵽
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
D
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
妸
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
樲
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
E
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
发
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
猤
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
F
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
旮
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
腂
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
G
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
妎
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
夻
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
H
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
丌
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
攈
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
J
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
咔
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
穒
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
K
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
垃
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
擽
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
L
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
嘸
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
椧
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
M
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
拏
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
瘧
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
N
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
筽
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
漚
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
O
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
妑
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
曝
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
P
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
七
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
裠
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
Q
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
亽
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
鶸
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
R
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
仨
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
蜶
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
S
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
侤
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
籜
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
T
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
屲
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
鶩
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
W
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
夕
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
鑂
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
X
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
丫
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
韻
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
Y
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
帀
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
咗
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
Z
'
;
END
IF
;
END
LOOP;
RETURN
V_RETURN;
END
;
OR
REPLACE
FUNCTION
F_PINYIN(P_NAME
IN
VARCHAR2
)
RETURN
VARCHAR2
AS
V_COMPARE
VARCHAR2
(
100
);
V_RETURN
VARCHAR2
(
4000
);
FUNCTION
F_NLSSORT(P_WORD
IN
VARCHAR2
)
RETURN
VARCHAR2
AS
BEGIN
RETURN
NLSSORT(P_WORD,
'
NLS_SORT=SCHINESE_PINYIN_M
'
);
END
;
BEGIN
FOR
I
IN
1
..NVL(LENGTH(P_NAME),
0
) LOOP
V_COMPARE :
=
F_NLSSORT(SUBSTR(P_NAME, I,
1
));
IF
V_COMPARE
>=
F_NLSSORT(
'
吖
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
驁
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
A
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
八
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
簿
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
B
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
嚓
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
錯
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
C
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
咑
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
鵽
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
D
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
妸
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
樲
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
E
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
发
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
猤
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
F
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
旮
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
腂
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
G
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
妎
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
夻
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
H
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
丌
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
攈
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
J
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
咔
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
穒
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
K
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
垃
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
擽
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
L
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
嘸
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
椧
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
M
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
拏
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
瘧
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
N
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
筽
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
漚
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
O
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
妑
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
曝
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
P
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
七
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
裠
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
Q
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
亽
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
鶸
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
R
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
仨
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
蜶
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
S
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
侤
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
籜
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
T
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
屲
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
鶩
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
W
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
夕
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
鑂
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
X
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
丫
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
韻
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
Y
'
;
ELSIF V_COMPARE
>=
F_NLSSORT(
'
帀
'
)
AND
V_COMPARE
<=
F_NLSSORT(
'
咗
'
)
THEN
V_RETURN :
=
V_RETURN
||
'
Z
'
;
END
IF
;
END
LOOP;
RETURN
V_RETURN;
END
;
使用代码大概如下所示：
使用例子1：
代码
///
<summary>
///
根据商品名称获取商品列表
///
</summary>
///
<param name="goodsType">
商品类型
</param>
///
<returns></returns>
public
List
<
GoodsInfo
>
FindByName(
string
goodsName)
{
string
sql
=
string
.Format(
"
Name like '%{0}%' or dbo.f_GetPy(Name) like '{0}%'
"
, goodsName);
return
this
.Find(sql);
}
使用例子2（基于Ajax的Web智能提示）：
数据库访问层的代码如下所示：
代码
///
<summary>
///
获取公司名称
///
</summary>
///
<param name="topCount"></param>
///
<param name="name"></param>
///
<returns></returns>
public
List
<
string
>
GetTopCompanyName(
int
topCount,
string
name)
{
string
sql
=
string
.Format(
@"
Select * from (Select Company_Name from tb_enterprise where Company_Name like '%{1}%' or F_PINYIN(Company_Name) like '%{1}%' )
WHERE ROWNUM <= {0} ORDER BY ROWNUM ASC
"
, topCount, name);
DataTable dt
=
SqlTable(sql);
List
<
string
>
list
=
new
List
<
string
>
();
foreach
(DataRow row
in
dt.Rows)
{
list.Add(row[
0
].ToString());
}
return
list;
}
Web前台部分页面如下所示：
代码
<
td
align
="left"
style
="background-color: #F1F6FF; width: 200px;"
>
<
asp:TextBox
ID
="txtCompanyName"
runat
="server"
Width
="200"
></
asp:TextBox
>
<
cc1:AutoCompleteExtraExtender
ID
="AutoCompleteExtraExtender1"
runat
="server"
ServiceMethod
="GetCompanyNameList"
TargetControlID
="txtCompanyName"
AsyncPostback
="false"
UseContextKey
="True"
AutoPostback
="true"
MinimumPrefixLength
="2"
CompletionInterval
="10"
OnItemSelected
="AutoCompleteExtraExtender1_ItemSelected"
>
</
cc1:AutoCompleteExtraExtender
>
</
td
>
td
align
="left"
style
="background-color: #F1F6FF; width: 200px;"
>
<
asp:TextBox
ID
="txtCompanyName"
runat
="server"
Width
="200"
></
asp:TextBox
>
<
cc1:AutoCompleteExtraExtender
ID
="AutoCompleteExtraExtender1"
runat
="server"
ServiceMethod
="GetCompanyNameList"
TargetControlID
="txtCompanyName"
AsyncPostback
="false"
UseContextKey
="True"
AutoPostback
="true"
MinimumPrefixLength
="2"
CompletionInterval
="10"
OnItemSelected
="AutoCompleteExtraExtender1_ItemSelected"
>
</
cc1:AutoCompleteExtraExtender
>
</
td
>
Web后台页面的代码如下所示：
代码
[System.Web.Services.WebMethodAttribute(), System.Web.Script.Services.ScriptMethodAttribute()]
public
static
string
[][] GetCompanyNameList(
string
prefixText,
int
count,
string
contextKey)
{
//
获取自动完成的选项数据
List
<
string
[]
>
list
=
new
List
<
string
[]
>
();
List
<
string
>
nameList
=
BLLFactory
<
Enterprise
>
.Instance.GetTopCompanyName(count, prefixText.ToUpper());
for
(
int
i
=
0
; i
<
nameList.Count; i
++
)
{
string
[] Respuesta
=
new
string
[
2
];
Respuesta[
0
]
=
nameList[i];
Respuesta[
1
]
=
i.ToString();
list.Add(Respuesta);
}
return
list.ToArray();
}
protected
void
AutoCompleteExtraExtender1_ItemSelected(
object
sender, EventArgs e)
{
//
TextBox txtCompanyName = FindControl(this.AutoCompleteExtraExtender1.TargetControlID) as TextBox;
//
string companyName = txtCompanyName.Text;
//
根据用户选项更新显示相关内容
BindData();
}
public
static
string
[][] GetCompanyNameList(
string
prefixText,
int
count,
string
contextKey)
{
//
获取自动完成的选项数据
List
<
string
[]
>
list
=
new
List
<
string
[]
>
();
List
<
string
>
nameList
=
BLLFactory
<
Enterprise
>
.Instance.GetTopCompanyName(count, prefixText.ToUpper());
for
(
int
i
=
0
; i
<
nameList.Count; i
++
)
{
string
[] Respuesta
=
new
string
[
2
];
Respuesta[
0
]
=
nameList[i];
Respuesta[
1
]
=
i.ToString();
list.Add(Respuesta);
}
return
list.ToArray();
}
protected
void
AutoCompleteExtraExtender1_ItemSelected(
object
sender, EventArgs e)
{
//
TextBox txtCompanyName = FindControl(this.AutoCompleteExtraExtender1.TargetControlID) as TextBox;
//
string companyName = txtCompanyName.Text;
//
根据用户选项更新显示相关内容
BindData();
}
这样就可以，在界面上输入几个简单的英文字符或者中文名称，就会有智能提示的列表出现，选择其中一个可以显示相关的信息了。
由于在Web开发中，为了实现Ajax的智能提示效果，需要一个特殊的类库，地址如下所示：
https://files.cnblogs.com/wuhuacong/AutoCompleteExtra.rar