Language:

Queryparser

Language: Deutsch
Programming Language: C#
Published by: Thomas
Last Update: 4/13/2006
Views: 1307

Description

QueryParser ist eine kleine C#-Klasse, die es ermöglicht, Such-Abfragen in eine entsprechende SQL-WHERE-Bedingung zu zerlegen. Das Original stammt von Claudius Ceteras, ich habe es für meine Zwecke in C# übersetzt und will es euch nicht vorenthalten, da ich es für ganz nützlich halte.

Beispielaufruf:

protected void Button1_Click(object sender, EventArgs e)
{
QueryParser qp = new QueryParser();
qp.DefaultOperator = QueryOperator.AND;
qp.QueryType = QueryType.Difference;
qp.MinDifference = 2;
Label1.Text = "SELECT * FROM table WHERE " + qp.Parse(TextBox1.Text, new string[] { "firstname", "lastname" });

qp = new QueryParser();
qp.DefaultOperator = QueryOperator.AND;
qp.QueryType = QueryType.Like;
Label2.Text = "SELECT * FROM table WHERE " + qp.Parse(TextBox1.Text, new string[] { "firstname", "lastname" });

}

Ergebnis von "edmund AND stottern OR "Stotter Ede":

SELECT * FROM table WHERE (DIFFERENCE(firstname,'edmund') >= 2 OR DIFFERENCE(lastname,'edmund') >= 2) AND (DIFFERENCE(firstname,'stottern') >= 2 OR DIFFERENCE(lastname,'stottern') >= 2) OR (DIFFERENCE(firstname,'Stotter Ede') >= 2 OR DIFFERENCE (lastname,'Stotter Ede') >= 2)

SELECT * FROM table WHERE (firstname LIKE '%edmund%' OR lastname LIKE '%edmund%') AND (firstname LIKE '%stottern%' OR lastname LIKE '%stottern%') OR (firstname LIKE '%Stotter Ede%' OR lastname LIKE '%Stotter Ede%')

Code

1 /* Search Query Parser 2 * Version 1.2 3 * Last change: 04. April 2006 4 * 5 * Converted to C# and extended by Thomas Bandt (http://blog.thomasbandt.de) 6 * Based on http://www.glengamoi.com/pipermail/asp.net/2005-May/015598.html 7 * 8 * For further informations see: http://blog.thomasbandt.de 9 */ 10 11 using System; 12 using System.Collections.Generic; 13 using System.Text; 14 15 namespace SnippetCenter.Bll.Search 16 { 17 18 /// <summary> 19 /// Defines the default operator to combine the several conditions. At 20 /// the moment there are no other operators than OR and AND which makes 21 /// sense. 22 /// </summary> 23 public enum QueryOperator 24 { 25 OR, 26 AND 27 } 28 29 /// <summary> 30 /// What type of query should be returned - one which works with Like %% 31 /// or one which uses the DIFFERENCE(). 32 /// </summary> 33 public enum QueryType 34 { 35 Like, 36 Difference 37 } 38 39 /// <summary> 40 /// This class offers in fact the Parse method which parses a query to 41 /// a usable sql where condition statement 42 /// </summary> 43 public class QueryParser 44 { 45 46 /// <summary> 47 /// Contains the serveral terms for the internal parsing process 48 /// </summary> 49 private List<string> SearchTerms; 50 51 /// <summary> 52 /// Defines the characters which are parsed out the search string 53 /// </summary> 54 private const string ExcludedCharacters = "AND OR NOT ( )"; 55 56 /// <summary> 57 /// Default Operator 58 /// </summary> 59 private QueryOperator defaultOperator = QueryOperator.OR; 60 public QueryOperator DefaultOperator 61 { 62 get { return defaultOperator; } 63 set { defaultOperator = value; } 64 } 65 66 /// <summary> 67 /// Query Type 68 /// </summary> 69 private QueryType queryType = QueryType.Like; 70 public QueryType QueryType 71 { 72 get { return queryType; } 73 set { queryType = value; } 74 } 75 76 /// <summary> 77 /// Represents the maximum value of difference allowed by 78 /// using the DIFFERENCE() function 79 /// </summary> 80 private int minDifference = 3; 81 public int MinDifference 82 { 83 get { return minDifference; } 84 set { minDifference = value; } 85 } 86 87 /// <summary> 88 /// Constructor 89 /// </summary> 90 public QueryParser() 91 { 92 SearchTerms = new List<string>(); 93 } 94 95 /// <summary> 96 /// Parsing procedure 97 /// </summary> 98 /// <param name="searchString">Contains the user input, for example "Peace NOT War"</param> 99 /// <param name="fields">String Array, defines the fields in which should be looked for in the database</param> 100 /// <returns>Returns what has to be right to SELECT * FROM Table WHERE </returns> 101 public string Parse(string searchString, string[] fields) 102 { 103 if (!string.IsNullOrEmpty(searchString)) 104 { 105 106 searchString += " "; 107 bool inString = false; 108 string tok = string.Empty; 109 110 // Loop trough searchString and strip it 111 for (int i = 0; i < searchString.Length; i++) 112 { 113 char character = searchString[i]; 114 switch (character) 115 { 116 case '"': 117 inString = !inString; 118 if (tok.Length > 0) 119 { 120 AddToSearchTerms(tok, !inString); 121 tok = string.Empty; 122 } 123 break; 124 case '(': 125 if (!inString) 126 { 127 if (tok.Length > 0) 128 { 129 AddToSearchTerms(tok, false); 130 tok = string.Empty; 131 } 132 AddToSearchTerms(character.ToString(), false); 133 } 134 else 135 { 136 tok += character.ToString(); 137 } 138 break; 139 case ')': 140 goto case '('; 141 default: 142 if ((inString || !character.Equals(' ')) && i+1 < searchString.Length) 143 { 144 tok += character.ToString(); 145 } 146 else 147 { 148 if (tok.Length > 0) 149 { 150 AddToSearchTerms(tok, false); 151 tok = string.Empty; 152 } 153 } 154 break; 155 } 156 } 157 158 StringBuilder where = new StringBuilder(string.Empty); 159 bool wasString = false; 160 string defaultOperator = DefaultOperator.ToString(); 161 162 // Loop trough all SearchTerms items 163 for (int i = 0; i < SearchTerms.Count; i++) 164 { 165 if (ExcludedCharacters.IndexOf(SearchTerms[i]) > -1) 166 { 167 // Prevent constructs like this one: WHERE AND NOT ... 168 if (i > 0 || SearchTerms[i].ToUpper() != "AND") 169 { 170 if (SearchTerms[i] == "(") 171 where.AppendFormat(" {0} ", defaultOperator); 172 173 // Add operator to statement 174 where.AppendFormat(" {0} ", SearchTerms[i]); 175 176 if (SearchTerms[i] == ")") 177 where.AppendFormat(" {0} ", defaultOperator); 178 179 wasString = false; 180 } 181 } 182 else 183 { 184 // Add the default operator to link the single statements 185 if (wasString) 186 where.AppendFormat(" {0} ", defaultOperator); 187 // Add the term for each database column (field). 188 switch (QueryType) 189 { 190 case QueryType.Difference: 191 where.Append("("); 192 for (int y = 0; y < fields.Length; y++) 193 { 194 where.AppendFormat("DIFFERENCE({0},{1}) >= {2}", fields[y], SearchTerms[i], MinDifference); 195 if (y + 1 < fields.Length) 196 where.Append(" OR "); 197 } 198 where.Append(")"); 199 break; 200 default: 201 where.AppendFormat(" ({0} LIKE {1}) ", string.Join(string.Format(" LIKE {0} OR ", SearchTerms[i]), fields), SearchTerms[i]); 202 break; 203 } 204 wasString = true; 205 } 206 } 207 208 // Return the Where statement. Ready :-) 209 return where.ToString(); 210 211 } 212 else 213 { 214 // When searchString is empty, then return an empty string. 215 return string.Empty; 216 } 217 218 } 219 220 /// <summary> 221 /// Buffers an operator or a term to SearchTerms list 222 /// </summary> 223 /// <param name="term">Contains the string which has to be added.</param> 224 /// <param name="isTerm">Define whether the string is a term or an operator.</param> 225 private void AddToSearchTerms(string term, bool isTerm) 226 { 227 // Add an operator 228 if (!isTerm && ExcludedCharacters.ToUpper().IndexOf(term.ToUpper()) > -1) 229 { 230 SearchTerms.Add(term.ToUpper()); 231 } 232 // Add a new term 233 else 234 { 235 switch (QueryType) 236 { 237 case QueryType.Difference: 238 SearchTerms.Add(string.Format("\'{0}\'", term.Replace("'", "''"))); 239 break; 240 default: 241 SearchTerms.Add(string.Format("\'%{0}%\'", term.Replace("'", "''"))); 242 break; 243 } 244 } 245 } 246 247 } 248 }

No comments avaiable

Add a comment

Name *  

Email (won't be displayed) *    

Website  

Comment *  

Sicherheitscode Security Code *    

RSS