Esempio n. 1
0
        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>( );
        }
Esempio n. 3
0
        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);
        }
Esempio n. 4
0
        public void IsDate_Fixed_IsReturned(string sql, int isDate)
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <int>(sql);

            value.Should( ).Be(isDate);
        }
Esempio n. 5
0
        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})");
            }
        }
Esempio n. 6
0
        public void CurrentUser_Fixed_IsReturned(  )
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <string>("SELECT CURRENT_USER");

            value.Should( ).Be("dbo");
        }
Esempio n. 7
0
        public void SystemUser_Fixed_IsReturned(  )
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <string>("SELECT SYSTEM_USER");

            value.Should( ).NotBeEmpty( );
        }
Esempio n. 8
0
        public void ConvertInvalidStringWithFormat_Fixed_OriginalValueIsReturned(  )
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <string>("SELECT CONVERT(varchar, 'Invalid', 101);");

            value.Should( ).Be("Invalid");
        }
Esempio n. 9
0
        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);
        }
Esempio n. 10
0
        public void Connection_NotOpened_CloseOk()
        {
            var    connection = new MemoryDbConnection( );
            Action act        = () => connection.Close(  );

            connection.State.Should( ).Be(ConnectionState.Closed);
        }
Esempio n. 11
0
        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);
            }
        }
Esempio n. 15
0
        public void CoalesceString_Fixed_IsReturned(string sql, string expected)
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <string>(sql);

            value.Should( ).Be(expected);
        }
Esempio n. 16
0
        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");
        }
Esempio n. 17
0
        public void VariableAtAtVersion_Fixed_IsReturned(  )
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <string>("SELECT @@Version");

            value.Should( ).StartWith("SQL Memory Database V");
        }
Esempio n. 18
0
        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);
        }
Esempio n. 19
0
        public void SessionUser_Fixed_IsReturned(  )
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <string>("SELECT SESSION_USER");

            value.Should( ).Be("dbo");
        }
Esempio n. 20
0
        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));
        }
Esempio n. 21
0
        public void UserName_Fixed_IsReturned(  )
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <string>("SELECT USER_NAME()");

            value.Should( ).Be("dbo");
        }
Esempio n. 22
0
        public void CastStringInvalidToDate_Fixed_ExceptionIsThrows(  )
        {
            using var connection = new MemoryDbConnection( );
            Action action = () => connection.ExecuteScalar <DateTime>("SELECT CAST('Invalid' AS datetime);");

            action.Should( ).Throw <SqlInvalidCastException>( );
        }
Esempio n. 23
0
        public void NullInt_Fixed_IsReturned(string sql, int expected)
        {
            using var connection = new MemoryDbConnection( );
            var value = connection.ExecuteScalar <int>(sql);

            value.Should( ).Be(expected);
        }
Esempio n. 24
0
        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");
        }
Esempio n. 26
0
        public void Convert_Fixed_IntIsReturned(  )
        {
            using var connection = new MemoryDbConnection( );
            var scalar = connection.ExecuteScalar <int>("SELECT CONVERT(int, 14.85);");

            scalar.Should( ).Be(14);
        }
Esempio n. 27
0
        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);
            }
        }
Esempio n. 28
0
        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);
        }
Esempio n. 30
0
        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);
        }