Inheritance: SqlBuilderBase, ISelectFrom, IFunctionOrFrom, IWhereOrOrderBy, IAndOrOrderBy, IGroupBy, IOrderBy, IWhereSingleColumn, IHavingOrOrderBy, IWhereExists
        public void AndWhereThrowsArgumentExceptionForNullPredicate()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);

            var exception = Assert.Throws<ArgumentException>(
                () => sqlBuilder.AndWhere(null, new object()));

            Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("predicate"), exception.Message);
        }
        public void AverageThrowsArgumentExceptionForNullColumnAlias()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);

            var exception = Assert.Throws<ArgumentException>(
                () => sqlBuilder.Average("Column", null));

            Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("columnAlias"), exception.Message);
        }
        public void AndWhereThrowsArgumentExceptionForEmptyColumn()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);

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

            Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("column"), exception.Message);
        }
        public void AndWhereThrowsArgumentNullExceptionForEmptyArgs()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);

            var exception = Assert.Throws<ArgumentNullException>(
                () => sqlBuilder.AndWhere("x = ?", null));

            Assert.Equal("args", exception.ParamName);
        }
        public void SelectDistinctColumnWithSqlCharacters()
        {
            var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, (string)null);

            var sqlQuery = sqlBuilder
                .Distinct("CreditLimit")
                .From(typeof(Customer))
                .ToSqlQuery();

            Assert.Equal("SELECT DISTINCT [CreditLimit] FROM [Sales].[Customers]", sqlQuery.CommandText);
            Assert.Equal(0, sqlQuery.Arguments.Count);
        }
        public void SelectDistinctColumn()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, (string)null);

            var sqlQuery = sqlBuilder
                .Distinct("CreditLimit")
                .From(typeof(Customer))
                .ToSqlQuery();

            Assert.Equal("SELECT DISTINCT CreditLimit FROM Sales.Customers", sqlQuery.CommandText);
            Assert.Equal(0, sqlQuery.Arguments.Count);
        }
        public void SelectFromWhereOr()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1", "Column2");

            var sqlQuery = sqlBuilder
                .From("Table")
                .Where("Column1").IsEqualTo("Foo")
                .OrWhere("Column2").IsEqualTo("Bar")
                .ToSqlQuery();

            Assert.Equal("SELECT Column1,Column2 FROM Table WHERE (Column1 = ?) OR (Column2 = ?)", 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.String, sqlQuery.Arguments[1].DbType);
            Assert.Equal("Bar", sqlQuery.Arguments[1].Value);
        }
        public void NotBetweenThrowsArgumentExceptionForNullUpper()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);

            var exception = Assert.Throws<ArgumentNullException>(
                () => sqlBuilder.NotBetween(1, null));

            Assert.Equal("upper", exception.ParamName);
        }
        public void SelectFromSpecifyingColumnsAndTableNameWithSqlCharacters()
        {
            var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1", "Column2");

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

            Assert.Equal("SELECT [Column1],[Column2] FROM [Table]", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectFromOrderByDescendingWithSqlCharacters()
        {
            var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Column1", "Column2");

            var sqlQuery = sqlBuilder
                .From("Table")
                .OrderByDescending("Column1", "Column2")
                .ToSqlQuery();

            Assert.Equal("SELECT [Column1],[Column2] FROM [Table] ORDER BY [Column1] DESC,[Column2] DESC", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectAverageWithOtherColumn()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Id");

            var sqlQuery = sqlBuilder
                .Average("CreditLimit")
                .From(typeof(Customer))
                .Where("CustomerStatusId").IsEqualTo(CustomerStatus.Active)
                .GroupBy("CustomerStatusId")
                .ToSqlQuery();

            Assert.Equal("SELECT Id,AVG(CreditLimit) AS CreditLimit FROM Sales.Customers WHERE (CustomerStatusId = ?) GROUP BY CustomerStatusId", sqlQuery.CommandText);

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

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType);
            Assert.Equal(CustomerStatus.Active, sqlQuery.Arguments[0].Value);
        }
        public void SelectAverageWithAlias()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, (string)null);

            var sqlQuery = sqlBuilder
                .Average("CreditLimit", columnAlias: "AverageCreditLimit")
                .From(typeof(Customer))
                .Where("CustomerStatusId").IsEqualTo(CustomerStatus.Active)
                .ToSqlQuery();

            Assert.Equal("SELECT AVG(CreditLimit) AS AverageCreditLimit FROM Sales.Customers WHERE (CustomerStatusId = ?)", sqlQuery.CommandText);

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

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType);
            Assert.Equal(CustomerStatus.Active, sqlQuery.Arguments[0].Value);
        }
        public void OrderByDescendingThrowsArgumentNullException()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId");

            Assert.Throws<ArgumentException>(
                () => sqlBuilder.From("Customer").OrderByDescending((string)null));

            Assert.Throws<ArgumentException>(
                () => sqlBuilder.From("Customer").OrderByDescending(string.Empty));

            Assert.Throws<ArgumentNullException>(
                () => sqlBuilder.From("Customer").OrderByDescending((string[])null));
        }
        public void OrderByDescendingAppendsColumnsCorrectlyForSingleColumn()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId");

            var sqlQuery = sqlBuilder.From("Customer")
                .OrderByDescending("CustomerId")
                .ToSqlQuery();

            Assert.Equal("SELECT CustomerId FROM Customer ORDER BY CustomerId DESC", sqlQuery.CommandText);
        }
        public void OrderByAscendingAppendsColumnsCorrectlyForMultipleColumns()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId");

            var sqlQuery = sqlBuilder.From("Customer")
                .OrderByAscending("FirstName", "LastName")
                .ToSqlQuery();

            Assert.Equal("SELECT CustomerId FROM Customer ORDER BY FirstName ASC,LastName ASC", sqlQuery.CommandText);
        }
        public void NotInThrowsArgumentNullExceptionForNullSqlQuery()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerId");

            Assert.Throws<ArgumentNullException>(
                () => sqlBuilder.From("Customer").Where("Column").NotIn((SqlQuery)null));
        }
        public void NotExistsThrowArgumentNullExceptionForNullSqlQuery()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);

            var exception = Assert.Throws<ArgumentNullException>(
                () => sqlBuilder.From("Customer").Where().NotExists(null));

            Assert.Equal("subQuery", exception.ParamName);
        }
        public void SelectChainingMultipleFunctions()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1");

            var sqlQuery = sqlBuilder
                .Count("Column2", "Col2")
                .Max("Column3", "Col3")
                .From("Table")
                .ToSqlQuery();

            Assert.Equal("SELECT Column1,COUNT(Column2) AS Col2,MAX(Column3) AS Col3 FROM Table", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectFromOrderByDescendingThenAscending()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1", "Column2");

            var sqlQuery = sqlBuilder
                .From("Table")
                .OrderByDescending("Column1")
                .OrderByAscending("Column2")
                .ToSqlQuery();

            Assert.Equal("SELECT Column1,Column2 FROM Table ORDER BY Column1 DESC,Column2 ASC", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectFromSpecifyingColumnsAndTypeWithSqlCharacters()
        {
            var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, "Name", "DateOfBirth");

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

            Assert.Equal("SELECT [Name],[DateOfBirth] FROM [Sales].[Customers]", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectFromSpecifyingColumnsAndTableName()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1", "Column2");

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

            Assert.Equal("SELECT Column1,Column2 FROM Table", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectFromTypeWithSqlCharacters()
        {
            var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance);

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

            Assert.Equal("SELECT [Created],[CreditLimit],[DateOfBirth],[Id],[Name],[CustomerStatusId],[Updated],[Website] FROM [Sales].[Customers]", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectFromSpecifyingColumnsAndType()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Name", "DateOfBirth");

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

            Assert.Equal("SELECT Name,DateOfBirth FROM Sales.Customers", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void MinThrowsArgumentExceptionForNullColumn()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);

            var exception = Assert.Throws<ArgumentException>(
                () => sqlBuilder.Min(null));

            Assert.Equal(ExceptionMessages.ArgumentNullOrEmpty.FormatWith("columnName"), exception.Message);
        }
        public void SelectFromType()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty);

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

            Assert.Equal("SELECT Created,CreditLimit,DateOfBirth,Id,Name,CustomerStatusId,Updated,Website FROM Sales.Customers", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectCountWithAlias()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, (string)null);

            var sqlQuery = sqlBuilder
                .Count("Id", columnAlias: "CustomerCount")
                .From(typeof(Customer))
                .ToSqlQuery();

            Assert.Equal("SELECT COUNT(Id) AS CustomerCount FROM Sales.Customers", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
        public void SelectFromWhereComplex()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "Column1", "Column2", "Column3");

            var sqlQuery = sqlBuilder
                .From("Table")
                .Where("Column1 = @p0 OR @p0 IS NULL", "Foo")
                .AndWhere("Column2 BETWEEN @p0 AND @p1", DateTime.Today.AddDays(-1), DateTime.Today)
                .OrWhere("Column3 IN (@p0, @p1, @p2, @p3)", 1, 2, 3, 4)
                .ToSqlQuery();

            Assert.Equal("SELECT Column1,Column2,Column3 FROM Table WHERE (Column1 = @p0 OR @p0 IS NULL) AND (Column2 BETWEEN @p1 AND @p2) OR (Column3 IN (@p3, @p4, @p5, @p6))", sqlQuery.CommandText);

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

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

            Assert.Equal(DbType.DateTime, sqlQuery.Arguments[1].DbType);
            Assert.Equal(DateTime.Today.AddDays(-1), sqlQuery.Arguments[1].Value);

            Assert.Equal(DbType.DateTime, sqlQuery.Arguments[2].DbType);
            Assert.Equal(DateTime.Today, sqlQuery.Arguments[2].Value);

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

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

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

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[6].DbType);
            Assert.Equal(4, sqlQuery.Arguments[6].Value);
        }
        public void SelectCountWithOtherColumn()
        {
            var sqlBuilder = new SelectSqlBuilder(SqlCharacters.Empty, "CustomerStatusId");

            var sqlQuery = sqlBuilder
                .Count("Id")
                .From(typeof(Customer))
                .GroupBy("CustomerStatusId")
                .ToSqlQuery();

            Assert.Equal("SELECT CustomerStatusId,COUNT(Id) AS Id FROM Sales.Customers GROUP BY CustomerStatusId", sqlQuery.CommandText);
            Assert.Empty(sqlQuery.Arguments);
        }
Example #29
0
        /// <summary>
        /// Builds an SqlQuery to select the database record with the specified identifier for the type specified by the IObjectInfo.
        /// </summary>
        /// <param name="objectInfo">The object information.</param>
        /// <param name="identifier">The identifier of the instance to select.</param>
        /// <returns>
        /// The created <see cref="SqlQuery" />.
        /// </returns>
        public SqlQuery BuildSelectSqlQuery(IObjectInfo objectInfo, object identifier)
        {
            if (objectInfo == null)
            {
                throw new ArgumentNullException("objectInfo");
            }

            string selectCommand;

            if (!this.selectCommandCache.TryGetValue(objectInfo.ForType, out selectCommand))
            {
                var selectSqlQuery = new SelectSqlBuilder(this.SqlCharacters)
                    .From(objectInfo)
                    .Where(objectInfo.TableInfo.IdentifierColumn.ColumnName).IsEqualTo(identifier)
                    .ToSqlQuery();

                var newSelectCommandCache = new Dictionary<Type, string>(this.selectCommandCache);
                newSelectCommandCache[objectInfo.ForType] = selectSqlQuery.CommandText;

                this.selectCommandCache = newSelectCommandCache;

                return selectSqlQuery;
            }

            return new SqlQuery(selectCommand, identifier);
        }
        public void SelectCountWithSqlCharacters()
        {
            var sqlBuilder = new SelectSqlBuilder(MsSqlCharacters.Instance, (string)null);

            var sqlQuery = sqlBuilder
                .Count("Id")
                .From(typeof(Customer))
                .Where("CustomerStatusId").IsEqualTo(CustomerStatus.Active)
                .ToSqlQuery();

            Assert.Equal("SELECT COUNT([Id]) AS Id FROM [Sales].[Customers] WHERE ([CustomerStatusId] = @p0)", sqlQuery.CommandText);

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

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[0].DbType);
            Assert.Equal(CustomerStatus.Active, sqlQuery.Arguments[0].Value);
        }