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>
/// It's the Class to construct the TdoEntityType-SQL expressions
/// </summary>
[CLSCompliant(true)]
[Serializable()]
public sealed class TdoSqlExpression : IDisposable
{
#region Constants
/// <summary>
/// Represents the sole instance an empty TdoSqlExpression
/// </summary>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2104:DoNotDeclareReadOnlyMutableReferenceTypes")]
public static readonly TdoSqlExpression Empty = new TdoSqlExpression();
#endregion Constants
#region Fields
/// <summary>
/// left operand
/// </summary>
private object left;
/// <summary>
/// Operator
/// </summary>
private SqlOperator Operator;
/// <summary>
/// Right operand
/// </summary>
private object right;
/// <summary>
/// Contains the parameters list to construct the Where clause
/// </summary>
private TdoParameterCollection whereparametercollection;
#endregion Fields
#region Properties
/// <summary>
/// Contains the parameters list to construct the Where clause
/// </summary>
private TdoParameterCollection WhereParameterCollection
{
get
{
return this.whereparametercollection;
}
set
{
this.whereparametercollection = value;
}
}
#endregion Properties
#region Constructors
/// <summary>
/// Default Constructor.Initializes the TdoSqlExpression to Empty expression.
/// </summary>
private TdoSqlExpression()
{
this.Operator = SqlOperator.Is;
}
/// <summary>
/// Constructor of TdoSqlExpression class.
/// </summary>
/// <param name="left">TdoSqlExpression on the left of the operator </param>
/// <param name="Op">Operator</param>
/// <param name="right">TdoSqlExpression on the right of the operator</param>
internal TdoSqlExpression(TdoSqlExpression left, SqlOperator Op, TdoSqlExpression right)
{
this.left = left;
this.Operator = Op;
this.right = right;
this.WhereParameterCollection = ((TdoSqlExpression)right).whereparametercollection;
}
/// <summary>
/// Constructor of TdoSqlExpression class.
/// </summary>
/// <param name="left">string on the left of the operator</param>
/// <param name="Op">Operator</param>
/// <param name="right">string on the right of the operator</param>
/// <param name="tdoParameterCollection">Parameters collection to construct parametric queries</param>
private TdoSqlExpression(string left, SqlOperator Op, object right, TdoParameterCollection tdoParameterCollection)
{
this.left = left;
this.Operator = Op;
this.right = right;
this.WhereParameterCollection = tdoParameterCollection;
}
/// <summary>
/// Constructor of TdoSqlExpression class.
/// </summary>
/// <param name="left">TdoString on the left of the operator</param>
/// <param name="Op">Operator</param>
/// <param name="right">string on the right of the operator</param>
internal TdoSqlExpression(ITdoColumn left, SqlOperator Op, object right)
{
this.left = left as ITdoColumn;
this.Operator = Op;
this.right = right;
this.WhereParameterCollection = ((TdoEntityBase)((ITdoColumn)left).TdoEntity).pTdoParameterCollection;
}
#endregion Constructors
#region Operators
/// <summary>
/// Operator OR
/// </summary>
/// <param name="left">TdoSqlExpression on the left of the operator</param>
/// <param name="right">TdoSqlExpression on the right of the operator</param>
/// <returns>Returns an expression as TdoSqlExpression useful to convert the Where Clause in string type</returns>
public static TdoSqlExpression operator |(TdoSqlExpression left, TdoSqlExpression right)
{
if (left == TdoSqlExpression.Empty)
return right;
if (right == TdoSqlExpression.Empty)
return left;
return new TdoSqlExpression((TdoSqlExpression)left, SqlOperator.Or, (TdoSqlExpression)right);
}
/// <summary>
/// Operator OR
/// </summary>
/// <param name="left">TdoSqlExpression on the lef of the operator</param>
/// <param name="right">TdoSqlExpression on the right of the operator</param>
/// <returns>Returns an expression as TdoSqlExpression useful to convert the Where Clause in string type</returns>
public static TdoSqlExpression operator &(TdoSqlExpression left, TdoSqlExpression right)
{
if (left == TdoSqlExpression.Empty)
return right;
if (right == TdoSqlExpression.Empty)
return left;
return new TdoSqlExpression((TdoSqlExpression)left, SqlOperator.And, (TdoSqlExpression)right);
}
/// <summary>
/// Operator NOT
/// </summary>
/// <param name="expr">TdoSqlExpression to which apply the operator </param>
/// <returns>TdoSqlExpression denied NOT (expr)</returns>
public static TdoSqlExpression operator !(TdoSqlExpression expr)
{
if (expr == TdoSqlExpression.Empty)
return expr;
return new TdoSqlExpression((TdoSqlExpression)TdoSqlExpression.Empty, SqlOperator.Not, (TdoSqlExpression)expr);
}
/// <summary>
/// Converts TdoSqlExpression expression into a TdoEntityType-Sql string expression.
/// </summary>
/// <param name="expr">TdoSqlExpression to convert</param>
/// <returns>TdoEntityType-Sql string expression</returns>
public static implicit operator string(TdoSqlExpression expr)
{
return expr.ToStringExpression();
}
#endregion Operators
#region Methods
private string GetFullColumnName(ITdoColumn column)
{
return column.TdoEntity.EntityName + "." + column.ColumnName;
}
/// <summary>
/// Converts TdoSqlExpression expression into a TdoEntityType-Sql string expression.
/// </summary>
/// <returns>Returns a string that contains the TdoEntityType-SQL expression</returns>
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1500:VariableNamesShouldNotMatchFieldNames"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1305:SpecifyIFormatProvider"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity")]
internal string ToStringExpression()
{
string left = "";
string op = "";
string right = "";
TdoSqlExpression expr = this;
//Left Expression
if (expr == TdoSqlExpression.Empty || this.left == null && this.right == null) // TdoSqlExpression.Empty
{
return "";
}
if (expr.left as TdoSqlExpression != null)
{
left = "(" + (TdoSqlExpression)(expr.left);
}
else if (expr.left as String != null)
{
left = (string)(expr.left);
}
else if (expr.left as ITdoColumn != null)
{
left = this.GetFullColumnName(((ITdoColumn)expr.left));
}
else
{
throw new InvalidOperationException(String.Format("An error has occurred during TdoSqlExpression parsing.\r\nLeft expression must be a {0} or {1} or {2}.", typeof(ITdoColumn).FullName, typeof(String).FullName, typeof(TdoSqlExpression).FullName));
}
//Operator Expression
op = (string)(new SqlOperatorHelper(expr.Operator));
if (expr.Operator == SqlOperator.Not) op += " (";
if (expr.right as DBNull != null)
{
if (expr.Operator == SqlOperator.Equal)
{
op = new SqlOperatorHelper(SqlOperator.Is);
}
else if (expr.Operator == SqlOperator.NotEqual)
{
op = new SqlOperatorHelper(SqlOperator.IsNot);
}
else
{
throw new InvalidExpressionException("Operator " + expr.Operator.ToString() + " cannot be applied with NULL values.");
}
}
//Right Expression
SqlParameter p;
string sright = expr.right.GetType().Name;
switch (sright)
{
case ("TdoSqlExpression"): right = (TdoSqlExpression)(expr.right) + ")"; break;
case ("Byte[]"):
right = expr.WhereParameterCollection.NextParamName();
p = new SqlParameter(right, (Byte[])(expr.right));
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
break;
case ("Guid"):
right = expr.WhereParameterCollection.NextParamName();
p = new SqlParameter(right, (Guid)(expr.right));
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
break;
case ("Char"):
case ("String"):
right = expr.WhereParameterCollection.NextParamName();
p = new SqlParameter(right, (string)(expr.right));
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
break;
case ("Boolean"):
right = expr.WhereParameterCollection.NextParamName();
p = new SqlParameter(right, (((bool)(expr.right)) ? "1" : "0"));
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
break;
case ("DateTime"):
case ("TimeStamp"):
// international format 'yyyy/mm/dd HH:SS:NN'
DateTime dt = ((DateTime)(expr.right));
right = expr.WhereParameterCollection.NextParamName();
p = new SqlParameter(right, dt);
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
break;
case ("Byte"):
case ("SByte"):
case ("Double"):
case ("Decimal"):
case ("Single"):
case ("UInt16"):
case ("UInt32"):
case ("UInt64"):
case ("Int16"):
case ("Int32"):
case ("Int64"):
right = expr.WhereParameterCollection.NextParamName();
p = new SqlParameter(right, expr.right);
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
break;
case ("DBNull"):
right = "NULL";
break;
case ("TdoWheresetOfValues"):
TdoWheresetOfValues values = ((TdoWheresetOfValues)(expr.right));
string tempris = "(";
for (int z = 0; z < values.setOfValues.Length; z++)
{
string paramname = expr.WhereParameterCollection.NextParamName();
tempris += paramname + ",";
SqlParameter sqlParameter = new SqlParameter(paramname, values.setOfValues[z]);
switch (values.setOfValues[z].GetType().Name)
{
case ("Byte"): sqlParameter.SqlDbType = SqlDbType.TinyInt; break;
case ("Int16"): sqlParameter.SqlDbType = SqlDbType.SmallInt; break;
case ("Int32"): sqlParameter.SqlDbType = SqlDbType.Int; break;
case ("Int64"): sqlParameter.SqlDbType = SqlDbType.BigInt; break;
}
expr.WhereParameterCollection.ParameterCollection.Add(sqlParameter);
}
tempris = tempris.Substring(0, tempris.Length - 1); //Remove the last comma;
tempris += ")";
right += tempris;
break;
case ("TdoWhereRange"):
switch (((TdoWhereRange)(expr.right)).From.GetType().Name)
{
case ("Guid"):
case ("Char"):
case ("DateTime"):
case ("TimeStamp"):
case ("String"):
string right1 = expr.WhereParameterCollection.NextParamName();
string right2 = expr.WhereParameterCollection.NextParamName();
right = right1 + " AND " + right2;
p = new SqlParameter(right1, ((TdoWhereRange)(expr.right)).From);
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
p = new SqlParameter(right2, ((TdoWhereRange)(expr.right)).To);
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
break;
case ("Byte"):
case ("SByte"):
case ("Double"):
case ("Decimal"):
case ("Single"):
case ("UInt16"):
case ("UInt32"):
case ("UInt64"):
case ("Int16"):
case ("Int32"):
case ("Int64"):
right1 = expr.WhereParameterCollection.NextParamName();
right2 = expr.WhereParameterCollection.NextParamName();
right = right1 + " AND " + right2;
p = new SqlParameter(right1, ((TdoWhereRange)(expr.right)).From);
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
p = new SqlParameter(right2, ((TdoWhereRange)(expr.right)).To);
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
break;
}
break;
default:
ITdoColumn rightcolumn = expr.right as ITdoColumn;
if (rightcolumn == null)
right = expr.right.ToString();
else
{
if (rightcolumn.IsConstant)
{
if (!rightcolumn.IsNull)
{
right = expr.WhereParameterCollection.NextParamName();
p = new SqlParameter(right, rightcolumn.ObjectValue);
p.SourceColumn = left;
expr.WhereParameterCollection.ParameterCollection.Add(p);
}
else
{
if (expr.Operator == SqlOperator.Equal)
{
op = new SqlOperatorHelper(SqlOperator.Is);
}
else if (expr.Operator == SqlOperator.NotEqual)
{
op = new SqlOperatorHelper(SqlOperator.IsNot);
}
else
{
throw new InvalidExpressionException("Operator " + expr.Operator.ToString() + " cannot be applied with NULL values.");
}
right = "NULL";
}
}
else
{
right = this.GetFullColumnName(rightcolumn);
}
}
break;
}
string ris = String.Format("({0} {1} {2})", left, op, right);
return ris;
}
/// <summary>
/// Convert expressions in TdoEntityType-SQL
/// </summary>
/// <returns>Returns a string that contains the TdoEntityType-SQL expression</returns>
public override string ToString()
{
return this.ToStringExpression();
}
#endregion Methods
#region IDisposable Members
/// <summary>
/// Release TdoSqlExpression Resources
/// </summary>
public void Dispose()
{
this.whereparametercollection.Reset();
}
#endregion
}
}
|