With the development of the application, its database is also developing. While old assemblies can be replaced with new ones without much difficulty, changing the database is not so easy. Its data must be preserved. In this case, the easiest way is to run a SQL script that promotes the database to the desired state. It can be written manually, but it is much more convenient to use the possibilities of SQL Server Management Studio and have the scripts generated automatically. A new table can be regenerated. However, not its modification.
This can be used, for example, for the primitive initialization strategy of the Entity Framework, the principle of which can look something like this:
public class ChangeScripts<T> : IDatabaseInitializer<T> where T : DbContext {
public void InitializeDatabase(T context) {
var current = Decimal.Parse(File.ReadAllText("dbversion.config"));
var files = from file in Directory.GetFiles("change scripts", "*.sql")
let version = Decimal.Parse(Path.GetFileNameWithoutExtension(file))
where version > current
let script = File.ReadAllText(file)
select new { version, script };
foreach (var file in files)
context.Database.SqlCommand(file.script);
if (files.Count() > 0) {
var latest = files.Max(f => f.version);
File.WriteAllText("dbversion.config", latest.ToString());
}
}
}The file stores the version of the database and in a certain folder there are change scripts that have the same name as the version number to which the database is promoted. When the database needs to be modified, the change script is only added to the folder. The initialization class finds out the database version, and if it finds a SQL script of a newer version, it promotes the database to this version. Finally, it updates the database version number file.