Code:
/ Dotnetfx_Win7_3.5.1 / Dotnetfx_Win7_3.5.1 / 3.5.1 / DEVDIV / depot / DevDiv / releases / Orcas / NetFXw7 / ndp / fx / src / DLinq / Dlinq / SqlClient / SqlMethods.cs / 1 / SqlMethods.cs
using System; using System.Collections.Generic; using System.Diagnostics; using System.Text; using System.Text.RegularExpressions; using System.Reflection; using System.Diagnostics.CodeAnalysis; namespace System.Data.Linq.SqlClient { public static class SqlMethods { ////// Counts the number of year boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of year boundaries crossed between the dates. public static int DateDiffYear(DateTime startDate, DateTime endDate) { return endDate.Year - startDate.Year; } ////// Counts the number of year boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of year boundaries crossed between the dates. public static int? DateDiffYear(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffYear(startDate.Value,endDate.Value); } else { return null; } } ////// Counts the number of year boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of year boundaries crossed between the dates. public static int DateDiffYear(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffYear(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of year boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of year boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffYear(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffYear(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of month boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of month boundaries crossed between the dates. public static int DateDiffMonth(DateTime startDate, DateTime endDate) { return 12 * (endDate.Year - startDate.Year) + endDate.Month - startDate.Month; } ////// Counts the number of month boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of month boundaries crossed between the dates. public static int? DateDiffMonth(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMonth(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of month boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of month boundaries crossed between the dates. public static int DateDiffMonth(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffMonth(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of month boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of month boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffMonth(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMonth(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of day boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of day boundaries crossed between the dates. public static int DateDiffDay(DateTime startDate, DateTime endDate) { TimeSpan diff = endDate.Date - startDate.Date; return diff.Days; } ////// Counts the number of day boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of day boundaries crossed between the dates. public static int? DateDiffDay(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffDay(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of day boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of day boundaries crossed between the dates. public static int DateDiffDay(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffDay(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of day boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of day boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffDay(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffDay(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of hour boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of hour boundaries crossed between the dates. public static int DateDiffHour(DateTime startDate, DateTime endDate) { checked { return DateDiffDay(startDate, endDate) * 24 + endDate.Hour - startDate.Hour; } } ////// Counts the number of hour boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of hour boundaries crossed between the dates. public static int? DateDiffHour(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffHour(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of hour boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of hour boundaries crossed between the dates. public static int DateDiffHour(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffHour(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of hour boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of hour boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffHour(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffHour(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of minute boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of minute boundaries crossed between the dates. public static int DateDiffMinute(DateTime startDate, DateTime endDate) { checked { return DateDiffHour(startDate, endDate) * 60 + endDate.Minute - startDate.Minute; } } ////// Counts the number of minute boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of minute boundaries crossed between the dates. public static int? DateDiffMinute(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMinute(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of minute boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of minute boundaries crossed between the dates. public static int DateDiffMinute(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffMinute(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of minute boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of minute boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffMinute(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMinute(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of second boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of second boundaries crossed between the dates. public static int DateDiffSecond(DateTime startDate, DateTime endDate) { checked { return DateDiffMinute(startDate, endDate) * 60 + endDate.Second - startDate.Second; } } ////// Counts the number of second boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of second boundaries crossed between the dates. public static int? DateDiffSecond(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffSecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of second boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of second boundaries crossed between the dates. public static int DateDiffSecond(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffSecond(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of second boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of second boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffSecond(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffSecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of millisecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of millisecond boundaries crossed between the dates. public static int DateDiffMillisecond(DateTime startDate, DateTime endDate) { checked { return DateDiffSecond(startDate, endDate) * 1000 + endDate.Millisecond - startDate.Millisecond; } } ////// Counts the number of millisecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of millisecond boundaries crossed between the dates. public static int? DateDiffMillisecond(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMillisecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of millisecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of millisecond boundaries crossed between the dates. public static int DateDiffMillisecond(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffMillisecond(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of millisecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of millisecond boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffMillisecond(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMillisecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of microsecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of microsecond boundaries crossed between the dates. public static int DateDiffMicrosecond(DateTime startDate, DateTime endDate) { checked { return (int)((endDate.Ticks - startDate.Ticks) / 10); } } ////// Counts the number of microsecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of microsecond boundaries crossed between the dates. public static int? DateDiffMicrosecond(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMicrosecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of microsecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of microsecond boundaries crossed between the dates. public static int DateDiffMicrosecond(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffMicrosecond(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of microsecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of microsecond boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffMicrosecond(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMicrosecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of nanosecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of nanosecond boundaries crossed between the dates. public static int DateDiffNanosecond(DateTime startDate, DateTime endDate) { checked { return (int)((endDate.Ticks - startDate.Ticks) * 100); } } ////// Counts the number of nanosecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of nanosecond boundaries crossed between the dates. public static int? DateDiffNanosecond(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffNanosecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of nanosecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of nanosecond boundaries crossed between the dates. public static int DateDiffNanosecond(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffNanosecond(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of nanosecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of nanosecond boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffNanosecond(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffNanosecond(startDate.Value, endDate.Value); } else { return null; } } ////// This function is translated to Sql Server's LIKE function. /// It cannot be used on the client. /// /// The string that is to be matched. /// The pattern which may involve wildcards %,_,[,],^. ///true if there is a match. [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "pattern", Justification = "[....]: Method is a placeholder for a server-side method.")] [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "matchExpression", Justification = "[....]: Method is a placeholder for a server-side method.")] public static bool Like(string matchExpression, string pattern) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } ////// This function is translated to Sql Server's LIKE function. /// It cannot be used on the client. /// /// The string that is to be matched. /// The pattern which may involve wildcards %,_,[,],^. /// The escape character to use in front of %,_,[,],^ if they are not used as wildcards. ///true if there is a match. [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "pattern", Justification = "[....]: Method is a placeholder for a server-side method.")] [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "matchExpression", Justification = "[....]: Method is a placeholder for a server-side method.")] [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "escapeCharacter", Justification = "[....]: Method is a placeholder for a server-side method.")] public static bool Like(string matchExpression, string pattern, char escapeCharacter) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } ////// This function is translated to Sql Server's DATALENGTH function. It differs /// from LEN in that it includes trailing spaces and will count UNICODE characters /// per byte. /// It cannot be used on the client. /// /// The string to take the length of. ///length of the string [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "value", Justification = "[....]: Method is a placeholder for a server-side method.")] internal static int RawLength(string value) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } ////// This function is translated to Sql Server's DATALENGTH function. /// It cannot be used on the client. /// /// The byte array to take the length of. ///length of the array [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "value", Justification = "[....]: Method is a placeholder for a server-side method.")] internal static int RawLength(byte[] value) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } ////// This function is translated to Sql Server's DATALENGTH function. /// It cannot be used on the client. /// /// The Binary value to take the length of. ///length of the Binary [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "value", Justification = "[....]: Method is a placeholder for a server-side method.")] internal static int RawLength(Binary value) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } } public static class SqlHelpers { public static string GetStringContainsPattern(string text, char escape) { if (text == null) { throw Error.ArgumentNull("text"); } return "%" + EscapeLikeText(text, escape) + "%"; } public static string GetStringStartsWithPattern(string text, char escape) { if (text == null) { throw Error.ArgumentNull("text"); } return EscapeLikeText(text, escape) + "%"; } public static string GetStringEndsWithPattern(string text, char escape) { if (text == null) { throw Error.ArgumentNull("text"); } return "%" + EscapeLikeText(text, escape); } private static string EscapeLikeText(string text, char escape) { if (!(text.Contains("%") || text.Contains("_") || text.Contains("[") || text.Contains("^"))) { return text; } StringBuilder sb = new StringBuilder(text.Length); foreach (char c in text) { if (c == '%' || c == '_' || c == '[' || c == '^' || c == escape) { sb.Append(escape); } sb.Append(c); } return sb.ToString(); } public static string TranslateVBLikePattern(string pattern, char escape) { if (pattern == null) { throw Error.ArgumentNull("pattern"); } const char vbMany = '*'; const char sqlMany = '%'; const char vbSingle = '?'; const char sqlSingle = '_'; const char vbDigit = '#'; const string sqlDigit = "[0-9]"; const char vbOpenBracket = '['; const char sqlOpenBracket = '['; const char vbCloseBracket = ']'; const char sqlCloseBracket = ']'; const char vbNotList = '!'; const char sqlNotList = '^'; const char vbCharRange = '-'; const char sqlCharRange = '-'; // walk the string, performing conversions StringBuilder result = new StringBuilder(); bool bracketed = false; bool charRange = false; bool possibleNotList = false; int numBracketedCharacters = 0; foreach (char patternChar in pattern) { if (bracketed) { numBracketedCharacters++; // if we're in a possible NotList, anything other than a close bracket will confirm it if (possibleNotList) { if (patternChar != vbCloseBracket) { result.Append(sqlNotList); possibleNotList = false; } else { result.Append(vbNotList); possibleNotList = false; } } switch (patternChar) { case vbNotList: { // translate to SQL's NotList only if the first character in the group if (numBracketedCharacters == 1) { // latch this, and detect the next cycle possibleNotList = true; } else { result.Append(patternChar); } break; } case vbCloseBracket: { // close down the bracket group bracketed = false; possibleNotList = false; result.Append(sqlCloseBracket); break; } case vbCharRange: { if (charRange) { // we've seen the char range indicator already -- SQL // doesn't support multiple ranges in the same group throw Error.VbLikeDoesNotSupportMultipleCharacterRanges(); } else { // remember that we've seen this in the group charRange = true; result.Append(sqlCharRange); break; } } case sqlNotList: { if (numBracketedCharacters == 1) { // need to escape this one result.Append(escape); } result.Append(patternChar); break; } default: { if (patternChar == escape) { result.Append(escape); result.Append(escape); } else { result.Append(patternChar); } break; } } } else { switch (patternChar) { case vbMany: { result.Append(sqlMany); break; } case vbSingle: { result.Append(sqlSingle); break; } case vbDigit: { result.Append(sqlDigit); break; } case vbOpenBracket: { // we're openning a bracketed group, so reset the group state bracketed = true; charRange = false; numBracketedCharacters = 0; result.Append(sqlOpenBracket); break; } // SQL's special characters need to be escaped case sqlMany: case sqlSingle: { result.Append(escape); result.Append(patternChar); break; } default: { if (patternChar == escape) { result.Append(escape); result.Append(escape); } else { result.Append(patternChar); } break; } } } } if (bracketed) { throw Error.VbLikeUnclosedBracket(); } return result.ToString(); } } } // File provided for Reference Use Only by Microsoft Corporation (c) 2007. // Copyright (c) Microsoft Corporation. All rights reserved. using System; using System.Collections.Generic; using System.Diagnostics; using System.Text; using System.Text.RegularExpressions; using System.Reflection; using System.Diagnostics.CodeAnalysis; namespace System.Data.Linq.SqlClient { public static class SqlMethods { ////// Counts the number of year boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of year boundaries crossed between the dates. public static int DateDiffYear(DateTime startDate, DateTime endDate) { return endDate.Year - startDate.Year; } ////// Counts the number of year boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of year boundaries crossed between the dates. public static int? DateDiffYear(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffYear(startDate.Value,endDate.Value); } else { return null; } } ////// Counts the number of year boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of year boundaries crossed between the dates. public static int DateDiffYear(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffYear(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of year boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(YEAR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of year boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffYear(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffYear(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of month boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of month boundaries crossed between the dates. public static int DateDiffMonth(DateTime startDate, DateTime endDate) { return 12 * (endDate.Year - startDate.Year) + endDate.Month - startDate.Month; } ////// Counts the number of month boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of month boundaries crossed between the dates. public static int? DateDiffMonth(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMonth(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of month boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of month boundaries crossed between the dates. public static int DateDiffMonth(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffMonth(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of month boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MONTH,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of month boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffMonth(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMonth(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of day boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of day boundaries crossed between the dates. public static int DateDiffDay(DateTime startDate, DateTime endDate) { TimeSpan diff = endDate.Date - startDate.Date; return diff.Days; } ////// Counts the number of day boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of day boundaries crossed between the dates. public static int? DateDiffDay(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffDay(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of day boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of day boundaries crossed between the dates. public static int DateDiffDay(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffDay(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of day boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(DAY,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of day boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffDay(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffDay(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of hour boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of hour boundaries crossed between the dates. public static int DateDiffHour(DateTime startDate, DateTime endDate) { checked { return DateDiffDay(startDate, endDate) * 24 + endDate.Hour - startDate.Hour; } } ////// Counts the number of hour boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of hour boundaries crossed between the dates. public static int? DateDiffHour(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffHour(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of hour boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of hour boundaries crossed between the dates. public static int DateDiffHour(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffHour(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of hour boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(HOUR,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of hour boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffHour(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffHour(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of minute boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of minute boundaries crossed between the dates. public static int DateDiffMinute(DateTime startDate, DateTime endDate) { checked { return DateDiffHour(startDate, endDate) * 60 + endDate.Minute - startDate.Minute; } } ////// Counts the number of minute boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of minute boundaries crossed between the dates. public static int? DateDiffMinute(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMinute(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of minute boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of minute boundaries crossed between the dates. public static int DateDiffMinute(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffMinute(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of minute boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MINUTE,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of minute boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffMinute(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMinute(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of second boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of second boundaries crossed between the dates. public static int DateDiffSecond(DateTime startDate, DateTime endDate) { checked { return DateDiffMinute(startDate, endDate) * 60 + endDate.Second - startDate.Second; } } ////// Counts the number of second boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of second boundaries crossed between the dates. public static int? DateDiffSecond(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffSecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of second boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of second boundaries crossed between the dates. public static int DateDiffSecond(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffSecond(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of second boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(SECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of second boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffSecond(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffSecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of millisecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of millisecond boundaries crossed between the dates. public static int DateDiffMillisecond(DateTime startDate, DateTime endDate) { checked { return DateDiffSecond(startDate, endDate) * 1000 + endDate.Millisecond - startDate.Millisecond; } } ////// Counts the number of millisecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of millisecond boundaries crossed between the dates. public static int? DateDiffMillisecond(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMillisecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of millisecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of millisecond boundaries crossed between the dates. public static int DateDiffMillisecond(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffMillisecond(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of millisecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MILLISECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of millisecond boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffMillisecond(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMillisecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of microsecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of microsecond boundaries crossed between the dates. public static int DateDiffMicrosecond(DateTime startDate, DateTime endDate) { checked { return (int)((endDate.Ticks - startDate.Ticks) / 10); } } ////// Counts the number of microsecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of microsecond boundaries crossed between the dates. public static int? DateDiffMicrosecond(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMicrosecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of microsecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of microsecond boundaries crossed between the dates. public static int DateDiffMicrosecond(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffMicrosecond(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of microsecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(MICROSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of microsecond boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffMicrosecond(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffMicrosecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of nanosecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of nanosecond boundaries crossed between the dates. public static int DateDiffNanosecond(DateTime startDate, DateTime endDate) { checked { return (int)((endDate.Ticks - startDate.Ticks) * 100); } } ////// Counts the number of nanosecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of nanosecond boundaries crossed between the dates. public static int? DateDiffNanosecond(DateTime? startDate, DateTime? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffNanosecond(startDate.Value, endDate.Value); } else { return null; } } ////// Counts the number of nanosecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of nanosecond boundaries crossed between the dates. public static int DateDiffNanosecond(DateTimeOffset startDate, DateTimeOffset endDate) { return DateDiffNanosecond(startDate.UtcDateTime, endDate.UtcDateTime); } ////// Counts the number of nanosecond boundaries crossed between the startDate and endDate. /// Corresponds to SQL Server's DATEDIFF(NANOSECOND,startDate,endDate). /// /// Starting date for the calculation. /// Ending date for the calculation. ///Number of nanosecond boundaries crossed between the dates. [SuppressMessage("Microsoft.MSInternal", "CA908:AvoidTypesThatRequireJitCompilationInPrecompiledAssemblies", Justification = "[....]: This is an issue in CLR that they are going to fix in Orcas SP1. Entered Bug# 175920 to remove these suppressions after the issue is fixed.")] public static int? DateDiffNanosecond(DateTimeOffset? startDate, DateTimeOffset? endDate) { if (startDate.HasValue && endDate.HasValue) { return DateDiffNanosecond(startDate.Value, endDate.Value); } else { return null; } } ////// This function is translated to Sql Server's LIKE function. /// It cannot be used on the client. /// /// The string that is to be matched. /// The pattern which may involve wildcards %,_,[,],^. ///true if there is a match. [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "pattern", Justification = "[....]: Method is a placeholder for a server-side method.")] [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "matchExpression", Justification = "[....]: Method is a placeholder for a server-side method.")] public static bool Like(string matchExpression, string pattern) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } ////// This function is translated to Sql Server's LIKE function. /// It cannot be used on the client. /// /// The string that is to be matched. /// The pattern which may involve wildcards %,_,[,],^. /// The escape character to use in front of %,_,[,],^ if they are not used as wildcards. ///true if there is a match. [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "pattern", Justification = "[....]: Method is a placeholder for a server-side method.")] [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "matchExpression", Justification = "[....]: Method is a placeholder for a server-side method.")] [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "escapeCharacter", Justification = "[....]: Method is a placeholder for a server-side method.")] public static bool Like(string matchExpression, string pattern, char escapeCharacter) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } ////// This function is translated to Sql Server's DATALENGTH function. It differs /// from LEN in that it includes trailing spaces and will count UNICODE characters /// per byte. /// It cannot be used on the client. /// /// The string to take the length of. ///length of the string [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "value", Justification = "[....]: Method is a placeholder for a server-side method.")] internal static int RawLength(string value) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } ////// This function is translated to Sql Server's DATALENGTH function. /// It cannot be used on the client. /// /// The byte array to take the length of. ///length of the array [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "value", Justification = "[....]: Method is a placeholder for a server-side method.")] internal static int RawLength(byte[] value) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } ////// This function is translated to Sql Server's DATALENGTH function. /// It cannot be used on the client. /// /// The Binary value to take the length of. ///length of the Binary [SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "value", Justification = "[....]: Method is a placeholder for a server-side method.")] internal static int RawLength(Binary value) { throw Error.SqlMethodOnlyForSql(MethodInfo.GetCurrentMethod()); } } public static class SqlHelpers { public static string GetStringContainsPattern(string text, char escape) { if (text == null) { throw Error.ArgumentNull("text"); } return "%" + EscapeLikeText(text, escape) + "%"; } public static string GetStringStartsWithPattern(string text, char escape) { if (text == null) { throw Error.ArgumentNull("text"); } return EscapeLikeText(text, escape) + "%"; } public static string GetStringEndsWithPattern(string text, char escape) { if (text == null) { throw Error.ArgumentNull("text"); } return "%" + EscapeLikeText(text, escape); } private static string EscapeLikeText(string text, char escape) { if (!(text.Contains("%") || text.Contains("_") || text.Contains("[") || text.Contains("^"))) { return text; } StringBuilder sb = new StringBuilder(text.Length); foreach (char c in text) { if (c == '%' || c == '_' || c == '[' || c == '^' || c == escape) { sb.Append(escape); } sb.Append(c); } return sb.ToString(); } public static string TranslateVBLikePattern(string pattern, char escape) { if (pattern == null) { throw Error.ArgumentNull("pattern"); } const char vbMany = '*'; const char sqlMany = '%'; const char vbSingle = '?'; const char sqlSingle = '_'; const char vbDigit = '#'; const string sqlDigit = "[0-9]"; const char vbOpenBracket = '['; const char sqlOpenBracket = '['; const char vbCloseBracket = ']'; const char sqlCloseBracket = ']'; const char vbNotList = '!'; const char sqlNotList = '^'; const char vbCharRange = '-'; const char sqlCharRange = '-'; // walk the string, performing conversions StringBuilder result = new StringBuilder(); bool bracketed = false; bool charRange = false; bool possibleNotList = false; int numBracketedCharacters = 0; foreach (char patternChar in pattern) { if (bracketed) { numBracketedCharacters++; // if we're in a possible NotList, anything other than a close bracket will confirm it if (possibleNotList) { if (patternChar != vbCloseBracket) { result.Append(sqlNotList); possibleNotList = false; } else { result.Append(vbNotList); possibleNotList = false; } } switch (patternChar) { case vbNotList: { // translate to SQL's NotList only if the first character in the group if (numBracketedCharacters == 1) { // latch this, and detect the next cycle possibleNotList = true; } else { result.Append(patternChar); } break; } case vbCloseBracket: { // close down the bracket group bracketed = false; possibleNotList = false; result.Append(sqlCloseBracket); break; } case vbCharRange: { if (charRange) { // we've seen the char range indicator already -- SQL // doesn't support multiple ranges in the same group throw Error.VbLikeDoesNotSupportMultipleCharacterRanges(); } else { // remember that we've seen this in the group charRange = true; result.Append(sqlCharRange); break; } } case sqlNotList: { if (numBracketedCharacters == 1) { // need to escape this one result.Append(escape); } result.Append(patternChar); break; } default: { if (patternChar == escape) { result.Append(escape); result.Append(escape); } else { result.Append(patternChar); } break; } } } else { switch (patternChar) { case vbMany: { result.Append(sqlMany); break; } case vbSingle: { result.Append(sqlSingle); break; } case vbDigit: { result.Append(sqlDigit); break; } case vbOpenBracket: { // we're openning a bracketed group, so reset the group state bracketed = true; charRange = false; numBracketedCharacters = 0; result.Append(sqlOpenBracket); break; } // SQL's special characters need to be escaped case sqlMany: case sqlSingle: { result.Append(escape); result.Append(patternChar); break; } default: { if (patternChar == escape) { result.Append(escape); result.Append(escape); } else { result.Append(patternChar); } break; } } } } if (bracketed) { throw Error.VbLikeUnclosedBracket(); } return result.ToString(); } } } // File provided for Reference Use Only by Microsoft Corporation (c) 2007. // Copyright (c) Microsoft Corporation. All rights reserved.
Link Menu
This book is available now!
Buy at Amazon US or
Buy at Amazon UK
- CodeExpressionStatement.cs
- ContentElementAutomationPeer.cs
- TextTreeExtractElementUndoUnit.cs
- ObjectComplexPropertyMapping.cs
- Section.cs
- GenericsInstances.cs
- CreateSequence.cs
- NavigatingCancelEventArgs.cs
- HtmlTableCellCollection.cs
- PathGeometry.cs
- HtmlControl.cs
- PasswordBox.cs
- XmlIncludeAttribute.cs
- TimeSpanValidatorAttribute.cs
- HttpsChannelListener.cs
- SubMenuStyleCollection.cs
- ReturnType.cs
- AuthorizationSection.cs
- Decorator.cs
- DataGridColumnEventArgs.cs
- IndexingContentUnit.cs
- LineGeometry.cs
- ScriptBehaviorDescriptor.cs
- ClockController.cs
- DataGridViewImageCell.cs
- ResourceManager.cs
- LowerCaseStringConverter.cs
- DirectionalLight.cs
- RectangleHotSpot.cs
- WindowsTokenRoleProvider.cs
- DeclarationUpdate.cs
- PrintDialog.cs
- SignatureConfirmations.cs
- SoapConverter.cs
- InfoCardTrace.cs
- Stopwatch.cs
- TickBar.cs
- ObjectConverter.cs
- IODescriptionAttribute.cs
- StringUtil.cs
- PhysicalFontFamily.cs
- DocumentSchemaValidator.cs
- ExpressionBuilder.cs
- BitmapMetadataEnumerator.cs
- BoundColumn.cs
- DesignTimeTemplateParser.cs
- EventSourceCreationData.cs
- ToolStripPanel.cs
- cookie.cs
- CleanUpVirtualizedItemEventArgs.cs
- ToolStripSettings.cs
- AccessViolationException.cs
- wgx_exports.cs
- ToolboxItemCollection.cs
- Attributes.cs
- FileRecordSequenceHelper.cs
- OverlappedAsyncResult.cs
- ResourceDescriptionAttribute.cs
- DataGridViewLinkCell.cs
- WebPartMinimizeVerb.cs
- JoinTreeSlot.cs
- EpmCustomContentDeSerializer.cs
- XmlWrappingReader.cs
- TargetControlTypeAttribute.cs
- Int32Storage.cs
- CompiledELinqQueryState.cs
- SparseMemoryStream.cs
- DragStartedEventArgs.cs
- ModelChangedEventArgsImpl.cs
- CollectionChange.cs
- RIPEMD160.cs
- HttpHostedTransportConfiguration.cs
- UnicodeEncoding.cs
- PropertyChangeTracker.cs
- QuaternionAnimationBase.cs
- WorkflowEventArgs.cs
- StaticSiteMapProvider.cs
- ClassDataContract.cs
- ReferencedCollectionType.cs
- RectAnimationBase.cs
- DataGridViewCellEventArgs.cs
- RemotingServices.cs
- SystemWebExtensionsSectionGroup.cs
- DriveNotFoundException.cs
- IndexerNameAttribute.cs
- StandardBindingReliableSessionElement.cs
- TextWriterEngine.cs
- ISAPIApplicationHost.cs
- BamlLocalizableResource.cs
- PersistChildrenAttribute.cs
- DataGridColumnCollectionEditor.cs
- PackWebRequest.cs
- ParserExtension.cs
- ProviderIncompatibleException.cs
- CustomTrackingQuery.cs
- ViewManager.cs
- QilReplaceVisitor.cs
- SvcFileManager.cs
- CodeCommentStatement.cs
- ConnectionStringsExpressionBuilder.cs