public void TestOracleClientCriteriaExtensionMethods()
{
var table = new TestTable();
var criteria = table.Select();
Assert.AreEqual("CURRENT_TIMESTAMP", criteria.GetCurrentDate().Sql);
}
public void TestSqlClientCriteriaExtensionMethods()
{
var table = new TestTable();
var criteria = table.Select();
var en = criteria.SortByRandom().SortBys.Keys.GetEnumerator();
en.MoveNext();
Assert.AreEqual("newid()", en.Current.ColumnName);
Assert.AreEqual("getdate()", criteria.GetCurrentDate().Sql);
Assert.AreEqual("getutcdate()", criteria.GetCurrentUtcDate().Sql);
}
public void TestCriteriaMembers()
{
var table = new TestTable();
var criteria = table.Select(table.Int32Column, table.StringColumn, table.StringColumn);
Assert.AreEqual(2, criteria.ResultColumns.Count);
criteria.SetIsDistinct(true);
Assert.IsTrue(criteria.IsDistinct);
criteria.SetMaxResults(10).SetMaxResults(10);
Assert.AreEqual(10, criteria.MaxResults);
criteria.SetSkipResults(10).SetSkipResults(10);
Assert.AreEqual(10, criteria.SkipResults);
criteria.SortBy(table.Int32Column, true).ThenSortBy(table.StringColumn, false);
Assert.AreEqual(2, criteria.SortBys.Count);
criteria.And(table.Int32Column == 1 && table.StringColumn.Like("%abc%"))
.Or(!(table.DateTimeColumn < DateTime.Now || table.GuidColumn != new Guid()));
Assert.AreEqual(2, criteria.Conditions.Count);
Assert.AreEqual(2, criteria.ConditionAndOrs.Count);
var serializer = new DataContractSerializer(typeof(QueryCriteria));
var stream = new MemoryStream();
serializer.WriteObject(stream, criteria);
stream.Seek(0, SeekOrigin.Begin);
var deserializedCriteria = (QueryCriteria)serializer.ReadObject(stream);
Assert.AreEqual(criteria.ConditionAndOrs.Count, deserializedCriteria.ConditionAndOrs.Count);
Assert.AreEqual(criteria.Conditions.Count, deserializedCriteria.Conditions.Count);
Assert.AreEqual(criteria.IsDistinct, deserializedCriteria.IsDistinct);
Assert.AreEqual(criteria.MaxResults, deserializedCriteria.MaxResults);
Assert.AreEqual(criteria.ResultColumns.Count, deserializedCriteria.ResultColumns.Count);
Assert.AreEqual(criteria.SkipResults, deserializedCriteria.SkipResults);
Assert.AreEqual(criteria.SortBys.Count, deserializedCriteria.SortBys.Count);
}
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);
}
