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
- CultureTableRecord.cs
- RouteItem.cs
- ProcessHost.cs
- SqlUserDefinedTypeAttribute.cs
- CodeAccessPermission.cs
- SessionConnectionReader.cs
- BlockCollection.cs
- SafeRightsManagementSessionHandle.cs
- shaperfactory.cs
- PartDesigner.cs
- EntityTypeBase.cs
- BamlStream.cs
- DataKey.cs
- indexingfiltermarshaler.cs
- httpserverutility.cs
- ProfileProvider.cs
- EmptyQuery.cs
- PkcsUtils.cs
- SqlCachedBuffer.cs
- BitmapEffectrendercontext.cs
- KeyGestureValueSerializer.cs
- DataGridRow.cs
- FontInfo.cs
- RowUpdatedEventArgs.cs
- WorkflowInstanceQuery.cs
- DependencyPropertyChangedEventArgs.cs
- Soap.cs
- ChannelProtectionRequirements.cs
- SerializationException.cs
- MsmqIntegrationReceiveParameters.cs
- TextSimpleMarkerProperties.cs
- LazyTextWriterCreator.cs
- StickyNote.cs
- PageAsyncTaskManager.cs
- TextDecorationCollectionConverter.cs
- StoreContentChangedEventArgs.cs
- HtmlWindow.cs
- Camera.cs
- XmlLoader.cs
- IgnoreFlushAndCloseStream.cs
- JobCollate.cs
- ThreadStaticAttribute.cs
- CodeBinaryOperatorExpression.cs
- DurableDispatcherAddressingFault.cs
- CompositeFontInfo.cs
- Border.cs
- ButtonBaseAutomationPeer.cs
- SecurityTokenAuthenticator.cs
- WebPartManagerInternals.cs
- SkipStoryboardToFill.cs
- ReverseComparer.cs
- Authorization.cs
- PasswordRecovery.cs
- SiteMapSection.cs
- OutputCacheSection.cs
- InternalBase.cs
- xmlformatgeneratorstatics.cs
- AutoGeneratedField.cs
- EnvironmentPermission.cs
- AutomationTextAttribute.cs
- datacache.cs
- Int32Rect.cs
- DataServiceHostFactory.cs
- Point4DValueSerializer.cs
- RegexGroup.cs
- RequiredFieldValidator.cs
- FacetDescription.cs
- AttributeTableBuilder.cs
- C14NUtil.cs
- TypeValidationEventArgs.cs
- ScrollEvent.cs
- MultiAsyncResult.cs
- Char.cs
- XmlRawWriter.cs
- AssemblyContextControlItem.cs
- PrtTicket_Base.cs
- TextFormatter.cs
- Rotation3D.cs
- DictionaryBase.cs
- WebPartMinimizeVerb.cs
- EdmComplexTypeAttribute.cs
- WindowsScrollBarBits.cs
- WindowsFormsHelpers.cs
- BaseDataBoundControl.cs
- ThreadInterruptedException.cs
- ToolboxItemCollection.cs
- InvalidFilterCriteriaException.cs
- BrowserCapabilitiesCodeGenerator.cs
- WindowsSecurityTokenAuthenticator.cs
- OleDbFactory.cs
- XPathDocument.cs
- PropertyNames.cs
- Size3DValueSerializer.cs
- TextElementCollection.cs
- WebPart.cs
- IdentifierElement.cs
- Semaphore.cs
- UriExt.cs
- SqlClientWrapperSmiStream.cs
- InputLanguageSource.cs