提示suggest

1  数据库 汉字字母的转换函数

 

2  改变数据源 然后重新绑定下

 

create table tb1
(
id integer,
name varchar(20)
)

 

 

 

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

 

 

 

 

 

扩展方法连数据库 执行语句

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace db
{
  public static   class Extend
    {
      public static DataTable ExecuteToDataTable(this string strsql)
      {
          string StrConn = "Data Source=.;user=sa;password=123456;Initial Catalog=master";
          SqlConnection Connection = new SqlConnection(StrConn);
          if (Connection.State != ConnectionState.Open)
              Connection.Close();
          Connection.Open();

          SqlCommand cmd = new SqlCommand();
          cmd.CommandText = strsql;

          SqlDataAdapter Adapter = new SqlDataAdapter(cmd.CommandText, Connection);
         DataSet ds = new DataSet();
                Adapter.Fill(ds);
          Connection.Close();
          return ds.Tables[0];
      }

    }
}

 

 

 

from : textBox  Combox

 

    public partial class Form1 : Form
    {

        DataTable dt = null;

        public Form1()
        {
            InitializeComponent();

            comboBox1.DisplayMember = "Name";
            comboBox1.ValueMember  = "Id";
}
        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
         
        }
        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            dt = FindByName(textBox1.Text.Trim());
            comboBox1.DataSource = dt;
            comboBox1.DroppedDown = true;
        }

        public DataTable  FindByName(string goodsName)
        {
            string sql = string.Format("select * from tb1 where Name like '%{0}%' or dbo.f_GetPy(Name) like '{0}%' ", goodsName);
            return sql.ExecuteToDataTable();
        }