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.TdoSqlExpressionDom{
/// <summary>
/// Class for the SQL clauses.
/// </summary>
[Serializable]
[CLSCompliant(true)]
public sealed class Clause
{
/// <summary>
/// Private constructor
/// </summary>
private Clause()
{
}
/// <summary>
/// Returns the Where clause
/// </summary>
/// <param name="clauses">
/// <code>TdoSqlExpression</code> clause. Use overloaded language operators to generate <code>TdoSqlExpression</code> clause.
/// <example><code><![CDATA[Clause.Where(tdo.tEmployees.Firstname=="Nancy" & !(tdo.tEmployees.Lastname==DBNull.Value)).]]></code></example>
/// </param>
/// <returns>TdoEntityType-SQL WHERE Clause</returns>
internal static string Where(string clauses)
{
if (String.IsNullOrEmpty(clauses))
return String.Empty;
return " WHERE " + clauses + " ";
}
/// <summary>
/// returns the Where clause
/// </summary>
/// <param name="clauses">
/// <code>TdoSqlExpression</code> clause. Use overloaded language operators to generate <code>TdoSqlExpression</code> clause.
/// Ex.: <code>Clause.Where(tdo.tEmployees.Firstname=="Nancy" & !(tdo.tEmployees.Lastname!=DBNull.Value)).</code>
/// </param>
/// <returns>TdoEntityType-SQL WHERE Clause</returns>
public static string Where(TdoSqlExpression clauses)
{
if (clauses == TdoSqlExpression.Empty)
return " ";
return Clause.Where(clauses.ToStringExpression());
}
/// <summary>
/// Order by clause for Where Clause (Default ASC Order)
/// </summary>
/// <param name="fields"> fields to order (Default is ASC Order)</param>
/// <returns>Returns the Order by clause string </returns>
public static string OrderBy(params ITdoColumn[] fields)
{
object[] pars = new object[fields.Length * 2];
int count = 0;
for (int i = 0; i < fields.Length; i++)
{
pars[count++] = fields[i];
pars[count++] = OrderByOperator.Asc;
}
return Clause.OrderBy(pars);
}
/// <summary>
/// Order by clause for Where Clause
/// </summary>
/// <param name="column1">Sort Key Column 1</param>
/// <param name="order1">OrderByOperator.Asc or OrderByOperator.Desc</param>
/// <returns>Returns the Order by clause string </returns>
public static string OrderBy(ITdoColumn column1, OrderByOperator order1)
{
return Clause.OrderBy(new object[] { column1, order1 });
}
/// <summary>
/// Order by clause for Where Clause
/// </summary>
/// <param name="column1">Sort Key Column 1</param>
/// <param name="order1">OrderByOperator.Asc or OrderByOperator.Desc</param>
/// <param name="column2">Sort Key Column 2</param>
/// <param name="order2">OrderByOperator.Asc or OrderByOperator.Desc</param>
/// <returns>Returns the Order by clause string </returns>
public static string OrderBy(ITdoColumn column1, OrderByOperator order1, ITdoColumn column2, OrderByOperator order2)
{
return Clause.OrderBy(new object[] { column1, order1, column2, order2 });
}
/// <summary>
/// Order by clause for Where Clause
/// </summary>
/// <param name="column1">Sort Key Column 1</param>
/// <param name="order1">OrderByOperator.Asc or OrderByOperator.Desc</param>
/// <param name="column2">Sort Key Column 2</param>
/// <param name="order2">OrderByOperator.Asc or OrderByOperator.Desc</param>
/// <param name="column3">Sort Key Column 3</param>
/// <param name="order3">OrderByOperator.Asc or OrderByOperator.Desc</param>
/// <returns>Returns the Order by clause string </returns>
public static string OrderBy(ITdoColumn column1, OrderByOperator order1, ITdoColumn column2, OrderByOperator order2, ITdoColumn column3, OrderByOperator order3)
{
return Clause.OrderBy(new object[] { column1, order1, column2, order2, column3, order3 });
}
/// <summary>
/// Order by clause for Where Clause
/// </summary>
/// <param name="fieldsAndSorts"> fields to order and sort clause .</param>
/// <returns>Returns the Order by clause string </returns>
/// <exception cref="System.ArgumentException">thrown when order by clause parameters number is incorrect</exception>
public static string OrderBy(params object[] fieldsAndSorts)
{
string str = " ORDER BY ";
if (fieldsAndSorts.Length % 2 != 0)
{
throw new ArgumentException("Error in the Order by clause:the number of parameters given is incorrect. Always give two parameters such as: field1,Clause.Asc obj Clause.Desc");
}
for (int i = 0; i < fieldsAndSorts.Length; i += 2)
{
if ((fieldsAndSorts[i] as ITdoColumn)!=null)
{
if (!String.IsNullOrEmpty(((ITdoColumn)(fieldsAndSorts[i])).TdoEntity.EntityName))
str += ((ITdoColumn)(fieldsAndSorts[i])).TdoEntity.EntityName + "." + ((ITdoColumn)(fieldsAndSorts[i])).ColumnName + " " + (fieldsAndSorts[i + 1].ToString()) + ",";
else
str += ((ITdoColumn)(fieldsAndSorts[i])).ColumnName + " " + (fieldsAndSorts[i + 1].ToString()) + ",";
}
else
{
str += fieldsAndSorts[i].ToString() + " " + (fieldsAndSorts[i + 1].ToString()) + ",";
}
if ((fieldsAndSorts[i + 1].ToString()).ToUpper().Trim() != "ASC" && (fieldsAndSorts[i + 1].ToString()).ToUpper().Trim() != "DESC")
{
throw new ArgumentException("Error in the Order by clause: the " + i.ToString() + " parameter must be 'ASC' obj 'DESC'.");
}
}
str = str.Substring(0, str.Length - 1); //Remove the last comma
str += " ";
return str;
}
/// <summary>
/// Group By clause for the Where statement
/// </summary>
/// <param name="firstField">The first field of the Group By clause (obligatory)</param>
/// <param name="otherFields">Other possible fields for the Group By clause</param>
/// <returns> the GroupBy clause string</returns>
public static string GroupBy(ITdoColumn firstField, params ITdoColumn[] otherFields)
{
string str = " GROUP BY " + firstField.TdoEntity.EntityName + "." + firstField.ColumnName + ",";
for (int i = 0; i < otherFields.Length; i++)
{
if (!String.IsNullOrEmpty(otherFields[i].TdoEntity.EntityName))
str += otherFields[i].TdoEntity.EntityName + "." + otherFields[i].ColumnName + ",";
else
str += otherFields[i].ColumnName + ",";
}
str = str.Substring(0, str.Length - 1); //Remove the last comma
return str; ;
}
}
}
|