public async Task TestReader_One_Param()
{
Insert(_tableName, (1, "hello"), (2, "world"));
var sql = Host.Services.GetService <SqlQueryWorker>();
foreach (var database in Host.Services.GetServices <BaseDatabase>())
{
var input = new SqlQueryInput();
input.ConnectionName = database.Name;
input.Query = $"SELECT * FROM {_tableName} WHERE col1 = @p1";
input.Type = SqlQueryType.Reader;
var para = new SqlQueryParameter
{
Name = "p1",
Value = JsonDocument.Parse("2").RootElement
};
input.Params = new [] { para };
var output = await sql.DoJsonWork <SqlQueryInput, SqlQueryOutput>(input);
Assert.Single(output.Result);
Assert.Equal("world", ((JsonElement)output.Result[0]["col2"]).GetString());
Assert.Equal(2, ((JsonElement)output.Result[0]["col1"]).GetInt32());
}
}
public void UpdateParameter_Test()
{
// Arrange
int queryParametersCountBeforeAct = queryParametersDbSet.Count();
int queryParameterIdBeforeAct = queryParametersDbSet.First().SqlQueryParameterId;
int queryParameterParamIdBeforeAct = queryParametersDbSet.First().ParameterId;
int queryParameterQueryIdBeforeAct = queryParametersDbSet.First().SqlQueryId;
SqlQueryParameter queryParamToUpdate = queryParametersDbSet.First();
int newQueryParameterParamId = 999;
int newQueryParameterQueryId = 999;
queryParamToUpdate.ParameterId = newQueryParameterParamId;
queryParamToUpdate.SqlQueryId = newQueryParameterQueryId;
// Act
queryParamRepo.Update(queryParamToUpdate);
SqlQueryParameter updatedParam = queryParametersDbSet.First();
// Assert
Assert.AreEqual(newQueryParameterParamId, updatedParam.ParameterId);
Assert.AreEqual(newQueryParameterQueryId, updatedParam.SqlQueryId);
Assert.AreEqual(queryParameterIdBeforeAct, updatedParam.SqlQueryParameterId);
Assert.AreEqual(queryParametersCountBeforeAct, queryParametersDbSet.Count());
Assert.AreNotEqual(queryParameterParamIdBeforeAct, updatedParam.ParameterId);
Assert.AreNotEqual(queryParameterQueryIdBeforeAct, updatedParam.SqlQueryId);
}
public void CreateQueryParameter_Test()
{
// Arrange
int queryParametersCountBeforeAct = queryParametersDbSet.Count();
int lastQueryParameterIdInDbSetBeforeAct = queryParametersDbSet
.Last()
.SqlQueryParameterId;
int queryParameterId = 12;
int parameterId = 12;
int queryId = 12;
SqlQueryParameter queryParameterToCreate = new SqlQueryParameter()
{
SqlQueryParameterId = queryParameterId,
ParameterId = parameterId,
SqlQueryId = queryId
};
// Act
queryParamRepo.Create(queryParameterToCreate);
SqlQueryParameter createdQueryParam = queryParametersDbSet.Last();
// Assert
Assert.IsTrue(queryParametersDbSet.Count() > queryParametersCountBeforeAct);
Assert.AreNotEqual(
lastQueryParameterIdInDbSetBeforeAct,
createdQueryParam.SqlQueryParameterId
);
Assert.AreEqual(queryParameterId, createdQueryParam.SqlQueryParameterId);
Assert.AreEqual(parameterId, createdQueryParam.ParameterId);
Assert.AreEqual(queryId, createdQueryParam.SqlQueryId);
}
/// <summary>
/// Extracts a date value in DateTime (yyyy, MM, dd) format to a T-SQL query parameter.
/// </summary>
/// <param name="leftOrRightPart">The left or right side of a boolean expression.</param>
/// <param name="sqlQueryParameter">The corresponding T-SQL query parameter that contains the extracted date value.</param>
private void ExtractHardCodedDateFromLocalVariable
(
Expression leftOrRightPart,
out SqlQueryParameter sqlQueryParameter
)
{
// The expectation is that the date is set with a value using "= new DateTime (yyyy, MM, dd)" syntax.
// Hence, the statement is expected to be a "new ()" expression.
var newExpression = leftOrRightPart as NewExpression;
sqlQueryParameter = null;
// Is it a "new ()" expression?
if (newExpression != null)
{
// Get the year, month and date parts from the constructor arguments.
var yearMonthAndDateParts
= newExpression
.Arguments
.Select(v => Int32.Parse(v.ToString()))
.ToList();
// Form the date from the yyyy, MM, and dd parts.
var dateValue = new DateTime(yearMonthAndDateParts [0], yearMonthAndDateParts [1], yearMonthAndDateParts [2]);
var dateText = dateValue.ToString(ORACLE_DATE_FORMAT);
// Prepare the T-SQL parameter.
sqlQueryParameter = new SqlQueryParameter(SQL_PARAM_DATE_VALUE_PREFIX + DateTime.Now.ToString(DATE_TIME_STAMP_FORMAT), dateText, SqlDbType.DateTime);
}
}
public void SqlQueryParameterExtensions_EmptyArray_ReturnEmptyArray()
{
SqlQueryParameter[] array = new SqlQueryParameter[0];
SqlParameter[] result = array.ToSqlParameters();
Assert.IsNotNull(result);
Assert.AreEqual(0, result.Length);
}
public CompanyDataObject GetData(string companyTypeCode, Func <SqlDataReader, CompanyDataObject> companyDataMapper)
{
SqlQueryParameter sqlQueryParameter = new SqlQueryParameter {
ParameterName = "CompanyTypeCode", ParameterDirection = DbParameterDirection.Input, ParamentType = CodeParameterType.String, ParameterSize = 100, ParameterValue = companyTypeCode
};
return(companyDataMapper(CommandTypeManager.ExecuteReader("CompanyDataFetch", sqlQueryParameter)));
}
/// <summary>
/// Adds the query parameter to the list.
/// </summary>
/// <param name="parameter">The T-SQL query parameter to be added.</param>
private void AddToSqlQueryParameters(SqlQueryParameter parameter)
{
// Is it already added?
if (this.sqlParameters.FirstOrDefault(p => p.Name == parameter.Name) == null)
{
// Not yet.
this.sqlParameters.Add(parameter);
}
}
private void BindQueryAndParams(int queryId, string[] parameters)
{
foreach (string param in parameters)
{
SqlQueryParameter item = new SqlQueryParameter();
item.SqlQueryId = queryId;
item.ParameterId = int.Parse(param);
db.SqlQueriesParameters.Add(item);
db.SaveChanges();
}
}
public static qGis_Object[] GetObjects(qGis_Rectangle boundary)
{
SqlQueryParameter sql_left = new SqlQueryParameter("@Left", boundary.NorthWest.Longitude);
SqlQueryParameter sql_right = new SqlQueryParameter("@Right", boundary.NorthEast.Longitude);
SqlQueryParameter sql_top = new SqlQueryParameter("@Top", boundary.NorthEast.Latitude);
SqlQueryParameter sql_bottom = new SqlQueryParameter("@Bottom", boundary.SouthWest.Latitude);
DbRow[] objects = DbRow.Select(schema2, "SELECT * FROM qGIS_Objects WHERE (Longitude BETWEEN @Left AND @Right) AND (Latitude BETWEEN @Bottom AND @Top)", new SqlQueryParameter [] { sql_left, sql_right, sql_top, sql_bottom });
return(DbRow.CreateArray <qGis_Object>(objects, c => new qGis_Object(c)));
}
public void SqlQueryParameterExtensions_ArrayWithMultipleRequiredPlaceholder_ThrowFirstError()
{
SqlQueryParameter[] array = new SqlQueryParameter[]
{
new SqlQueryParameter("ColumnA", Placeholders.IsRequired()),
new SqlQueryParameter("ColumnB", Placeholders.IsRequired())
};
RequiredPlaceholderIsNullException exception = Assert.Throws <RequiredPlaceholderIsNullException>(() => array.ToSqlParameters());
Assert.AreEqual("ColumnA", exception.ColumnName);
Assert.AreEqual("The value for ColumnA is required but has not been set", exception.Message);
}
public void SqlQueryParameterExtensions_ArrayWithNull_ReturnDBNull()
{
SqlQueryParameter[] array = new SqlQueryParameter[]
{
new SqlQueryParameter("ColumnA", null)
};
SqlParameter[] result = array.ToSqlParameters();
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Length);
Assert.AreEqual("@ColumnA", result[0].ParameterName);
Assert.AreEqual(DBNull.Value, result[0].Value);
}
public static qGis_Object[] GetObjects(qGis_Rectangle boundary, int search_id)
{
SqlQueryParameter sql_left = new SqlQueryParameter("@Left", boundary.NorthWest.Longitude);
SqlQueryParameter sql_right = new SqlQueryParameter("@Right", boundary.NorthEast.Longitude);
SqlQueryParameter sql_top = new SqlQueryParameter("@Top", boundary.NorthEast.Latitude);
SqlQueryParameter sql_bottom = new SqlQueryParameter("@Bottom", boundary.SouthWest.Latitude);
qDbs_Search search = new qDbs_Search(search_id);
qDbs_DataGroupConfig data_group_config = new qDbs_DataGroupConfig(search.DataGroupID);
DbRow[] results = search.GetResults(schema2, string.Format("INNER JOIN qGIS_Objects ON qGIS_Objects.ReferenceID = {0}", data_group_config.KeyColumn), string.Format("qGIS_Objects.DataGroupID = {0} AND (Longitude BETWEEN @Left AND @Right) AND (Latitude BETWEEN @Bottom AND @Top)", search.DataGroupID), sql_left, sql_right, sql_top, sql_bottom);
return(DbRow.CreateArray <qGis_Object>(results, c => new qGis_Object(c)));
}
public void SqlQueryParameterExtensions_ArrayWithResolver_CallResolverAndReturnResult()
{
Func <int> resolveAction = () => 123;
SqlQueryParameter[] array = new SqlQueryParameter[]
{
new SqlQueryParameter("ColumnA", new Resolver <int>(resolveAction))
};
SqlParameter[] result = array.ToSqlParameters();
Assert.IsNotNull(result);
Assert.AreEqual(1, result.Length);
Assert.AreEqual("@ColumnA", result[0].ParameterName);
Assert.AreEqual(123, result[0].Value);
}
/// <summary>
/// Prepares the expression text corresponding to the specified side of the
/// boolean expression.
/// </summary>
/// <param name="oppositeSidePropertyType">The property type of the opposite side of the boolean expression.</param>
/// <param name="builder">The string builder/</param>
/// <param name="theOtherSideText">The requested side text of the expression.</param>
/// <param name="theOtherSideSqlQueryParameter">A T-SQL query parameter prepared for the side in question.</param>
private void PrepareTextForLeftOrRightSide
(
Type oppositeSidePropertyType,
StringBuilder builder,
string theOtherSideText,
SqlQueryParameter theOtherSideSqlQueryParameter
)
{
// Get the opposite side's property's type.
if (oppositeSidePropertyType != null)
{
// Is it a number?
if (oppositeSidePropertyType.IsNumber())
{
// Yes.
builder.Append(theOtherSideText);
}
// Is it text?
else if (oppositeSidePropertyType.IsText())
{
// Yes. Prepare a T-SQL parameter, and add it to the query text.
var sqlParameter = new SqlQueryParameter(theOtherSideText.Replace("\"", String.Empty), SqlDbType.NVarChar);
this.AddToSqlQueryParameters(sqlParameter);
builder.Append(sqlParameter.Name);
}
// Is it a date-time?
else if (oppositeSidePropertyType == typeof(DateTime))
{
// Is a T-SQL parameter already created for the other side?
if (theOtherSideSqlQueryParameter != null)
{
// Yes.
builder.Append("convert (datetime, '");
builder.Append(theOtherSideSqlQueryParameter.Value);
builder.Append("', 106)");
}
else
{
// No. Just use the specified text for the date's value.
builder.Append("convert (datetime, '");
builder.Append(theOtherSideText.Replace("\"", String.Empty));
builder.Append("', 106)");
}
}
}
}
public void GetQueryParameterById_Test()
{
// Arrange
int queryParameterId = queryParametersDbSet.First().SqlQueryParameterId;
int queryParametersCountBeforeAct = queryParametersDbSet.Count();
// Act
SqlQueryParameter queryParam = queryParamRepo.Get(queryParameterId);
// Assert
Assert.AreEqual(
queryParameterId,
queryParam.SqlQueryParameterId
);
Assert.AreEqual(
queryParametersCountBeforeAct,
queryParametersDbSet.Count()
);
Assert.AreEqual(
queryParametersDbSet.First().SqlQueryParameterId,
queryParam.SqlQueryParameterId
);
}
/// <summary>
/// Gets the local variable value, and type from the specified member expression.
/// </summary>
/// <param name="localVarExpression">The member expression with the local variable.</param>
/// <param name="localVariableName">The name of the local variable.</param>
/// <param name="localVariableValue">The value of the local variable.</param>
/// <param name="localVariableType">The type of the local variable.</param>
/// <param name="sqlQueryParameter">The T-SQL query parameter corresponding to the local variable found.</param>
private bool AreLocalVariableDetailsExtractable
(
Expression localVarExpression,
out string localVariableName,
out object localVariableValue,
out Type localVariableType,
out SqlQueryParameter sqlQueryParameter
)
{
try
{
// Get the member expression.
var memberExpression = localVarExpression as MemberExpression;
// Is it a valid member expression?
if (memberExpression == null)
{
// No.
localVariableName = String.Empty;
localVariableValue = null;
localVariableType = null;
sqlQueryParameter = null;
return(false);
}
// Get the local variable value, and its details.
this.ExtractLocalVariableFromExpression
(
memberExpression,
out localVariableName,
out localVariableValue,
out localVariableType
);
// Form the SQL parameter based on data types.
if (localVariableType == typeof(char))
{
// Char.
sqlQueryParameter = new SqlQueryParameter(PARAM_PREFIX + localVariableName, localVariableValue.ToString(), SqlDbType.NChar);
}
else if (localVariableType == typeof(string))
{
// String.
sqlQueryParameter = new SqlQueryParameter(PARAM_PREFIX + localVariableName, localVariableValue.ToString(), SqlDbType.NVarChar);
}
else if (localVariableType.IsNumber())
{
// Numeric - short, int, long, etc.
sqlQueryParameter = new SqlQueryParameter(PARAM_PREFIX + localVariableName, localVariableValue.ToString(), SqlDbType.BigInt);
}
else if (localVariableType == typeof(DateTime))
{
// DateTime.
var dateValue = (DateTime)localVariableValue;
sqlQueryParameter = new SqlQueryParameter(PARAM_PREFIX + localVariableName, dateValue.ToString(ORACLE_DATE_FORMAT), SqlDbType.DateTime);
}
else
{
// Unknown, or un-handleable type. Cannot continue.
throw (new NotImplementedException("Processing of SQL parameter for type \"" + localVariableType.Name + "\" is not yet implemented."));
}
return(true);
}
catch
{
// The provided expression IS NOT a member expression.
// Hence, the local variable cannot be extracted.
localVariableName = String.Empty;
localVariableValue = null;
localVariableType = null;
sqlQueryParameter = null;
return(false);
}
}
public void CreateQueryWithTwoParams_WhenSecondParameterIsNotNull()
{
// Arrange
int queryElementsCountBeforeAct = queriesDbSet.Count();
int queryParametersElementsCountBeforeAct = queryParametersDbSet.Count();
int parametersElementsCountBeforeAct = parametersDbSet.Count();
string createQueryName = "New query";
string createQueryContent = "string type";
int firstQueryParameterId = parametersElementsCountBeforeAct + 1;
string firstQueryParameterName = "CreatedQueryFirstParameterName";
string firstQueryParameterRuName = "первый параметр запроса";
string firstQueryParameterType = "text";
int secondQueryParameterId = firstQueryParameterId + 1;
string secondQueryParameterName = "CreateQuerySecondParameterName";
string secondQueryParameterRuName = "второй параметр запроса";
string secondQueryParameterType = "phone";
SqlQuery queryToCreate = new SqlQuery()
{
SqlQueryId = queryElementsCountBeforeAct + 1,
SqlQueryName = createQueryName,
SqlQueryContent = createQueryContent,
SqlQueryCreatedDate = DateTime.Now
};
Parameter firstParameter = new Parameter()
{
ParameterId = firstQueryParameterId,
ParameterName = firstQueryParameterName,
ParameterRuName = firstQueryParameterRuName,
ParameterType = firstQueryParameterType
};
parametersDbSet.Add(firstParameter);
Parameter secondParameter = new Parameter()
{
ParameterId = secondQueryParameterId,
ParameterName = secondQueryParameterName,
ParameterRuName = secondQueryParameterRuName,
ParameterType = secondQueryParameterType
};
parametersDbSet.Add(secondParameter);
string[] parameterIds = new string[]
{
firstParameter.ParameterId.ToString(),
secondParameter.ParameterId.ToString()
};
// Act
queryRepo.Create(queryToCreate, parameterIds);
SqlQuery createdQuery = queriesDbSet.Last();
int lastQueryParameterId = queryParametersDbSet.Last().SqlQueryParameterId;
SqlQueryParameter[] twoLastQueryParameters = new SqlQueryParameter[]
{
queryParametersDbSet.Last(),
queryParametersDbSet.ToList()[queryParametersDbSet.Count() - 2]
};
// Assert
Assert.IsTrue(queriesDbSet.Count() > queryElementsCountBeforeAct);
Assert.IsTrue(parametersDbSet.Count() > parametersElementsCountBeforeAct);
Assert.IsTrue(queryParametersDbSet.Count() > queryParametersElementsCountBeforeAct);
foreach (SqlQueryParameter queryParameter in twoLastQueryParameters)
{
Assert.IsTrue(queryParameter.SqlQueryId == createdQuery.SqlQueryId);
Assert.IsTrue(
queryParameter.ParameterId == firstQueryParameterId ||
queryParameter.ParameterId == secondQueryParameterId
);
}
}
public static qGis_Object[] GetObjects(qGis_Rectangle boundary)
{
SqlQueryParameter sql_left = new SqlQueryParameter("@Left", boundary.NorthWest.Longitude);
SqlQueryParameter sql_right = new SqlQueryParameter("@Right", boundary.NorthEast.Longitude);
SqlQueryParameter sql_top = new SqlQueryParameter("@Top", boundary.NorthEast.Latitude);
SqlQueryParameter sql_bottom = new SqlQueryParameter("@Bottom", boundary.SouthWest.Latitude);
DbRow[] objects = DbRow.Select(schema2, "SELECT * FROM qGIS_Objects WHERE (Longitude BETWEEN @Left AND @Right) AND (Latitude BETWEEN @Bottom AND @Top)", new SqlQueryParameter [] { sql_left, sql_right, sql_top, sql_bottom });
return DbRow.CreateArray<qGis_Object>(objects, c => new qGis_Object(c));
}
public static qGis_Object[] GetObjects(qGis_Rectangle boundary, int search_id)
{
SqlQueryParameter sql_left = new SqlQueryParameter("@Left", boundary.NorthWest.Longitude);
SqlQueryParameter sql_right = new SqlQueryParameter("@Right", boundary.NorthEast.Longitude);
SqlQueryParameter sql_top = new SqlQueryParameter("@Top", boundary.NorthEast.Latitude);
SqlQueryParameter sql_bottom = new SqlQueryParameter("@Bottom", boundary.SouthWest.Latitude);
qDbs_Search search = new qDbs_Search(search_id);
qDbs_DataGroupConfig data_group_config = new qDbs_DataGroupConfig(search.DataGroupID);
DbRow[] results = search.GetResults(schema2, string.Format("INNER JOIN qGIS_Objects ON qGIS_Objects.ReferenceID = {0}", data_group_config.KeyColumn), string.Format("qGIS_Objects.DataGroupID = {0} AND (Longitude BETWEEN @Left AND @Right) AND (Latitude BETWEEN @Bottom AND @Top)", search.DataGroupID), sql_left, sql_right, sql_top, sql_bottom);
return DbRow.CreateArray<qGis_Object>(results, c => new qGis_Object(c));
}
/// <summary>
/// Handles the ".In ()" and ".NotIn ()" calls within the WHERE condition
/// boolean expressions.
/// </summary>
/// <param name="expression">The boolean expression.</param>
/// <returns></returns>
private string HandleInCall(Expression expression)
{
Type memberPropertyType;
PropertyInfo memberPropertyInfo;
string [] elements;
string memberPropertyText;
// Extract the following:
// (1) The text of the property that calls the ".In ()" method.
// (2) The type of the property that calls the ".In ()" method.
// (3) The array of values in the ".In ()" part.
this.ExtractInArrayContents
(
expression,
out memberPropertyInfo,
out memberPropertyType,
out memberPropertyText,
out elements
);
var inOrNotInText
= (expression.ToString().IndexOf(".In(", StringComparison.Ordinal) != -1)
? " in ("
: " not in (";
// Are the elements numbers?
if (memberPropertyType.IsNumber())
{
return(memberPropertyText + inOrNotInText + String.Join(", ", elements) + ")");
}
// Are the elements text?
if (memberPropertyType.IsText())
{
var elementsReplacedBySqlParameters = new List <string> ();
elements
.ToList()
.ForEach
(
e =>
{
var sqlParameter = new SqlQueryParameter(e.Replace("\"", String.Empty), SqlDbType.NVarChar);
this.AddToSqlQueryParameters(sqlParameter);
elementsReplacedBySqlParameters.Add(sqlParameter.Name);
}
);
return
(
memberPropertyText
+ inOrNotInText
+ String.Join(", ", elementsReplacedBySqlParameters.Select(s => s).ToList())
+ ")"
);
}
// Are the elements dates?
if (memberPropertyType == typeof(DateTime))
{
return(memberPropertyText + inOrNotInText + String.Join(", ", elements.Select(s => "convert (datetime, '" + s.Replace("\"", String.Empty) + "', 106)").ToList()) + ")");
}
// The type is not ready yet to be processed.
throw (new NotImplementedException("Handling of \".In ()\" call with type \"" + memberPropertyType.Name + "\" is not yet implemented."));
}
/// <summary>
/// Gets the text of the specified binary expression.
/// </summary>
/// <param name="expression">The expression.</param>
/// <returns></returns>
private string GetTextOf(Expression expression)
{
var builder = new StringBuilder();
var expressionText
= expression
.ToString()
.Replace("(", String.Empty)
.Replace(")", String.Empty);
// Is it a " is " statement?
if (expression.NodeType == ExpressionType.TypeIs)
{
return(this.HandleIsNullCondition(expressionText));
}
// Is it a " like " operator based call?
if
(
expression.NodeType == ExpressionType.Call
&&
(
expressionText.IndexOf(".StartsWith", StringComparison.Ordinal) != -1 ||
expressionText.IndexOf(".EndsWith", StringComparison.Ordinal) != -1 ||
expressionText.IndexOf(".Like", StringComparison.Ordinal) != -1 ||
expressionText.IndexOf(".NotLike", StringComparison.Ordinal) != -1
)
)
{
return(this.HandleStartsWithEndsWithLikeOrNotLikeCalls(expression));
}
// Is it an " in " call?
if (expression.NodeType == ExpressionType.Call)
{
return(this.HandleInCall(expression));
}
SqlQueryParameter leftSqlQueryParameter = null, rightSqlQueryParameter = null;
Type leftPropertyType, rightPropertyType;
PropertyInfo leftPropertyInfo, rightPropertyInfo;
// Get a binary expression.
var binaryExpression = (BinaryExpression)expression;
// Get the left part.
var leftPart = binaryExpression.Left;
var rightPart = binaryExpression.Right;
// Get the individual left and right part types.
var leftType = leftPart.NodeType;
var rightType = rightPart.NodeType;
string leftPropertyText, rightPropertyText;
// Is there a property expression on the left side?
if (this.IsPropertyPartExtractbleFromExpression(leftPart, out leftPropertyInfo, out leftPropertyText, out leftPropertyType) == false)
{
// The left part of the expression does not contain a property name.
string leftLocalVariableName;
object leftLocalVariableValue;
Type leftLocalVariableType;
if (this.AreLocalVariableDetailsExtractable(leftPart, out leftLocalVariableName, out leftLocalVariableValue, out leftLocalVariableType, out leftSqlQueryParameter))
{
// Left side is a variable.
}
}
// Is there a property expression on the right side?
if (this.IsPropertyPartExtractbleFromExpression(rightPart, out rightPropertyInfo, out rightPropertyText, out rightPropertyType) == false)
{
// The left part of the expression does not contain a property name.
string rightLocalVariableName;
object rightLocalVariableValue;
Type rightLocalVariableType;
if (this.AreLocalVariableDetailsExtractable(rightPart, out rightLocalVariableName, out rightLocalVariableValue, out rightLocalVariableType, out rightSqlQueryParameter))
{
// Right side is a variable.
}
}
// Get the logical operator.
var op = binaryExpression.NodeType;
var temporaryBuilder = new StringBuilder();
var variableUsedIsNullAndConditionToBeIgnored = false;
// Process the left side of the boolean expression.
switch (leftType)
{
// Property access, or variable name usage. Example: "c.Grade", local variable "searchName", etc.
case ExpressionType.MemberAccess:
// Is it a local variable usage?
if (leftSqlQueryParameter != null)
{
// Yes. Use the corresponding SQL query parameter.
temporaryBuilder.Append(leftSqlQueryParameter.Name);
this.AddToSqlQueryParameters(leftSqlQueryParameter);
}
else
{
// No, it is just a property access (like, "c.Grade").
temporaryBuilder.Append(leftPropertyText);
}
break;
// Usage of constants. Example: 1, 2, 3, "A", "B", "C", etc.
case ExpressionType.Constant:
case ExpressionType.New:
var leftText = leftPart.ToString();
// Is it a hard-coded date?
if (leftText.IndexOf("new DateTime", StringComparison.Ordinal) != -1)
{
// Yes.
// https://msdn.microsoft.com/en-us/library/bb361179%28v=vs.110%29.aspx
this.ExtractHardCodedDateFromLocalVariable(rightPart, out leftSqlQueryParameter);
}
// Process the left side hard-coded date or constant value.
this.PrepareTextForLeftOrRightSide(rightPropertyType, temporaryBuilder, leftText, leftSqlQueryParameter);
break;
}
// Handle logical operator in the boolean expression (such as, "==", ">=", etc.)
this.HandleLogicalOperator(op, temporaryBuilder);
// Handle the right side of the boolean expression.
switch (rightType)
{
// Property access, or variable name usage. Example: "c.Grade", local variable "searchName", etc.
case ExpressionType.MemberAccess:
// Is it a local variable usage?
if (rightSqlQueryParameter != null)
{
// Yes. Use the corresponding SQL query parameter.
temporaryBuilder.Append(rightSqlQueryParameter.Name);
this.AddToSqlQueryParameters(rightSqlQueryParameter);
}
else
{
// No, it is just a property access (like, "c.Grade"), or a variable being referenced.
if (String.IsNullOrEmpty(rightPropertyText) == false)
{
// There is either a property name reference, or a variable used with some value in it.
temporaryBuilder.Append(rightPropertyText);
}
else
{
// The referenced variable is NULL. Ignore this condition, and don't add to collection.
variableUsedIsNullAndConditionToBeIgnored = true;
}
}
break;
case ExpressionType.Constant:
case ExpressionType.New:
var rightText = rightPart.ToString();
// Is it a hard-coded date?
if (rightText.IndexOf("new DateTime", StringComparison.Ordinal) != -1)
{
// Yes.
// https://msdn.microsoft.com/en-us/library/bb361179%28v=vs.110%29.aspx
this.ExtractHardCodedDateFromLocalVariable(rightPart, out rightSqlQueryParameter);
}
// Process the right side hard-coded date or constant value.
this.PrepareTextForLeftOrRightSide(leftPropertyType, temporaryBuilder, rightText, rightSqlQueryParameter);
break;
}
// Is the variable used is NULL, and hence the comparison condition is NOT TO BE added?
if (variableUsedIsNullAndConditionToBeIgnored)
{
// Yes.
builder.Append(ALWAYS_TRUE_CONDITION);
}
else
{
// No, the condition is a valid one. Add it to the set.
builder.Append(temporaryBuilder);
}
// Return the T-SQL equivalent of the binary expression.
return(builder.ToString());
}
