winform用户输入查询与拼音首字母的结合，提高用户的操作体验

在Winform界面中，同样也可以做到智能提示，由于Winform中的响应速度比较快，我们可以根据输入的首字母或者部分中文快速更新列表内容即可，如下图所示。

其实以上两个，都是需要一个Sql函数，就是把中文转换为首字母的函数，以便能够快速搜索内容，下面我列出SqlServer和Oracle的转换首字母的函数。以飨读者。

SqlServer的汉字转拼音码的函数:

<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->--

-- 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的汉字转拼音首字母的函数：

<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->CREATE ORREPLACE
FUNCTION F_PINYIN(P_NAMEIN
VARCHAR2)RETURN
VARCHAR2 
AS

     V_COMPARE VARCHAR2(100);

     V_RETURN VARCHAR2(4000);



     FUNCTION F_NLSSORT(P_WORDIN
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：

<!--



Code highlighting produced by Actipro CodeHighlighter (freeware)

http://www.CodeHighlighter.com/



-->        ///<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);

            returnthis.Find(sql);

        }