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
- LinkedList.cs
- SourceElementsCollection.cs
- DataGridHeaderBorder.cs
- PropertyKey.cs
- Convert.cs
- SqlClientWrapperSmiStreamChars.cs
- DelayedRegex.cs
- categoryentry.cs
- EFColumnProvider.cs
- Graphics.cs
- PersonalizationProvider.cs
- PublisherIdentityPermission.cs
- ModelUtilities.cs
- HttpHandlersSection.cs
- ListItem.cs
- Comparer.cs
- MetadataSource.cs
- FontSource.cs
- SymmetricKey.cs
- HitTestParameters3D.cs
- WebServiceFaultDesigner.cs
- XmlNodeList.cs
- SigningCredentials.cs
- ManagementObjectSearcher.cs
- StringBuilder.cs
- SelectionBorderGlyph.cs
- NonClientArea.cs
- GeneralTransform2DTo3DTo2D.cs
- ScrollChangedEventArgs.cs
- ChannelReliableSession.cs
- XmlIlGenerator.cs
- ProviderManager.cs
- SchemaNamespaceManager.cs
- XmlSchemaSimpleTypeRestriction.cs
- SystemIcmpV4Statistics.cs
- DefaultPrintController.cs
- CheckBoxList.cs
- ButtonBase.cs
- DictionaryChange.cs
- InstanceCreationEditor.cs
- SiteMapDesignerDataSourceView.cs
- XmlNodeList.cs
- HttpWebRequestElement.cs
- DataServiceCollectionOfT.cs
- EdmEntityTypeAttribute.cs
- StaticDataManager.cs
- RadialGradientBrush.cs
- _SSPIWrapper.cs
- LoginName.cs
- PropertyBuilder.cs
- AttachmentService.cs
- HttpWebRequest.cs
- Random.cs
- XmlSchemaRedefine.cs
- PointLight.cs
- UnicastIPAddressInformationCollection.cs
- XmlRootAttribute.cs
- IPEndPointCollection.cs
- ExpandableObjectConverter.cs
- ScriptingWebServicesSectionGroup.cs
- ObfuscationAttribute.cs
- CommandField.cs
- BorderGapMaskConverter.cs
- SafeNativeMethods.cs
- ControlIdConverter.cs
- Properties.cs
- WebBrowserContainer.cs
- SQLBytesStorage.cs
- HeaderFilter.cs
- ListBindingHelper.cs
- CalendarAutomationPeer.cs
- SystemFonts.cs
- CollectionContainer.cs
- RepeaterItemCollection.cs
- NumericUpDownAcceleration.cs
- RotationValidation.cs
- TagNameToTypeMapper.cs
- RSAPKCS1KeyExchangeDeformatter.cs
- Completion.cs
- StaticContext.cs
- MetadataItemSerializer.cs
- EntitySetRetriever.cs
- Cursor.cs
- JoinGraph.cs
- ManagedFilter.cs
- MarkupProperty.cs
- RSAPKCS1SignatureFormatter.cs
- DataViewManager.cs
- EventDescriptor.cs
- SettingsSection.cs
- WebPartTransformerCollection.cs
- XmlAttributeOverrides.cs
- DocumentViewerAutomationPeer.cs
- DependencyPropertyChangedEventArgs.cs
- SoapIgnoreAttribute.cs
- RegexReplacement.cs
- BamlTreeNode.cs
- ListViewHitTestInfo.cs
- LoadedOrUnloadedOperation.cs
- ArithmeticException.cs