SqlMethods.cs source code in C# .NET

Source code for the .NET framework in C#

                        

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

Network programming in C#, Network Programming in VB.NET, Network Programming in .NET
This book is available now!
Buy at Amazon US or
Buy at Amazon UK