using System;
using System.Xml;
using System.Xml.Serialization;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Diagnostics;
using System.Runtime;
using System.Runtime.Serialization;
using Tdo;
using Tdo.Common;
using Tdo.Common.Entities;
using Tdo.Common.Entities.Tables;
using Tdo.Common.Entities.Views;
using Tdo.Common.Helper;
using Tdo.Common.TdoSqlExpressionDom;
using Tdo.Common.TdoTypes;
namespace Tdo.Common.Programmability{
/// <summary>
/// Implements TDO SQL Functions for Select Methods
/// </summary>
[CLSCompliant(true)]
[Serializable()]
public static class Functions
{
#region Distinct
/// <summary>
/// The DISTINCT eliminates duplicate rows from the results of a SELECT statement
/// SELECT DISTINCT tdoColumn1, tdoColumn2, ... tdoColumnN FROM ....
/// </summary>
/// <param name="tdoColumns">The Tdo columns.</param>
/// <returns>Returns DISTINCT rows.</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static ITdoColumn Distinct(params ITdoColumn[] tdoColumns)
{
ITdoColumn result = new TdoString();
result.IsConstant = false;
if (tdoColumns.Length > 0)
{
for (int i = 0; i < tdoColumns.Length; i++)
{
result.ColumnName += Utility.ColumnOrValueWithoutAlias(tdoColumns[i]) + ",";
}
result.ColumnName = "DISTINCT " + result.ColumnName.TrimEnd(',');
}
else
{
result.ColumnName = "DISTINCT *";
}
result.ReadOnly = true;
return result;
}
#endregion Distinct
#region Count
/// <summary>
/// SQL Count Function.
/// SELECT COUNT(tdoColumn) as columnName
/// </summary>
/// <param name="tdoColumn">Field Name for Count</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns the number of items in a group. </returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T Count<T>(ITdoColumn tdoColumn, string columnName)
where T : ITdoExactNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName = "COUNT (" + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
/// <summary>
/// Specifies that COUNT returns the number of pUnique nonnull values.
/// SELECT COUNT(DISTINCT tdoColumn) as columnName
/// </summary>
/// <param name="tdoColumn">Field Name for Count Distinct</param>
/// <param name="columnName">Counter Name</param>
/// <returns>Returns the number of items in a group. </returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T CountDistinct<T>(ITdoColumn tdoColumn, string columnName)
where T : ITdoNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName = "COUNT (DISTINCT " + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Concat
/// <summary>
/// Concats the specified tdo columns.
/// </summary>
/// <param name="tdoColumns">The tdo columns.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T Concat<T>(ITdoColumn[] tdoColumns, string columnName)
where T : ITdoColumn, new()
{
T t = new T();
t.Caption = columnName;
t.ColumnName = String.Empty;
foreach (ITdoColumn c in tdoColumns)
{
t.ColumnName += String.Concat(Utility.ColumnOrValueWithoutAlias(c), "+");
}
t.ColumnName = t.ColumnName.TrimEnd('+');
t.ReadOnly = true;
return t;
}
/// <summary>
/// Concats the specified tdo columns.
/// </summary>
/// <param name="tdoColumn1">The tdo column1.</param>
/// <param name="tdoColumn2">The tdo column2.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T Concat<T>(ITdoColumn tdoColumn1, ITdoColumn tdoColumn2, string columnName)
where T : ITdoColumn, new()
{
return Functions.Concat<T>(new ITdoColumn[] { tdoColumn1, tdoColumn2 }, columnName);
}
/// <summary>
/// Concats the specified tdo columns.
/// </summary>
/// <param name="tdoColumn1">The tdo column1.</param>
/// <param name="tdoColumn2">The tdo column2.</param>
/// <param name="tdoColumn3">The tdo column3.</param>
/// <param name="columnName">Name of the column.</param>
/// <returns></returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T Concat<T>(ITdoColumn tdoColumn1, ITdoColumn tdoColumn2, ITdoColumn tdoColumn3, string columnName)
where T : ITdoColumn, new()
{
return Functions.Concat<T>(new ITdoColumn[] { tdoColumn1, tdoColumn2, tdoColumn3 }, columnName);
}
#endregion Concat
#region Abs
/// <summary>
/// Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
/// ABS ( numeric_expression )
/// </summary>
/// <param name="tdoColumn">Field Name for Abs</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns the absolute, positive actualValue of the given numeric expression.</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters")]
public static T Abs<T>(ITdoNumericColumn tdoColumn, string columnName)
where T : ITdoNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName="ABS ("+Utility.ColumnOrValueWithoutAlias(tdoColumn)+") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Avg
/// <summary>
/// Returns the average of the values in a group. Null values are ignored.
/// AVG ( numeric_expression )
/// </summary>
/// <param name="tdoColumn">Field Name for Avg</param>
/// <param name="columnName">Column Name</param>
/// <returns>Is an expression of the exact numeric or approximate numeric data type category</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T Avg<T>(ITdoNumericColumn tdoColumn, string columnName)
where T : ITdoNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName = "AVG (" + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region AvgDistinct
/// <summary>
/// Returns the average of the values in a group. Null values are ignored.
/// Specifies that AVG be performed only on each pUnique instance of a actualValue, regardless of how many times the actualValue occurs.
/// AVG ( DISTINCT numeric_expression )
/// </summary>
/// <param name="tdoColumn">Field Name for Avg</param>
/// <param name="columnName">Column Name</param>
/// <returns>Is an expression of the exact numeric or approximate numeric data type category</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T AvgDistinct<T>(ITdoNumericColumn tdoColumn, string columnName)
where T : ITdoNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName = "AVG (DISTINCT " + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region DateAdd
/// <summary>
/// Is the parameter that specifies on which part of the date to return a new actualValue.
/// </summary>
public enum TdoDatePart
{
/// <summary>
/// Year
/// </summary>
Year,
/// <summary>
/// Quarter
/// </summary>
Quarter,
/// <summary>
/// Month
/// </summary>
Month,
/// <summary>
/// Day Of Year
/// </summary>
DayOfYear,
/// <summary>
/// Day
/// </summary>
Day,
/// <summary>
/// Week
/// </summary>
Week,
/// <summary>
/// Hour
/// </summary>
Hour,
/// <summary>
/// Minute
/// </summary>
Minute,
/// <summary>
/// Second
/// </summary>
Second,
/// <summary>
/// Millisecond
/// </summary>
Millisecond
}
/// <summary>
/// Returns a new datetime actualValue based on adding an interval to the specified date.
/// DATEADD ( datepart , number, datetime )
/// </summary>
/// <param name="datepart">Is the parameter that specifies on which part of the date to return a new actualValue.</param>
/// <param name="number">Is the actualValue used to increment datepart.</param>
/// <param name="datetime">Is an expression that returns a datetime or smalldatetime actualValue</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns TdoDateTime</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1305:SpecifyIFormatProvider")]
public static TdoDateTime DateAdd(TdoDatePart datepart,int number,TdoDateTime datetime, string columnName)
{
TdoDateTime t=new TdoDateTime();
t.Caption = columnName;
t.ColumnName = "DATEADD (" + datepart.ToString() + "," + number.ToString() + "," + Utility.ColumnOrValueWithoutAlias(datetime) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region DateDiff
/// <summary>
/// Returns the number of date and time boundaries crossed between two specified dates.
/// DATEDIFF ( datepart , startdate , enddate )
/// </summary>
/// <param name="datepart">Is the parameter that specifies on which part of the date to return a new actualValue.</param>
/// <param name="startdate">Is the beginning date for the calculation.</param>
/// <param name="enddate">Is the ending date for the calculation.</param>
/// <param name="columnName">Column Name</param>
/// <returns>startdate is subtracted from enddate. If startdate is later than enddate, a negative actualValue is returned.</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T DateDiff<T>(TdoDatePart datepart,TdoDateTime startdate,TdoDateTime enddate, string columnName)
where T : ITdoNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName = "DATEDIFF (" + datepart.ToString() + "," + Utility.ColumnOrValueWithoutAlias(startdate) + "," + Utility.ColumnOrValueWithoutAlias(enddate) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region TdoDatePart
/// <summary>
/// Returns an integer representing the specified datepart of the specified date.
/// DATEPART ( datepart , date )
/// </summary>
/// <param name="datepart">Is the parameter that specifies on which part of the date to return a new actualValue.</param>
/// <param name="datetime">Is the date for the calculation.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoInt32 representing the specified datepart of the specified date.</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1719:ParameterNamesShouldNotMatchMemberNames"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static T DatePart<T>(TdoDatePart datepart,TdoDateTime datetime, string columnName)
where T : ITdoNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName = "DATEPART (" + datepart.ToString() + "," + Utility.ColumnOrValueWithoutAlias(datetime)+ ") " + (!String.IsNullOrEmpty(columnName) ? "as [" + columnName + "] " : "");
t.ReadOnly=true;
return t;
}
#endregion
#region DateName
/// <summary>
/// Returns a character string representing the specified datepart of the specified date.
/// DATENAME ( datepart , date )
/// </summary>
/// <param name="datename">Is the parameter that specifies on which part of the date to return a new actualValue.</param>
/// <param name="datetime">Is the date for the calculation.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoString representing the specified datepart of the specified date.</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1719:ParameterNamesShouldNotMatchMemberNames"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters")]
public static TdoString DateName(TdoDatePart datename,TdoDateTime datetime, string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "DATENAME (" + datename.ToString() + "," + Utility.ColumnOrValueWithoutAlias(datetime) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Min
/// <summary>
/// Returns the minimum actualValue in the expression.
/// </summary>
/// <param name="tdoColumn">Field Name for Min</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a actualValue same as expression.</returns>
public static T1 Min<T1, T2>(T2 tdoColumn, string columnName)
where T1 : ITdoNoBlobColumn, new()
where T2 : ITdoNoBlobColumn
{
T1 t=new T1();
t.Caption = columnName;
t.ColumnName = "MIN (" + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region MinDistinct
/// <summary>
/// Returns the Minimum (only pUnique values are considered) actualValue in the expression.
/// MinDistinct ( numeric_expression )
/// </summary>
/// <param name="tdoColumn">Field Name for MinDistinct</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a actualValue same as expression.</returns>
public static T1 MinDistinct<T1, T2>(T2 tdoColumn, string columnName)
where T1 : ITdoNoBlobColumn, new()
where T2 : ITdoNoBlobColumn
{
T1 t=new T1();
t.Caption = columnName;
t.ColumnName = "MIN (DISTINCT " + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Max
/// <summary>
/// Returns the Maximum actualValue in the expression.
/// Max ( DateTime )
/// </summary>
/// <param name="tdoColumn">Field Name for Max</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a actualValue same as expression.</returns>
public static T1 Max<T1, T2>(T2 tdoColumn, string columnName)
where T1 : ITdoNoBlobColumn, new()
where T2 : ITdoNoBlobColumn
{
T1 t=new T1();
t.Caption = columnName;
t.ColumnName = "MAX (" + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region MaxDistinct
/// <summary>
/// Returns the Maximum (only pUnique values are considered) actualValue in the expression.
/// MaxDistinct ( numeric_expression )
/// </summary>
/// <param name="tdoColumn">Field Name for MaxDistinct</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a actualValue same as expression.</returns>
public static T1 MaxDistinct<T1, T2>(T2 tdoColumn, string columnName)
where T1 : ITdoNoBlobColumn, new()
where T2 : ITdoNoBlobColumn
{
T1 t=new T1();
t.Caption = columnName;
t.ColumnName = "MAX (DISTINCT " + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Sum
/// <summary>
/// Returns the sum of all the values in the expression. SUM can be used with numeric columnNames only. Null values are ignored.
/// Sum ( numeric_expression )
/// </summary>
/// <param name="tdoColumn">Field Name for Sum</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns the summation of all expression values in the most precise expression data type.</returns>
public static T Sum<T>(T tdoColumn, string columnName)
where T : ITdoNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName = "SUM (" + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region SumDistinct
/// <summary>
/// Returns the Sum of all pUnique values in the expression. Sum Distinct can be used with numeric columnNames only. Null values are ignored.
/// SUM (DISTINCT numeric_expression )
/// </summary>
/// <param name="tdoColumn">Field Name for Sum Distinct</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns the summation of all expression values in the most precise expression data type.</returns>
public static T SumDistinct<T>(T tdoColumn, string columnName)
where T : ITdoNumericColumn, new()
{
T t=new T();
t.Caption = columnName;
t.ColumnName = "SUM (DISTINCT " + Utility.ColumnOrValueWithoutAlias(tdoColumn) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Left
/// <summary>
/// Returns the part of a character string starting at a specified number of characters from the left.
/// RIGHT ( character_expression , integer_expression )
/// </summary>
/// <param name="expression">Is an expression of character or binary data. expression can be a constant or column. character_expression must be of a data type that can be implicitly convertible to TdoString or TdoBinary.</param>
/// <param name="length">Is a positive whole number. If integer_expression is negative, a null string is returned.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoString representing the specified partial TdoString.</returns>
public static TdoString Left(TdoString expression,int length, string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "RIGHT (" + Utility.ColumnOrValueWithoutAlias(expression) + "," + length.ToString() + ") ";
t.ReadOnly=true;
return t;
}
/// <summary>
/// Returns the part of a character string starting at a specified number of characters from the left.
/// RIGHT ( character_expression , integer_expression )
/// </summary>
/// <param name="expression">Is an expression of character or binary data. expression can be a constant or column. character_expression must be of a data type that can be implicitly convertible to TdoString or TdoBinary.</param>
/// <param name="length">Is a positive whole number. If integer_expression is negative, a null string is returned.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoString representing the specified partial TdoString.</returns>
public static TdoBinary Left(TdoBinary expression,int length, string columnName)
{
TdoBinary t = new TdoBinary();
t.Caption = columnName;
t.ColumnName = "RIGHT (" + Utility.ColumnOrValueWithoutAlias(expression) + "," + length.ToString() + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Right
/// <summary>
/// Returns the part of a character string starting a specified number of integer_expression characters from the right.
/// RIGHT ( character_expression , integer_expression )
/// </summary>
/// <param name="expression">Is an expression of character or binary data. expression can be a constant or column. character_expression must be of a data type that can be implicitly convertible to TdoString or TdoBinary.</param>
/// <param name="length">Is the starting position, expressed as a positive whole number. If integer_expression is negative, an error is returned.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoString representing the specified partial TdoString.</returns>
public static TdoString Right(TdoString expression,int length, string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "RIGHT (" + Utility.ColumnOrValueWithoutAlias(expression) + "," + length.ToString() + ") ";
t.ReadOnly=true;
return t;
}
/// <summary>
/// Returns the part of a character string starting a specified number of integer_expression characters from the right.
/// RIGHT ( character_expression , integer_expression )
/// </summary>
/// <param name="expression">Is an expression of character or binary data. expression can be a constant or column. character_expression must be of a data type that can be implicitly convertible to TdoString or TdoBinary.</param>
/// <param name="length">Is the starting position, expressed as a positive whole number. If integer_expression is negative, an error is returned.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoString representing the specified partial TdoString.</returns>
public static TdoBinary Right(TdoBinary expression,int length, string columnName)
{
TdoBinary t=new TdoBinary();
t.Caption = columnName;
t.ColumnName = "RIGHT (" + Utility.ColumnOrValueWithoutAlias(expression) + "," + length.ToString() + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region SubString
/// <summary>
/// Returns part of a TdoString or TdoBinary expression.
/// SUBSTRING ( expression , start , length )
/// </summary>
/// <param name="expression">Is a TdoString, TdoBinary, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.</param>
/// <param name="start">Is an int that specifies where the substring begins (1-based).</param>
/// <param name="length">Is an integer that specifies the length of the substring (the number of characters or bytes to return).</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types.</returns>
public static TdoString SubString(TdoString expression,int start, int length, string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "SUBSTRING (" + Utility.ColumnOrValueWithoutAlias(expression) + "," + start.ToString() + "," + length.ToString() + ") ";
t.ReadOnly=true;
return t;
}
/// <summary>
/// Returns part of a TdoString or TdoBinary expression.
/// SUBSTRING ( expression , start , length )
/// </summary>
/// <param name="expression">Is a TdoString, TdoBinary, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.</param>
/// <param name="start">Is an int that specifies where the substring begins (1-based).</param>
/// <param name="length">Is an integer that specifies the length of the substring (the number of characters or bytes to return).</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types.</returns>
public static TdoBinary SubString(TdoBinary expression,int start, int length, string columnName)
{
TdoBinary t=new TdoBinary();
t.Caption = columnName;
t.ColumnName = "SUBSTRING (" + Utility.ColumnOrValueWithoutAlias(expression) + "," + start.ToString() + "," + length.ToString() + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Upper
/// <summary>
/// Returns a character expression with lowercase character data converted to uppercase.
/// UPPER ( character_expression )
/// </summary>
/// <param name="expression">Is an expression of character data. character_expression can be a constant, variable, or column of TdoString.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns an Uppercase TdoString </returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters")]
public static TdoString Upper(TdoString expression,string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "UPPER (" + Utility.ColumnOrValueWithoutAlias(expression) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Lower
/// <summary>
/// Returns a character expression with uppercase character data converted to lowercase.
/// LOWER ( character_expression )
/// </summary>
/// <param name="expression">Is an expression of character data. character_expression can be a constant, variable, or column of TdoString.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns an Lowercase TdoString </returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters")]
public static TdoString Lower(TdoString expression,string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "LOWER (" + Utility.ColumnOrValueWithoutAlias(expression) + ") " + (!String.IsNullOrEmpty(columnName) ? "as [" + columnName + "] " : "");
t.ReadOnly=true;
return t;
}
#endregion
#region LTrim
/// <summary>
/// Returns a character expression after removing leading blanks.
/// LTRIM ( character_expression )
/// </summary>
/// <param name="expression">Is an expression of character data. character_expression can be a constant, variable, or column of TdoString.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoString </returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters")]
public static TdoString LTrim(TdoString expression,string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "LTRIM (" + Utility.ColumnOrValueWithoutAlias(expression) + ") " + (!String.IsNullOrEmpty(columnName) ? "as [" + columnName + "] " : "");
t.ReadOnly=true;
return t;
}
#endregion
#region RTrim
/// <summary>
/// Returns a character string after truncating all trailing blanks.
/// RTRIM ( character_expression )
/// </summary>
/// <param name="expression">Is an expression of character data. character_expression can be a constant, variable, or column of TdoString.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoString </returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters")]
public static TdoString RTrim(TdoString expression,string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "RTRIM (" + Utility.ColumnOrValueWithoutAlias(expression) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region Reverse
/// <summary>
/// Returns the reverse of a character expression.
/// REVERSE ( character_expression )
/// </summary>
/// <param name="expression">Is an expression of character data. character_expression can be a constant, variable, or column of TdoString.</param>
/// <param name="columnName">Column Name</param>
/// <returns>Returns a TdoString </returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1011:ConsiderPassingBaseTypesAsParameters")]
public static TdoString Reverse(TdoString expression,string columnName)
{
TdoString t=new TdoString();
t.Caption = columnName;
t.ColumnName = "REVERSE (" + Utility.ColumnOrValueWithoutAlias(expression) + ") ";
t.ReadOnly=true;
return t;
}
#endregion
#region TOP/TOP Percent
/// <summary>
/// The TOP clause limits the number of rows returned in the result set.
/// SELECT TOP n PERCENT tdoColumn1, tdoColumn2, ... tdoColumnN FROM ....
/// </summary>
/// <param name="n">n is the percentage of the result set rows to return.</param>
/// <param name="tdoColumns">The Tdo columns.</param>
/// <returns>ITDOColumns surrounded by TOP clause.</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static ITdoColumn TopPercent(double n, params ITdoColumn[] tdoColumns)
{
ITdoColumn result = new TdoString();
result.IsConstant = false;
if (tdoColumns.Length > 0)
{
for (int i = 0; i < tdoColumns.Length; i++)
{
result.ColumnName += Utility.ColumnOrValueWithoutAlias(tdoColumns[i]) + ",";
}
result.ColumnName = String.Format("TOP {0}{1} {2}",n.ToString().Replace(',','.')," PERCENT", result.ColumnName.TrimEnd(','));
}
else
{
result.ColumnName = String.Format("TOP {0}{1} *", n.ToString().Replace(',', '.'), " PERCENT");
}
result.ReadOnly = true;
return result;
}
/// <summary>
/// The TOP clause limits the number of rows returned in the result set.
/// SELECT TOP x tdoColumn1, tdoColumn2, ... tdoColumnN FROM ....
/// </summary>
/// <param name="n">n specifies how many rows are returned, n is the number of rows to return.</param>
/// <param name="tdoColumns">The Tdo columns.</param>
/// <returns>ITDOColumns surrounded by TOP clause.</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
public static ITdoColumn Top(int n, params ITdoColumn[] tdoColumns)
{
ITdoColumn result = new TdoString();
result.IsConstant = false;
if (tdoColumns.Length > 0)
{
for (int i = 0; i < tdoColumns.Length; i++)
{
result.ColumnName += Utility.ColumnOrValueWithoutAlias(tdoColumns[i]) + ",";
}
result.ColumnName = String.Format("TOP {0} {1}", n, result.ColumnName.TrimEnd(','));
}
else
{
result.ColumnName = String.Format("TOP {0} *", n);
}
result.ReadOnly = true;
return result;
}
#endregion TOP/TOP Percent
}
}
|