using System;
using System.Collections.Generic;
using System.Dynamic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
namespace CQRS_Simple.Infrastructure.Dapper
{
///
/// Dynamic query class.
///
public sealed class DynamicQuery
{
///
/// Gets the insert query.
///
/// Name of the table.
/// The item.
///
/// The Sql query based on the item properties.
///
public static string GetInsertQuery(string tableName, object item)
{
PropertyInfo[] props = item.GetType().GetProperties(
BindingFlags.Public |
BindingFlags.Instance);
var columns = props.Where(x => x.Name != "Id").Select(p => p.Name).ToArray();
return string.Format("INSERT INTO {0} ({1}) OUTPUT Inserted.Id VALUES (@{2})",
tableName,
string.Join(",", columns),
string.Join(",@", columns));
}
///
/// Gets the update query.
///
/// Name of the table.
/// The item.
///
/// The Sql query based on the item properties.
///
public static string GetUpdateQuery(string tableName, dynamic item)
{
PropertyInfo[] props = item.GetType().GetProperties(BindingFlags.Public |
BindingFlags.Instance);
string[] columns = props.Where(x => x.Name != "Id").Select(p => p.Name).ToArray();
var parameters = columns.Select(name => name + "=@" + name).ToList();
return string.Format("UPDATE {0} SET {1} WHERE Id=@Id", tableName, string.Join(",", parameters));
}
///
/// Gets the dynamic query.
///
/// Name of the table.
/// The expression.
/// A result object with the generated sql and dynamic params.
public static QueryResult GetDynamicQuery(string tableName, Expression> expression)
{
var queryProperties = new List();
var body = (BinaryExpression)expression.Body;
IDictionary expando = new ExpandoObject();
var builder = new StringBuilder();
// walk the tree and build up a list of query parameter objects
// from the left and right branches of the expression tree
WalkTree(body, ExpressionType.Default, ref queryProperties);
// convert the query parms into a SQL string and dynamic property object
builder.Append("SELECT * FROM ");
builder.Append(tableName);
builder.Append(" WHERE ");
for (int i = 0; i < queryProperties.Count(); i++)
{
QueryParameter item = queryProperties[i];
if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
builder.Append(string.Format("{0} {1} {2} @{1} ", item.LinkingOperator, item.PropertyName,
item.QueryOperator));
}
else
{
builder.Append(string.Format("{0} {1} @{0} ", item.PropertyName, item.QueryOperator));
}
expando[item.PropertyName] = item.PropertyValue;
}
return new QueryResult(builder.ToString().TrimEnd(), expando);
}
///
/// Walks the tree.
///
/// The body.
/// Type of the linking.
/// The query properties.
private static void WalkTree(BinaryExpression body, ExpressionType linkingType,
ref List queryProperties)
{
if (body.NodeType != ExpressionType.AndAlso && body.NodeType != ExpressionType.OrElse)
{
string propertyName = GetPropertyName(body);
dynamic propertyValue = body.Right;
string opr = GetOperator(body.NodeType);
string link = GetOperator(linkingType);
queryProperties.Add(new QueryParameter(link, propertyName, propertyValue.Value, opr));
}
else
{
WalkTree((BinaryExpression)body.Left, body.NodeType, ref queryProperties);
WalkTree((BinaryExpression)body.Right, body.NodeType, ref queryProperties);
}
}
///
/// Gets the name of the property.
///
/// The body.
/// The property name for the property expression.
private static string GetPropertyName(BinaryExpression body)
{
string propertyName = body.Left.ToString().Split(new char[] { '.' })[1];
if (body.Left.NodeType == ExpressionType.Convert)
{
// hack to remove the trailing ) when convering.
propertyName = propertyName.Replace(")", string.Empty);
}
return propertyName;
}
///
/// Gets the operator.
///
/// The type.
///
/// The expression types SQL server equivalent operator.
///
///
private static string GetOperator(ExpressionType type)
{
switch (type)
{
case ExpressionType.Equal:
return "=";
case ExpressionType.NotEqual:
return "!=";
case ExpressionType.LessThan:
return "<";
case ExpressionType.GreaterThan:
return ">";
case ExpressionType.AndAlso:
case ExpressionType.And:
return "AND";
case ExpressionType.Or:
case ExpressionType.OrElse:
return "OR";
case ExpressionType.Default:
return string.Empty;
default:
throw new NotImplementedException();
}
}
}
///
/// Class that models the data structure in coverting the expression tree into SQL and Params.
///
internal class QueryParameter
{
public string LinkingOperator { get; set; }
public string PropertyName { get; set; }
public object PropertyValue { get; set; }
public string QueryOperator { get; set; }
///
/// Initializes a new instance of the class.
///
/// The linking operator.
/// Name of the property.
/// The property value.
/// The query operator.
internal QueryParameter(string linkingOperator, string propertyName, object propertyValue, string queryOperator)
{
this.LinkingOperator = linkingOperator;
this.PropertyName = propertyName;
this.PropertyValue = propertyValue;
this.QueryOperator = queryOperator;
}
}
}