这里用到几个函数,一个SQL自定义函数,主要按汉语拼音字母排序,这个函数来源于网上收集，记不得作者是谁了，还有那个取汉字首字母(大写)，首先在这里感谢他们。

Create function GetPy(@str nvarchar(4000))

returns nvarchar(4000)

as

begin

declare @word nchar(1),@PY nvarchar(4000)

set @PY=''

while len(@str)>0

begin

set @word=left(@str,1)

--如果非汉字字符，返回原字符

set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901

then (select top 1 PY from (

select 'A' as PY,N'驁' as word

union all select 'B',N'簿'

union all select 'C',N'錯'

union all select 'D',N'鵽'

union all select 'E',N'樲'

union all select 'F',N'鰒'

union all select 'G',N'腂'

union all select 'H',N'夻'

union all select 'J',N'攈'

union all select 'K',N'穒'

union all select 'L',N'鱳'

union all select 'M',N'旀'

union all select 'N',N'桛'

union all select 'O',N'漚'

union all select 'P',N'曝'

union all select 'Q',N'囕'

union all select 'R',N'鶸'

union all select 'S',N'蜶'

union all select 'T',N'籜'

union all select 'W',N'鶩'

union all select 'X',N'鑂'

union all select 'Y',N'韻'

union all select 'Z',N'咗'

) T

where word>=@word collate Chinese_PRC_CS_AS_KS_WS

order by PY ASC) else @word end)

set @str=right(@str,len(@str)-1)

end

return @PY

end

然后在查询进，用这样SQL语句

Select Id,Title From Table_a order by dbo.getpy(title)

这样做后TITLE已经按汉语拼音字母顺序排列了，

还有一个取汉字取拼音首字母(大写)　函数

1 Public Shared Function Extract_HZ()Function Extract_HZ(ByVal HZ As String) As String

2 Dim ZW(2) As Byte

3 Dim HZ_INT As Long

4 ZW = System.Text.Encoding.Default.GetBytes(HZ)

5 Dim i1 As Integer = CType((ZW(0)), Short)

6 Dim i2 As Integer = CType((ZW(1)), Short)

7 HZ_INT = i1 * 256 + i2

8 If (HZ_INT >= 45217) AndAlso (HZ_INT <= 45252) Then

9 Return "A"

10 End If

11 If (HZ_INT >= 45253) AndAlso (HZ_INT <= 45760) Then

12 Return "B"

13 End If

14 If (HZ_INT >= 45761) AndAlso (HZ_INT <= 46317) Then

15 Return "C"

16 End If

17 If (HZ_INT >= 46318) AndAlso (HZ_INT <= 46825) Then

18 Return "D"

19 End If

20 If (HZ_INT >= 46826) AndAlso (HZ_INT <= 47009) Then

21 Return "E"

22 End If

23 If (HZ_INT >= 47010) AndAlso (HZ_INT <= 47296) Then

24 Return "F"

25 End If

26 If (HZ_INT >= 47297) AndAlso (HZ_INT <= 47613) Then

27 Return "G"

28 End If

29 If (HZ_INT >= 47614) AndAlso (HZ_INT <= 48118) Then

30 Return "H"

31 End If

32 If (HZ_INT >= 48119) AndAlso (HZ_INT <= 49061) Then

33 Return "J"

34 End If

35 If (HZ_INT >= 49062) AndAlso (HZ_INT <= 49323) Then

36 Return "K"

37 End If

38 If (HZ_INT >= 49324) AndAlso (HZ_INT <= 49895) Then

39 Return "L"

40 End If

41 If (HZ_INT >= 49896) AndAlso (HZ_INT <= 50370) Then

42 Return "M"

43 End If

44 If (HZ_INT >= 50371) AndAlso (HZ_INT <= 50613) Then

45 Return "N"

46 End If

47 If (HZ_INT >= 50614) AndAlso (HZ_INT <= 50621) Then

48 Return "O"

49 End If

50 If (HZ_INT >= 50622) AndAlso (HZ_INT <= 50905) Then

51 Return "P"

52 End If

53 If (HZ_INT >= 50906) AndAlso (HZ_INT <= 51386) Then

54 Return "Q"

55 End If

56 If (HZ_INT >= 51387) AndAlso (HZ_INT <= 51445) Then

57 Return "R"

58 End If

59 If (HZ_INT >= 51446) AndAlso (HZ_INT <= 52217) Then

60 Return "S"

61 End If

62 If (HZ_INT >= 52218) AndAlso (HZ_INT <= 52697) Then

63 Return "T"

64 End If

65 If (HZ_INT >= 52698) AndAlso (HZ_INT <= 52979) Then

66 Return "W"

67 End If

68 If (HZ_INT >= 52980) AndAlso (HZ_INT <= 53640) Then

69 Return "X"

70 End If

71 If (HZ_INT >= 53689) AndAlso (HZ_INT <= 54480) Then

72 Return "Y"

73 End If

74 If (HZ_INT >= 54481) AndAlso (HZ_INT <= 55289) Then

75 Return "Z"

76 End If

77 ' Return ("")

78 Return (UCase(Left(HZ, 1)))

79 End Function

接着写一个函数接入字母分隔线

1 Private Function DownListInsertZM(ByVal DropDownListId As DropDownList, ByVal InsertHH As String) As DropDownList

2 Dim j, s, i, c, r As Integer

3 Dim tempdual As String

4 For c = 65 To 90 '大写ASCII码循环

5 i = 0

6 For j = 0 To DropDownListId.Items.Count - 1

7 tempdual = Extract_HZ(DropDownListId.Items(j).Text).Trim

8 If tempdual = Chr(c) Then

9 i = i + 1 '统计相同项数

10 s = j

11 End If

12 Next

13 r = s - i + 1 '计算插入位置的索引号

14 If r < 0 Then

15 r = 0

16 End If

17 If i <> 0 Then

18 DropDownListId.Items.Insert(r, New ListItem((InsertHH & Chr(c) & InsertHH), "*"))

19 End If

20 Next

21 DropDownListId.Items.Insert(0, New ListItem((InsertHH & "请选择" & InsertHH))) '插入"请选择"为Dropdownlist为第一项

22 End Function

23

最后便可实现如下效果:

---请选择---

----A----

啊

爱国者

----B----

变速器

................

笔者后来想了一下，也可以在DataTable实现这个操作，因我是用DataReader的方法读数据，所在后面这个插入分隔线的操作应该在数据绑定之后再进行，如果在DATATABLE中就插好了，可以直接绑定。待有时间写个DataTable的。小弟第一次发文章，请各位指教。