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
- mactripleDES.cs
- TargetParameterCountException.cs
- QilInvokeLateBound.cs
- ProxyWebPartManagerDesigner.cs
- BuiltInExpr.cs
- CodeMemberField.cs
- SymLanguageType.cs
- NullReferenceException.cs
- ContentPlaceHolder.cs
- EditingCommands.cs
- LinqDataSourceUpdateEventArgs.cs
- SerializationSectionGroup.cs
- NullReferenceException.cs
- DbCommandDefinition.cs
- TypeLibraryHelper.cs
- SQLBytesStorage.cs
- ListSortDescription.cs
- MimeXmlImporter.cs
- PreProcessor.cs
- PathParser.cs
- OleDbRowUpdatingEvent.cs
- GroupItem.cs
- XmlNullResolver.cs
- CodeIndexerExpression.cs
- codemethodreferenceexpression.cs
- ConsumerConnectionPoint.cs
- ManifestResourceInfo.cs
- FullTrustAssembliesSection.cs
- XPathDocumentIterator.cs
- PageParserFilter.cs
- DurableErrorHandler.cs
- SelectingProviderEventArgs.cs
- PermissionAttributes.cs
- ApplicationManager.cs
- QuestionEventArgs.cs
- ContentPresenter.cs
- HwndPanningFeedback.cs
- GridViewItemAutomationPeer.cs
- SkinBuilder.cs
- VideoDrawing.cs
- Win32PrintDialog.cs
- typedescriptorpermission.cs
- MethodExpression.cs
- DataGridState.cs
- IntegerValidator.cs
- DocumentPropertiesDialog.cs
- FolderBrowserDialog.cs
- PackageRelationshipCollection.cs
- ProviderCollection.cs
- FilterQuery.cs
- XsltInput.cs
- __FastResourceComparer.cs
- IdentitySection.cs
- ToolStripDropDownButton.cs
- CachingHintValidation.cs
- GridViewPageEventArgs.cs
- RotationValidation.cs
- TimeZone.cs
- TemplatedWizardStep.cs
- InvalidCommandTreeException.cs
- GridViewCancelEditEventArgs.cs
- Parsers.cs
- OciLobLocator.cs
- LinkLabel.cs
- RegexRunnerFactory.cs
- fixedPageContentExtractor.cs
- ShutDownListener.cs
- NetworkInformationException.cs
- TabletDeviceInfo.cs
- RemoteWebConfigurationHostStream.cs
- RootBrowserWindowProxy.cs
- MappedMetaModel.cs
- DataServiceHostFactory.cs
- DPTypeDescriptorContext.cs
- WebPartConnectionsConnectVerb.cs
- ControlDesigner.cs
- ComponentDispatcherThread.cs
- ClientConfigurationHost.cs
- DiscoveryServerProtocol.cs
- SystemWebSectionGroup.cs
- UserPersonalizationStateInfo.cs
- InstanceStoreQueryResult.cs
- StackBuilderSink.cs
- ResourceManagerWrapper.cs
- ButtonField.cs
- IgnorePropertiesAttribute.cs
- GetPageNumberCompletedEventArgs.cs
- ExternalCalls.cs
- PortCache.cs
- ProgressiveCrcCalculatingStream.cs
- MemberPath.cs
- CompatibleIComparer.cs
- RawTextInputReport.cs
- MembershipValidatePasswordEventArgs.cs
- StorageBasedPackageProperties.cs
- CodeGeneratorOptions.cs
- OdbcReferenceCollection.cs
- Int32RectConverter.cs
- TdsParserHelperClasses.cs
- CodeDirectionExpression.cs