public void SelectCoalesce_EmptyValue_ShouldBeReplaced( )
{
const string sqlInsert = @"INSERT [dbo].[Lookup] ([Key], [Value], [DisplayValue] ) VALUES (N'EPSG', N'EPSG:3857', NULL)";
const string sqlCreate = @"
CREATE TABLE [dbo].[Lookup]
(
[Id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Key] NVARCHAR(50) NOT NULL,
[Value] NVARCHAR(50) NOT NULL,
[DisplayValue] NVARCHAR(MAX) NULL
)";
const string sqlSelect = @"
SELECT [Key]
, [Value]
, COALESCE([DisplayValue],[Value]) as [DisplayValue]
FROM [dbo].[Lookup]
";
using var connection = new MemoryDbConnection();
connection.Execute(sqlCreate);
connection.Execute(sqlInsert);
var result = connection.QuerySingle <LookupEntity>(sqlSelect);
result.DisplayValue.Should().Be(result.Value);
}
public async Task InsertApplication_TooManyValues_ThrowsException( )
{
await using var connection = new MemoryDbConnection( );
await connection.OpenAsync( );
var command = connection.CreateCommand( );
command.CommandText = "INSERT INTO application ([Name],[User]) VALUES (@Name, @User, @DefName)";
command.Parameters.Add(new MemoryDbParameter( )
{
ParameterName = "Name", Value = "Name String"
});
command.Parameters.Add(new MemoryDbParameter( )
{
ParameterName = "User", Value = "User String"
});
command.Parameters.Add(new MemoryDbParameter( )
{
ParameterName = "DefName", Value = "DefName String"
});
await command.PrepareAsync( );
Func <Task> act = async() => { await command.ExecuteNonQueryAsync( ); };
await act.Should( ).ThrowAsync <SqlInsertTooManyValuesException>( );
}
public void WhereClause_IntParameterWithCalculation_ShouldFilter( )
{
var applications = new ApplicationDto[]
{
new ApplicationDto {
Name = "Name1", User = "******", DefName = "Def1"
},
new ApplicationDto {
Name = "Name2", User = "******", DefName = "Def2"
},
new ApplicationDto {
Name = "Name3", User = "******", DefName = "Def3"
},
};
const string sqlInsert = "INSERT INTO application ([Name], [User], [DefName]) VALUES (@Name, @User, @DefName)";
using var connection = new MemoryDbConnection();
connection.GetMemoryDatabase().Clear();
connection.Execute(SqlStatements.SqlCreateTableApplication);
connection.Execute(sqlInsert, applications);
string sqlSelect = $"{SqlStatements.SqlSelectApplication} WHERE Id = @id + 1";
int id = 0;
var appsQueried = connection.Query <ApplicationDto>(sqlSelect, new { id }).ToList();
appsQueried.Count.Should().Be(1);
appsQueried.First().Id.Should().Be(id + 1);
}
public void IsDate_Fixed_IsReturned(string sql, int isDate)
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <int>(sql);
value.Should( ).Be(isDate);
}
public static void InitDb( )
{
var createTablesSql = SqlStatements.SqlCreateTableApplication + "\n"
+ SqlStatements.SqlCreateTableApplicationFeature + "\n"
+ SqlStatements.SqlCreateTableApplicationAction + "\n"
+ SqlStatements.SqlCreateTableTexts;
using var connection = new MemoryDbConnection( );
connection.GetMemoryDatabase( ).Clear( );
connection.Execute(createTablesSql);
for (int appIndex = 0; appIndex < 3; appIndex++)
{
connection.Execute("INSERT INTO application ([Name],[User],[DefName]) VALUES (N'Name String', N'User String', N'DefName String')");
}
for (int applicationId = 1; applicationId <= 3; applicationId++)
{
for (int index = 1; index <= 4; index++)
{
connection.Execute($"INSERT INTO application_action ([Name],[Action],[Order],[fk_application]) VALUES (N'Action {applicationId}-{index}', N'Do Something {applicationId}-{index}', {index}, {applicationId})");
}
connection.Execute($"INSERT INTO application_feature ([fk_application]) VALUES ({applicationId})");
}
}
public void CurrentUser_Fixed_IsReturned( )
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <string>("SELECT CURRENT_USER");
value.Should( ).Be("dbo");
}
public void SystemUser_Fixed_IsReturned( )
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <string>("SELECT SYSTEM_USER");
value.Should( ).NotBeEmpty( );
}
public void ConvertInvalidStringWithFormat_Fixed_OriginalValueIsReturned( )
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <string>("SELECT CONVERT(varchar, 'Invalid', 101);");
value.Should( ).Be("Invalid");
}
public void Cast_Fixed_IntIsReturned( )
{
using var connection = new MemoryDbConnection( );
var scalar = connection.ExecuteScalar <int>("SELECT CAST(14.85 AS int);");
scalar.Should( ).Be(14);
}
public void Connection_NotOpened_CloseOk()
{
var connection = new MemoryDbConnection( );
Action act = () => connection.Close( );
connection.State.Should( ).Be(ConnectionState.Closed);
}
public void TryConvertInvalidStringWithFormat_Fixed_NullIsReturned( )
{
using var connection = new MemoryDbConnection( );
var date = connection.ExecuteScalar <string>("SELECT TRY_CONVERT(varchar, 'Invalid', 101);");
date.Should( ).Be("Invalid");
}
public void CalculatedField_Simple_Succeeds( )
{
using var connection = new MemoryDbConnection( );
connection.GetMemoryDatabase( ).Clear( );
connection.Execute(InitSalesOrderHeader);
var headers = connection.Query <SalesOrderHeaderCalculatedDto>("SELECT TotalDue, SalesOrderID, SubTotal, TaxAmt, Freight FROM [Sales].[SalesOrderHeader]").ToList( );
}
public void CalculatedField_AsYearFromDate_Succeeds( )
{
const string sql = @"
SET DATEFORMAT ymd
GO
/****** Object: Table [Sales].[SalesOrderHeader] Script Date: 7/13/2020 4:17:08 PM ******/
CREATE TABLE [Sales].[SalesOrderHeader](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OrderDate] [datetime] NOT NULL,
[Year] AS DATEPART(year, [OrderDate]),
CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [Sales].[SalesOrderHeader] ([OrderDate]) VALUES ('2018-01-02')
GO
INSERT [Sales].[SalesOrderHeader] ([OrderDate]) VALUES ('2019-01-02')
GO
INSERT [Sales].[SalesOrderHeader] ([OrderDate]) VALUES ('2020-01-02')
GO
";
using var connection = new MemoryDbConnection( );
connection.GetMemoryDatabase( ).Clear( );
connection.Execute(sql);
var headers = connection.Query <SalesOrderHeaderCalculatedDto>("SELECT Year, OrderDate FROM [Sales].[SalesOrderHeader]").ToList( );
foreach (var header in headers)
{
header.Year.Should( ).Be(header.OrderDate.Year);
}
}
public void CalculatedField_AsFixedString_Succeeds( )
{
const string sql = @"
/****** Object: Table [Sales].[SalesOrderHeader] Script Date: 7/13/2020 4:17:08 PM ******/
CREATE TABLE [Sales].[SalesOrderHeader](
[Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SalesOrderID] [int] NOT NULL,
[SalesOrderNumber] AS CONVERT([nchar](23),[SalesOrderID]),
CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [Sales].[SalesOrderHeader] ([SalesOrderID]) VALUES (123)
GO
INSERT [Sales].[SalesOrderHeader] ([SalesOrderID]) VALUES (456)
GO
";
using var connection = new MemoryDbConnection( );
connection.GetMemoryDatabase( ).Clear( );
connection.Execute(sql);
var headers = connection.Query <SalesOrderHeaderCalculatedDto>("SELECT SalesOrderID, SalesOrderNumber FROM [Sales].[SalesOrderHeader]").ToList( );
foreach (var header in headers)
{
header.SalesOrderNumber.Should( ).StartWith(header.SalesOrderID.ToString( ));
header.SalesOrderNumber.Length.Should( ).Be(23);
}
}
public void CoalesceString_Fixed_IsReturned(string sql, string expected)
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <string>(sql);
value.Should( ).Be(expected);
}
public void Cast_Fixed_String4IsReturned( )
{
using var connection = new MemoryDbConnection( );
var scalar = connection.ExecuteScalar <string>("SELECT CAST(15.6 AS varchar(4));");
scalar.Should( ).Be("15.6");
}
public void VariableAtAtVersion_Fixed_IsReturned( )
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <string>("SELECT @@Version");
value.Should( ).StartWith("SQL Memory Database V");
}
public void CastString_Fixed_FloatIsReturned( )
{
using var connection = new MemoryDbConnection( );
var scalar = connection.ExecuteScalar <double>("SELECT CAST('14.85' AS float);");
scalar.Should( ).Be(14.85);
}
public void SessionUser_Fixed_IsReturned( )
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <string>("SELECT SESSION_USER");
value.Should( ).Be("dbo");
}
public void CastStringToDate_Fixed_DateIsReturned( )
{
using var connection = new MemoryDbConnection( );
var date = connection.ExecuteScalar <DateTime>("SELECT CAST('2020-04-05' AS datetime);");
date.Should( ).Be(new DateTime(2020, 4, 5));
}
public void UserName_Fixed_IsReturned( )
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <string>("SELECT USER_NAME()");
value.Should( ).Be("dbo");
}
public void CastStringInvalidToDate_Fixed_ExceptionIsThrows( )
{
using var connection = new MemoryDbConnection( );
Action action = () => connection.ExecuteScalar <DateTime>("SELECT CAST('Invalid' AS datetime);");
action.Should( ).Throw <SqlInvalidCastException>( );
}
public void NullInt_Fixed_IsReturned(string sql, int expected)
{
using var connection = new MemoryDbConnection( );
var value = connection.ExecuteScalar <int>(sql);
value.Should( ).Be(expected);
}
public void TryCastStringInvalidToDate_Fixed_NullIsReturned( )
{
using var connection = new MemoryDbConnection( );
var date = connection.ExecuteScalar <DateTime?>("SELECT TRY_CAST('Invalid' AS datetime);");
date.Should( ).BeNull( );
}
public async Task InsertApplication_CorrectSql_RowIsAdded( )
{
await using var connection = new MemoryDbConnection( );
await connection.OpenAsync( );
var command = connection.CreateCommand( );
command.CommandText = "INSERT INTO application ([Name],[User],[DefName]) VALUES (@Name, @User, @DefName)";
command.Parameters.Add(new MemoryDbParameter( )
{
ParameterName = "Name", Value = "Name String"
});
command.Parameters.Add(new MemoryDbParameter( )
{
ParameterName = "User", Value = "User String"
});
command.Parameters.Add(new MemoryDbParameter( )
{
ParameterName = "DefName", Value = "DefName String"
});
await command.PrepareAsync( );
await command.ExecuteNonQueryAsync( );
var rows = connection.GetMemoryDatabase( ).Tables["dbo.application"].Rows;
rows.Count.Should( ).Be(1, "A new row should be added");
var row = rows.First( );
row[0].Should( ).Be(1);
row[1].Should( ).Be("Name String");
row[2].Should( ).Be("User String");
row[3].Should( ).Be("DefName String");
}
public void Convert_Fixed_IntIsReturned( )
{
using var connection = new MemoryDbConnection( );
var scalar = connection.ExecuteScalar <int>("SELECT CONVERT(int, 14.85);");
scalar.Should( ).Be(14);
}
public void WhereClause_OptionalTestLike_ShouldFilter(string name, int count)
{
var applications = new ApplicationDto[]
{
new ApplicationDto {
Name = "Name1", User = "******", DefName = "Def1"
},
new ApplicationDto {
Name = "Name2", User = "******", DefName = "Def2"
},
new ApplicationDto {
Name = "Name3", User = "******", DefName = "Def3"
},
};
const string sqlInsert = "INSERT INTO application ([Name], [User], [DefName]) VALUES (@Name, @User, @DefName)";
using var connection = new MemoryDbConnection();
connection.GetMemoryDatabase().Clear();
connection.Execute(SqlStatements.SqlCreateTableApplication);
connection.Execute(sqlInsert, applications);
string sqlSelect = $"{SqlStatements.SqlSelectApplication} WHERE @name is NULL OR [Name] LIKE @name";
var appsQueried = connection.Query <ApplicationDto>(sqlSelect, new { name = name }).ToList();
appsQueried.Count.Should().Be(count);
if (count == 1)
{
appsQueried.First().Name.Should().Be(name);
}
}
public void ConvertDateWithFormat_Fixed_StringIsReturned( )
{
using var connection = new MemoryDbConnection( );
var scalar = connection.ExecuteScalar <string>("SELECT CONVERT(varchar, '2014-02-15', 101)");
scalar.Should( ).Be("02/15/2014");
}
public async Task SelectApplication_CorrectSqlSingleRowParameter_RowRead( )
{
await using var connection = new MemoryDbConnection( );
await connection.OpenAsync( );
var command = connection.CreateCommand( );
command.CommandText = "SELECT Id, [Name], [User], DefName AS DefaultName FROM application WHERE Id = @Id";
command.Parameters.Add(new MemoryDbParameter( )
{
ParameterName = "Id", Value = 2
});
await command.PrepareAsync( );
var recordsRead = 0;
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
reader["Id"].Should( ).Be(2);
reader["Name"].Should( ).Be("Name String");
reader["User"].Should( ).Be("User String");
reader["DefaultName"].Should( ).Be("DefName String");
recordsRead++;
}
recordsRead.Should( ).Be(1);
}
public void SelectCoalesce_FilledValue_ShouldNotBeReplaced(string key, int count)
{
const string sqlInsert = @"
INSERT [dbo].[Lookup] ([Key], [Value], [DisplayValue] ) VALUES (N'EPSG', N'EPSG:3857' , N'WGS 84 / Pseudo-Mercator')
INSERT [dbo].[Lookup] ([Key], [Value], [DisplayValue] ) VALUES (N'EPSG', N'EPSG:4230' , N'ED50 / Longitude/latitude')
INSERT [dbo].[Lookup] ([Key], [Value], [DisplayValue] ) VALUES (N'EPSG', N'EPSG:4258' , N'ETRS89 / Longitude/latitude')
";
const string sqlCreate = @"
CREATE TABLE [dbo].[Lookup]
(
[Id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Key] NVARCHAR(50) NOT NULL,
[Value] NVARCHAR(50) NOT NULL,
[DisplayValue] NVARCHAR(MAX) NULL
)";
const string sqlSelect = @"
SELECT [Key]
, [Value]
, COALESCE([DisplayValue],[Value]) as [DisplayValue]
FROM [dbo].[Lookup]
WHERE @key IS NULL OR [Key] = @key
";
using var connection = new MemoryDbConnection();
connection.Execute(sqlCreate);
connection.Execute(sqlInsert);
var result = connection.Query <LookupEntity>(sqlSelect, new { key }).ToList();
result.Count.Should().Be(count);
}