Recommended way of copying/managing data in my database on startup in .net core/ ef core?

asp.net-core entity-framework-core postman sql-server

Question

The gist of my situation is that I'm dealing with a big database, and depending on the contents of it my code could do all kinds of unexpected things. The data is large, but it's not expected to change heavily any time soon, it's all in a single database on an mssql database on my computer. Let's call it a table of dogs that never change but are essential to me running my tests.

At the moment I'm trying to write a Postman script to "automate manually testing" how my code performs on this database. One issue of that is that the tests that I will be running will leave data in the database outside of the dog table, that I don't want to affect my next test. (Lists of dogs, user accounts, that kinda stuff)

Maybe an obvious solution, from what I can see, would be to have my database with the dogs in a separate database on my computer from the one that's used by my application, and have my application copy that database every time I start the application. Another could be to make a backup of the database before I start the test, and have the database restored to the point where it last was at the end of the test. This will save a significant amount of time (I have lots of dogs), and would allow me to just delete the data that was created as a side effect of my API tests.

However, I'm a beginner when it comes to (MS)SQL. Are these good approaches to solving the problem? How would I go about doing any of this on my database? Can I do this from within ef core?

1
0
3/29/2020 4:40:33 PM

Accepted Answer

Alright guys, thanks for the input!

I managed to solve this by making the backup restore completely separate from whatever I do from EF Core. After more research I found the docs on seeding, although what theyre doing is a bit different from this recommend against this, and recommend you put in the data through a separate application.

Now in my case, I really didn't want a separate application for this that I would have to start every time I want to run E2E tests or do manual testing, so I put this in the Main method of my API (probably bad practice, but it works for what I need). A static seeding function that uses an SqlConnection and SqlCommand to restore the database, nothing complicated once you get there but it took me a while to figure out so figured I'd share for those who are curious.

Note that in order to do the backup, I had to change the connection string for the initial database to be the master database. Appsettings connectionstrings ended up looking like this:


  "ConnectionStrings": {
    "Default": "Server=.\\SQLExpress;AttachDbFilename=c:\\MyDoggos.mdf;Database=MyDoggos; Trusted_Connection=Yes; MultipleActiveResultSets=true",
    "Seeding": "Server=.\\SQLExpress;AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQLEXPRESS\\MSSQL\\DATA\\master.mdf;Database=master; Trusted_Connection=Yes; MultipleActiveResultSets=true"

  },
    public class Program
    {
        public static void Main(string[] args)
        {
            DatabaseSeeder.Seed();
            CreateHostBuilder(args).Build().Run();
        }
    public class DatabaseSeeder
    {
        public static void Seed()
        {
            string environment = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");

            if (environment == "Development")
            {
                string basePath = Directory.GetCurrentDirectory();

                var builder = new ConfigurationBuilder()
.SetBasePath(basePath)
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);

                IConfigurationRoot configuration = builder.Build();

                string connectionString = configuration.GetConnectionString("Seeding");

                var stm = @"RESTORE DATABASE MyDoggos FROM DISK = 'DogsBackup.bak';";

                using (var con = new SqlConnection(connectionString))
                {
                    con.Open();

                    var cmd = new SqlCommand(stm, con);
                    cmd.ExecuteNonQuery();


                }

            }

        }
    }

Note how for the seeding I use the master database as the initial database in the connection string but for EF core I use the MyDoggos database as my connection string for the datacontext. Even though the master database is the initial database, since I specify the MyDoggos database for the query only MyDoggos is affected.

Not sure if you can somehow specify the database to use in a similar way when using EF core, but this is how I ended up working around it, with 2 connection strings.

0
3/30/2020 3:16:28 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow