/// <summary>
/// Creates new instance of OrderByField
/// </summary>
/// <param name="OrderField">Field to order by</param>
/// <param name="Direction">Sort direction</param>
public OrderByField(Field OrderField, SortDirection Direction)
{
if (OrderField == null)
throw new ArgumentNullException("OrderField");
this.Field = OrderField;
this.Direction = Direction;
}
/// <summary>
/// Creates T-SQL Delete query based on parameters provided
/// </summary>
/// <param name="TableName">Table name to delete record from</param>
/// <param name="PrimaryKeyField">PrimaryKey field</param>
/// <param name="PrimaryKeyValue">PrimaryKey value</param>
/// <returns>T-SQL query for deleting record with parameters given</returns>
protected virtual string CreateDeleteQuery(string TableName, Field PrimaryKeyField, object PrimaryKeyValue)
{
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, string.Format("Creating delete query for TableName: {0}, PrimaryKeyField: {1}, PrimaryKeyValue: {2}", TableName, PrimaryKeyField, PrimaryKeyValue));
#endif
#endregion
string result = string.Format("DELETE FROM {0} WHERE {1} = '{2}'", TableName, PrimaryKeyField.InternalName, Tools.EscapeQueryValue(PrimaryKeyValue));
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Delete query: " + result);
#endif
#endregion
return result;
}
/// <summary>
/// Executes insert query
/// </summary>
/// <param name="TableName">Table name to insert record in</param>
/// <param name="ColumnValues">Column values to insert</param>
/// <param name="PrimaryKeyField">Primary Key column</param>
/// <param name="PrimaryKeyValue">Primary key column value</param>
/// <returns>ID of inserted record</returns>
public object ExecuteInsertQuery(string TableName, Dictionary<Field, string> ColumnValues, Field PrimaryKeyField, object PrimaryKeyValue)
{
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Entering: ExecuteInsertQuery");
#endif
#endregion
if (ColumnValues.Count == 0)
throw new dhtmlxException("Data row cannot be inserted because there were no data specified");
try
{
object result = this.ExecuteScalar(this.CreateInsertQuery(TableName, ColumnValues));
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Result: " + Convert.ToString(result));
#endif
#endregion
return result;
}
catch (Exception ex)
{
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Exception cought: " + ex.Message);
#endif
#endregion
throw new dhtmlxException("Insert operation failed with the followind error: " + ex.Message);
}
}
/// <summary>
/// Executes update query
/// </summary>
/// <param name="TableName">TableName that contains record to be updated</param>
/// <param name="NewColumnValues">Collection of values to be updated in target query</param>
/// <param name="PrimaryKeyField">Primary key field to search target record by</param>
/// <param name="PrimaryKeyValue">Primary key field value </param>
public virtual void ExecuteUpdateQuery(string TableName, Dictionary<Field, string> NewColumnValues, Field PrimaryKeyField, object PrimaryKeyValue)
{
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Entering: ExecuteUpdateQuery");
#endif
#endregion
if (NewColumnValues.Count == 0)
throw new dhtmlxException("Data row cannot be updated because there were no data specified");
if (PrimaryKeyField == null)
throw new dhtmlxException("Data row cannot be updated because there were no primary key provided");
try
{
this.ExecuteNonQuery(this.CreateUpdateQuery(TableName, NewColumnValues, PrimaryKeyField, PrimaryKeyValue));
}
catch (Exception ex)
{
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Exception cought: " + ex.Message);
#endif
#endregion
throw new dhtmlxException("Update operation failed with the followind error: " + ex.Message);
}
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Leaving: ExecuteUpdateQuery");
#endif
#endregion
}
/// <summary>
/// Created T-SQL Update query
/// </summary>
/// <param name="TableName">TableName to update records in</param>
/// <param name="NewColumnValues">New columns values</param>
/// <param name="PrimaryKeyField">PrimaryKey field</param>
/// <param name="PrimaryKeyValue">PrimaryKey value</param>
/// <returns>T-SQL Update query</returns>
protected virtual string CreateUpdateQuery(string TableName, Dictionary<Field, string> NewColumnValues, Field PrimaryKeyField, object PrimaryKeyValue)
{
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, string.Format("Creating UpdateQuery from TableName: {0}, Fields-Values: {1}, PrimaryKeyField: {2}, PrimaryKeyValue: {3}", TableName, Tools.Join(NewColumnValues.Select(item => item.Key.ToString() + " = " + item.Value), ", "), PrimaryKeyField, PrimaryKeyValue));
#endif
#endregion
string updateQuery = string.Format("UPDATE {0} SET {{0}} WHERE {1} = '{2}'", TableName, PrimaryKeyField.InternalName, Tools.EscapeQueryValue(PrimaryKeyValue));
List<string> updateFieldStatements = new List<string>();
foreach (Field updateField in NewColumnValues.Keys)
{
string updateFieldStatement = string.Format("{0} = '{1}'", updateField.InternalName, Tools.EscapeQueryValue(NewColumnValues[updateField]));
updateFieldStatements.Add(updateFieldStatement);
}
string result = string.Format(updateQuery, string.Join(", ", updateFieldStatements.ToArray()));
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Update query: " + result);
#endif
#endregion
return result;
}
/// <summary>
/// Creates new instance of DataAction
/// </summary>
/// <param name="ActionType">Type of data action</param>
/// <param name="TableName">Table name</param>
/// <param name="Data">Collection of column values</param>
/// <param name="UserData">Collection of userdata values</param>
/// <param name="PrimaryKeyField">PrimaryKey field</param>
/// <param name="PrimaryKeyValue">Old PrimaryKey value</param>
/// <param name="PostoperationalPrimaryKeyValue">New PrimaryKey value</param>
public DataAction(ActionType ActionType, string TableName, Dictionary<Field, string> Data, Dictionary<string, string> UserData, Field PrimaryKeyField, object PrimaryKeyValue, object PostoperationalPrimaryKeyValue)
{
this.PrimaryKeyValue = PrimaryKeyValue;
this.PostoperationalPrimaryKeyValue = PostoperationalPrimaryKeyValue;
this._Data = Data;
this.TableName = TableName;
this.PrimaryKeyField = PrimaryKeyField;
this.SetInitialActionType(ActionType);
this._UserData = UserData;
}
public DataAction(string CustomActionName, string TableName, Dictionary<Field, string> Data, Dictionary<string, string> UserData, Field PrimaryKeyField, object PrimaryKeyValue, object PostoperationalPrimaryKeyValue)
: this(ActionType.Custom, TableName, Data, UserData, PrimaryKeyField, PrimaryKeyValue, PostoperationalPrimaryKeyValue)
{
this.CustomActionName = CustomActionName;
}
public DataAction(string CustomActionName, string TableName, Dictionary<Field, string> Data, Dictionary<string, string> UserData, Field PrimaryKeyField, object PrimaryKeyValue)
: this(CustomActionName, TableName, Data, UserData, PrimaryKeyField, PrimaryKeyValue, null)
{
}
/// <summary>
/// Creates new instance of FieldRule
/// </summary>
/// <param name="RuleField">Field to compare</param>
/// <param name="CompareOperator">Compare operator</param>
/// <param name="CompareValue">Value to compare field with</param>
public FieldRule(Field RuleField, Operator CompareOperator, object CompareValue)
{
this.Field = RuleField;
this.Operator = CompareOperator;
this.Value = CompareValue;
}
/// <summary>
/// Creates new instance of OrderByField
/// </summary>
/// <param name="OrderField">Field to order by</param>
/// <param name="SortDirection">Sort direction (Asc or Desc)</param>
public OrderByField(Field OrderField, string SortDirection)
: this(OrderField, OrderByField.ParseDirection(SortDirection))
{
}
/// <summary>
/// Converts custom SQL template into ready-to-execute sql statement
/// </summary>
/// <param name="SQL">SQL template</param>
/// <param name="Data">Collection of field-value pairs to use for template population</param>
/// <param name="PrimaryKeyField">Primary key field</param>
/// <param name="PrimaryKeyValue">Primary key value</param>
/// <returns>Ready-to-execute sql statement</returns>
private string ParseSQLTemplate(string SQL, Dictionary<Field, string> Data, Field PrimaryKeyField, object PrimaryKeyValue)
{
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Parsing custom sql: " + SQL);
#endif
#endregion
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Current values are: [" + string.Join(",", Data.Select(item => item.Key.ExternalName + " = " + item.Value).ToArray()) + "]");
#endif
#endregion
foreach (Field field in Data.Keys)
if (SQL.Contains("{" + field.ExternalName + "}"))
SQL = SQL.Replace("{" + field.ExternalName + "}", Tools.EscapeQueryValue(Data[field]));
if (PrimaryKeyField != null && SQL.Contains("{" + PrimaryKeyField.ExternalName + "}"))
SQL = SQL.Replace("{" + PrimaryKeyField.ExternalName + "}", Tools.EscapeQueryValue(PrimaryKeyValue));
#region LOG ENTRY
#if !NO_LOG
Log.WriteLine(this, "Parsed custom sql: " + SQL);
#endif
#endregion
return SQL;
}
