I work with a legacy Microsoft SQL Server database with many inconsistencies and large tables. When working on a .NET Core API with Entity Framework Core, I had to map tables from the database, which became a long a tedious process. I know there had to be a better way, than writing the configuration by hand. This where I worked to write a small console app to create the config for me.
This app started as a simple ruby script at first. As our team grew I converted it over to a .NET Core console app, so It could be used across our team. The app takes a CSV output of a sp_columns stored procedure from SQL Server database, reads it and outputs the config to the console.
You can check it out here: https://github.com/rebelweb/ConfigBuilder
Your coding skills are your most powerful asset, use them to your advantage. When you face a tedious repetitive task, see if you can write a few lines of code to save hours of time.
Sometimes a project’s requirements change and you need to change the database engine in which you store your data. In my case, I needed to switch from PostgreSQL to Microsoft SQL Server. Changing some configuration is easy, but sometimes moving data can be a challenge.
The main challenge I have faced when moving data between data stores, whether it be from MySQL to PostgreSQL or PostgreSQL to Microsoft SQL Server is the encoding between databases. This how characters are handled, in strings. It can cause issues when moving data, using conventional methods. In addition to it being easier, I find it more enjoyable to use code, rather than spending time manually working with the data.
Now, let’s get down to business looking over a code sample. The code sample below is a simple .NET Core Console that connects to one database via EntityFrameworkCore and stores the data in a List<> data type, to then write it back to the new database.
The DbContext to configure this setup is simple as well. It is just a context with a single property and the entity configuration.
Overall, if you are facing issues migrating data from database engine to another, give your ORM (Object Relational Mapper) a try.
If you want to see the working solution, you can see the full project here, https://github.com/rebelweb/DataConverter