Beispiel #1
0
        public void TestSqlQueryCommandFactory()
        {
            var table    = new TestTable();
            var criteria = table.Select();
            var fac      = new QueryCommandFactory();

            criteria.SetMaxResults(10);
            criteria.SortBy(table.Int32Column, true).ThenSortBy(table.StringColumn, false);
            criteria.And(table.Int32Column == null).Or(table.StringColumn.Like("test"));

            var predefinedColumnsCmd = fac.CreateCommand(criteria, false);

            Assert.AreEqual("SELECT TOP 10 [BooleanColumn], [ByteColumn], [Int16Column], [Int32Column], [Int64Column], [DateTimeColumn], [StringColumn], [GuidColumn], [DoubleColumn], [DecimalColumn] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p1 ORDER BY [Int32Column] DESC, [StringColumn]", predefinedColumnsCmd.CommandText);
            Assert.AreEqual(1, predefinedColumnsCmd.Parameters.Count);
            Assert.AreEqual("SELECT COUNT(1) FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p1", fac.CreateCommand(criteria, true).CommandText);

            criteria.Select((table.Int32Column + 1).As("ID"), (table.StringColumn.As("Name")));
            var noPagingCmd = fac.CreateCommand(criteria, false);

            Assert.AreEqual("SELECT TOP 10 [Int32Column] + @p1 AS [ID], [StringColumn] AS [Name] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p2 ORDER BY [Int32Column] DESC, [StringColumn]", noPagingCmd.CommandText);
            Assert.AreEqual(2, noPagingCmd.Parameters.Count);
            Assert.AreEqual("SELECT COUNT(1) FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p1", fac.CreateCommand(criteria, true).CommandText);

            criteria.SetSkipResults(10);
            var pagingCmd = fac.CreateCommand(criteria, false);

            Assert.AreEqual("WITH [__T] AS (SELECT TOP 20 [Int32Column] + @p1 AS [ID], [StringColumn] AS [Name], ROW_NUMBER() OVER (ORDER BY [Int32Column] DESC, [StringColumn]) AS [__Pos] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p2) SELECT [__T].[ID], [__T].[Name] FROM [__T] WHERE [__T].[__Pos] > 10 AND [__T].[__Pos] <= 20", pagingCmd.CommandText);
            Assert.AreEqual(2, pagingCmd.Parameters.Count);
            Assert.AreEqual("SELECT COUNT(1) FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p1", fac.CreateCommand(criteria, true).CommandText);

            criteria.SetIsDistinct(true);

            pagingCmd = fac.CreateCommand(criteria, false);
            Assert.AreEqual("WITH [__T] AS (SELECT DISTINCT TOP 20 [Int32Column] + @p1 AS [ID], [StringColumn] AS [Name], ROW_NUMBER() OVER (ORDER BY [Int32Column] DESC, [StringColumn]) AS [__Pos] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p2) SELECT [__T].[ID], [__T].[Name] FROM [__T] WHERE [__T].[__Pos] > 10 AND [__T].[__Pos] <= 20", pagingCmd.CommandText);
            Assert.AreEqual(2, pagingCmd.Parameters.Count);
            Assert.AreEqual("SELECT COUNT(1) FROM (SELECT DISTINCT [Int32Column] + @p1 AS [ID], [StringColumn] AS [Name] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p2) [__T]", fac.CreateCommand(criteria, true).CommandText);

            Assert.AreEqual(1, pagingCmd.Parameters[0].Value);
            Assert.AreEqual("test", pagingCmd.Parameters[1].Value);

            pagingCmd.Parameters[0].Value = null;

            pagingCmd = fac.CreateCommand(criteria, false);
            Assert.AreEqual(1, pagingCmd.Parameters[0].Value);
            Assert.AreEqual("test", pagingCmd.Parameters[1].Value);

            Assert.AreNotEqual(pagingCmd, fac.CreateCommand(criteria, false));


            var insertCriteria =
                table.Insert(table.Int32Column.Set(1), table.DateTimeColumn.Set(DateTime.Now));
            var insertCmd = fac.CreateCommand(insertCriteria, false);

            Assert.AreEqual("INSERT INTO [TestTable] ([Int32Column], [DateTimeColumn]) VALUES (@p1, @p2)", insertCmd.CommandText);
            Assert.AreEqual(2, insertCmd.Parameters.Count);

            var updateCriteria =
                table.Update(table.Int32Column.Set(1), table.DateTimeColumn.Set(DateTime.Now)).Where(table.GuidColumn == Guid.NewGuid());
            var updateCmd = fac.CreateCommand(updateCriteria, false);

            Assert.AreEqual("UPDATE [TestTable] SET [Int32Column] = @p1, [DateTimeColumn] = @p2 WHERE [GuidColumn] = @p3", updateCmd.CommandText);
            Assert.AreEqual(3, updateCmd.Parameters.Count);

            var deleteCriteria =
                table.Delete(table.GuidColumn == Guid.NewGuid());
            var deleteCmd = fac.CreateCommand(deleteCriteria, false);

            Assert.AreEqual("DELETE FROM [TestTable] WHERE [GuidColumn] = @p1", deleteCmd.CommandText);
            Assert.AreEqual(1, deleteCmd.Parameters.Count);
        }