| public AddParameterToStatement ( object obj ) : void | ||
| obj | object | The parameter to add |
| return | void | |
/// <summary>
/// Creates a sql update statement from the name and value provided,
/// with no date specified
/// </summary>
/// <param name="settingName">The setting name</param>
/// <param name="settingValue">The value to set to</param>
/// <returns>Returns a sql statement object</returns>
private SqlStatement CreateUpdateStatementNoDate(string settingName, string settingValue)
{
var statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append("update " + _tableName + " set SettingValue = ");
statement.AddParameterToStatement(settingValue);
statement.Statement.Append(" where SettingName = ");
statement.AddParameterToStatement(settingName);
return(statement);
}
/// <summary>
/// Returns the sql statement to update the number in the
/// database
/// </summary>
/// <returns>Returns an ISqlStatementCollection containing
/// the statement</returns>
public IEnumerable <ISqlStatement> GetPersistSql()
{
var sqlStatement = string.Format(" update {0} set {1} = ", _tableName, _settingValueFieldName);
var statement = new SqlStatement(DatabaseConnection.CurrentConnection,
sqlStatement);
statement.AddParameterToStatement(_currentNumber.ToString());
statement.Statement.Append(string.Format(" where {0} = ", _settingFieldName));
statement.AddParameterToStatement(_setting);
return(new [] { statement });
}
/// <summary>
/// Creates a sql select statement to retrieve the named setting
/// at the date specified
/// </summary>
/// <param name="settingName">The setting name</param>
/// <param name="date">The date</param>
/// <returns>Returns a sql statement object</returns>
private SqlStatement CreateSelectStatement(string settingName, DateTime date)
{
var statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append("select SettingValue from " + _tableName + " where SettingName = ");
statement.AddParameterToStatement(settingName);
statement.Statement.Append(" and (StartDate <= ");
statement.AddParameterToStatement(date);
statement.Statement.Append(" or StartDate is null) ");
statement.Statement.Append(" and (EndDate > ");
statement.AddParameterToStatement(date);
statement.Statement.Append(" or EndDate is null)");
return(statement);
}
/// <summary>
/// Creates a sql insert statement from the name and value provided
/// </summary>
/// <param name="settingName">The setting name</param>
/// <param name="settingValue">The value to set to</param>
/// <returns>Returns a sql statement object</returns>
private SqlStatement CreateInsertStatement(string settingName, string settingValue)
{
var statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append("insert into " + _tableName + " (SettingName, SettingValue, StartDate, EndDate) ");
statement.Statement.Append("values (");
statement.AddParameterToStatement(settingName);
statement.Statement.Append(", ");
statement.AddParameterToStatement(settingValue);
statement.Statement.Append(", ");
statement.AddParameterToStatement(DateTime.Now);
statement.Statement.Append(", ");
statement.AddParameterToStatement(null);
statement.Statement.Append(") ");
return(statement);
}
private static void UpdateDatabaseLockAsExpired(int lockDuration)
{
SqlStatement sqlStatement = new SqlStatement(DatabaseConnection.CurrentConnection);
sqlStatement.Statement.Append("UPDATE `contact_person` SET ");
sqlStatement.Statement.Append(DatabaseConnection.CurrentConnection.SqlFormatter.DelimitField("DateTimeLocked"));
sqlStatement.Statement.Append(" = ");
sqlStatement.AddParameterToStatement(DateTime.Now.AddMinutes(-1*lockDuration - 1));
DatabaseConnection.CurrentConnection.ExecuteSql(sqlStatement);
}
///<summary>
/// Returns the appropriate sql statement collection depending on the state of the object.
/// E.g. Update SQL, InsertSQL or DeleteSQL.
///</summary>
public IEnumerable <ISqlStatement> GetPersistSql()
{
SqlStatement tranSql = new SqlStatement(DatabaseConnection.CurrentConnection);
string sql = "INSERT INTO " + this._transactionLogTable + " (" +
this._dateTimeUpdatedFieldName + ", " +
this._logonUserFieldName + ", " +
this._windowsUserFieldName + ", " +
this._machineUpdateName + ", " +
this._businessObjectTypeNameFieldName + ", " +
this._crudActionFieldName + ", " +
this._businessObjectToStringFieldName + ", " +
this._dirtyXmlFieldName + ") VALUES ( ";
tranSql.Statement.Append(sql);
tranSql.AddParameterToStatement(DateTime.Now);
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(GetLogonUserName());
tranSql.Statement.Append(", ");
WindowsIdentity currentWindowsUser = WindowsIdentity.GetCurrent();
if (currentWindowsUser != null)
{
tranSql.AddParameterToStatement(currentWindowsUser.Name);
}
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(Environment.MachineName);
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(_buObjToLog.ClassDef.ClassName);
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(GetCrudAction(_buObjToLog));
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(_buObjToLog.ToString());
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(_buObjToLog.DirtyXML);
tranSql.Statement.Append(")");
return(new [] { tranSql });
}
/// <summary>
/// Generates an "update" sql statement for the properties in the
/// business object
/// </summary>
/// <param name="tableName">The table name</param>
/// <param name="propsToInclude">A collection of properties to update,
/// if the previous include-all boolean was not set to true</param>
/// <param name="isSuperClassStatement">Whether a super-class is involved</param>
/// <param name="currentClassDef">The current class definition</param>
private void GenerateSingleUpdateStatement(string tableName, IBOPropCol propsToInclude,
bool isSuperClassStatement, ClassDef currentClassDef)
{
_updateSql = new SqlStatement(_connection);
_updateSql.Statement.Append(
@"UPDATE " + _connection.SqlFormatter.DelimitTable(tableName) + " SET ");
int includedProps = 0;
foreach (BOProp prop in _bo.Props.SortedValues)
{
if (propsToInclude.Contains(prop.PropertyName))
{
PrimaryKeyDef primaryKeyDef = (PrimaryKeyDef)_bo.ClassDef.PrimaryKeyDef ?? (PrimaryKeyDef)_bo.ID.KeyDef;
if (prop.IsDirty &&
((primaryKeyDef.IsGuidObjectID && !primaryKeyDef.Contains(prop.PropertyName)) ||
!primaryKeyDef.IsGuidObjectID))
{
includedProps++;
_updateSql.Statement.Append(_connection.SqlFormatter.DelimitField(prop.DatabaseFieldName));
_updateSql.Statement.Append(" = ");
//prop.PropDef.GetDataMapper().GetDatabaseValue(prop.Value);
_updateSql.AddParameterToStatement(prop.Value);
_updateSql.Statement.Append(", ");
}
}
}
_updateSql.Statement.Remove(_updateSql.Statement.Length - 2, 2); //remove the last ", "
if (isSuperClassStatement)
{
_updateSql.Statement.Append(" WHERE " + StatementGeneratorUtils.PersistedDatabaseWhereClause(BOPrimaryKey.GetSuperClassKey(currentClassDef, _bo), _updateSql));
}
else
{
_updateSql.Statement.Append(" WHERE " + StatementGeneratorUtils.PersistedDatabaseWhereClause((BOKey)_bo.ID, _updateSql));
}
if (includedProps > 0)
{
_statements.Add(_updateSql);
}
}
/// <summary>
/// Constructor to initialise a new generator, supplying a specific
/// table name and starting seed value
/// </summary>
/// <param name="settingName">The database setting name that
/// stores the number</param>
/// <param name="tableName">The database table name that
/// stores the number</param>
/// <param name="seedValue">The seed value to begin incrementing
/// from</param>
/// <param name="settingNameFieldName"></param>
/// <param name="settingValueFieldName"></param>
public DatabaseNumberGenerator(string settingName, string tableName, int seedValue, string settingNameFieldName, string settingValueFieldName)
{
var statement =
new SqlStatement(DatabaseConnection.CurrentConnection,
string.Format("select {0} from ", settingValueFieldName) + tableName + string.Format(" where {0} = ", settingNameFieldName));
statement.AddParameterToStatement(settingName);
IDataReader reader = null;
var hasNumber = false;
var number = 0;
try
{
using (reader = DatabaseConnection.CurrentConnection.LoadDataReader(statement))
{
if (reader.Read())
{
hasNumber = true;
number = Convert.ToInt32(reader.GetValue(0));
}
}
}
finally
{
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
if (!hasNumber)
{
number = seedValue;
DatabaseConnection.CurrentConnection.ExecuteRawSql(
string.Format("insert into {0} ({1}, {2}) values ('{3}', {4})",
tableName, settingNameFieldName, settingValueFieldName, settingName, seedValue));
}
_numberUpdater = new NumberUpdate(number, settingName, tableName, settingNameFieldName, settingValueFieldName);
}
/// <summary>
/// Creates a sql select statement to retrieve the named setting
/// at the date specified
/// </summary>
/// <param name="settingName">The setting name</param>
/// <param name="date">The date</param>
/// <returns>Returns a sql statement object</returns>
private SqlStatement CreateSelectStatement(string settingName, DateTime date)
{
var statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append("select SettingValue from " + _tableName + " where SettingName = ");
statement.AddParameterToStatement(settingName);
statement.Statement.Append(" and (StartDate <= ");
statement.AddParameterToStatement(date);
statement.Statement.Append(" or StartDate is null) ");
statement.Statement.Append(" and (EndDate > ");
statement.AddParameterToStatement(date);
statement.Statement.Append(" or EndDate is null)");
return statement;
}
///<summary>
/// Returns the appropriate sql statement collection depending on the state of the object.
/// E.g. Update SQL, InsertSQL or DeleteSQL.
///</summary>
public IEnumerable<ISqlStatement> GetPersistSql()
{
SqlStatement tranSql = new SqlStatement(DatabaseConnection.CurrentConnection);
string sql = "INSERT INTO " + this._transactionLogTable + " (" +
this._dateTimeUpdatedFieldName + ", " +
this._logonUserFieldName + ", " +
this._windowsUserFieldName + ", " +
this._machineUpdateName + ", " +
this._businessObjectTypeNameFieldName + ", " +
this._crudActionFieldName + ", " +
this._businessObjectToStringFieldName + ", " +
this._dirtyXmlFieldName + ") VALUES ( ";
tranSql.Statement.Append(sql);
tranSql.AddParameterToStatement(DateTime.Now);
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(GetLogonUserName());
tranSql.Statement.Append(", ");
WindowsIdentity currentWindowsUser = WindowsIdentity.GetCurrent();
if (currentWindowsUser != null) tranSql.AddParameterToStatement(currentWindowsUser.Name);
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(Environment.MachineName);
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(_buObjToLog.ClassDef.ClassName);
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(GetCrudAction(_buObjToLog));
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(_buObjToLog.ToString());
tranSql.Statement.Append(", ");
tranSql.AddParameterToStatement(_buObjToLog.DirtyXML);
tranSql.Statement.Append(")");
return new [] { tranSql };
}
public void TestAddingParameterToStatement()
{
var addingParamStatement = new SqlStatement(_connection);
addingParamStatement.AddParameterToStatement(1);
Assert.AreEqual("@Param0", addingParamStatement.Statement.ToString(),
"AddParameterToStatement is not building statement correctly.");
}
/// <summary>
/// Constructor to initialise a new generator, supplying a specific
/// table name and starting seed value
/// </summary>
/// <param name="settingName">The database setting name that
/// stores the number</param>
/// <param name="tableName">The database table name that
/// stores the number</param>
/// <param name="seedValue">The seed value to begin incrementing
/// from</param>
/// <param name="settingNameFieldName"></param>
/// <param name="settingValueFieldName"></param>
public DatabaseNumberGenerator(string settingName, string tableName, int seedValue, string settingNameFieldName, string settingValueFieldName)
{
var statement =
new SqlStatement(DatabaseConnection.CurrentConnection,
string.Format("select {0} from ", settingValueFieldName) + tableName + string.Format(" where {0} = ", settingNameFieldName));
statement.AddParameterToStatement(settingName);
IDataReader reader = null;
var hasNumber = false;
var number = 0;
try
{
using (reader = DatabaseConnection.CurrentConnection.LoadDataReader(statement))
{
if (reader.Read())
{
hasNumber = true;
number = Convert.ToInt32(reader.GetValue(0));
}
}
}
finally
{
if (reader != null && !reader.IsClosed) reader.Close();
}
if (!hasNumber)
{
number = seedValue;
DatabaseConnection.CurrentConnection.ExecuteRawSql(
string.Format("insert into {0} ({1}, {2}) values ('{3}', {4})",
tableName, settingNameFieldName, settingValueFieldName, settingName, seedValue));
}
_numberUpdater = new NumberUpdate(number, settingName, tableName, settingNameFieldName, settingValueFieldName);
}
/// <summary>
/// Generates an "update" sql statement for the properties in the
/// business object
/// </summary>
/// <param name="tableName">The table name</param>
/// <param name="propsToInclude">A collection of properties to update,
/// if the previous include-all boolean was not set to true</param>
/// <param name="isSuperClassStatement">Whether a super-class is involved</param>
/// <param name="currentClassDef">The current class definition</param>
private void GenerateSingleUpdateStatement(string tableName, IBOPropCol propsToInclude,
bool isSuperClassStatement, ClassDef currentClassDef)
{
_updateSql = new SqlStatement(_connection);
_updateSql.Statement.Append(
@"UPDATE " + _connection.SqlFormatter.DelimitTable(tableName) + " SET ");
int includedProps = 0;
foreach (BOProp prop in _bo.Props.SortedValues)
{
if (propsToInclude.Contains(prop.PropertyName))
{
PrimaryKeyDef primaryKeyDef = (PrimaryKeyDef)_bo.ClassDef.PrimaryKeyDef ?? (PrimaryKeyDef)_bo.ID.KeyDef;
if (prop.IsDirty &&
((primaryKeyDef.IsGuidObjectID && !primaryKeyDef.Contains(prop.PropertyName)) ||
!primaryKeyDef.IsGuidObjectID))
{
includedProps++;
_updateSql.Statement.Append(_connection.SqlFormatter.DelimitField(prop.DatabaseFieldName));
_updateSql.Statement.Append(" = ");
//prop.PropDef.GetDataMapper().GetDatabaseValue(prop.Value);
_updateSql.AddParameterToStatement(prop.Value);
_updateSql.Statement.Append(", ");
}
}
}
_updateSql.Statement.Remove(_updateSql.Statement.Length - 2, 2); //remove the last ", "
if (isSuperClassStatement)
{
_updateSql.Statement.Append(" WHERE " + StatementGeneratorUtils.PersistedDatabaseWhereClause(BOPrimaryKey.GetSuperClassKey(currentClassDef, _bo), _updateSql));
}
else
{
_updateSql.Statement.Append(" WHERE " + StatementGeneratorUtils.PersistedDatabaseWhereClause((BOKey) _bo.ID, _updateSql));
}
if (includedProps > 0)
{
_statements.Add(_updateSql);
}
}
/// <summary>
/// Creates a sql update statement from the name and value provided,
/// with no date specified
/// </summary>
/// <param name="settingName">The setting name</param>
/// <param name="settingValue">The value to set to</param>
/// <returns>Returns a sql statement object</returns>
private SqlStatement CreateUpdateStatementNoDate(string settingName, string settingValue)
{
var statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append("update " + _tableName + " set SettingValue = ");
statement.AddParameterToStatement(settingValue);
statement.Statement.Append(" where SettingName = ");
statement.AddParameterToStatement(settingName);
return statement;
}
private static void UpdateDatabaseLockAsExpired(string numberType, int lockDuration)
{
var sqlStatement = new SqlStatement(DatabaseConnection.CurrentConnection);
sqlStatement.Statement.Append("UPDATE `numbergenerator` SET ");
sqlStatement.Statement.Append(DatabaseConnection.CurrentConnection.SqlFormatter.DelimitField("DateTimeLocked"));
sqlStatement.Statement.Append(" = ");
sqlStatement.AddParameterToStatement(DateTime.Now.AddMinutes(-1 * lockDuration - 1));
sqlStatement.Statement.Append(" WHERE ");
sqlStatement.Statement.Append(DatabaseConnection.CurrentConnection.SqlFormatter.DelimitField("NumberType"));
sqlStatement.Statement.Append(" = ");
sqlStatement.AddParameterToStatement(numberType);
DatabaseConnection.CurrentConnection.ExecuteSql(sqlStatement);
}
/// <summary>
/// Creates a sql insert statement from the name and value provided
/// </summary>
/// <param name="settingName">The setting name</param>
/// <param name="settingValue">The value to set to</param>
/// <returns>Returns a sql statement object</returns>
private SqlStatement CreateInsertStatement(string settingName, string settingValue)
{
var statement = new SqlStatement(DatabaseConnection.CurrentConnection);
statement.Statement.Append("insert into " + _tableName + " (SettingName, SettingValue, StartDate, EndDate) ");
statement.Statement.Append("values (");
statement.AddParameterToStatement(settingName);
statement.Statement.Append(", ");
statement.AddParameterToStatement(settingValue);
statement.Statement.Append(", ");
statement.AddParameterToStatement(DateTime.Now);
statement.Statement.Append(", ");
statement.AddParameterToStatement(null);
statement.Statement.Append(") ");
return statement;
}
/// <summary>
/// Returns the sql statement to update the number in the
/// database
/// </summary>
/// <returns>Returns an ISqlStatementCollection containing
/// the statement</returns>
public IEnumerable<ISqlStatement> GetPersistSql()
{
var sqlStatement = string.Format(" update {0} set {1} = ", _tableName, _settingValueFieldName);
var statement = new SqlStatement(DatabaseConnection.CurrentConnection,
sqlStatement);
statement.AddParameterToStatement(_currentNumber.ToString());
statement.Statement.Append(string.Format(" where {0} = ", _settingFieldName));
statement.AddParameterToStatement(_setting);
return new [] {statement};
}
