C＃&放大器; SQL Server CE的：对待定义的符号后字（+或放大器;）作为搜索一个单独的词

本文介绍了C＃&放大器; SQL Server CE的：对待定义的符号后字（+或放大器;）作为搜索一个单独的词的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我在一个简单的翻译应用程序基于C＃和放大器的工作;的SQL Server CE 3.5 我有过 textBox1.Text 与正常SQL搜索数据库中的特定列搜索文本框查询[ SELECT ... LIKE'％％'] 的我想实现什么：的 我要搜索的数据库中的所有地点的某些符号之后的所有单词（+为例），所以他们并不需要写在完整的上下文（字后字，因为他们在数据库中） 的换句话说：的 我想某些符号后拆的话，那么该程序搜索每个字独立（搜索符号之前的字和后象征单独的每个单词） 示例：的 。如果我试图寻找价值举证责任倒置，我有把它写在以前的环境，但是对于用户来讲，这将不适用。所以，我希望他把一个符号，两者之间的话，他愿意以搜索（即他应该寻找减负+证明） 图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.5I 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.jpgEdit - 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 @ CodeProjectAssuming 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 T1WHERE 1 = 1And ( 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的：对待定义的符号后字（+或放大器;）作为搜索一个单独的词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！