protected virtual Command Filter(ValueCompareFilter filter)
{
//Validating if there are filters defined
if (filter == null) return null;
Command command = new Command();
CommandParameter param = new CommandParameter(filter.ValueToCompare, null, filter.Column.DbType);
command.Parameters.Add(param);
command.Script =
EqualityComparison
(
ColumnFullName(filter),
param.Name,
filter.Operator
);
return command;
}
protected virtual Command Filter(RangeFilter filter)
{
//Validating if there are filters defined
if (filter == null) return null;
Command command = new Command();
CommandParameter minParam = new CommandParameter(filter.MinValue, null, filter.Column.DbType);
command.Parameters.Add(minParam);
CommandParameter maxParam = new CommandParameter(filter.MaxValue, null, filter.Column.DbType);
command.Parameters.Add(maxParam);
command.Append
(
LogicalExpression
(
EqualityComparison
(
ColumnFullName(filter),
minParam.Name,
CompareOperator.GreaterThanEqual
),
EqualityComparison
(
ColumnFullName(filter),
maxParam.Name,
CompareOperator.LessThanEqual
),
LogicalOperator.And,
false
)
);
return command;
}
protected CommandParameter CreateCommandParameter(object value, DbType dbType, ParameterDirection direction, string name)
{
CommandParameter param = new CommandParameter();
param.Value = value;
param.DbType = dbType;
param.Direction = direction;
if (string.IsNullOrWhiteSpace(name))
{
param.Name = "@" + dbType + Random.Next().ToString();
}
else
{
param.Name = name;
}
return param;
}
/// <summary>
/// Creates a UPDATE query for a single dataobject's Table
/// </summary>
/// <param name="table">
/// Table considered in the Update
/// </param>
/// <param name="dobj">
/// object instance for perform the update
/// </param>
/// <returns>
/// Sql string for update the specified object
/// </returns>
public virtual Command Update(Update update)
{
//Validating if table argument is null
if (update == null)
{
throw new ArgumentNullException("update");
}
//Creating Update sentence begin
Command command = "UPDATE " + EncloseName(update.Table.Name) + " SET ";
//Crossing DataMembers that aren't primary key
foreach (var value in update.Set)
{
CommandParameter param = new CommandParameter(value.Value, "@" + value.Column.Name, value.Column.DbType);
command.Parameters.Add(param);
//Building the sql sentence...
command.Script +=
EncloseName(value.Column.Name) +
" = " + param.Name +
", ";
}
//Remove the ", " if there is at least one updated property
command.Script = command.Script.TrimEnd(',', ' ');
//filters
command += WhereClause(update.Where);
command.Script += ScriptSeparator;
//Returning the sentence
return command;
}
/// <summary>
/// Creates a Insert Sentence for the specified Table with
/// the values of the object indicated
/// </summary>
/// <param name="table">
/// Table for Insert sentence create
/// </param>
/// <param name="dobj">
/// object instance with the values to use in the Insert sentence
/// </param>
/// <returns>
/// Insert Sql Sentence
/// </returns>
public virtual Command Insert(Insert insert)
{
//Validating if table or dobj arguments are null
if (insert == null)
{
throw new ArgumentNullException("insert");
}
string columnsList = string.Empty, valuesList = string.Empty;
Command command = new Command();
foreach (ColumnValue cv in insert.Values)
{
Int64 intValue = Int64.MinValue;
if (cv.Column.IsNumeric)
{
try
{
intValue = (Int64)System.Convert.ChangeType(cv.Value, typeof(Int64));
}
catch { }
}
//if this is an autoincrement primary key and the value is null, ignore this field so its autogenerated
if
(
cv.Column.IsAutoNumber &&
cv.Column.IsPrimaryKey &&
(cv.Value == null || intValue == 0)
)
{
//ignore
continue;
}
//otherwise, treat normally
else
{
CommandParameter param = CreateCommandParameter(cv);
columnsList += EncloseName(cv.Column.Name) + ", ";
valuesList += param.Name + ", ";
command.Parameters.Add(param);
}
}
//Removing last ", "
if (columnsList.EndsWith(", "))
{
columnsList = columnsList.Remove(columnsList.Length - 2, 2);
}
if (valuesList.EndsWith(", "))
{
valuesList = valuesList.Remove(valuesList.Length - 2, 2);
}
//Creating Insert Sentence and return it
command.Script =
"INSERT INTO " + EncloseName(insert.Table.Name) + " " +
EncloseOnParenthesis(columnsList) +
" VALUES " +
EncloseOnParenthesis(valuesList);
command.Script += ScriptSeparator;
return(command);
}