Exemplo n.º 1
0
        public void PageWithWhereAndOrderByMultiLine()
        {
            var sqlDialect = new MsSql2012Dialect();

            var sqlQuery = new SqlQuery(@"SELECT
 CustomerId,
 Name,
 DateOfBirth,
 CustomerStatusId
 FROM
 Customers
 WHERE
 CustomerStatusId = @p0
 ORDER BY
 Name ASC", CustomerStatus.Active);

            var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));

            Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers WHERE CustomerStatusId = @p0 ORDER BY Name ASC OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY", paged.CommandText);
            Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]);

            Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
            Assert.Equal(0, paged.Arguments[1].Value);

            Assert.Equal(DbType.Int32, paged.Arguments[2].DbType);
            Assert.Equal(25, paged.Arguments[2].Value);
        }
Exemplo n.º 2
0
        public void PageQueryThrowsArgumentNullExceptionForNullSqlCharacters()
        {
            var sqlDialect = new MsSql2012Dialect();

            var exception = Assert.Throws <ArgumentNullException>(
                () => sqlDialect.PageQuery(null, PagingOptions.None));
        }
Exemplo n.º 3
0
        public void GetLimitStringWithSqlComments()
        {
            var d             = new MsSql2012Dialect();
            var limitSqlQuery = d.GetLimitString(new SqlString(" /* criteria query */ SELECT p from lcdtm"), null, new SqlString("2"));

            Assert.That(limitSqlQuery, Is.Not.Null);
            Assert.That(limitSqlQuery.ToString(), Is.EqualTo(" /* criteria query */ SELECT p from lcdtm ORDER BY CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY"));
        }
Exemplo n.º 4
0
        public void OnlyOffsetLimit()
        {
            var d = new MsSql2012Dialect();

            SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"), null, new SqlString("10"));

            Assert.That(str.ToString(), Is.EqualTo("select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_ from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY"));
        }
Exemplo n.º 5
0
        public void BuildSelectInsertIdSqlQueryThrowsArgumentNullExceptionForNullObjectInfo()
        {
            var sqlDialect = new MsSql2012Dialect();

            var exception = Assert.Throws <ArgumentNullException>(
                () => sqlDialect.BuildSelectInsertIdSqlQuery(null));

            Assert.Equal("objectInfo", exception.ParamName);
        }
Exemplo n.º 6
0
        public void GetLimitStringWithInnerOrder()
        {
            var d = new MsSql2012Dialect();

            var str = d.GetLimitString(new SqlString("SELECT * FROM A LEFT JOIN (SELECT top 7 * FROM B ORDER BY name) AS B on A.Name = B.Name"), new SqlString("111"), new SqlString("222"));

            Assert.AreEqual(
                "SELECT * FROM A LEFT JOIN (SELECT top 7 * FROM B ORDER BY name) AS B on A.Name = B.Name ORDER BY CURRENT_TIMESTAMP OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());
        }
Exemplo n.º 7
0
        public void GetLimitString()
        {
            var d = new MsSql2012Dialect();

            SqlString str = d.GetLimitString(new SqlString("select distinct c.Contact_Id as Contact1_19_0_, c.Rating as Rating2_19_0_, c.Last_Name as Last_Name3_19_0, c.First_Name as First_Name4_19_0 from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name"), new SqlString("111"), new SqlString("222"));

            Assert.AreEqual(
                "select distinct c.Contact_Id as Contact1_19_0_, c.Rating as Rating2_19_0_, c.Last_Name as Last_Name3_19_0, c.First_Name as First_Name4_19_0 from dbo.Contact c where COALESCE(c.Rating, 0) > 0 order by c.Rating desc , c.Last_Name , c.First_Name OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT fish.id FROM fish ORDER BY CURRENT_TIMESTAMP OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT DISTINCT fish_.id FROM fish fish_ ORDER BY CURRENT_TIMESTAMP OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_"), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_ ORDER BY CURRENT_TIMESTAMP OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT * FROM fish ORDER BY name OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT fish.id, fish.name FROM fish ORDER BY name DESC"), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT fish.id, fish.name FROM fish ORDER BY name DESC OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT * FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t ORDER BY name DESC"), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT * FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t ORDER BY name DESC OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count FROM fish"), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count FROM fish ORDER BY CURRENT_TIMESTAMP OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT * FROM fish WHERE scales = ? ORDER BY CURRENT_TIMESTAMP OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());

            str = d.GetLimitString(new SqlString("SELECT f.Type, COUNT(DISTINCT f.Name) AS Name FROM Fish f GROUP BY f.Type ORDER BY COUNT(DISTINCT f.Name)"), new SqlString("111"), new SqlString("222"));
            Assert.AreEqual(
                "SELECT f.Type, COUNT(DISTINCT f.Name) AS Name FROM Fish f GROUP BY f.Type ORDER BY COUNT(DISTINCT f.Name) OFFSET 111 ROWS FETCH FIRST 222 ROWS ONLY",
                str.ToString());
        }
Exemplo n.º 8
0
        public void BuildSelectInsertIdSqlQueryForIdentifierStrategySequence()
        {
            ObjectInfo.MappingConvention = new ConventionMappingConvention(
                UnitTest.GetConventionMappingSettings(IdentifierStrategy.Sequence));

            var sqlDialect = new MsSql2012Dialect();

            var sqlQuery = sqlDialect.BuildSelectInsertIdSqlQuery(ObjectInfo.For(typeof(Customer)));

            Assert.Equal("SELECT @@id", sqlQuery.CommandText);
            Assert.Equal(0, sqlQuery.Arguments.Count);
        }
Exemplo n.º 9
0
        private static void VerifyLimitStringForStoredProcedureCalls(string sql)
        {
            var d        = new MsSql2012Dialect();
            var limitSql = d.GetLimitString(new SqlString(sql), null, new SqlString("2"));

            Assert.That(limitSql, Is.Null, "Limit only: {0}", sql);

            limitSql = d.GetLimitString(new SqlString(sql), new SqlString("10"), null);
            Assert.That(limitSql, Is.Null, "Offset only: {0}", sql);

            limitSql = d.GetLimitString(new SqlString(sql), new SqlString("10"), new SqlString("2"));
            Assert.That(limitSql, Is.Null, "Limit and Offset: {0}", sql);
        }
Exemplo n.º 10
0
        public void PageWithNoWhereButOrderBy()
        {
            var sqlDialect = new MsSql2012Dialect();

            var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY CustomerId ASC");

            var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));

            Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY CustomerId ASC OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY", paged.CommandText);

            Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
            Assert.Equal(0, paged.Arguments[0].Value);

            Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
            Assert.Equal(25, paged.Arguments[1].Value);
        }
Exemplo n.º 11
0
        public void PageNonQualifiedWildcardQuery()
        {
            var sqlDialect = new MsSql2012Dialect();

            var sqlQuery = new SqlQuery("SELECT * FROM Customers ORDER BY CustomerId");

            var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));

            Assert.Equal("SELECT * FROM Customers ORDER BY CustomerId OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY", paged.CommandText);

            Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
            Assert.Equal(0, paged.Arguments[0].Value);

            Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
            Assert.Equal(25, paged.Arguments[1].Value);
        }
Exemplo n.º 12
0
        public void PageAppendsOrderByGetDateIfNoOrderByClause()
        {
            var sqlDialect = new MsSql2012Dialect();

            var sqlQuery = new SqlQuery("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers");

            var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));

            Assert.Equal("SELECT CustomerId, Name, DateOfBirth, CustomerStatusId FROM Customers ORDER BY CURRENT_TIMESTAMP OFFSET @p0 ROWS FETCH NEXT @p1 ROWS ONLY", paged.CommandText);

            Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
            Assert.Equal(0, paged.Arguments[0].Value);

            Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
            Assert.Equal(25, paged.Arguments[1].Value);
        }
Exemplo n.º 13
0
        public void InsertInstanceQueryForIdentifierStrategyAssigned()
        {
            ObjectInfo.MappingConvention = new ConventionMappingConvention(
                UnitTest.GetConventionMappingSettings(IdentifierStrategy.Assigned));

            var sqlDialect = new MsSql2012Dialect();

            var customer = new Customer
            {
                Created     = new DateTime(2011, 12, 24),
                CreditLimit = 10500.00M,
                DateOfBirth = new System.DateTime(1975, 9, 18),
                Id          = 134875,
                Name        = "Joe Bloggs",
                Status      = CustomerStatus.Active,
                Updated     = DateTime.Now,
                Website     = new Uri("http://microliteorm.wordpress.com")
            };

            var sqlQuery = sqlDialect.BuildInsertSqlQuery(ObjectInfo.For(typeof(Customer)), customer);

            Assert.Equal("INSERT INTO [Sales].[Customers] ([Created],[CreditLimit],[DateOfBirth],[Id],[Name],[CustomerStatusId],[Website]) VALUES (@p0,@p1,@p2,@p3,@p4,@p5,@p6)", sqlQuery.CommandText);
            Assert.Equal(7, sqlQuery.Arguments.Count);

            Assert.Equal(DbType.DateTime, sqlQuery.Arguments[0].DbType);
            Assert.Equal(customer.Created, sqlQuery.Arguments[0].Value);

            Assert.Equal(DbType.Decimal, sqlQuery.Arguments[1].DbType);
            Assert.Equal(customer.CreditLimit, sqlQuery.Arguments[1].Value);

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

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

            Assert.Equal(DbType.String, sqlQuery.Arguments[4].DbType);
            Assert.Equal(customer.Name, sqlQuery.Arguments[4].Value);

            Assert.Equal(DbType.Int32, sqlQuery.Arguments[5].DbType);
            Assert.Equal((int)customer.Status, sqlQuery.Arguments[5].Value);

            Assert.Equal(DbType.String, sqlQuery.Arguments[6].DbType);
            Assert.Equal("http://microliteorm.wordpress.com/", sqlQuery.Arguments[6].Value);
        }
Exemplo n.º 14
0
        public void GetLimitStringWithSqlCommonTableExpression()
        {
            const string SQL = @"
				WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
				(   -- Anchor member definition
					SELECT  ManagerID, EmployeeID, Title, Deptid, 0 AS Level
					FROM    MyEmployees
					WHERE   ManagerID IS NULL
					
					UNION ALL
					
					-- Recursive member definition
					SELECT  e.ManagerID, e.EmployeeID, e.Title, e.Deptid, Level + 1
					FROM    MyEmployees AS e
					INNER JOIN DirectReports AS ON e.ManagerID = d.EmployeeID
				)
				-- Statement that executes the CTE
				SELECT  ManagerID, EmployeeID, Title, Level
				FROM    DirectReports"                ;

            const string EXPECTED_SQL = @"
				WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
				(   -- Anchor member definition
					SELECT  ManagerID, EmployeeID, Title, Deptid, 0 AS Level
					FROM    MyEmployees
					WHERE   ManagerID IS NULL
					
					UNION ALL
					
					-- Recursive member definition
					SELECT  e.ManagerID, e.EmployeeID, e.Title, e.Deptid, Level + 1
					FROM    MyEmployees AS e
					INNER JOIN DirectReports AS ON e.ManagerID = d.EmployeeID
				)
				-- Statement that executes the CTE
				SELECT  ManagerID, EmployeeID, Title, Level
				FROM    DirectReports ORDER BY CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY"                ;

            var d             = new MsSql2012Dialect();
            var limitSqlQuery = d.GetLimitString(new SqlString(SQL), null, new SqlString("2"));

            Assert.That(limitSqlQuery, Is.Not.Null);
            Assert.That(limitSqlQuery.ToString(), Is.EqualTo(EXPECTED_SQL));
        }
Exemplo n.º 15
0
        public void GetDropSequenceString()
        {
            var d = new MsSql2012Dialect();

            Assert.That(d.GetDropSequenceString("[foo].[bar_seq]"), Is.EqualTo("IF EXISTS (SELECT * FROM sys.sequences WHERE object_id = OBJECT_ID(N'[foo].[bar_seq]')) DROP SEQUENCE [foo].[bar_seq]"));
        }