应用自定义类，取汉字拼音首字母

定义：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using ExecPro;

/// <summary>
/// 根据中文或数字或英文，找出拼音和它相同的记录集
/// </summary>
public class GetPinyin
{
    private string record;
    private string field;
    private string values;
    private string connstring;
    private string querystring;

    /// <summary>
    /// 设置源记录集（是一段SQL语句）
    /// </summary>
    public string Record
    {
        get { return record; }
        set { record = value; }
    }

    /// <summary>
    /// 设置要匹配的字段
    /// </summary>
    public string Field
    {
        get { return field; }
        set { field = value; }
    }

    /// <summary>
    /// 设置要验证的值
    /// </summary>
    public string Value
    {
        get { return values; }
        set { values = value; }
    }

    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    public string ConnString
    {
        get { return connstring; }
        set { connstring = value; }
    }

   
    /// <summary>
    /// 构造函数
    /// </summary>
    public GetPinyin() { }
    /// <summary>
    /// 构造函数
    /// </summary>
    public GetPinyin(string Record, string Field, string Value, string Connstring)
    {
        this.Record = Record;
        this.Field = Field;
        this.Value = Value;
        this.ConnString = Connstring;
    }

    /// <summary>
    /// 返回类型为String的查询语句
    /// </summary>
    public string QueryString()
    {
        string SqlString,CurSearchField,CurWhere,ChangValues;
        int i,m,len,startindex,ChinseFlag,ChangeChar;
        SqlString = "";
        CurSearchField = "";
        CurWhere = "";
        field = field + ",";
        len = field.Length;
        startindex=0;
        ChinseFlag=0;   //默认不包含中文
        ChangValues = "";
        for (m = 0; m < values.Length; m++)
        {
            char t;
            t = Convert.ToChar(values.Substring(m, 1));
            if (!((t >= 'a' && t <= 'z') || (t >= 'A' && t <= 'Z') || (t >= '0' && t <= '9') || (t == ' ')))
            {

                ChangeChar = Convert.ToInt32(t);
                if (ChangeChar >= 0x4e00 && ChangeChar <= 0x9fa5)  
                    ChinseFlag = 1;

                ChangValues = ChangValues + t.ToString();
            }
            else
            {
                if (t == ' ')
                    ChangValues = ChangValues + "%";
                else
                    ChangValues = ChangValues + t.ToString();

            }
        }

        if (ChinseFlag == 0)
        {
            for (i = 0; i < len; i++)
            {
                if (field.Substring(i, 1) == ",")
                {

                    CurSearchField = field.Substring(startindex, i - startindex);
                    startindex = i + 1;

                    if (CurWhere == "")
                        CurWhere = " dbo.GetPyOfWord(" + CurSearchField + ") like '%'" + " + '" + ChangValues + "' + '%'";
                    else
                        CurWhere = CurWhere + " or " + " dbo.GetPyOfWord(" + CurSearchField + ") like '%'" + " + '" + ChangValues + "' + '%'";
                }
            }
        }
        else
        {
            for (i = 0; i < len; i++)
            {
                if (field.Substring(i, 1) == ",")
                {

                    CurSearchField = field.Substring(startindex, i - startindex);
                    startindex = i + 1;

                    if (CurWhere == "")
                        CurWhere = CurSearchField + "  like '%'" + " + '" + ChangValues + "' + '%'";
                    else
                        CurWhere = CurWhere + " or " + CurSearchField + "  like '%'" + " + '" + ChangValues + "' + '%'";
                }
            }
        }

        if (CurWhere != "")
            SqlString = "select * from (" + record + ") tt where " + CurWhere;
        else
            SqlString = "select * from (" + record + ") tt";

        return SqlString;
    }

 

    /// <summary>
    /// 返回类型为Reader的记录集
    /// </summary>
    public SqlDataReader ReturnReader()
    {
        string SqlString;
        //SqlString = "select * from (" + record + ") tt where dbo.GetPyOfWord(" + field + ") like '%' + " + "dbo.GetPyOfWord('" + values + "') + '%'";
        SqlString = QueryString();
        SqlDataReader Read = ExecPro.ExecPro.ExecuteReader(connstring, CommandType.Text, SqlString);
        return Read;
    }

    /// <summary>
    /// 返回类型为DataSet的记录集
    /// </summary>
    public DataSet ReturnDataSet()
    {
        string SqlString;
        //SqlString = "select * from (" + record + ") tt where dbo.GetPyOfWord(" + field + ") like '%" + "dbo.GetPyOfWord(" + values + ")%'";
        SqlString = QueryString();
        DataSet  DA = ExecPro.ExecPro.ExecuteDataset(connstring, CommandType.Text, SqlString);
        return DA;
    }
}

 

调用方法
protected void Button1_Click(object sender, EventArgs e)
    {
        GetPinyin PY = new GetPinyin();

        PY.Record = "select cityname from city";
        PY.Field = "cityname,ct"; //支持多字段
        PY.Value = TextBox1.Text;
        PY.ConnString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
        GridView1.DataSource = PY.ReturnDataSet() ;
        //GridView1.DataSource = PY.ReturnReader();
        GridView1.DataBind();   
    } 

附：SQL SERVER函数

CREATE function f_GetFirstPy(@str varchar(50)='')
    returns char(1)
as
begin
    declare @n int, @c char(1), @chn nchar(1)
    select @n=63, @chn=substring(@str,1,1)
    if @chn > 'z'
        if( @chn < '八' ) set @c = 'A'
        else if ( @chn < '嚓' ) set @c = 'B'
        else if ( @chn < '咑' ) set @c = 'C'
        else if ( @chn < '妸' ) set @c = 'D'
        else if ( @chn < '发' ) set @c = 'E'
        else if ( @chn < '旮' ) set @c = 'F'
        else if ( @chn < '铪' ) set @c = 'G'
        else if ( @chn < '丌' ) set @c = 'H'
        else if ( @chn < '丌' ) set @c = 'I'
        else if ( @chn < '咔' ) set @c = 'J'
        else if ( @chn < '垃' ) set @c = 'K'
        else if ( @chn < '嘸' ) set @c = 'L'
        else if ( @chn < '拏' ) set @c = 'M'
        else if ( @chn < '噢' ) set @c = 'N'
        else if ( @chn < '妑' ) set @c = 'O'
        else if ( @chn < '七' ) set @c = 'P'
        else if ( @chn < '呥' ) set @c = 'Q'
        else if ( @chn < '仨' ) set @c = 'R'
        else if ( @chn < '他' ) set @c = 'S'
        else if ( @chn < '屲' ) set @c = 'T'
        else if ( @chn < '屲' ) set @c = 'U'
        else if ( @chn < '屲' ) set @c = 'V'
        else if ( @chn < '夕' ) set @c = 'W'
        else if ( @chn < '丫' ) set @c = 'X'
        else if ( @chn < '帀' ) set @c = 'Y'
        else set @c = 'Z'
    else if @chn <= 'z' and @chn >= 'a'
        set @c = upper(@chn)
    else if @chn <= 'Z' and @chn >= 'A'
        set @c = @chn
    else
        set @c =@chn
    return (@c)
end

 

CREATE function GetPyOfWord(@str varchar(100)='')
    returns varchar(100)
as
begin
    declare @length  int
    declare @i int
    declare @curstring varchar(100)
    declare @py varchar(100)
    set @length=len(@str)
    set @i=1
    set @py=''
    while (@i< =@length)
    begin
 set @curstring=substring(@str,@i,1)
 set @py=@py+dbo.f_GetFirstPy(@curstring)
        set @i=@i+1
    end
    return (@py)
end