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); }