C＃&放大器; SQL Server CE的：对待定义的符号后字（+或放大器;）作为搜索一个单独的词 [英] C# & SQL Server CE : treat words after defined symbols (+ or &) as a seperate word in search

问题描述

我在一个简单的翻译应用程序基于C＃和放大器的工作;的SQL Server CE 3.5

我有过 textBox1.Text 与正常SQL搜索数据库中的特定列搜索文本框查询[ SELECT ... LIKE'％％']

---

的我想实现什么：的
我要搜索的数据库中的所有地点的某些符号之后的所有单词（+为例），所以他们并不需要写在完整的上下文（字后字，因为他们在数据库中）

的换句话说：的
< STRONG>我想某些符号后拆的话，那么该程序搜索每个字独立（搜索符号之前的字和后象征单独的每个单词）

< HR>

示例：的
。如果我试图寻找价值举证责任倒置，我有把它写在以前的环境，但是对于用户来讲，这将不适用。所以，我希望他把一个符号，两者之间的话，他愿意以搜索（即他应该寻找减负+证明）

图1： http://i.imgur.com/sd5Y5B7.jpg ，图2：的 http://i.imgur.com/gVj41xP.jpg

---

编辑 - 我的搜索按钮的代码：

  SQLCMD =新SqlCeCommand 
（SELECT * FROM T1 WHERE EnglishWord LIKE'％+ textBox1.Text +％或EnglishDesc LIKE'％+ textBox1.Text +％或ArabicWord LIKE'％+ textBox1.Text +％ 'OR ArabicDesc LIKE'％+ textBox1.Text +％'，sqlcon）; 
 
试
 {
 listView1.Items.Clear（）; 
 sqldr = sqlcmd.ExecuteReader（）; 
 
，而（sqldr.Read（））
 {
 
 ListViewItem的项目=新的ListViewItem（sqldr [ID]的ToString（））; 
 item.SubItems.Add（sqldr [EnglishWord]的ToString（））; 
 item.SubItems.Add（sqldr [EnglishDesc]的ToString（））; 
 item.SubItems.Add（sqldr [ArabicDesc]的ToString（））; 
 item.SubItems.Add（sqldr [ArabicWord]的ToString（））; 
 item.SubItems.Add（sqldr [主题]的ToString（））; 
 
 listView1.Items.Add（项目）; 
} 
 listView1.Enabled = TRUE; 
 label7.Text = listView1.Items.Count.ToString（）; 
} 
赶上（SqlCeException前）
 {
 MessageBox.Show（错误：+ ex.Message，有事吗）; 
} 
  

解决方案

这是答案：理查德推定 @ 的 CodeProject上 的

---

假设你想找到的每个单词出现在所有的记录至少四个一列，这样的事情应该工作：

  SQLCMD = sqlcon.CreateCommand（）; 
 
的String []字= textBox1.Text.Split（新[] {'+'}，StringSplitOptions.RemoveEmptyEntries）; 
 StringBuilder的查询=新的StringBuilder（SELECT * FROM T1 WHERE 1 = 1）; 
 
为（INT指数= 0;指数 -  LT; words.Length;指数++）
 {
串wordToFind =字[指数] 
字符串参数名称=@word+指数; 
 sqlcmd.Parameters.AddWithValue（参数名称，％+ wordToFind +％）; 
 query.AppendFormat（AND（EnglishWord像{0}或EnglishDesc像{0}或ArabicWord像{0}或ArabicDesc像{0}），参数名称）; 
} 
 
 sqlcmd.CommandText = query.ToString（）; 
  

这也将修复代码中的SQL注入[^]漏洞。

如果对负担+证明用户搜索时，查询结果会是这个样子：

  SELECT 
 * 
起价
 T1 
，其中
 1 = 1 
和
（
 EnglishWord像@ WORD0 
或
 EnglishDesc像@ WORD0 
或
 ArabicWord像@ WORD0 
或
 ArabicDesc像@ WORD0 
）
和
 （
 EnglishWord像@字1 
或
 EnglishDesc像@字1 
或
 ArabicWord像@字1 
或
 ArabicDesc像@字1 
）
 
 / * 
参数：
  -  @ WORD0 =％负担％'
  -  @字1 ='％屈服％'
 * / 
  

I'm working on a simple translation application based on C# & SQL Server CE 3.5

I have a search textbox that searches certain columns in database through textBox1.Text with normal SQL query [SELECT.. LIKE '% %']

---

What I want to achieve : I want to search for all the words after certain symbols (+ for example) in all locations in database , so they don't need to be written in the full context (word after word as they exist in database)

In other words : I want to split words after certain symbols , so that the program search for each word independently (search the word before symbol and each word after symbol separately)

---

Example: If I tried to search for the value "burden of proof" , I've to write it in the previous context, but for the user this will not apply. So I want him to put a symbol in-between the two words he is willing to search for (namely he should search for "burden+proof")

Picture 1 : http://i.imgur.com/sd5Y5B7.jpg , Picture 2 : http://i.imgur.com/gVj41xP.jpg

---

Edit - my search button code :

sqlcmd = new SqlCeCommand
                ("SELECT * FROM T1 WHERE EnglishWord like '%" + textBox1.Text + "%' OR EnglishDesc like '%" + textBox1.Text + "%' OR ArabicWord like '%" + textBox1.Text + "%' OR ArabicDesc like '%" + textBox1.Text + "%' ", sqlcon);

            try
            {
                listView1.Items.Clear();
                sqldr = sqlcmd.ExecuteReader();

                while (sqldr.Read())
                {

                    ListViewItem item = new ListViewItem(sqldr["ID"].ToString());
                    item.SubItems.Add(sqldr["EnglishWord"].ToString());
                    item.SubItems.Add(sqldr["EnglishDesc"].ToString());
                    item.SubItems.Add(sqldr["ArabicDesc"].ToString());
                    item.SubItems.Add(sqldr["ArabicWord"].ToString());
                    item.SubItems.Add(sqldr["Subject"].ToString());

                    listView1.Items.Add(item);
                }
                listView1.Enabled = true;
                label7.Text = listView1.Items.Count.ToString();
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show("Error: " + ex.Message, "Something wrong");
            }

解决方案

Answer by : Richard Deeming @ CodeProject

---

Assuming you want to find all records where each word appears in at least one of the four columns, something like this should work:

sqlcmd = sqlcon.CreateCommand();

string[] words = textBox1.Text.Split(new[] { '+' }, StringSplitOptions.RemoveEmptyEntries);
StringBuilder query = new StringBuilder("SELECT * FROM T1 WHERE 1 = 1");

for (int index = 0; index < words.Length; index++)
{
    string wordToFind = words[index];
    string parameterName = "@word" + index;
    sqlcmd.Parameters.AddWithValue(parameterName, "%" + wordToFind + "%");
    query.AppendFormat(" AND (EnglishWord Like {0} OR EnglishDesc Like {0} OR ArabicWord Like {0} OR ArabicDesc Like {0})", parameterName);
}

sqlcmd.CommandText = query.ToString();

This will also fix the SQL Injection[^] vulnerability in your code.

If the user searches for burden+proof, the resulting query will look something like this:

SELECT
    *
FROM
    T1
WHERE
    1 = 1
And
    (
        EnglishWord Like @word0 
    Or 
        EnglishDesc Like @word0 
    Or 
        ArabicWord Like @word0 
    Or 
        ArabicDesc Like @word0
    )
And
    (
        EnglishWord Like @word1 
    Or 
        EnglishDesc Like @word1 
    Or 
        ArabicWord Like @word1 
    Or 
        ArabicDesc Like @word1
    )

/*
Parameters:
- @word0 = '%burden%'
- @word1 = '%proof%'
*/

这篇关于C＃&放大器; SQL Server CE的：对待定义的符号后字（+或放大器;）作为搜索一个单独的词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！