How to Testing

Sql In Memory

and

apply db changes

Index

Before to start
Boring introduction
How to update sql db changes
Sql In Memory
Summary

Before to start

Testing
Sql Skills
Use any ORM
Entity Framework

In order to understand completly this presentantion it's necesary be familiar with

Boring introduction

Testing Pyramid

bit.ly/automateRegressionTestPlan

Altered

@jecaestevez

Why integration test are important ?

Let's see a bit of history

Ado.net appear in 2005 and Entity Framework in 2007

about 2006

Ado.Net

Between 2005 to 2007

Smart people

about 2007

Entity Framework

Why You Should Use a ORM

Ten advantages of an ORM

Entity Framework

Bulks
Big updates
complex relationships

about 2010

Dapper

how to apply sql changes to db ?

2. EF Migration for Database first

1. Invent a Custom tool to apply

sql changes to db

More existent tools for apply db changes

Database first

other approaches

Databse first

    public class EfDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //TODO Extract connection string to a secret
            optionsBuilder.UseSqlServer(@"Server=.\;Database=EFDatabaseFirstDB;
                    Trusted_Connection=True;MultipleActiveResultSets=true");
        }
    }

Databse first

    public partial class CreateDatabase : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
                IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Items')
                BEGIN
                    DROP TABLE [dbo].[Items]
                END
                CREATE TABLE [dbo].[Items](
	                [id] [int] IDENTITY(1,1) NOT NULL,
	                [Name] [nvarchar](max) NULL,
	                [Description] [nvarchar](max) NULL,
	                [Expiration] [datetime2](7) NOT NULL,
                 CONSTRAINT [PK_Items] 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] TEXTIMAGE_ON [PRIMARY]
            );
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"DROP TABLE [dbo].[Items]);
        }
    }

add migration

PM > add-migration CreateDatabase

Databse first

PM> update-database –verbose

update Database

dotnet ef database update

or

Testing Sql in Memory

EF Core

Database First

public class EfDbContext : DbContext
{
    public DbSet<Item> Items { get; set; }

    public EfDbContext(DbContextOptions<EfDbContext> options) : base(options)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            //TODO Extract connection string to a secret
            optionsBuilder.UseSqlServer(@"Server=.\;Database=EFDatabaseFirstDB;
            Trusted_Connection=True;MultipleActiveResultSets=true");
        }
    }
}

Items

ID|Name|Description

Table

EF Core

Database First

var itemSaved = new Item();

//Arrange
var expirationDay = DateTime.Now.AddYears(1);

//Act
using (var context = new EfDbContext())
{
    var newItem = new Item()
    {
        Name = "Ron Palido",
        Description = "Drink",
        Expiration = expirationDay

    };

    context.Add(newItem);
    context.SaveChanges();

    itemSaved = context.Items.Find(1);
}

//Assert            
Assert.IsNotNull(itemSaved, "Failed -Item not saved");

Saving in

Sql server

EF Core

to the rescue

var options = new DbContextOptionsBuilder<EfDbContext>()
 .UseInMemoryDatabase(databaseName: "InMemory_EFDatabaseFirstDB")
 .Options;

var itemSaved = new Item();

//Arrange
var expirationDay = DateTime.Now.AddYears(1);

//Act
using (var context = new EfDbContext(options))
{
    var newItem = new Item()
    {
        Name = "Ron Palido",
        Description = "Drink",
        Expiration = expirationDay

    };

    context.Add(newItem);
    context.SaveChanges();

    itemSaved = context.Items.Find(1);
}
//Assert            
Assert.IsNotNull(itemSaved, "Failed -Item not saved");

In Memory

Saving

created automatic

DB

wait , wait .. Remember

Sql server feature used will

in Memory

EF Core

sql Lite

Microsoft documentation

var connection = new SqliteConnection("DataSource=:memory:");
    connection.Open();

var options = new DbContextOptionsBuilder<EfDbContext>()
  .UseSqlite(connection)
  .Options;

var itemSaved = new Item();

//Arrange
var expirationDay = DateTime.Now.AddYears(1);

//Act
using (var context = new EfDbContext(options))
{
    var newItem = new Item()
    {
        Name = "Ron Palido", Description = "Drink", Expiration = expirationDay

    };

    context.Add(newItem);
    context.SaveChanges();

    itemSaved = context.Items.Find(1);
}

//Assert            
Assert.IsNotNull(itemSaved, "Failed -Item not saved");

Saving

wait , wait .. Remember

Sql server feature used will

in Memory

Lets run the test creating new DB

from scratch

    [TestClass]
    public class IntegrationTestSqlServerDal
    {
        private DbContextOptions<EfDbContext> _options;

        [TestInitialize]
        public void Init()
        {
            string randomDBName = "EFDatabaseFirstDB" + Guid.NewGuid();

            _options = new DbContextOptionsBuilder<EfDbContext>()
             .UseSqlServer($"Server=.;Database={randomDBName};
                Trusted_Connection=True;MultipleActiveResultSets=true")
             .Options;

            using (var context = new EfDbContext(_options))
            {
                context.Database.Migrate();
            };
        }

    [TestClass]
    public class IntegrationTestSqlServerDal
    {
        private DbContextOptions<EfDbContext> _options;

     
        [TestMethod]
        public void Given_NoItems_Them_AddNewItem()
        {

            var itemSaved = new Item();

            //Arrange
            var expirationDay = DateTime.Now.AddYears(1);

            //Act
            using (var context = new EfDbContext(_options))
            {
                var newItem = new Item()
                {
                    Name = "Ron Palido",
                    Description = "Drink",
                    Expiration = expirationDay

                };

                context.Add(newItem);
                context.SaveChanges();

Saving in

Sql server

Random

new

Different type of test

Sql In Memory
Sql lite
Sql server

do you want learn more?

Let's do a workshop!

Summary

No need custom tool for apply db changes
Run sql data access layer in memory
Be sure all the changes has been applied

Sql In Memory Testing

By Jesús Estévez

Sql In Memory Testing

1,043

Jesús Estévez

Just another simple developer