Code:
/ Net / Net / 3.5.50727.3053 / DEVDIV / depot / DevDiv / releases / Orcas / SP / ndp / fx / src / DLinq / Dlinq / SqlClient / SqlMethods.cs / 2 / 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
- StyleHelper.cs
- DataProtection.cs
- MaskInputRejectedEventArgs.cs
- ObjRef.cs
- StringCollection.cs
- TextMarkerSource.cs
- PathFigureCollectionConverter.cs
- ErrorRuntimeConfig.cs
- UriWriter.cs
- WMIGenerator.cs
- IndexingContentUnit.cs
- CatalogPartDesigner.cs
- TrackingParameters.cs
- SmtpReplyReader.cs
- IgnoreSection.cs
- D3DImage.cs
- FreeFormDesigner.cs
- CookielessHelper.cs
- AttachedPropertyBrowsableForTypeAttribute.cs
- odbcmetadatacollectionnames.cs
- WindowInteropHelper.cs
- NoneExcludedImageIndexConverter.cs
- CursorConverter.cs
- PolicyLevel.cs
- SendContent.cs
- VirtualizedContainerService.cs
- PasswordDeriveBytes.cs
- DictionaryEntry.cs
- CommentAction.cs
- Environment.cs
- FrameworkTemplate.cs
- ClientApiGenerator.cs
- QilUnary.cs
- ConnectionStringsExpressionBuilder.cs
- BinaryMessageEncodingBindingElement.cs
- CheckBoxPopupAdapter.cs
- OracleDateTime.cs
- CollectionType.cs
- DesignerTextViewAdapter.cs
- SplitContainer.cs
- PriorityChain.cs
- PublisherMembershipCondition.cs
- ConnectionsZoneDesigner.cs
- DataGridViewColumnDesignTimeVisibleAttribute.cs
- SuppressedPackageProperties.cs
- HashMembershipCondition.cs
- GAC.cs
- CommunicationObjectFaultedException.cs
- httpstaticobjectscollection.cs
- TreeViewImageIndexConverter.cs
- ViewStateModeByIdAttribute.cs
- Debugger.cs
- BackgroundFormatInfo.cs
- Environment.cs
- TextContainerHelper.cs
- CategoryAttribute.cs
- HuffModule.cs
- PanelStyle.cs
- FixedSchema.cs
- SchemaCollectionCompiler.cs
- HtmlSelect.cs
- DocumentPageViewAutomationPeer.cs
- JsonQueryStringConverter.cs
- CanonicalFontFamilyReference.cs
- StreamWriter.cs
- SByteConverter.cs
- DataObjectCopyingEventArgs.cs
- MiniParameterInfo.cs
- LocalizationCodeDomSerializer.cs
- RuleProcessor.cs
- XmlIncludeAttribute.cs
- StylusDownEventArgs.cs
- EastAsianLunisolarCalendar.cs
- SqlDependencyListener.cs
- ToolStripItemImageRenderEventArgs.cs
- RuleSettings.cs
- ObjectCloneHelper.cs
- ClickablePoint.cs
- TcpClientSocketManager.cs
- Geometry3D.cs
- GridViewRowEventArgs.cs
- RtfToXamlLexer.cs
- Substitution.cs
- WebBrowserSiteBase.cs
- ClientRolePrincipal.cs
- SqlEnums.cs
- SecurityMessageProperty.cs
- DropShadowBitmapEffect.cs
- commandenforcer.cs
- Header.cs
- PageThemeParser.cs
- DataGridViewElement.cs
- IisTraceListener.cs
- RoutedEventConverter.cs
- ToolStripSplitButton.cs
- IncrementalReadDecoders.cs
- COAUTHINFO.cs
- CounterSetInstance.cs
- BaseAppDomainProtocolHandler.cs
- Sql8ExpressionRewriter.cs