private void ExportDataAsCsv(ViewPage page, DbDataReader reader, StreamWriter writer) { var firstField = true; for (var i = 0; (i < page.Fields.Count); i++) { var field = page.Fields[i]; if (!field.Hidden && (field.Type != "DataView")) { if (firstField) { firstField = false; } else { writer.Write(System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator); } if (!(string.IsNullOrEmpty(field.AliasName))) { field = page.FindField(field.AliasName); } writer.Write("\"{0}\"", field.Label.Replace("\"", "\"\"")); } field.NormalizeDataFormatString(); } writer.WriteLine(); while (reader.Read()) { firstField = true; for (var j = 0; (j < page.Fields.Count); j++) { var field = page.Fields[j]; if (!field.Hidden && (field.Type != "DataView")) { if (firstField) { firstField = false; } else { writer.Write(System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator); } if (!(string.IsNullOrEmpty(field.AliasName))) { field = page.FindField(field.AliasName); } var text = string.Empty; var v = reader[field.Name]; if (!(DBNull.Value.Equals(v))) { if (!(string.IsNullOrEmpty(field.DataFormatString))) { text = string.Format(field.DataFormatString, v); } else { text = Convert.ToString(v); } writer.Write("\"{0}\"", text.Replace("\"", "\"\"")); } else { writer.Write("\"\""); } } } writer.WriteLine(); } }
private void ExportDataAsRowset(ViewPage page, DbDataReader reader, StreamWriter writer) { var s = "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"; var dt = "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"; var rs = "urn:schemas-microsoft-com:rowset"; var z = "#RowsetSchema"; var settings = new XmlWriterSettings(); settings.CloseOutput = false; var output = XmlWriter.Create(writer, settings); output.WriteStartDocument(); output.WriteStartElement("xml"); output.WriteAttributeString("xmlns", "s", null, s); output.WriteAttributeString("xmlns", "dt", null, dt); output.WriteAttributeString("xmlns", "rs", null, rs); output.WriteAttributeString("xmlns", "z", null, z); // declare rowset schema output.WriteStartElement("Schema", s); output.WriteAttributeString("id", "RowsetSchema"); output.WriteStartElement("ElementType", s); output.WriteAttributeString("name", "row"); output.WriteAttributeString("content", "eltOnly"); output.WriteAttributeString("CommandTimeout", rs, "60"); var fields = new List <DataField>(); foreach (var field in page.Fields) { if (!((field.Hidden || (field.OnDemand || (field.Type == "DataView")))) && !(fields.Contains(field))) { var aliasField = field; if (!(string.IsNullOrEmpty(field.AliasName))) { aliasField = page.FindField(field.AliasName); } fields.Add(aliasField); } } var number = 1; foreach (var field in fields) { field.NormalizeDataFormatString(); output.WriteStartElement("AttributeType", s); output.WriteAttributeString("name", field.Name); output.WriteAttributeString("number", rs, number.ToString()); output.WriteAttributeString("nullable", rs, "true"); output.WriteAttributeString("name", rs, field.Label); output.WriteStartElement("datatype", s); var type = RowsetTypeMap[field.Type]; string dbType = null; if ("{0:c}".Equals(field.DataFormatString, StringComparison.CurrentCultureIgnoreCase)) { dbType = "currency"; } else if (!(string.IsNullOrEmpty(field.DataFormatString)) && field.Type != "DateTime") { type = "string"; } output.WriteAttributeString("type", dt, type); output.WriteAttributeString("dbtype", rs, dbType); output.WriteEndElement(); output.WriteEndElement(); number++; } output.WriteStartElement("extends", s); output.WriteAttributeString("type", "rs:rowbase"); output.WriteEndElement(); output.WriteEndElement(); output.WriteEndElement(); // output rowset data output.WriteStartElement("data", rs); while (reader.Read()) { output.WriteStartElement("row", z); foreach (var field in fields) { var v = reader[field.Name]; if (!(DBNull.Value.Equals(v))) { if (!(string.IsNullOrEmpty(field.DataFormatString)) && !(((field.DataFormatString == "{0:d}") || (field.DataFormatString == "{0:c}")))) { output.WriteAttributeString(field.Name, string.Format(field.DataFormatString, v)); } else if (field.Type == "DateTime") { output.WriteAttributeString(field.Name, ((DateTime)(v)).ToString("s")); } else { output.WriteAttributeString(field.Name, v.ToString()); } } } output.WriteEndElement(); } output.WriteEndElement(); output.WriteEndElement(); output.WriteEndDocument(); output.Close(); }
private void ExportDataAsRss(ViewPage page, DbDataReader reader, StreamWriter writer) { var appPath = Regex.Replace(HttpContext.Current.Request.Url.AbsoluteUri, "^(.+)Export.ashx.+$", "$1", RegexOptions.IgnoreCase); var settings = new XmlWriterSettings(); settings.CloseOutput = false; var output = XmlWriter.Create(writer, settings); output.WriteStartDocument(); output.WriteStartElement("rss"); output.WriteAttributeString("version", "2.0"); output.WriteStartElement("channel"); output.WriteElementString("title", ((string)(_view.Evaluate("string(concat(/c:dataController/@label, \' | \', @label))", Resolver)))); output.WriteElementString("lastBuildDate", DateTime.Now.ToString("r")); output.WriteElementString("language", System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToLower()); var rowCount = 0; while ((rowCount < MaximumRssItems) && reader.Read()) { output.WriteStartElement("item"); var hasTitle = false; var hasPubDate = false; var desc = new StringBuilder(); for (var i = 0; (i < page.Fields.Count); i++) { var field = page.Fields[i]; if (!field.Hidden && field.Type != "DataView") { if (rowCount == 0) { field.NormalizeDataFormatString(); } if (!(string.IsNullOrEmpty(field.AliasName))) { field = page.FindField(field.AliasName); } var text = string.Empty; var v = reader[field.Name]; if (!(DBNull.Value.Equals(v))) { if (!(string.IsNullOrEmpty(field.DataFormatString))) { text = string.Format(field.DataFormatString, v); } else { text = Convert.ToString(v); } } if (!hasPubDate && (field.Type == "DateTime")) { hasPubDate = true; if (!(string.IsNullOrEmpty(text))) { output.WriteElementString("pubDate", ((DateTime)(reader[field.Name])).ToString("r")); } } if (!hasTitle) { hasTitle = true; output.WriteElementString("title", text); var link = new StringBuilder(); link.Append(_config.Evaluate("string(/c:dataController/@name)")); foreach (var pkf in page.Fields) { if (pkf.IsPrimaryKey) { link.Append(string.Format("&{0}={1}", pkf.Name, reader[pkf.Name])); } } var itemGuid = string.Format("{0}Details.aspx?l={1}", appPath, HttpUtility.UrlEncode(Convert.ToBase64String(Encoding.Default.GetBytes(link.ToString())))); output.WriteElementString("link", itemGuid); output.WriteElementString("guid", itemGuid); } else if (!(string.IsNullOrEmpty(field.OnDemandHandler)) && (field.OnDemandStyle == OnDemandDisplayStyle.Thumbnail)) { if (text.Equals("1")) { desc.AppendFormat("{0}:<br /><img src=\"{1}Blob.ashx?{2}=t", HttpUtility.HtmlEncode(field.Label), appPath, field.OnDemandHandler); foreach (var f in page.Fields) { if (f.IsPrimaryKey) { desc.Append("|"); desc.Append(reader[f.Name]); } } desc.Append("\" style=\"width:92px;height:71px;\"/><br />"); } } else { desc.AppendFormat("{0}: {1}<br />", HttpUtility.HtmlEncode(field.Label), HttpUtility.HtmlEncode(text)); } } } output.WriteStartElement("description"); output.WriteCData(string.Format("<span style=\\\"font-size:small;\\\">{0}</span>", desc.ToString())); output.WriteEndElement(); output.WriteEndElement(); rowCount++; } output.WriteEndElement(); output.WriteEndElement(); output.WriteEndDocument(); output.Close(); }
public int ExecuteAction(ActionArgs args, ActionResult result, ViewPage page) { DataTable t = GetTable(args.Controller, null); if (args.CommandName == "Insert") { DataRow r = t.NewRow(); foreach (FieldValue v in args.Values) { DataField f = page.FindField(v.Name); if (f.IsPrimaryKey && f.ReadOnly) { object key = null; if (f.Type == "Guid") { key = Guid.NewGuid(); } else if (!(PrimaryKeys.TryGetValue(args.Controller, out key))) { key = -1; PrimaryKeys.Add(args.Controller, key); } else { key = (Convert.ToInt32(key) - 1); PrimaryKeys[args.Controller] = key; } r[v.Name] = key; result.Values.Add(new FieldValue(v.Name, key)); FieldValue fv = args.SelectFieldValueObject(v.Name); fv.NewValue = key; fv.Modified = true; } else if (v.Modified) { if (v.NewValue == null) { r[v.Name] = DBNull.Value; } else { r[v.Name] = v.NewValue; } } } t.Rows.Add(r); return(1); } else { DataRow targetRow = null; foreach (DataRow r in t.Rows) { bool matched = true; foreach (DataField f in page.Fields) { if (f.IsPrimaryKey) { object kv = r[f.Name]; object kv2 = args.SelectFieldValueObject(f.Name).OldValue; if (((kv == null) || (kv2 == null)) || !((kv.ToString() == kv2.ToString()))) { matched = false; break; } } } if (matched) { targetRow = r; break; } } if (targetRow == null) { return(0); } if (args.CommandName == "Delete") { t.Rows.Remove(targetRow); } else { foreach (FieldValue v in args.Values) { if (v.Modified) { if (v.NewValue == null) { targetRow[v.Name] = DBNull.Value; } else { targetRow[v.Name] = v.NewValue; } } } } return(1); } }
void IPlugIn.ProcessPageRequest(PageRequest request, ViewPage page) { if (page.Rows.Count == 0) { page.Icons = new string[0]; return; } if (!(_requireProcessing)) { List<string> icons = new List<string>(); for (int i = 0; (i < page.Rows.Count); i++) { string rowDir = AnnotationPlugIn.GenerateDataRecordPath(request.Controller, page, null, i); if (Directory.Exists(rowDir)) icons.Add("Attachment"); else icons.Add(null); } page.Icons = icons.ToArray(); return; } List<DynamicExpression> expressions = new List<DynamicExpression>(page.Expressions); DynamicExpression de = new DynamicExpression(); de.Target = "Annotations"; de.Scope = DynamicExpressionScope.CategoryVisibility; de.Type = DynamicExpressionType.ClientScript; de.Test = "!this.get_isInserting()"; de.ViewId = page.View; expressions.Add(de); page.Expressions = expressions.ToArray(); if (!(_retrieveAnnotations)) return; DataField field = page.FindField("_Annotation_AttachmentNew"); if (field != null) { int fieldIndex = page.Fields.IndexOf(field); string newValue = String.Format("{0},{1}|{2}", request.Controller, field.Name, Regex.Replace(((string)(page.Rows[0][fieldIndex])), "^\\w+\\|(.+)$", "$1")); if (field.Name == "_Annotation_AttachmentNew") newValue = ("null|" + newValue); page.Rows[0][fieldIndex] = newValue; } string p = AnnotationPlugIn.GenerateDataRecordPath(request.Controller, page, null, 0); if (Directory.Exists(p)) { string[] files = Directory.GetFiles(p, "*.xml"); List<object> values = new List<object>(page.Rows[0]); int i = (files.Length - 1); while (i >= 0) { string filename = files[i]; XPathDocument doc = new XPathDocument(filename); XPathNavigator nav = doc.CreateNavigator().SelectSingleNode("/*"); DataField f = null; if (nav.Name == "note") { f = new DataField(); f.Name = "_Annotation_Note"; f.Type = "String"; f.HeaderText = String.Format(Localizer.Replace("AnnotationNoteDynamicFieldHeaderText", "{0} written at {1}"), ReadNameAndEmail(nav), Convert.ToDateTime(nav.GetAttribute("timestamp", String.Empty))); f.Columns = 50; f.Rows = 7; f.TextMode = TextInputMode.Note; values.Add(nav.Value); } else if (nav.Name == "attachment") { f = new DataField(); f.Name = "_Annotation_Attachment"; f.Type = "Byte[]"; f.HeaderText = String.Format(Localizer.Replace("AnnotationAttachmentDynamicFieldHeaderText", "{0} attached <b>{1}</b> at {2}"), ReadNameAndEmail(nav), nav.GetAttribute("fileName", String.Empty), Convert.ToDateTime(nav.GetAttribute("timestamp", String.Empty))); f.OnDemand = true; f.OnDemandHandler = "AnnotationPlugIn"; f.OnDemandStyle = OnDemandDisplayStyle.Link; if (nav.GetAttribute("contentType", String.Empty).StartsWith("image/")) f.OnDemandStyle = OnDemandDisplayStyle.Thumbnail; f.CategoryIndex = (page.Categories.Count - 1); values.Add(nav.GetAttribute("value", String.Empty)); } if (f != null) { f.Name = (f.Name + Path.GetFileNameWithoutExtension(filename)); f.AllowNulls = true; f.CategoryIndex = (page.Categories.Count - 1); if (!(Controller.UserIsInRole("Administrators"))) f.ReadOnly = true; page.Fields.Add(f); } i = (i - 1); } page.Rows[0] = values.ToArray(); if (files.Length > 0) { page.Categories[(page.Categories.Count - 1)].Tab = Localizer.Replace("AnnotationTab", "Notes & Attachments"); expressions.RemoveAt((expressions.Count - 1)); page.Expressions = expressions.ToArray(); } } else { de.Test = "this.get_isEditing() && this.get_view()._displayAnnotations"; ActionGroup g = new ActionGroup(); page.ActionGroups.Add(g); g.Scope = "ActionBar"; g.Flat = true; Action a = new Action(); g.Actions.Add(a); a.WhenLastCommandName = "Edit"; a.WhenView = page.View; a.CommandName = "ClientScript"; a.CommandArgument = "this.get_view()._displayAnnotations=true;this._focusedFieldName = \'_Annotation_No" + "teNew\';this._raiseSelectedDelayed=false;"; a.HeaderText = Localizer.Replace("AnnotationActionHeaderText", "Annotate"); a.CssClass = "AttachIcon"; a.WhenClientScript = "this.get_view()._displayAnnotations!=true;"; } }
protected virtual void AppendFilterExpressionsToWhere(StringBuilder sb, ViewPage page, DbCommand command, SelectClauseDictionary expressions, string whereClause) { string quickFindHint = page.QuickFindHint; bool firstCriteria = String.IsNullOrEmpty(_viewFilter); if (!(firstCriteria)) { EnsureWhereKeyword(sb); sb.AppendLine("("); sb.Append(ProcessViewFilter(page, command, expressions)); } int matchListCount = 0; bool firstDoNotMatch = true; string logicalConcat = "and "; if (page.Filter != null) { foreach (string filterExpression in page.Filter) { Match matchingMode = MatchingModeRegex.Match(filterExpression); if (matchingMode.Success) { bool doNotMatch = (matchingMode.Groups["Match"].Value == "_donotmatch_"); if (doNotMatch) { if (firstDoNotMatch) { firstDoNotMatch = false; EnsureWhereKeyword(sb); if (!(firstCriteria)) { sb.AppendLine(")"); } if (matchListCount > 0) { sb.AppendLine(")"); } if (!(firstCriteria) || (matchListCount > 0)) { sb.AppendLine("and"); } matchListCount = 0; sb.AppendLine(" not"); firstCriteria = true; } } if (matchListCount == 0) { EnsureWhereKeyword(sb); if (!(firstCriteria)) { sb.Append(") and"); } // the list of matches begins sb.AppendLine("("); } else { sb.AppendLine(")"); sb.AppendLine("or"); } // begin a list of conditions for the next match if (matchingMode.Groups["Scope"].Value == "$all$") { logicalConcat = " and "; } else { logicalConcat = " or "; } matchListCount++; firstCriteria = true; } Match filterMatch = FilterExpressionRegex.Match(filterExpression); if (filterMatch.Success) { // "ProductName:?g", "CategoryCategoryName:=Condiments\x00=Seafood" bool firstValue = true; string fieldOperator = " or "; if (Regex.IsMatch(filterMatch.Groups["Values"].Value, ">|<")) { fieldOperator = " and "; } Match valueMatch = FilterValueRegex.Match(filterMatch.Groups["Values"].Value); while (valueMatch.Success) { string alias = filterMatch.Groups["Alias"].Value; string operation = valueMatch.Groups["Operation"].Value; string paramValue = valueMatch.Groups["Value"].Value; if ((operation == "~") && (alias == "_quickfind_")) { alias = page.Fields[0].Name; } bool deepSearching = alias.Contains(","); DataField field = page.FindField(alias); if (((((field != null) && field.AllowQBE) || (operation == "~")) && (((page.DistinctValueFieldName != field.Name || (matchListCount > 0)) || (operation == "~")) || (page.AllowDistinctFieldInFilter || page.CustomFilteredBy(field.Name)))) || deepSearching) { if (firstValue) { if (firstCriteria) { EnsureWhereKeyword(sb); sb.AppendLine("("); firstCriteria = false; } else { sb.Append(logicalConcat); } sb.Append("("); firstValue = false; } else { sb.Append(fieldOperator); } if (deepSearching) { string deepSearchFieldName = alias.Substring(0, alias.IndexOf(',')); string hint = alias.Substring((deepSearchFieldName.Length + 1)); string deepFilterExpression = (deepSearchFieldName + filterExpression.Substring(filterExpression.IndexOf(':'))); AppendDeepFilter(hint, page, command, sb, deepFilterExpression); } else if (operation == "~") { paramValue = Convert.ToString(StringToValue(paramValue)); List <string> words = new List <string>(); List <List <string> > phrases = new List <List <string> >(); phrases.Add(words); CultureInfo currentCulture = CultureInfo.CurrentCulture; string textDateNumber = ("\\p{L}\\d" + Regex.Escape((currentCulture.DateTimeFormat.DateSeparator + (currentCulture.DateTimeFormat.TimeSeparator + currentCulture.NumberFormat.NumberDecimalSeparator)))); string[] removableNumericCharacters = new string[] { currentCulture.NumberFormat.NumberGroupSeparator, currentCulture.NumberFormat.CurrencyGroupSeparator, currentCulture.NumberFormat.CurrencySymbol }; Match m = Regex.Match(paramValue, String.Format("\\s*(?\'Token\'((?\'Quote\'\")(?\'Value\'.+?)\")|((?\'Quote\'\\\')(?\'Value\'.+?)\\\')|(,|;|(^|\\s+" + ")-)|(?\'Value\'[{0}]+))", textDateNumber)); bool negativeSample = false; while (m.Success) { string token = m.Groups["Token"].Value.Trim(); if ((token == ",") || (token == ";")) { words = new List <string>(); phrases.Add(words); negativeSample = false; } else if (token == "-") { negativeSample = true; } else { string exactFlag = "="; if (String.IsNullOrEmpty(m.Groups["Quote"].Value)) { exactFlag = " "; } string negativeFlag = " "; if (negativeSample) { negativeFlag = "-"; negativeSample = false; } words.Add(String.Format("{0}{1}{2}", negativeFlag, exactFlag, m.Groups["Value"].Value)); } m = m.NextMatch(); } bool firstPhrase = true; foreach (List <String> phrase in phrases) { if (phrase.Count > 0) { if (firstPhrase) { firstPhrase = false; } else { sb.AppendLine("or"); } sb.AppendLine("("); bool firstWord = true; System.DateTime paramValueAsDate; foreach (string paramValueWord in phrase) { bool negativeFlag = (paramValueWord[0] == '-'); bool exactFlag = (paramValueWord[1] == '='); string comparisonOperator = "like"; if (exactFlag) { comparisonOperator = "="; } string pv = paramValueWord.Substring(2); bool paramValueIsDate = SqlStatement.TryParseDate(command.GetType(), pv, out paramValueAsDate); bool firstTry = true; DbParameter parameter = null; if (!(paramValueIsDate)) { pv = SqlStatement.EscapePattern(command, pv); } double paramValueAsNumber; string testNumber = pv; foreach (string s in removableNumericCharacters) { testNumber = testNumber.Replace(s, string.Empty); } bool paramValueIsNumber = double.TryParse(testNumber, out paramValueAsNumber); if (!(exactFlag) && !(pv.Contains("%"))) { pv = String.Format("%{0}%", pv); } if (firstWord) { firstWord = false; } else { sb.Append("and"); } if (negativeFlag) { sb.Append(" not"); } sb.Append("("); bool hasTests = false; DbParameter originalParameter = null; if (String.IsNullOrEmpty(quickFindHint) || !(quickFindHint.StartsWith(";"))) { foreach (DataField tf in page.Fields) { if ((tf.AllowQBE && String.IsNullOrEmpty(tf.AliasName)) && (!((tf.IsPrimaryKey && tf.Hidden)) && (!(tf.Type.StartsWith("Date")) || paramValueIsDate))) { hasTests = true; if ((parameter == null) || command.GetType().FullName.Contains("ManagedDataAccess")) { parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv; if (exactFlag && paramValueIsNumber) { parameter.DbType = DbType.Double; parameter.Value = paramValueAsNumber; } } if (!((exactFlag && ((!(tf.Type.Contains("String")) && !(paramValueIsNumber)) || (tf.Type.Contains("String") && paramValueIsNumber))))) { if (firstTry) { firstTry = false; } else { sb.Append(" or "); } if (tf.Type.StartsWith("Date")) { DbParameter dateParameter = command.CreateParameter(); dateParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); dateParameter.DbType = DbType.DateTime; command.Parameters.Add(dateParameter); dateParameter.Value = paramValueAsDate; if (negativeFlag) { sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); } sb.AppendFormat("({0} = {1})", expressions[tf.ExpressionName()], dateParameter.ParameterName); } else { bool skipLike = false; if (!((comparisonOperator == "=")) && ((tf.Type == "String") && ((tf.Len > 0) && (tf.Len < pv.Length)))) { string pv2 = pv; pv2 = pv2.Substring(1); if (tf.Len < pv2.Length) { pv2 = pv2.Substring(0, (pv2.Length - 1)); } if (pv2.Length > tf.Len) { skipLike = true; } else { originalParameter = parameter; parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv2; } } if (_serverRules.EnableResultSet) { string fieldNameExpression = expressions[tf.ExpressionName()]; if ((tf.Type != "String" && !(exactFlag)) || (tf.Type == "Boolean")) { fieldNameExpression = String.Format("convert({0}, \'System.String\')", fieldNameExpression); } if (negativeFlag) { sb.AppendFormat("({0} is not null)and", fieldNameExpression); } sb.AppendFormat("({0} {2} {1})", fieldNameExpression, parameter.ParameterName, comparisonOperator); } else if (skipLike) { sb.Append("1=0"); } else { if (negativeFlag) { sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); } if (DatabaseEngineIs(command, "Oracle", "DB2", "Firebird")) { sb.AppendFormat("(upper({0}) {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); parameter.Value = Convert.ToString(parameter.Value).ToUpper(); } else { sb.AppendFormat("({0} {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); } } } } if (originalParameter != null) { parameter = originalParameter; originalParameter = null; } } } } if (!(String.IsNullOrEmpty(quickFindHint)) && quickFindHint.Contains(";")) { sb.AppendLine(); if (hasTests) { sb.AppendLine("or"); } else { hasTests = true; } sb.AppendLine("("); bool firstHint = true; foreach (string hint in quickFindHint.Substring((quickFindHint.IndexOf(';') + 1)).Split(new char[] { ';' })) { if (firstHint) { firstHint = false; } else { sb.AppendLine(); sb.AppendLine("or"); } sb.AppendLine("("); string newFilterExpression = filterExpression; StringBuilder reversedFilterExpression = new StringBuilder(); if (negativeFlag) { bool firstExpressionPhrase = true; foreach (List <string> ph in phrases) { if (firstExpressionPhrase) { firstExpressionPhrase = false; } else { reversedFilterExpression.Append(","); } bool firstExpressionWord = true; foreach (string w in ph) { if (firstExpressionWord) { firstExpressionWord = false; } else { reversedFilterExpression.Append(" "); } if (!((w[0] == '-'))) { reversedFilterExpression.Append("-"); } if (w[1] == '=') { reversedFilterExpression.Append("\""); } reversedFilterExpression.Append(w.Substring(2)); if (w[1] == '=') { reversedFilterExpression.Append("\""); } } } newFilterExpression = ("_quickfind_:~" + ValueToString(reversedFilterExpression.ToString())); } AppendDeepFilter(hint, page, command, sb, newFilterExpression); sb.AppendLine(")"); } sb.AppendLine(")"); } if (!(hasTests)) { if (negativeFlag && quickFindHint.StartsWith(";")) { sb.Append("1=1"); } else { sb.Append("1=0"); } } sb.Append(")"); } sb.AppendLine(")"); } } if (firstPhrase) { sb.Append("1=1"); } } else if (operation.StartsWith("$")) { sb.Append(FilterFunctions.Replace(command, expressions, String.Format("{0}({1}$comma${2})", operation.TrimEnd('$'), alias, Convert.ToBase64String(Encoding.UTF8.GetBytes(paramValue))))); } else { DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterDbType(parameter, field.Type); sb.Append(expressions[field.ExpressionName()]); bool requiresRangeAdjustment = ((operation == "=") && (field.Type.StartsWith("DateTime") && !(StringIsNull(paramValue)))); if ((operation == "<>") && StringIsNull(paramValue)) { sb.Append(" is not null "); } else if ((operation == "=") && StringIsNull(paramValue)) { sb.Append(" is null "); } else { if (operation == "*") { sb.Append(" like "); parameter.DbType = DbType.String; if (!(paramValue.Contains("%"))) { paramValue = (SqlStatement.EscapePattern(command, paramValue) + "%"); } } else if (requiresRangeAdjustment) { sb.Append(">="); } else { sb.Append(operation); } try { parameter.Value = StringToValue(field, paramValue); if ((parameter.DbType == DbType.Binary) && (parameter.Value is Guid)) { parameter.Value = ((Guid)(parameter.Value)).ToByteArray(); } } catch (Exception) { parameter.Value = DBNull.Value; } sb.Append(parameter.ParameterName); command.Parameters.Add(parameter); if (requiresRangeAdjustment) { DbParameter rangeParameter = command.CreateParameter(); AssignParameterDbType(rangeParameter, field.Type); rangeParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); sb.Append(String.Format(" and {0} < {1}", expressions[field.ExpressionName()], rangeParameter.ParameterName)); if (field.Type == "DateTimeOffset") { DateTime dt = Convert.ToDateTime(parameter.Value); parameter.Value = new DateTimeOffset(dt).AddHours(-14); rangeParameter.Value = new DateTimeOffset(dt).AddDays(1).AddHours(14); } else { rangeParameter.Value = Convert.ToDateTime(parameter.Value).AddDays(1); } command.Parameters.Add(rangeParameter); } } } } valueMatch = valueMatch.NextMatch(); } if (!(firstValue)) { sb.AppendLine(")"); } } } } if (matchListCount > 0) { sb.AppendLine(")"); // the end of the "match" list sb.AppendLine(")"); firstCriteria = true; } if (!(firstCriteria)) { sb.AppendLine(")"); if (!(String.IsNullOrEmpty(whereClause))) { sb.Append("and "); } } if (!(String.IsNullOrEmpty(whereClause))) { sb.AppendLine("("); sb.AppendLine(whereClause); sb.AppendLine(")"); } }
private void AppendWhereExpressions(StringBuilder sb, DbCommand command, ViewPage page, SelectClauseDictionary expressions, FieldValue[] values) { sb.AppendLine(); sb.Append("where"); bool firstField = true; foreach (FieldValue v in values) { DataField field = page.FindField(v.Name); if ((field != null) && field.IsPrimaryKey) { sb.AppendLine(); if (firstField) { firstField = false; } else { sb.Append("and "); } sb.AppendFormat(RemoveTableAliasFromExpression(expressions[v.Name])); sb.AppendFormat("={0}p{1}", _parameterMarker, command.Parameters.Count); DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterValue(parameter, field.Type, v.OldValue); command.Parameters.Add(parameter); } } bool ignorePrimaryKeyInWhere = false; if (firstField) { foreach (FieldValue fv in values) { if (fv.Name == "_IgnorePrimaryKeyInWhere") { ignorePrimaryKeyInWhere = true; break; } } // if the first field has not been processed then a primary key has not been provided if (!(ignorePrimaryKeyInWhere)) { throw new Exception("A primary key field value is not provided."); } } if (ignorePrimaryKeyInWhere || _config.ConflictDetectionEnabled) { foreach (FieldValue v in values) { DataField field = page.FindField(v.Name); if ((field != null) && (!((field.IsPrimaryKey || field.OnDemand)) && !(v.ReadOnly))) { sb.AppendLine(); if (firstField) { firstField = false; } else { sb.Append("and "); } sb.Append(RemoveTableAliasFromExpression(expressions[v.Name])); if (v.OldValue == null) { sb.Append(" is null"); } else { sb.AppendFormat("={0}p{1}", _parameterMarker, command.Parameters.Count); DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterValue(parameter, field.Type, v.OldValue); command.Parameters.Add(parameter); } } } } sb.AppendLine(); }
private void ExportDataAsRss(ViewPage page, DbDataReader reader, StreamWriter writer) { string appPath = Regex.Replace(HttpContext.Current.Request.Url.AbsoluteUri, "^(.+)Export.ashx.+$", "$1", RegexOptions.IgnoreCase); XmlWriterSettings settings = new XmlWriterSettings(); settings.CloseOutput = false; XmlWriter output = XmlWriter.Create(writer, settings); output.WriteStartDocument(); output.WriteStartElement("rss"); output.WriteAttributeString("version", "2.0"); output.WriteStartElement("channel"); output.WriteElementString("title", ((string)(_view.Evaluate("string(concat(/c:dataController/@label, \' | \', @label))", Resolver)))); output.WriteElementString("lastBuildDate", DateTime.Now.ToString("r")); output.WriteElementString("language", System.Threading.Thread.CurrentThread.CurrentCulture.Name.ToLower()); int rowCount = 0; while ((rowCount < MaximumRssItems) && reader.Read()) { output.WriteStartElement("item"); bool hasTitle = false; bool hasPubDate = false; StringBuilder desc = new StringBuilder(); for (int i = 0; (i < page.Fields.Count); i++) { DataField field = page.Fields[i]; if (!(field.Hidden)) { if (rowCount == 0) field.NormalizeDataFormatString(); if (!(String.IsNullOrEmpty(field.AliasName))) field = page.FindField(field.AliasName); string text = String.Empty; object v = reader[field.Name]; if (!(DBNull.Value.Equals(v))) if (!(String.IsNullOrEmpty(field.DataFormatString))) text = String.Format(field.DataFormatString, v); else text = Convert.ToString(v); if (!(hasPubDate) && (field.Type == "DateTime")) { hasPubDate = true; if (!(String.IsNullOrEmpty(text))) output.WriteElementString("pubDate", ((DateTime)(reader[field.Name])).ToString("r")); } if (!(hasTitle)) { hasTitle = true; output.WriteElementString("title", text); StringBuilder link = new StringBuilder(); link.Append(_config.Evaluate("string(/c:dataController/@name)")); foreach (DataField pkf in page.Fields) if (pkf.IsPrimaryKey) link.Append(String.Format("&{0}={1}", pkf.Name, reader[pkf.Name])); string itemGuid = String.Format("{0}Details.aspx?l={1}", appPath, HttpUtility.UrlEncode(Convert.ToBase64String(Encoding.Default.GetBytes(link.ToString())))); output.WriteElementString("link", itemGuid); output.WriteElementString("guid", itemGuid); } else if (!(String.IsNullOrEmpty(field.OnDemandHandler)) && (field.OnDemandStyle == OnDemandDisplayStyle.Thumbnail)) { if (text.Equals("1")) { desc.AppendFormat("{0}:<br /><img src=\"{1}Blob.ashx?{2}=t", HttpUtility.HtmlEncode(field.Label), appPath, field.OnDemandHandler); foreach (DataField f in page.Fields) if (f.IsPrimaryKey) { desc.Append("|"); desc.Append(reader[f.Name]); } desc.Append("\" style=\"width:92px;height:71px;\"/><br />"); } } else desc.AppendFormat("{0}: {1}<br />", HttpUtility.HtmlEncode(field.Label), HttpUtility.HtmlEncode(text)); } } output.WriteStartElement("description"); output.WriteCData(String.Format("<span style=\\\"font-size:small;\\\">{0}</span>", desc.ToString())); output.WriteEndElement(); output.WriteEndElement(); rowCount++; } output.WriteEndElement(); output.WriteEndElement(); output.WriteEndDocument(); output.Close(); }
void IPlugIn.ProcessPageRequest(PageRequest request, ViewPage page) { if (page.Rows.Count == 0) { page.Icons = new string[0]; return; } if (!(_requireProcessing)) { List <string> icons = new List <string>(); for (int i = 0; (i < page.Rows.Count); i++) { string rowDir = AnnotationPlugIn.GenerateDataRecordPath(request.Controller, page, null, i); if (Directory.Exists(rowDir)) { icons.Add("Attachment"); } else { icons.Add(null); } } page.Icons = icons.ToArray(); return; } List <DynamicExpression> expressions = new List <DynamicExpression>(page.Expressions); DynamicExpression de = new DynamicExpression(); de.Target = "Annotations"; de.Scope = DynamicExpressionScope.CategoryVisibility; de.Type = DynamicExpressionType.ClientScript; de.Test = "!this.get_isInserting()"; de.ViewId = page.View; expressions.Add(de); page.Expressions = expressions.ToArray(); if (!(_retrieveAnnotations)) { return; } DataField field = page.FindField("_Annotation_AttachmentNew"); if (field != null) { int fieldIndex = page.Fields.IndexOf(field); string newValue = String.Format("{0},{1}|{2}", request.Controller, field.Name, Regex.Replace(((string)(page.Rows[0][fieldIndex])), "^\\w+\\|(.+)$", "$1")); if (field.Name == "_Annotation_AttachmentNew") { newValue = ("null|" + newValue); } page.Rows[0][fieldIndex] = newValue; } string p = AnnotationPlugIn.GenerateDataRecordPath(request.Controller, page, null, 0); if (Directory.Exists(p)) { string[] files = Directory.GetFiles(p, "*.xml"); List <object> values = new List <object>(page.Rows[0]); int i = (files.Length - 1); while (i >= 0) { string filename = files[i]; XPathDocument doc = new XPathDocument(filename); XPathNavigator nav = doc.CreateNavigator().SelectSingleNode("/*"); DataField f = null; if (nav.Name == "note") { f = new DataField(); f.Name = "_Annotation_Note"; f.Type = "String"; f.HeaderText = String.Format(Localizer.Replace("AnnotationNoteDynamicFieldHeaderText", "{0} written at {1}"), ReadNameAndEmail(nav), Convert.ToDateTime(nav.GetAttribute("timestamp", String.Empty))); f.Columns = 50; f.Rows = 7; f.TextMode = TextInputMode.Note; values.Add(nav.Value); } else if (nav.Name == "attachment") { f = new DataField(); f.Name = "_Annotation_Attachment"; f.Type = "Byte[]"; f.HeaderText = String.Format(Localizer.Replace("AnnotationAttachmentDynamicFieldHeaderText", "{0} attached <b>{1}</b> at {2}"), ReadNameAndEmail(nav), nav.GetAttribute("fileName", String.Empty), Convert.ToDateTime(nav.GetAttribute("timestamp", String.Empty))); f.OnDemand = true; f.OnDemandHandler = "AnnotationPlugIn"; f.OnDemandStyle = OnDemandDisplayStyle.Link; if (nav.GetAttribute("contentType", String.Empty).StartsWith("image/")) { f.OnDemandStyle = OnDemandDisplayStyle.Thumbnail; } f.CategoryIndex = (page.Categories.Count - 1); values.Add(nav.GetAttribute("value", String.Empty)); } if (f != null) { f.Name = (f.Name + Path.GetFileNameWithoutExtension(filename)); f.AllowNulls = true; f.CategoryIndex = (page.Categories.Count - 1); if (!(Controller.UserIsInRole("Administrators"))) { f.ReadOnly = true; } page.Fields.Add(f); } i = (i - 1); } page.Rows[0] = values.ToArray(); if (files.Length > 0) { page.Categories[(page.Categories.Count - 1)].Tab = Localizer.Replace("AnnotationTab", "Notes & Attachments"); expressions.RemoveAt((expressions.Count - 1)); page.Expressions = expressions.ToArray(); } } else { de.Test = "this.get_isEditing() && this.get_view()._displayAnnotations"; ActionGroup g = new ActionGroup(); page.ActionGroups.Add(g); g.Scope = "ActionBar"; g.Flat = true; Action a = new Action(); g.Actions.Add(a); a.WhenLastCommandName = "Edit"; a.WhenView = page.View; a.CommandName = "ClientScript"; a.CommandArgument = "this.get_view()._displayAnnotations=true;this._focusedFieldName = \'_Annotation_No" + "teNew\';this._raiseSelectedDelayed=false;"; a.HeaderText = Localizer.Replace("AnnotationActionHeaderText", "Annotate"); a.CssClass = "AttachIcon"; a.WhenClientScript = "this.get_view()._displayAnnotations!=true;"; } }
private void ExportDataAsCsv(ViewPage page, DbDataReader reader, StreamWriter writer) { bool firstField = true; for (int i = 0; (i < page.Fields.Count); i++) { DataField field = page.Fields[i]; if (!(field.Hidden)) { if (firstField) firstField = false; else writer.Write(System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator); if (!(String.IsNullOrEmpty(field.AliasName))) field = page.FindField(field.AliasName); writer.Write("\"{0}\"", field.Label.Replace("\"", "\"\"")); } field.NormalizeDataFormatString(); } writer.WriteLine(); while (reader.Read()) { firstField = true; for (int j = 0; (j < page.Fields.Count); j++) { DataField field = page.Fields[j]; if (!(field.Hidden)) { if (firstField) firstField = false; else writer.Write(System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator); if (!(String.IsNullOrEmpty(field.AliasName))) field = page.FindField(field.AliasName); string text = String.Empty; object v = reader[field.Name]; if (!(DBNull.Value.Equals(v))) { if (!(String.IsNullOrEmpty(field.DataFormatString))) text = String.Format(field.DataFormatString, v); else text = Convert.ToString(v); writer.Write("\"{0}\"", text.Replace("\"", "\"\"")); } else writer.Write("\"\""); } } writer.WriteLine(); } }
private void ExportDataAsRowset(ViewPage page, DbDataReader reader, StreamWriter writer) { string s = "uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"; string dt = "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"; string rs = "urn:schemas-microsoft-com:rowset"; string z = "#RowsetSchema"; XmlWriterSettings settings = new XmlWriterSettings(); settings.CloseOutput = false; XmlWriter output = XmlWriter.Create(writer, settings); output.WriteStartDocument(); output.WriteStartElement("xml"); output.WriteAttributeString("xmlns", "s", null, s); output.WriteAttributeString("xmlns", "dt", null, dt); output.WriteAttributeString("xmlns", "rs", null, rs); output.WriteAttributeString("xmlns", "z", null, z); // declare rowset schema output.WriteStartElement("Schema", s); output.WriteAttributeString("id", "RowsetSchema"); output.WriteStartElement("ElementType", s); output.WriteAttributeString("name", "row"); output.WriteAttributeString("content", "eltOnly"); output.WriteAttributeString("CommandTimeout", rs, "60"); List<DataField> fields = new List<DataField>(); foreach (DataField field in page.Fields) if (!((field.Hidden || field.OnDemand)) && !(fields.Contains(field))) { DataField aliasField = field; if (!(String.IsNullOrEmpty(field.AliasName))) aliasField = page.FindField(field.AliasName); fields.Add(aliasField); } int number = 1; foreach (DataField field in fields) { field.NormalizeDataFormatString(); output.WriteStartElement("AttributeType", s); output.WriteAttributeString("name", field.Name); output.WriteAttributeString("number", rs, number.ToString()); output.WriteAttributeString("nullable", rs, "true"); output.WriteAttributeString("name", rs, field.Label); output.WriteStartElement("datatype", s); string type = RowsetTypeMap[field.Type]; string dbType = null; if ("{0:c}".Equals(field.DataFormatString, StringComparison.CurrentCultureIgnoreCase)) dbType = "currency"; else if (!(String.IsNullOrEmpty(field.DataFormatString)) && field.Type != "DateTime") type = "string"; output.WriteAttributeString("type", dt, type); output.WriteAttributeString("dbtype", rs, dbType); output.WriteEndElement(); output.WriteEndElement(); number++; } output.WriteStartElement("extends", s); output.WriteAttributeString("type", "rs:rowbase"); output.WriteEndElement(); output.WriteEndElement(); output.WriteEndElement(); // output rowset data output.WriteStartElement("data", rs); while (reader.Read()) { output.WriteStartElement("row", z); foreach (DataField field in fields) { object v = reader[field.Name]; if (!(DBNull.Value.Equals(v))) if (!(String.IsNullOrEmpty(field.DataFormatString)) && !(((field.DataFormatString == "{0:d}") || (field.DataFormatString == "{0:c}")))) output.WriteAttributeString(field.Name, String.Format(field.DataFormatString, v)); else if (field.Type == "DateTime") output.WriteAttributeString(field.Name, ((DateTime)(v)).ToString("s")); else output.WriteAttributeString(field.Name, v.ToString()); } output.WriteEndElement(); } output.WriteEndElement(); output.WriteEndElement(); output.WriteEndDocument(); output.Close(); }
private bool ConfigureCommandForUpdate(DbCommand command, ViewPage page, SelectClauseDictionary expressions, string tableName, FieldValue[] values) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("update {0} set ", tableName); bool firstField = true; foreach (FieldValue v in values) { DataField field = page.FindField(v.Name); if ((field != null) && v.Modified) { sb.AppendLine(); if (firstField) firstField = false; else sb.Append(","); sb.AppendFormat(RemoveTableAliasFromExpression(expressions[v.Name])); if ((v.NewValue == null) && field.HasDefaultValue) sb.Append(String.Format("={0}", field.DefaultValue)); else { sb.AppendFormat("={0}p{1}", _parameterMarker, command.Parameters.Count); DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterValue(parameter, field.Type, v.NewValue); command.Parameters.Add(parameter); } } } if (firstField) return false; AppendWhereExpressions(sb, command, page, expressions, values); command.CommandText = sb.ToString(); return true; }
private void ConfigureCommandForSelect(DbCommand command, ViewPage page, SelectClauseDictionary expressions, string fromClause, string whereClause, string orderByClause, CommandConfigurationType commandConfiguration) { bool useServerPaging = ((commandConfiguration != CommandConfigurationType.SelectDistinct && !(_serverRules.EnableResultSet)) && (commandConfiguration != CommandConfigurationType.SelectAggregates && commandConfiguration != CommandConfigurationType.SelectFirstLetters)); bool useLimit = SupportsLimitInSelect(command); bool useSkip = SupportsSkipInSelect(command); if (useServerPaging) page.AcceptAllRows(); StringBuilder sb = new StringBuilder(); if (useLimit || useSkip) useServerPaging = false; bool countUsingHierarchy = false; if ((commandConfiguration == CommandConfigurationType.SelectCount) && (useServerPaging && RequiresHierarchy(page))) { countUsingHierarchy = true; commandConfiguration = CommandConfigurationType.Select; } if (commandConfiguration == CommandConfigurationType.SelectExisting) useServerPaging = false; if (commandConfiguration == CommandConfigurationType.SelectCount) sb.AppendLine("select count(*)"); else { if (useServerPaging) sb.AppendLine("with page_cte__ as ("); else if ((commandConfiguration == CommandConfigurationType.Sync) && useLimit) sb.Append("select * from (select @row_num := @row_num+1 row_number__,cte__.* from (select @r" + "ow_num:=0) r,("); sb.AppendLine("select"); if (useServerPaging) AppendRowNumberExpression(sb, page, expressions, orderByClause); if (commandConfiguration == CommandConfigurationType.SelectDistinct) { DataField distinctField = page.FindField(page.DistinctValueFieldName); string distinctExpression = expressions[distinctField.ExpressionName()]; if (distinctField.Type.StartsWith("Date")) { string commandType = command.GetType().ToString(); if (commandType == "System.Data.SqlClient.SqlCommand") distinctExpression = String.Format("DATEADD(dd, 0, DATEDIFF(dd, 0, {0}))", distinctExpression); if (commandType == "MySql.Data.MySqlClient.MySqlCommand") distinctExpression = String.Format("cast({0} as date)", distinctExpression); } sb.AppendFormat("distinct {0} \"{1}\"\r\n", distinctExpression, page.DistinctValueFieldName); } else if (commandConfiguration == CommandConfigurationType.SelectAggregates) AppendAggregateExpressions(sb, page, expressions); else if (commandConfiguration == CommandConfigurationType.SelectFirstLetters) { string substringFunction = "substring"; if (DatabaseEngineIs(command, "Oracle", "DB2")) substringFunction = "substr"; AppendFirstLetterExpressions(sb, page, expressions, substringFunction); } else { if ((commandConfiguration == CommandConfigurationType.Select) && useSkip) sb.AppendFormat(" first {0} skip {1}\r\n", page.PageSize, (page.PageSize * page.PageIndex)); if ((commandConfiguration == CommandConfigurationType.Sync) && useSkip) { // Only select the primary key. foreach (DataField field in page.Fields) if (field.IsPrimaryKey) { sb.Append(expressions[field.ExpressionName()]); break; } } else if (commandConfiguration == CommandConfigurationType.SelectExisting) sb.AppendLine("*"); else AppendSelectExpressions(sb, page, expressions, !(useServerPaging)); } } sb.AppendLine("from"); sb.AppendLine(fromClause); _hasWhere = false; if (String.IsNullOrEmpty(_viewFilter)) { _viewFilter = _view.GetAttribute("filter", String.Empty); if (String.IsNullOrEmpty(_viewFilter) && ((_viewType == "Form") && !(String.IsNullOrEmpty(page.LastView)))) { XPathNavigator lastView = _config.SelectSingleNode("/c:dataController/c:views/c:view[@id=\'{0}\']", page.LastView); if (lastView != null) _viewFilter = lastView.GetAttribute("filter", String.Empty); } } if (!(String.IsNullOrEmpty(_viewFilter))) _viewFilter = String.Format("({0})", _viewFilter); if (commandConfiguration == CommandConfigurationType.SelectExisting) { EnsureWhereKeyword(sb); sb.Append(expressions[page.InnerJoinForeignKey.ToLower()]); sb.Append("="); sb.Append(page.InnerJoinPrimaryKey); sb.AppendLine(" and "); } AppendSystemFilter(command, page, expressions); AppendAccessControlRules(command, page, expressions); if (((page.Filter != null) && (page.Filter.Length > 0)) || !(String.IsNullOrEmpty(_viewFilter))) AppendFilterExpressionsToWhere(sb, page, command, expressions, whereClause); else if (!(String.IsNullOrEmpty(whereClause))) { EnsureWhereKeyword(sb); sb.AppendLine(whereClause); } if (commandConfiguration == CommandConfigurationType.Select) { bool preFetch = RequiresPreFetching(page); if (useServerPaging) { if (!(ConfigureCTE(sb, page, command, expressions, countUsingHierarchy))) sb.Append(")\r\nselect * from page_cte__ "); if (!(countUsingHierarchy)) { sb.AppendFormat("where row_number__ > {0}PageRangeFirstRowNumber and row_number__ <= {0}PageRangeL" + "astRowNumber order by row_number__", _parameterMarker); DbParameter p = command.CreateParameter(); p.ParameterName = (_parameterMarker + "PageRangeFirstRowNumber"); p.Value = ((page.PageSize * page.PageIndex) + page.PageOffset); if (preFetch) p.Value = (((int)(p.Value)) - page.PageSize); command.Parameters.Add(p); DbParameter p2 = command.CreateParameter(); p2.ParameterName = (_parameterMarker + "PageRangeLastRowNumber"); p2.Value = ((page.PageSize * (page.PageIndex + 1)) + page.PageOffset); if (preFetch) p2.Value = (((int)(p2.Value)) + page.PageSize); command.Parameters.Add(p2); } } else { AppendOrderByExpression(sb, page, expressions, orderByClause); if (useLimit) { sb.AppendFormat("\r\nlimit {0}Limit_PageOffset, {0}Limit_PageSize", _parameterMarker); DbParameter p = command.CreateParameter(); p.ParameterName = (_parameterMarker + "Limit_PageOffset"); p.Value = ((page.PageSize * page.PageIndex) + page.PageOffset); if (preFetch && (((int)(p.Value)) > page.PageSize)) p.Value = (((int)(p.Value)) - page.PageSize); command.Parameters.Add(p); DbParameter p2 = command.CreateParameter(); p2.ParameterName = (_parameterMarker + "Limit_PageSize"); p2.Value = page.PageSize; if (preFetch) { int pagesToFetch = 2; if (((int)(p.Value)) > page.PageSize) pagesToFetch = 3; p2.Value = (page.PageSize * pagesToFetch); } command.Parameters.Add(p2); } } } else if (commandConfiguration == CommandConfigurationType.Sync) { if (useServerPaging) { if (!(ConfigureCTE(sb, page, command, expressions, false))) sb.Append(")\r\nselect * from page_cte__ "); sb.Append("where "); } else { if (useLimit || useSkip) AppendOrderByExpression(sb, page, expressions, orderByClause); if (!(useSkip)) sb.Append(") cte__)cte2__ where "); } bool first = true; if (!(useSkip)) foreach (DataField field in page.Fields) if (field.IsPrimaryKey) { if (first) first = false; else sb.AppendFormat(" and "); sb.AppendFormat("{2}{1}{3}={0}PrimaryKey_{1}", _parameterMarker, field.Name, _leftQuote, _rightQuote); } } else if ((commandConfiguration == CommandConfigurationType.SelectDistinct) || (commandConfiguration == CommandConfigurationType.SelectFirstLetters)) sb.Append("order by 1"); command.CommandText = sb.ToString(); _viewFilter = null; }
protected virtual void AppendFilterExpressionsToWhere(StringBuilder sb, ViewPage page, DbCommand command, SelectClauseDictionary expressions, string whereClause) { string quickFindHint = page.QuickFindHint; bool firstCriteria = String.IsNullOrEmpty(_viewFilter); if (!(firstCriteria)) { EnsureWhereKeyword(sb); sb.AppendLine("("); sb.Append(ProcessViewFilter(page, command, expressions)); } int matchListCount = 0; bool firstDoNotMatch = true; string logicalConcat = "and "; if (page.Filter != null) foreach (string filterExpression in page.Filter) { Match matchingMode = MatchingModeRegex.Match(filterExpression); if (matchingMode.Success) { bool doNotMatch = (matchingMode.Groups["Match"].Value == "_donotmatch_"); if (doNotMatch) { if (firstDoNotMatch) { firstDoNotMatch = false; EnsureWhereKeyword(sb); if (!(firstCriteria)) sb.AppendLine(")"); if (matchListCount > 0) sb.AppendLine(")"); if (!(firstCriteria) || (matchListCount > 0)) sb.AppendLine("and"); matchListCount = 0; sb.AppendLine(" not"); firstCriteria = true; } } if (matchListCount == 0) { EnsureWhereKeyword(sb); if (!(firstCriteria)) sb.Append(") and"); // the list of matches begins sb.AppendLine("("); } else { sb.AppendLine(")"); sb.AppendLine("or"); } // begin a list of conditions for the next match if (matchingMode.Groups["Scope"].Value == "$all$") logicalConcat = " and "; else logicalConcat = " or "; matchListCount++; firstCriteria = true; } Match filterMatch = FilterExpressionRegex.Match(filterExpression); if (filterMatch.Success) { // "ProductName:?g", "CategoryCategoryName:=Condiments\x00=Seafood" bool firstValue = true; string fieldOperator = " or "; if (Regex.IsMatch(filterMatch.Groups["Values"].Value, ">|<")) fieldOperator = " and "; Match valueMatch = FilterValueRegex.Match(filterMatch.Groups["Values"].Value); while (valueMatch.Success) { string alias = filterMatch.Groups["Alias"].Value; string operation = valueMatch.Groups["Operation"].Value; string paramValue = valueMatch.Groups["Value"].Value; if ((operation == "~") && (alias == "_quickfind_")) alias = page.Fields[0].Name; bool deepSearching = alias.Contains(","); DataField field = page.FindField(alias); if (((((field != null) && field.AllowQBE) || (operation == "~")) && (((page.DistinctValueFieldName != field.Name || (matchListCount > 0)) || (operation == "~")) || (page.AllowDistinctFieldInFilter || page.CustomFilteredBy(field.Name)))) || deepSearching) { if (firstValue) { if (firstCriteria) { EnsureWhereKeyword(sb); sb.AppendLine("("); firstCriteria = false; } else sb.Append(logicalConcat); sb.Append("("); firstValue = false; } else sb.Append(fieldOperator); if (deepSearching) { string deepSearchFieldName = alias.Substring(0, alias.IndexOf(',')); string hint = alias.Substring((deepSearchFieldName.Length + 1)); string deepFilterExpression = (deepSearchFieldName + filterExpression.Substring(filterExpression.IndexOf(':'))); AppendDeepFilter(hint, page, command, sb, deepFilterExpression); } else if (operation == "~") { paramValue = Convert.ToString(StringToValue(paramValue)); List<string> words = new List<string>(); List<List<string>> phrases = new List<List<string>>(); phrases.Add(words); CultureInfo currentCulture = CultureInfo.CurrentCulture; string textDateNumber = ("\\p{L}\\d" + Regex.Escape((currentCulture.DateTimeFormat.DateSeparator + (currentCulture.DateTimeFormat.TimeSeparator + currentCulture.NumberFormat.NumberDecimalSeparator)))); string[] removableNumericCharacters = new string[] { currentCulture.NumberFormat.NumberGroupSeparator, currentCulture.NumberFormat.CurrencyGroupSeparator, currentCulture.NumberFormat.CurrencySymbol}; Match m = Regex.Match(paramValue, String.Format("\\s*(?\'Token\'((?\'Quote\'\")(?\'Value\'.+?)\")|((?\'Quote\'\\\')(?\'Value\'.+?)\\\')|(,|;|(^|\\s+" + ")-)|(?\'Value\'[{0}]+))", textDateNumber)); bool negativeSample = false; while (m.Success) { string token = m.Groups["Token"].Value.Trim(); if ((token == ",") || (token == ";")) { words = new List<string>(); phrases.Add(words); negativeSample = false; } else if (token == "-") negativeSample = true; else { string exactFlag = "="; if (String.IsNullOrEmpty(m.Groups["Quote"].Value)) exactFlag = " "; string negativeFlag = " "; if (negativeSample) { negativeFlag = "-"; negativeSample = false; } words.Add(String.Format("{0}{1}{2}", negativeFlag, exactFlag, m.Groups["Value"].Value)); } m = m.NextMatch(); } bool firstPhrase = true; foreach (List<String> phrase in phrases) if (phrase.Count > 0) { if (firstPhrase) firstPhrase = false; else sb.AppendLine("or"); sb.AppendLine("("); bool firstWord = true; System.DateTime paramValueAsDate; foreach (string paramValueWord in phrase) { bool negativeFlag = (paramValueWord[0] == '-'); bool exactFlag = (paramValueWord[1] == '='); string comparisonOperator = "like"; if (exactFlag) comparisonOperator = "="; string pv = paramValueWord.Substring(2); bool paramValueIsDate = SqlStatement.TryParseDate(command.GetType(), pv, out paramValueAsDate); bool firstTry = true; DbParameter parameter = null; if (!(paramValueIsDate)) pv = SqlStatement.EscapePattern(command, pv); double paramValueAsNumber; string testNumber = pv; foreach (string s in removableNumericCharacters) testNumber = testNumber.Replace(s, string.Empty); bool paramValueIsNumber = double.TryParse(testNumber, out paramValueAsNumber); if (!(exactFlag) && !(pv.Contains("%"))) pv = String.Format("%{0}%", pv); if (firstWord) firstWord = false; else sb.Append("and"); if (negativeFlag) sb.Append(" not"); sb.Append("("); bool hasTests = false; DbParameter originalParameter = null; if (String.IsNullOrEmpty(quickFindHint) || !(quickFindHint.StartsWith(";"))) foreach (DataField tf in page.Fields) if ((tf.AllowQBE && String.IsNullOrEmpty(tf.AliasName)) && (!((tf.IsPrimaryKey && tf.Hidden)) && (!(tf.Type.StartsWith("Date")) || paramValueIsDate))) { hasTests = true; if ((parameter == null) || command.GetType().FullName.Contains("ManagedDataAccess")) { parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv; if (exactFlag && paramValueIsNumber) { parameter.DbType = DbType.Double; parameter.Value = paramValueAsNumber; } } if (!((exactFlag && ((!(tf.Type.Contains("String")) && !(paramValueIsNumber)) || (tf.Type.Contains("String") && paramValueIsNumber))))) { if (firstTry) firstTry = false; else sb.Append(" or "); if (tf.Type.StartsWith("Date")) { DbParameter dateParameter = command.CreateParameter(); dateParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); dateParameter.DbType = DbType.DateTime; command.Parameters.Add(dateParameter); dateParameter.Value = paramValueAsDate; if (negativeFlag) sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); sb.AppendFormat("({0} = {1})", expressions[tf.ExpressionName()], dateParameter.ParameterName); } else { bool skipLike = false; if (!((comparisonOperator == "=")) && ((tf.Type == "String") && ((tf.Len > 0) && (tf.Len < pv.Length)))) { string pv2 = pv; pv2 = pv2.Substring(1); if (tf.Len < pv2.Length) pv2 = pv2.Substring(0, (pv2.Length - 1)); if (pv2.Length > tf.Len) skipLike = true; else { originalParameter = parameter; parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); parameter.DbType = DbType.String; command.Parameters.Add(parameter); parameter.Value = pv2; } } if (_serverRules.EnableResultSet) { string fieldNameExpression = expressions[tf.ExpressionName()]; if ((tf.Type != "String" && !(exactFlag)) || (tf.Type == "Boolean")) fieldNameExpression = String.Format("convert({0}, \'System.String\')", fieldNameExpression); if (negativeFlag) sb.AppendFormat("({0} is not null)and", fieldNameExpression); sb.AppendFormat("({0} {2} {1})", fieldNameExpression, parameter.ParameterName, comparisonOperator); } else if (skipLike) sb.Append("1=0"); else { if (negativeFlag) sb.AppendFormat("({0} is not null)and", expressions[tf.ExpressionName()]); if (DatabaseEngineIs(command, "Oracle", "DB2", "Firebird")) { sb.AppendFormat("(upper({0}) {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); parameter.Value = Convert.ToString(parameter.Value).ToUpper(); } else sb.AppendFormat("({0} {2} {1})", expressions[tf.ExpressionName()], parameter.ParameterName, comparisonOperator); } } } if (originalParameter != null) { parameter = originalParameter; originalParameter = null; } } if (!(String.IsNullOrEmpty(quickFindHint)) && quickFindHint.Contains(";")) { sb.AppendLine(); if (hasTests) sb.AppendLine("or"); else hasTests = true; sb.AppendLine("("); bool firstHint = true; foreach (string hint in quickFindHint.Substring((quickFindHint.IndexOf(';') + 1)).Split(new char[] { ';'})) { if (firstHint) firstHint = false; else { sb.AppendLine(); sb.AppendLine("or"); } sb.AppendLine("("); string newFilterExpression = filterExpression; StringBuilder reversedFilterExpression = new StringBuilder(); if (negativeFlag) { bool firstExpressionPhrase = true; foreach (List<string> ph in phrases) { if (firstExpressionPhrase) firstExpressionPhrase = false; else reversedFilterExpression.Append(","); bool firstExpressionWord = true; foreach (string w in ph) { if (firstExpressionWord) firstExpressionWord = false; else reversedFilterExpression.Append(" "); if (!((w[0] == '-'))) reversedFilterExpression.Append("-"); if (w[1] == '=') reversedFilterExpression.Append("\""); reversedFilterExpression.Append(w.Substring(2)); if (w[1] == '=') reversedFilterExpression.Append("\""); } } newFilterExpression = ("_quickfind_:~" + ValueToString(reversedFilterExpression.ToString())); } AppendDeepFilter(hint, page, command, sb, newFilterExpression); sb.AppendLine(")"); } sb.AppendLine(")"); } if (!(hasTests)) if (negativeFlag && quickFindHint.StartsWith(";")) sb.Append("1=1"); else sb.Append("1=0"); sb.Append(")"); } sb.AppendLine(")"); } if (firstPhrase) sb.Append("1=1"); } else if (operation.StartsWith("$")) sb.Append(FilterFunctions.Replace(command, expressions, String.Format("{0}({1}$comma${2})", operation.TrimEnd('$'), alias, Convert.ToBase64String(Encoding.UTF8.GetBytes(paramValue))))); else { DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterDbType(parameter, field.Type); sb.Append(expressions[field.ExpressionName()]); bool requiresRangeAdjustment = ((operation == "=") && (field.Type.StartsWith("DateTime") && !(StringIsNull(paramValue)))); if ((operation == "<>") && StringIsNull(paramValue)) sb.Append(" is not null "); else if ((operation == "=") && StringIsNull(paramValue)) sb.Append(" is null "); else { if (operation == "*") { sb.Append(" like "); parameter.DbType = DbType.String; if (!(paramValue.Contains("%"))) paramValue = (SqlStatement.EscapePattern(command, paramValue) + "%"); } else if (requiresRangeAdjustment) sb.Append(">="); else sb.Append(operation); try { parameter.Value = StringToValue(field, paramValue); if ((parameter.DbType == DbType.Binary) && (parameter.Value is Guid)) parameter.Value = ((Guid)(parameter.Value)).ToByteArray(); } catch (Exception ) { parameter.Value = DBNull.Value; } sb.Append(parameter.ParameterName); command.Parameters.Add(parameter); if (requiresRangeAdjustment) { DbParameter rangeParameter = command.CreateParameter(); AssignParameterDbType(rangeParameter, field.Type); rangeParameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); sb.Append(String.Format(" and {0} < {1}", expressions[field.ExpressionName()], rangeParameter.ParameterName)); if (field.Type == "DateTimeOffset") { DateTime dt = Convert.ToDateTime(parameter.Value); parameter.Value = new DateTimeOffset(dt).AddHours(-14); rangeParameter.Value = new DateTimeOffset(dt).AddDays(1).AddHours(14); } else rangeParameter.Value = Convert.ToDateTime(parameter.Value).AddDays(1); command.Parameters.Add(rangeParameter); } } } } valueMatch = valueMatch.NextMatch(); } if (!(firstValue)) sb.AppendLine(")"); } } if (matchListCount > 0) { sb.AppendLine(")"); // the end of the "match" list sb.AppendLine(")"); firstCriteria = true; } if (!(firstCriteria)) { sb.AppendLine(")"); if (!(String.IsNullOrEmpty(whereClause))) sb.Append("and "); } if (!(String.IsNullOrEmpty(whereClause))) { sb.AppendLine("("); sb.AppendLine(whereClause); sb.AppendLine(")"); } }
private void AppendWhereExpressions(StringBuilder sb, DbCommand command, ViewPage page, SelectClauseDictionary expressions, FieldValue[] values) { sb.AppendLine(); sb.Append("where"); bool firstField = true; foreach (FieldValue v in values) { DataField field = page.FindField(v.Name); if ((field != null) && field.IsPrimaryKey) { sb.AppendLine(); if (firstField) firstField = false; else sb.Append("and "); sb.AppendFormat(RemoveTableAliasFromExpression(expressions[v.Name])); sb.AppendFormat("={0}p{1}", _parameterMarker, command.Parameters.Count); DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterValue(parameter, field.Type, v.OldValue); command.Parameters.Add(parameter); } } bool ignorePrimaryKeyInWhere = false; if (firstField) { foreach (FieldValue fv in values) if (fv.Name == "_IgnorePrimaryKeyInWhere") { ignorePrimaryKeyInWhere = true; break; } // if the first field has not been processed then a primary key has not been provided if (!(ignorePrimaryKeyInWhere)) throw new Exception("A primary key field value is not provided."); } if (ignorePrimaryKeyInWhere || _config.ConflictDetectionEnabled) foreach (FieldValue v in values) { DataField field = page.FindField(v.Name); if ((field != null) && (!((field.IsPrimaryKey || field.OnDemand)) && !(v.ReadOnly))) { sb.AppendLine(); if (firstField) firstField = false; else sb.Append("and "); sb.Append(RemoveTableAliasFromExpression(expressions[v.Name])); if (v.OldValue == null) sb.Append(" is null"); else { sb.AppendFormat("={0}p{1}", _parameterMarker, command.Parameters.Count); DbParameter parameter = command.CreateParameter(); parameter.ParameterName = String.Format("{0}p{1}", _parameterMarker, command.Parameters.Count); AssignParameterValue(parameter, field.Type, v.OldValue); command.Parameters.Add(parameter); } } } sb.AppendLine(); }
public int ExecuteAction(ActionArgs args, ActionResult result, ViewPage page) { DataTable t = GetTable(args.Controller, null); if (args.CommandName == "Insert") { DataRow r = t.NewRow(); foreach (FieldValue v in args.Values) { DataField f = page.FindField(v.Name); if (f.IsPrimaryKey && f.ReadOnly) { object key = null; if (f.Type == "Guid") key = Guid.NewGuid(); else if (!(PrimaryKeys.TryGetValue(args.Controller, out key))) { key = -1; PrimaryKeys.Add(args.Controller, key); } else { key = (Convert.ToInt32(key) - 1); PrimaryKeys[args.Controller] = key; } r[v.Name] = key; result.Values.Add(new FieldValue(v.Name, key)); FieldValue fv = args.SelectFieldValueObject(v.Name); fv.NewValue = key; fv.Modified = true; } else if (v.Modified) if (v.NewValue == null) r[v.Name] = DBNull.Value; else r[v.Name] = v.NewValue; } t.Rows.Add(r); return 1; } else { DataRow targetRow = null; foreach (DataRow r in t.Rows) { bool matched = true; foreach (DataField f in page.Fields) if (f.IsPrimaryKey) { object kv = r[f.Name]; object kv2 = args.SelectFieldValueObject(f.Name).OldValue; if (((kv == null) || (kv2 == null)) || !((kv.ToString() == kv2.ToString()))) { matched = false; break; } } if (matched) { targetRow = r; break; } } if (targetRow == null) return 0; if (args.CommandName == "Delete") t.Rows.Remove(targetRow); else foreach (FieldValue v in args.Values) if (v.Modified) if (v.NewValue == null) targetRow[v.Name] = DBNull.Value; else targetRow[v.Name] = v.NewValue; return 1; } }