Inheritance: SqlBuilderBase, IUpdate, ISetOrWhere
        public void TableThrowsArgumentExceptionForEmptyTableName()
        {
            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var exception = Assert.Throws<ArgumentException>(
                () => sqlBuilder.Table(""));

            Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("tableName"), exception.Message);
        }
        public void OrWhereThrowsArgumentExceptionForEmptyColumn()
        {
            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var exception = Assert.Throws<ArgumentException>(
                () => sqlBuilder.OrWhere(""));

            Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("column"), exception.Message);
        }
        public void UpdateSpecifyingTypeWithSqlCharacters()
        {
            var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);

            var sqlQuery = sqlBuilder
                .Table(typeof(Customer))
                .ToSqlQuery();

            Assert.Equal("UPDATE [Sales].[Customers] SET ", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void UpdateSpecifyingType()
        {
            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var sqlQuery = sqlBuilder
                .Table(typeof(Customer))
                .ToSqlQuery();

            Assert.Equal("UPDATE Sales.Customers SET ", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void UpdateSpecifyingTableName()
        {
            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .ToSqlQuery();

            Assert.Equal("UPDATE Table SET ", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void UpdateSpecifyingTableNameWithSqlCharacters()
        {
            var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .ToSqlQuery();

            Assert.Equal("UPDATE [Table] SET ", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void UpdateTableSetColumnValue()
        {
            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .SetColumnValue("Column1", "Foo")
                .SetColumnValue("Column2", 12)
                .WhereEquals("Id", 100122)
                .ToSqlQuery();

            Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE Id = ?", sqlQuery.CommandText);
            Assert.Equal(3, sqlQuery.Arguments.Count);
            Assert.Equal("Foo", sqlQuery.Arguments[0]);
            Assert.Equal(12, sqlQuery.Arguments[1]);
            Assert.Equal(100122, sqlQuery.Arguments[2]);
        }
Example #8
0
        /// <summary>
        /// Builds the command text to update a database record for the specified <see cref="IObjectInfo"/>.
        /// </summary>
        /// <param name="objectInfo">The object information.</param>
        /// <returns>
        /// The created command text.
        /// </returns>
        protected virtual string BuildUpdateCommandText(IObjectInfo objectInfo)
        {
            if (objectInfo == null)
            {
                throw new ArgumentNullException("objectInfo");
            }

            var builder = new UpdateSqlBuilder(this.SqlCharacters)
                       .Table(objectInfo);

            for (int i = 0; i < objectInfo.TableInfo.Columns.Count; i++)
            {
                var columnInfo = objectInfo.TableInfo.Columns[i];

                if (columnInfo.AllowUpdate)
                {
                    builder.SetColumnValue(columnInfo.ColumnName, null);
                }
            }

            var updateSqlQuery = builder
                .Where(objectInfo.TableInfo.IdentifierColumn.ColumnName).IsEqualTo(0)
                .ToSqlQuery();

            return updateSqlQuery.CommandText;
        }
Example #9
0
        /// <summary>
        /// Creates an SqlQuery to perform an update based upon the values in the object delta.
        /// </summary>
        /// <param name="objectDelta">The object delta to create the query for.</param>
        /// <returns>
        /// The created <see cref="SqlQuery" />.
        /// </returns>
        public SqlQuery BuildUpdateSqlQuery(ObjectDelta objectDelta)
        {
            if (objectDelta == null)
            {
                throw new ArgumentNullException("objectDelta");
            }

            if (log.IsDebug)
            {
                log.Debug(LogMessages.SqlDialect_CreatingSqlQuery, "UPDATE");
            }

            var objectInfo = ObjectInfo.For(objectDelta.ForType);

            var builder = new UpdateSqlBuilder(this.SqlCharacters)
                .Table(objectInfo);

            foreach (var change in objectDelta.Changes)
            {
                builder.SetColumnValue(change.Key, change.Value);
            }

            var sqlQuery = builder
                .Where(objectInfo.TableInfo.IdentifierColumn.ColumnName).IsEqualTo(objectDelta.Identifier)
                .ToSqlQuery();

            return sqlQuery;
        }
        public void UpdateTableSetColumnValueWithSqlCharacters()
        {
            var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .SetColumnValue("Column1", "Foo")
                .SetColumnValue("Column2", 12)
                .WhereEquals("Id", 100122)
                .ToSqlQuery();

            Assert.Equal("UPDATE [Table] SET [Column1] = @p0,[Column2] = @p1 WHERE [Id] = @p2", sqlQuery.CommandText);
            Assert.Equal(3, sqlQuery.Arguments.Count);
            Assert.Equal("Foo", sqlQuery.Arguments[0]);
            Assert.Equal(12, sqlQuery.Arguments[1]);
            Assert.Equal(100122, sqlQuery.Arguments[2]);
        }
        public void UpdateTableSetColumnValueWhereNotInSqlQuery()
        {
            var subQuery = new SqlQuery("SELECT Id FROM Table WHERE Column = ?", 1024);

            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .SetColumnValue("Column1", "Foo")
                .SetColumnValue("Column2", 12)
                .Where("Column3").NotIn(subQuery)
                .ToSqlQuery();

            Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Column3 NOT IN (SELECT Id FROM Table WHERE Column = ?))", sqlQuery.CommandText);

            Assert.Equal(3, sqlQuery.Arguments.Count);

            Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
            Assert.Equal("Foo", sqlQuery.Arguments[0].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType);
            Assert.Equal(12, sqlQuery.Arguments[1].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
            Assert.Equal(1024, sqlQuery.Arguments[2].Value);
        }
        public void UpdateTableSetColumnValueWhereNotInArgs()
        {
            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .SetColumnValue("Column1", "Foo")
                .SetColumnValue("Column2", 12)
                .Where("Column3").NotIn(1, 2, 3)
                .ToSqlQuery();

            Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Column3 NOT IN (?,?,?))", sqlQuery.CommandText);

            Assert.Equal(5, sqlQuery.Arguments.Count);

            Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
            Assert.Equal("Foo", sqlQuery.Arguments[0].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType);
            Assert.Equal(12, sqlQuery.Arguments[1].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
            Assert.Equal(1, sqlQuery.Arguments[2].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType);
            Assert.Equal(2, sqlQuery.Arguments[3].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[4].DbType);
            Assert.Equal(3, sqlQuery.Arguments[4].Value);
        }
        public void UpdateTableSetColumnValueWhereNotBetween()
        {
            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .SetColumnValue("Column1", "Foo")
                .SetColumnValue("Column2", 12)
                .Where("Id").NotBetween(1, 199)
                .ToSqlQuery();

            Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Id NOT BETWEEN ? AND ?)", sqlQuery.CommandText);

            Assert.Equal(4, sqlQuery.Arguments.Count);

            Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
            Assert.Equal("Foo", sqlQuery.Arguments[0].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType);
            Assert.Equal(12, sqlQuery.Arguments[1].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
            Assert.Equal(1, sqlQuery.Arguments[2].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[3].DbType);
            Assert.Equal(199, sqlQuery.Arguments[3].Value);
        }
        public void UpdateTableSetColumnValueWhereIsNull()
        {
            var sqlBuilder = new UpdateSqlBuilder(SqlCharacters.Empty);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .SetColumnValue("Column1", "Foo")
                .SetColumnValue("Column2", 12)
                .Where("Id").IsNull()
                .ToSqlQuery();

            Assert.Equal("UPDATE Table SET Column1 = ?,Column2 = ? WHERE (Id IS NULL)", sqlQuery.CommandText);

            Assert.Equal(2, sqlQuery.Arguments.Count);

            Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
            Assert.Equal("Foo", sqlQuery.Arguments[0].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType);
            Assert.Equal(12, sqlQuery.Arguments[1].Value);
        }
Example #15
0
        /// <summary>
        /// Builds an SqlQuery to update the database record for the specified instance with the current property values of the instance.
        /// </summary>
        /// <param name="objectInfo">The object information.</param>
        /// <param name="instance">The instance to update.</param>
        /// <returns>
        /// The created <see cref="SqlQuery" />.
        /// </returns>
        public SqlQuery BuildUpdateSqlQuery(IObjectInfo objectInfo, object instance)
        {
            if (objectInfo == null)
            {
                throw new ArgumentNullException("objectInfo");
            }

            string updateCommand;

            if (!this.updateCommandCache.TryGetValue(objectInfo.ForType, out updateCommand))
            {
                var builder = new UpdateSqlBuilder(this.SqlCharacters)
                    .Table(objectInfo);

                for (int i = 0; i < objectInfo.TableInfo.Columns.Count; i++)
                {
                    var columnInfo = objectInfo.TableInfo.Columns[i];

                    if (columnInfo.AllowUpdate)
                    {
                        builder.SetColumnValue(columnInfo.ColumnName, null);
                    }
                }

                var updateSqlQuery = builder.WhereEquals(objectInfo.TableInfo.IdentifierColumn.ColumnName, objectInfo.GetIdentifierValue(instance))
                    .ToSqlQuery();

                var newUpdateCommandCache = new Dictionary<Type, string>(this.updateCommandCache);
                newUpdateCommandCache[objectInfo.ForType] = updateSqlQuery.CommandText;
                updateCommand = updateSqlQuery.CommandText;

                this.updateCommandCache = newUpdateCommandCache;
            }

            var updateValues = objectInfo.GetUpdateValues(instance);

            return new SqlQuery(updateCommand, updateValues);
        }
Example #16
0
        /// <summary>
        /// Creates an SqlQuery to perform an update based upon the values in the object delta.
        /// </summary>
        /// <param name="objectDelta">The object delta to create the query for.</param>
        /// <returns>
        /// The created <see cref="SqlQuery" />.
        /// </returns>
        public SqlQuery BuildUpdateSqlQuery(ObjectDelta objectDelta)
        {
            if (objectDelta == null)
            {
                throw new ArgumentNullException("objectDelta");
            }

            var objectInfo = ObjectInfo.For(objectDelta.ForType);

            var builder = new UpdateSqlBuilder(this.SqlCharacters)
                .Table(objectInfo);

            foreach (var change in objectDelta.Changes)
            {
                builder.SetColumnValue(change.Key, change.Value);
            }

            var sqlQuery = builder
                .WhereEquals(objectInfo.TableInfo.IdentifierColumn.ColumnName, objectDelta.Identifier)
                .ToSqlQuery();

            return sqlQuery;
        }
        public void UpdateTableSetColumnValueWhereIsEqualToWithSqlCharacters()
        {
            var sqlBuilder = new UpdateSqlBuilder(MsSqlCharacters.Instance);

            var sqlQuery = sqlBuilder
                .Table("Table")
                .SetColumnValue("Column1", "Foo")
                .SetColumnValue("Column2", 12)
                .Where("Id").IsEqualTo(100122)
                .ToSqlQuery();

            Assert.Equal("UPDATE [Table] SET [Column1] = @p0,[Column2] = @p1 WHERE ([Id] = @p2)", sqlQuery.CommandText);

            Assert.Equal(3, sqlQuery.Arguments.Count);

            Assert.Equal(DbType.String, sqlQuery.Arguments[0].DbType);
            Assert.Equal("Foo", sqlQuery.Arguments[0].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[1].DbType);
            Assert.Equal(12, sqlQuery.Arguments[1].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[2].DbType);
            Assert.Equal(100122, sqlQuery.Arguments[2].Value);
        }