public void PageWithWhereAndOrderByMultiLine()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery(@"SELECT
[Customers].[CustomerId],
[Customers].[Name],
[Customers].[DoB],
[Customers].[StatusId]
FROM
[Sales].[Customers]
WHERE
[Customers].[StatusId] = @p0
ORDER BY
[Customers].[Name] ASC", CustomerStatus.Active);
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT [Customers].[CustomerId], [Customers].[Name], [Customers].[DoB], [Customers].[StatusId],ROW_NUMBER() OVER(ORDER BY [Customers].[Name] ASC) AS RowNumber FROM [Sales].[Customers] WHERE [Customers].[StatusId] = @p0) AS [MicroLitePagedResults] WHERE (RowNumber >= @p1 AND RowNumber <= @p2)", paged.CommandText);
Assert.Equal(sqlQuery.Arguments[0], paged.Arguments[0]);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(1, paged.Arguments[1].Value);
Assert.Equal(DbType.Int32, paged.Arguments[2].DbType);
Assert.Equal(25, paged.Arguments[2].Value);
}
public void PageQueryWithSubQuery()
{
var sqlDialect = new MsSql2005Dialect();
SqlCharacters.Current = MsSqlCharacters.Instance;
var sqlQuery = SqlBuilder
.Select("*")
.From(typeof(Customer))
.Where("Name LIKE @p0", "Fred%")
.AndWhere("SourceId").In(new SqlQuery("SELECT SourceId FROM Source WHERE Status = @p0", 1))
.ToSqlQuery();
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 2, resultsPerPage: 10));
Assert.Equal("SELECT * FROM (SELECT [Created],[CreditLimit],[DateOfBirth],[Id],[Name],[CustomerStatusId],[Updated],[Website],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM [Customers] WHERE (Name LIKE @p0) AND ([SourceId] IN (SELECT SourceId FROM Source WHERE Status = @p1))) AS [MicroLitePagedResults] WHERE (RowNumber >= @p2 AND RowNumber <= @p3)", paged.CommandText);
Assert.Equal(DbType.String, paged.Arguments[0].DbType);
Assert.Equal("Fred%", paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(1, paged.Arguments[1].Value);
Assert.Equal(DbType.Int32, paged.Arguments[2].DbType);
Assert.Equal(11, paged.Arguments[2].Value);
Assert.Equal(DbType.Int32, paged.Arguments[3].DbType);
Assert.Equal(20, paged.Arguments[3].Value);
}
public void GetLimitStringWithSqlComments()
{
var d = new MsSql2005Dialect();
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 TOP (2) p from lcdtm"));
}
public void OnlyOffsetLimit()
{
var d = new MsSql2005Dialect();
var 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 TOP (10) 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"));
}
public void OnlyOffsetLimit()
{
var d = new MsSql2005Dialect();
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"), 0, 10);
System.Console.WriteLine(str);
Assert.That(str.ToString(), Is.EqualTo("select distinct top 10 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"));
}
public void PageQueryThrowsArgumentNullExceptionIfSqlQueryIsNull()
{
var sqlDialect = new MsSql2005Dialect();
var exception = Assert.Throws <ArgumentNullException>(
() => sqlDialect.PageQuery(null, PagingOptions.ForPage(1, 10)));
Assert.Equal("sqlQuery", exception.ParamName);
}
public void GetIfExistsDropConstraintTest_with_quoted_catalog_quoted_schema_quoted_table()
{
var dialect = new MsSql2005Dialect();
const string expected = "if exists (select 1 from [Catalog].sys.objects" +
" where object_id = OBJECT_ID(N'[Catalog].[Schema].[Bar]')" +
" and parent_object_id = OBJECT_ID(N'[Catalog].[Schema].[Foo]'))";
var ifExistsDropConstraint = dialect.GetIfExistsDropConstraint("[Catalog]", "[Schema]", "[Foo]", "Bar");
Assert.That(ifExistsDropConstraint, Is.EqualTo(expected));
}
public void GetIfExistsDropConstraintTest_with_catalog_with_schema()
{
var dialect = new MsSql2005Dialect();
const string expected = "if exists (select 1 from Catalog.sys.objects" +
" where object_id = OBJECT_ID(N'Catalog.Schema.[Bar]')" +
" and parent_object_id = OBJECT_ID(N'Catalog.Schema.Foo'))";
var ifExistsDropConstraint = dialect.GetIfExistsDropConstraint("Catalog", "Schema", "Foo", "Bar");
Assert.That(ifExistsDropConstraint, Is.EqualTo(expected));
}
public void GetIfExistsDropConstraintTest_with_quoted_schema()
{
var dialect = new MsSql2005Dialect();
const string expected = "if exists (select 1 from sys.objects" +
" where object_id = OBJECT_ID(N'[Other].[Bar]')" +
" and parent_object_id = OBJECT_ID(N'[Other].Foo'))";
var ifExistsDropConstraint = dialect.GetIfExistsDropConstraint(null, "[Other]", "Foo", "Bar");
Assert.That(ifExistsDropConstraint, Is.EqualTo(expected));
}
public void GetLimitString()
{
var d = new MsSql2005Dialect();
var 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 TOP (222) Contact1_19_0_, Rating2_19_0_, Last_Name3_19_0, First_Name4_19_0 FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY q_.Rating2_19_0_ DESC, q_.Last_Name3_19_0, q_.First_Name4_19_0) as __hibernate_sort_row FROM (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) as q_) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), new SqlString("111"), new SqlString("222"));
Assert.AreEqual(
"SELECT TOP (222) id FROM (SELECT fish.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), new SqlString("111"), new SqlString("222"));
Assert.AreEqual(
"SELECT TOP (222) id FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM (SELECT DISTINCT fish_.id FROM fish fish_) as q_) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
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 TOP (222) ixx9_ FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM (SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_) as q_) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), new SqlString("111"), new SqlString("222"));
Assert.AreEqual(
"SELECT TOP (222) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
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 TOP (222) id, name FROM (SELECT fish.id, fish.name, ROW_NUMBER() OVER(ORDER BY fish.name DESC) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
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 TOP (222) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name DESC) as __hibernate_sort_row FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
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 TOP (222) *, some_count FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), new SqlString("111"), new SqlString("222"));
Assert.AreEqual(
"SELECT TOP (222) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
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 TOP (222) Type, Name FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT f.Name)) as __hibernate_sort_row FROM Fish f GROUP BY f.Type) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
str.ToString());
}
public void QualifyTableWithoutCatalogAndWithoutSchema()
{
var d = new MsSql2005Dialect();
var t = new Table
{
Name = "name"
};
Assert.That(t.GetQualifiedName(d), Is.EqualTo("name"));
Assert.That(d.Qualify(null, null, "table"), Is.EqualTo("table"));
}
public void GetIfExistsDropConstraintTest_without_schema()
{
MsSql2005Dialect dialect = new MsSql2005Dialect();
Table foo = new Table("Foo");
string expected = "if exists (select 1 from sys.objects" +
" where object_id = OBJECT_ID(N'[Bar]')" +
" AND parent_object_id = OBJECT_ID('Foo'))";
string ifExistsDropConstraint = dialect.GetIfExistsDropConstraint(foo, "Bar");
System.Console.WriteLine(ifExistsDropConstraint);
Assert.AreEqual(expected, ifExistsDropConstraint);
}
public void BuildSelectInsertIdSqlQueryForIdentifierStrategyDbGenerated()
{
ObjectInfo.MappingConvention = new ConventionMappingConvention(
UnitTest.GetConventionMappingSettings(IdentifierStrategy.DbGenerated));
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = sqlDialect.BuildSelectInsertIdSqlQuery(ObjectInfo.For(typeof(Customer)));
Assert.Equal("SELECT SCOPE_IDENTITY()", sqlQuery.CommandText);
Assert.Equal(0, sqlQuery.Arguments.Count);
}
public void SelectAndUpdateStringContainCustomWhere()
{
const string customWhere = "table_name='second'";
var dialect = new MsSql2005Dialect();
var tg = new TableGenerator();
tg.Configure(NHibernateUtil.Int64, new Dictionary <string, string> {
{ "where", customWhere }
}, dialect);
Assert.That(selectSql.GetValue(tg).ToString(), Is.StringContaining(customWhere));
Assert.That(updateSql.GetValue(tg).ToString(), Is.StringContaining(customWhere));
}
private static void VerifyLimitStringForStoredProcedureCalls(string sql)
{
var d = new MsSql2005Dialect();
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);
}
public void NH2809()
{
var d = new MsSql2005Dialect();
string t = d.GetTypeName(new BinarySqlType());
Assert.That(t, Is.EqualTo("VARBINARY(MAX)"));
t = d.GetTypeName(new BinarySqlType(), SqlClientDriver.MaxSizeForLengthLimitedBinary - 1, 0, 0);
Assert.That(t, Is.EqualTo(String.Format("VARBINARY({0})", SqlClientDriver.MaxSizeForLengthLimitedBinary - 1)));
t = d.GetTypeName(new BinarySqlType(), SqlClientDriver.MaxSizeForLengthLimitedBinary + 1, 0, 0);
Assert.That(t, Is.EqualTo("VARBINARY(MAX)"));
}
public void NH1187()
{
var d = new MsSql2005Dialect();
var result = d.GetLimitString(new SqlString("select concat(a.Description,', ', a.Description) as desc from Animal a"), new SqlString("111"), new SqlString("222"));
Assert.AreEqual("SELECT TOP (222) desc FROM (select concat(a.Description,', ', a.Description) as desc, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from Animal a) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row", result.ToString());
// The test use the function "cast" because cast need the keyWork "as" too
var str = d.GetLimitString(new SqlString("SELECT fish.id, cast('astring, with,comma' as string) as bar FROM fish"), new SqlString("111"), new SqlString("222"));
Assert.AreEqual(
"SELECT TOP (222) id, bar FROM (SELECT fish.id, cast('astring, with,comma' as string) as bar, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > 111 ORDER BY query.__hibernate_sort_row",
str.ToString());
}
public void PageNonQualifiedWildcardQuery()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery("SELECT * FROM Customers");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM Customers) AS [MicroLitePagedResults] WHERE (RowNumber >= @p0 AND RowNumber <= @p1)", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(1, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
}
public void PageWithNoWhereButOrderBy()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery("SELECT [CustomerId], [Name], [DoB], [StatusId] FROM [dbo].[Customers] ORDER BY [CustomerId] ASC");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT [CustomerId], [Name], [DoB], [StatusId],ROW_NUMBER() OVER(ORDER BY [CustomerId] ASC) AS MicroLiteRowNumber FROM [dbo].[Customers]) AS [MicroLitePagedResults] WHERE (MicroLiteRowNumber >= @p0 AND MicroLiteRowNumber <= @p1)", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(1, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
}
public void PageWithNoWhereOrOrderBySecondResultsPage()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery("SELECT [Customers].[CustomerId], [Customers].[Name], [Customers].[DoB], [Customers].[StatusId] FROM [Sales].[Customers]");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 2, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT [Customers].[CustomerId], [Customers].[Name], [Customers].[DoB], [Customers].[StatusId],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM [Sales].[Customers]) AS [MicroLitePagedResults] WHERE (RowNumber >= @p0 AND RowNumber <= @p1)", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(26, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(50, paged.Arguments[1].Value);
}
public void PageWithJoinWithAlias()
{
var sqlDialect = new MsSql2005Dialect();
var sqlQuery = new SqlQuery("SELECT [Customers].[CustomerId] AS CustId, [Invoices].[InvoiceId] FROM [Customers] INNER JOIN [Invoices] ON [Invoices].[CustomerId] = [Customers].[CustomerId]");
var paged = sqlDialect.PageQuery(sqlQuery, PagingOptions.ForPage(page: 1, resultsPerPage: 25));
Assert.Equal("SELECT * FROM (SELECT [Customers].[CustomerId] AS CustId, [Invoices].[InvoiceId],ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber FROM [Customers] INNER JOIN [Invoices] ON [Invoices].[CustomerId] = [Customers].[CustomerId]) AS [MicroLitePagedResults] WHERE (RowNumber >= @p0 AND RowNumber <= @p1)", paged.CommandText);
Assert.Equal(DbType.Int32, paged.Arguments[0].DbType);
Assert.Equal(1, paged.Arguments[0].Value);
Assert.Equal(DbType.Int32, paged.Arguments[1].DbType);
Assert.Equal(25, paged.Arguments[1].Value);
}
public void InsertInstanceQueryForIdentifierStrategyAssigned()
{
ObjectInfo.MappingConvention = new ConventionMappingConvention(
UnitTest.GetConventionMappingSettings(IdentifierStrategy.Assigned));
var sqlDialect = new MsSql2005Dialect();
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);
}
public void NH1187()
{
MsSql2005Dialect d = new MsSql2005Dialect();
SqlString result =
d.GetLimitString(new SqlString("select concat(a.Description,', ', a.Description) as desc from Animal a"), 1, 10, -1, -2);
System.Console.WriteLine(result);
Assert.AreEqual("SELECT TOP (?) desc FROM (select concat(a.Description,', ', a.Description) as desc, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row from Animal a) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row", result.ToString());
// The test use the function "cast" because cast need the keyWork "as" too
SqlString str =
d.GetLimitString(new SqlString("SELECT fish.id, cast('astring, with,comma' as string) as bar FROM fish"), 1, 10, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) id, bar FROM (SELECT fish.id, cast('astring, with,comma' as string) as bar, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
}
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 TOP (2) ManagerID, EmployeeID, Title, Level
FROM DirectReports" ;
var d = new MsSql2005Dialect();
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));
}
public void InquireSchema()
{
Dialect.MsSql2005Dialect dialect = new MsSql2005Dialect();
DbConnection conn = new SqlConnection(@"");
conn.Open();
DatabaseMetadata meta = new DatabaseMetadata(conn, dialect);
IDataBaseSchema schema = dialect.GetDataBaseSchema(conn);
var dt = schema.GetTables(null, null, null, new string[0]);
var cols = schema.GetColumns(null, null, null, null);
var keys = schema.GetForeignKeys(null, null, null);
foreach (DataRow r in dt.Rows)
{
var tableMeta = schema.GetTableMetadata(r, true);
Console.WriteLine(string.Format("Table {2}:[{0}].[{1}]", tableMeta.Schema, tableMeta.Name, tableMeta.Catalog));
ITableMetadata tm = meta.GetTableMetadata(tableMeta.Name, tableMeta.Schema, tableMeta.Catalog, false);
IColumnMetadata col = tm.GetColumnMetadata(cols.Rows[0].ItemArray[2] as string);
}
}
public void SupportsSelectInsertedIdentifierReturnsTrue()
{
var sqlDialect = new MsSql2005Dialect();
Assert.True(sqlDialect.SupportsSelectInsertedIdentifier);
}
public TableIdGeneratorParams()
{
ColumnType = NHibernateUtil.Int32;
Dialect = new MsSql2005Dialect();
}
public void GetLimitString()
{
MsSql2005Dialect d = new MsSql2005Dialect();
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"), 1, 10, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) Contact1_19_0_, Rating2_19_0_ FROM (select distinct c.Contact_Id as Contact1_19_0_, c._Rating as Rating2_19_0_, ROW_NUMBER() OVER(ORDER BY c.Rating DESC, c.Last_Name, c.First_Name) as __hibernate_sort_row from dbo.Contact c where COALESCE(c.Rating, 0) > 0) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT fish.id FROM fish"), 1, 10, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) id FROM (SELECT fish.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id FROM fish fish_"), 1, 10, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) id FROM (SELECT DISTINCT fish_.id, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT DISTINCT fish_.id as ixx9_ FROM fish fish_"), 1, 10, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) ixx9_ FROM (SELECT DISTINCT fish_.id as ixx9_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish fish_) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish ORDER BY name"), 5, 15, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT fish.id, fish.name FROM fish ORDER BY name DESC"), 7, 28, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) id, name FROM (SELECT fish.id, fish.name, ROW_NUMBER() OVER(ORDER BY fish.name DESC) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str =
d.GetLimitString(
new SqlString("SELECT * FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t ORDER BY name DESC"), 10, 20, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name DESC) as __hibernate_sort_row FROM fish LEFT JOIN (SELECT * FROM meat ORDER BY weight) AS t) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count FROM fish"), 1, 10, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) *, some_count FROM (SELECT *, (SELECT COUNT(1) FROM fowl WHERE fish_id = fish.id) AS some_count, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
str = d.GetLimitString(new SqlString("SELECT * FROM fish WHERE scales = ", Parameter.Placeholder), 1, 10, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM fish WHERE scales = ?) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
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)"), 1, 10, -1, -2);
System.Console.WriteLine(str);
Assert.AreEqual(
"SELECT TOP (?) Type, Name FROM (SELECT f.Type, COUNT(DISTINCT f.Name) AS Name, ROW_NUMBER() OVER(ORDER BY COUNT(DISTINCT f.Name)) as __hibernate_sort_row FROM Fish f GROUP BY f.Type) as query WHERE query.__hibernate_sort_row > ? ORDER BY query.__hibernate_sort_row",
str.ToString());
}
public void GetLimitStringWithSqlComments()
{
var d = new MsSql2005Dialect();
Assert.Throws <NotSupportedException>(() => d.GetLimitString(new SqlString(" /* criteria query */ SELECT p from lcdtm"), 0, 2));
}
