/// <summary>
/// Generates a collection of sql statements to delete the business
/// object from the database
/// </summary>
/// <returns>Returns a sql statement collection</returns>
public IEnumerable<ISqlStatement> Generate()
{
var statements = new List<ISqlStatement>();
//AddRelationshipDeleteStatements(statementCollection);
SqlStatement deleteSql = new SqlStatement(_connection);
deleteSql.Statement = new StringBuilder(
@"DELETE FROM " + _connection.SqlFormatter.DelimitTable(StatementGeneratorUtils.GetTableName(_bo)) +
" WHERE " + StatementGeneratorUtils.PersistedDatabaseWhereClause((BOKey) _bo.ID, deleteSql));
statements.Add(deleteSql);
IClassDef currentClassDef = _bo.ClassDef;
while (currentClassDef.IsUsingClassTableInheritance())
{
while (currentClassDef.SuperClassClassDef.SuperClassDef != null &&
currentClassDef.SuperClassClassDef.SuperClassDef.ORMapping == ORMapping.SingleTableInheritance)
{
currentClassDef = currentClassDef.SuperClassClassDef;
}
deleteSql = new SqlStatement(_connection);
deleteSql.Statement.Append(
"DELETE FROM " +
_connection.SqlFormatter.DelimitTable(currentClassDef.SuperClassClassDef.TableName) +
" WHERE " +
StatementGeneratorUtils.PersistedDatabaseWhereClause(BOPrimaryKey.GetSuperClassKey((ClassDef) currentClassDef, _bo), deleteSql));
statements.Add(deleteSql);
currentClassDef = currentClassDef.SuperClassClassDef;
}
return statements;
}
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);
}
public void Statement()
{
//---------------Set up test pack-------------------
var sql = new SqlStatement(_connection);
var sb = new StringBuilder("test");
//---------------Execute Test ----------------------
sql.Statement = sb;
//---------------Test Result -----------------------
Assert.AreSame(sb, sql.Statement);
}
/// <summary>
/// Adds a sql migration that can be performed
/// </summary>
/// <param name="number">The migration number</param>
/// <param name="sql">The sql statement object to add</param>
public void AddMigration(int number, SqlStatement sql) {
if (_migrations.ContainsKey(number))
{
throw new HabaneroApplicationException(String.Format(
"While processing a database migration, a duplicate migration " +
"number '{0}' was encountered. Each number must be unique.",
number));
}
_migrations.Add(number, sql);
}
public void TestCreateDatabaseExpressionWithTableNameNoFieldSeparators()
{
IExpression exp =
new Expression(new Parameter("Field1", "tb", "DBField1", ">=", "value1"), new SqlOperator("OR"),
new Parameter("Field2", "tb", "DBField2", ">=", "value2"));
SqlStatement st = new SqlStatement(DatabaseConnection.CurrentConnection);
exp.SqlExpressionString(st, "", "");
Assert.AreEqual("(tb.DBField1 >= ?Param0 OR tb.DBField2 >= ?Param1)", st.Statement.ToString());
Assert.AreEqual("value1", st.Parameters[0].Value);
Assert.AreEqual("value2", st.Parameters[1].Value);
}
public void TestCreateDatabaseExpressionNoTableName()
{
IExpression exp =
new Expression(new Parameter("Field1", ">=", "value1"), new SqlOperator("AND"),
new Parameter("Field2", ">=", "value2"));
SqlStatement st = new SqlStatement(DatabaseConnection.CurrentConnection);
exp.SqlExpressionString(st, "[", "]");
Assert.AreEqual("([Field1] >= ?Param0 AND [Field2] >= ?Param1)", st.Statement.ToString());
Assert.AreEqual("value1", st.Parameters[0].Value);
Assert.AreEqual("value2", st.Parameters[1].Value);
}
/// <summary>
/// Executes the given sql statement using the database connection
/// provided
/// </summary>
/// <param name="statement">The sql statement</param>
/// <param name="connection">The database connection</param>
public static void ExecuteSqlStatement(SqlStatement statement, IDbConnection connection)
{
if (statement == null) throw new ArgumentNullException("statement");
if (connection == null) throw new ArgumentNullException("connection");
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
IDbCommand cmd = connection.CreateCommand();
statement.SetupCommand(cmd);
cmd.ExecuteNonQuery();
}
public void SetupTestFixture()
{
var config = new DatabaseConfig(DatabaseConfig.SqlServer, "test", "test", "test", "test", "1000");
_connection = new DatabaseConnectionFactory().CreateConnection(config);
_testStatement = new SqlStatement(_connection);
_rawStatement = "insert into tb1 (field1, field2, field3) values (@Param1, @Param2, @Param3)";
_testStatement.Statement.Append(_rawStatement);
_addedParameter = _testStatement.AddParameter("Param1", "12345");
_testStatement.AddParameter("Param2", "67890");
_testStatement.AddParameter("Param3", "13579");
_command = _connection.GetConnection().CreateCommand();
_testStatement.SetupCommand(_command);
}
public void Test_NoColumnName_DoesntError_SqlServer()
{
//---------------Set up test pack-------------------
const string sql = "Select LookupValue + ', ' + LookupValue from database_lookup_int";
var sqlStatement = new SqlStatement(DatabaseConnection.CurrentConnection, sql);
//---------------Execute Test ----------------------
var dataTable = DatabaseConnection.CurrentConnection.LoadDataTable(sqlStatement, "", "");
//---------------Test Result -----------------------
Assert.AreEqual(1, dataTable.Columns.Count);
//---------------Tear Down -------------------------
}
public void Test_NoColumnName_DoesntError_SqlServer()
{
//---------------Set up test pack-------------------
//DatabaseConnection.CurrentConnection = new DatabaseConnectionSqlServer("System.Data", "System.Data.SqlClient.SqlConnection","server=localhost;database=habanero_test_trunk;user=sa;password=sa");
const string sql = "Select FirstName + ', ' + Surname from tbPersonTable";
var sqlStatement = new SqlStatement(DatabaseConnection.CurrentConnection, sql);
//---------------Execute Test ----------------------
var dataTable = DatabaseConnection.CurrentConnection.LoadDataTable(sqlStatement, "", "");
//---------------Test Result -----------------------
Assert.AreEqual(1, dataTable.Columns.Count);
//---------------Tear Down -------------------------
}
private string CreateInClause(SqlStatement statement, object value)
{
string paramName = statement.ParameterNameGenerator.GetNextParameterName();
string inClause = "(";
Criteria.CriteriaValues criteriaValues = (Criteria.CriteriaValues)value;
int i = 0;
foreach (var paramValue in criteriaValues)
{
statement.AddParameter(paramName, paramValue);
inClause += paramName;
if (i < criteriaValues.Count - 1)
{
inClause += ", ";
paramName = statement.ParameterNameGenerator.GetNextParameterName();
}
i++;
}
inClause += ")";
return inClause;
}
private string AddParameter(object value, SqlStatement statement)
{
if (value == null) value = "NULL";
var resolvableValue = value as IResolvableToValue;
if (resolvableValue != null)
{
value = resolvableValue.ResolveToValue();
}
if (value is Criteria.CriteriaValues)
{
return CreateInClause(statement, value);
}
var paramName = statement.ParameterNameGenerator.GetNextParameterName();
statement.AddParameter(paramName, value, value.GetType());
return paramName;
}
private void AppendWhereClause(StringBuilder builder, SqlStatement statement)
{
Criteria fullCriteria = Criteria.MergeCriteria(_selectQuery.Criteria, _selectQuery.DiscriminatorCriteria);
ClassDef classDef = (ClassDef) _selectQuery.ClassDef;
if (classDef != null && classDef.ClassID.HasValue)
{
Criteria classIDCriteria = new Criteria("DMClassID", Criteria.ComparisonOp.Equals, classDef.ClassID.Value);
classIDCriteria.Field.Source = this.Source;
fullCriteria = Criteria.MergeCriteria(fullCriteria, classIDCriteria);
}
if (fullCriteria == null) return;
builder.Append(" WHERE ");
CriteriaDB criteriaDB = new CriteriaDB(fullCriteria);
string whereClause = criteriaDB.ToString(_sqlFormatter, value => AddParameter(value, statement),Aliases);
builder.Append(whereClause);
}
public void AddParameter_WithOnlyValue()
{
//---------------Set up test pack-------------------
const string startSql = "select * from bob WHERE name = ";
var builder = new SqlStatement(_connection, startSql);
const string paramName = "@Param0";
const DbType expectedParamType = DbType.String;
//---------------Execute Test ----------------------
var param = builder.AddParameter(paramName, "bob");
var actual = builder.Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedParamType, param.DbType);
Assert.AreEqual(1, builder.Parameters.Count);
Assert.AreEqual(startSql, actual);
}
/// <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>
/// 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;
}
public void TestAddingParameterToStatement()
{
var addingParamStatement = new SqlStatement(_connection);
addingParamStatement.AddParameterToStatement(1);
Assert.AreEqual("@Param0", addingParamStatement.Statement.ToString(),
"AddParameterToStatement is not building statement correctly.");
}
public void TestSqlStatementConstructor()
{
var newTest = new SqlStatement(_connection, "select * from bob");
Assert.AreEqual("select * from bob", newTest.Statement.ToString(),
"SqlStatement constructor does not set the statement correctly.");
}
public void AddParameter_WhenSqlServerCE_StringGreaterThan4000_ShouldReturnSqlServerNTextType()
{
//---------------Set up test pack-------------------
var sqlStatement = new SqlStatement(GetSQLServerCeConnection(), "some statement");
var bigTextValue = TestUtil.GetRandomString(4001);
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var parameter = sqlStatement.AddParameter("ntextParam", bigTextValue);
//---------------Test Result -----------------------
Assert.AreEqual(SqlDbType.NText,ReflectionUtilities.GetPropertyValue(parameter,"SqlDbType"));
}
public void AddParameter_WhenSqlServerCE_ByteArray_ShouldReturnSqlServerImageType()
{
//---------------Set up test pack-------------------
var sqlStatement = new SqlStatement(GetSQLServerCeConnection(), "some statement");
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var parameter = sqlStatement.AddParameter("imageParam", new byte[8001]);
//---------------Test Result -----------------------
Assert.AreEqual(SqlDbType.Image,ReflectionUtilities.GetPropertyValue(parameter,"SqlDbType"));
}
public void Equals_MatchingSqlAndNonMatchingNumbers()
{
//---------------Set up test pack-------------------
var test1 = new SqlStatement(_connection, "test");
var test2 = new SqlStatement(_connection, "test");
test1.AddParameter("param0", 1);
//---------------Test Result -----------------------
Assert.IsFalse(test1.Equals(test2));
}
public void Equals_MatchingSqlAndNonMatchingParamNames()
{
//---------------Set up test pack-------------------
var test1 = new SqlStatement(_connection, "test");
var test2 = new SqlStatement(_connection, "test");
test1.AddParameter("param0", 1);
test2.AddParameter("sdf", 1);
//---------------Test Result -----------------------
Assert.AreNotEqual(test1, test2);
}
public void Equals_NonMatchingSql()
{
//---------------Set up test pack-------------------
var test1 = new SqlStatement(_connection, "test1");
var test2 = new SqlStatement(_connection, "test2");
//---------------Test Result -----------------------
Assert.AreNotEqual(test1, test2);
}
public void Test_NoColumnName_DoesntError()
{
//---------------Set up test pack-------------------
const string sql = "Select FirstName + ', ' + Surname from contactpersoncompositekey";
var sqlStatement = new SqlStatement(DatabaseConnection.CurrentConnection, sql);
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var dataTable = DatabaseConnection.CurrentConnection.LoadDataTable(sqlStatement, "", "");
//---------------Test Result -----------------------
Assert.AreEqual(1, dataTable.Columns.Count);
}
///<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>
/// Creates an ISqlStatement out of the SelectQuery given in the constructor, to be used to load
/// from a database
/// </summary>
/// <returns>An ISqlStatement that can be executed against an IDatabaseConnection</returns>
public ISqlStatement CreateSqlStatement(ISqlFormatter sqlFormatter)
{
_sqlFormatter = sqlFormatter;
SqlStatement statement = new SqlStatement(_databaseConnection);
StringBuilder builder = statement.Statement;
CheckRecordOffSetAndAppendFields(builder);
AppendMainSelectClause(statement, builder);
if (this.FirstRecordToLoad > 0)
{
AppendOrderByFirstSelect(builder);
builder.Append(" ");
AppendNoOfRecordsClauseAtEnd(builder);
AppendOrderBySecondSelect(builder);
}
return statement;
}
/// <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 void AppendMainSelectClause(SqlStatement statement, StringBuilder builder)
{
builder.Append("SELECT ");
AppendLimitClauseAtBeginning(builder);
AppendFields(builder);
AppendFrom(builder);
AppendWhereClause(builder, statement);
AppendOrderByClause(builder);
AppendLimitClauseAtEnd(builder);
}
/// <summary>
/// Initialises the sql statement with a autoincrementing object
/// </summary>
private void InitialiseStatement(string tableName, ISupportsAutoIncrementingField supportsAutoIncrementingField)
{
_dbFieldList = new StringBuilder(_bo.Props.Count * 20);
_dbValueList = new StringBuilder(_bo.Props.Count * 20);
var statement = new InsertSqlStatement(_connection);
statement.TableName = tableName;
statement.SupportsAutoIncrementingField = supportsAutoIncrementingField;
_insertSql = statement;
_gen = _connection.CreateParameterNameGenerator();
_firstField = true;
}
public void AddParameter_WithNullValueAndTimeSpanType_ShouldBeDateTimeDbType()
{
//---------------Set up test pack-------------------
const string startSql = "select * from bob WHERE name = ";
var builder = new SqlStatement(_connection, startSql);
const string paramName = "@Param0";
var paramType = typeof(TimeSpan);
const DbType expectedParamType = DbType.DateTime;
//---------------Execute Test ----------------------
var param = builder.AddParameter(paramName, null, paramType);
var actual = builder.Statement.ToString();
//---------------Test Result -----------------------
Assert.AreEqual(expectedParamType, param.DbType);
Assert.AreEqual(1, builder.Parameters.Count);
Assert.AreEqual(startSql, actual);
}
