Project scenario:
Using FreeSql, including all ORM databases, there will be such a problem. In codefirst mode, it is recommended not to use it in production environments automatically according to the code. Why?
In fact, it is not recommended to use it. It is mainly because when data is automatically generated based on the code, it is very likely to cause data loss. For example, modifying the field type, the result of automatic update may not be what you think.
However, some usage scenarios require automatic upgrade in production environment, for example
We have a CS client product, which is used locally offline by customers and deployed locally by customers. The database is also a local database. The version is iterated from 1000 to 1100, and 100 versions have been released in the middle. There may be multiple database changes in the middle. Our customers may also be spread all over the country, and the versions are also different. If customers do not have new requirements, they may always use the current old version. They will only upgrade the version if they have new requirements or want to use new features. Therefore, the upgrade time is also uncertain. The upgrade requires customers to install a new version of the software and automatically upgrade after running.
Then it really cannot be used in production environments?
Solution:
Summary description:
The solution is actually automatic upgrade, but it will be automatically upgraded when it is judged that it needs to be upgraded, and the database will be backed up before upgrading.
Specific process
Every time a database is changed in the program, the corresponding version in the program is modified when the version is released. For example, the beginning is 1000, the latest is 1100
Add SysConfig table to the database, the field contains DbVer to indicate the current database version number
Add DbLog table to the database to record the database upgrade log. This option is optional
During the first installation, checking that the database file does not exist, indicating that the first installation is completed. During the first installation, the SysConfig table and DbLog table are created, and the SysConfig table DbVer is updated as the version number recorded in the program. Add DbLog table log
When running again later, first get the SysConfig table DbVer to determine whether it is consistent with the program.
If the database is larger than the program, it means running a lower version of the program, and it can be disabled according to the situation. You can also do not synchronize the database and continue to run, and decide based on actual conditions. If the consistency requirements for program and database are relatively high, the operation can be prohibited.
If the database is smaller than the program, it means that the database needs to be upgraded. At this time, first back up the existing database and then perform synchronous database.
Detailed description:
Just upload the code, it's clearer than anything else
File Code
using ;
using System;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
namespace WindowsClient
{
static class Program
{
/// <summary>
/// Customer database path /// </summary>
private static string CustDbPath = + $"\\database\\";
/// <summary>
/// Database ORM /// </summary>
public static IFreeSql fsql;
/// <summary>
/// Server database version /// </summary>
private static int ServerDbVer = 1000;
/// <summary>
/// The main entry point of the application. /// </summary>
[STAThread]
static void Main()
{
try
{
//Whether the database exists, used to insert initial data, must be judged before freesql instantiation, because the database will be automatically created during instantiation. var custDbPathExists = (CustDbPath);
//deebug automatically synchronizes the entity structure to the database, release manually synchronizes the entity structure to the database, and release bool syncDbStructure = false;
#if DEBUG
syncDbStructure = true;
#endif
fsql = new ()
.UseConnectionString(, $@"Data Source={CustDbPath}; Pooling=true;Min Pool Size=1")
.UseAutoSyncStructure(syncDbStructure) //deebug automatically synchronizes the entity structure to the database, release manually synchronizes the entity structure to the database, and release .UseMonitorCommand(cmd => ($"Thread：{}\r\n"))
.Build(); //Please be sure to define it as Singleton singleton mode
if(syncDbStructure)
{
//It is mainly used in development mode to enable database modification to take effect quickly. If this sentence is not added, it will only be synchronized when the table is used. (GetTypesByTableAttribute());
}
if (custDbPathExists == false)
{
//The database file does not exist, which means it is the first installation (GetTypesByTableAttribute());
var sysConfig = new SysConfig();
= ServerDbVer;
var dbResult = (sysConfig).ExecuteAffrows();
if (dbResult <= 0)
throw new Exception("Initial database failed.");
var row = new DbLog();
= ServerDbVer;
(row).ExecuteAffrows();
}
int localDbVer = <SysConfig>().First().DbVer;
if (localDbVer != ServerDbVer)
{
//The database version is different, it needs to be upgraded //Back up the database (CustDbPath, + $"\\database\\cust{:yyyyMMddHHmmss}.db");
//Upgrade the database (GetTypesByTableAttribute());
var row = new DbLog();
= ServerDbVer;
(row).ExecuteAffrows();
}
();
(false);
(new FrmMain());
}
catch (Exception e)
{
((), "It's wrong", , );
}
}
public static Type[] GetTypesByTableAttribute()
{
List<Type> tableAssembies = new List<Type>();
foreach (Type type in (typeof(IEntity)).GetExportedTypes())
{
foreach (Attribute attribute in ())
{
if (attribute is TableAttribute tableAttribute)
{
if ( == false)
{
(type);
}
}
}
};
return ();
}
}
}
using ;
using System;
using ;
using ;
using ;
using ;
namespace
{
/// <summary>
///
/// </summary>
[Table(Name = "sys_config")]
public class SysConfig : IEntity
{
/// <summary>
/// Primary key /// </summary>
[Column(Name = "id", IsIdentity = true, IsPrimary = true)]
public int Id { get; set; }
/// <summary>
/// Primary key /// </summary>
[Column(Name = "dbVer")]
public int DbVer { get; set; }
/// <summary>
/// Creation time /// </summary>
[Column(ServerTime = , CanUpdate = false)]
public DateTime CreateTime { get; set; }
/// <summary>
/// Modify time /// </summary>
[Column(ServerTime = , CanUpdate = true)]
public DateTime UpdateTime { get; set; }
}
}
using ;
using System;
using ;
using ;
using ;
using ;
namespace
{
/// <summary>
///
/// </summary>
[Table(Name = "db_log")]
public class DbLog : IEntity
{
/// <summary>
/// Primary key /// </summary>
[Column(Name = "id", IsIdentity = true, IsPrimary = true)]
public int Id { get; set; }
/// <summary>
/// Primary key /// </summary>
[Column(Name = "dbVer")]
public int DbVer { get; set; }
/// <summary>
/// Creation time /// </summary>
[Column(ServerTime = , CanUpdate = false)]
public DateTime CreateTime { get; set; }
/// <summary>
/// Modify time /// </summary>
[Column(ServerTime = , CanUpdate = true)]
public DateTime UpdateTime { get; set; }
}
}
Summarize:
In the past, it was handwritten SQL statements, but now it is really much more convenient to use FreeSql.
The above is the detailed content of c# using FreeSql production environment to automatically upgrade the backup database. For more information about c# using FreeSql to automatically upgrade the backup database, please pay attention to my other related articles!