一、Nuget 引入 SqlSugarCo
二、新建实体
using SqlSugar;
namespace Model.DBEntity
{
/// <summary>
/// 国家地区表
/// </summary>
[SugarTable("Country")]//当和数据库名称不一样可以设置表别名 指定表明
public class Country
{
/// <summary>
/// 自增主键ID,可给多个字段设置主键达到双主键、复合主键、联合主键、多个主键
/// </summary>
[SugarColumn(IsPrimaryKey =true, IsIdentity = true,ColumnDescription="主键")]//设置主键,设置自增,字段注释
public int FID { get; set; }
/// <summary>
/// 上级ID
/// </summary>
[SugarColumn(ColumnName = "FPID", ColumnDescription = "上级主键")]//数据库列名与实体不一样,指定数据库字段名
public int FPID { get; set; }
/// <summary>
/// 名称
/// </summary>
[SugarColumn(ColumnDescription = "名称")]
public string? FName { get; set; }
/// <summary>
/// 递归自己
/// </summary>
[SugarColumn(IsIgnore =true, ColumnDescription = "下级集合")]//IsIgnore ORM不处理该列 【忽略】
public List<Country>? CountryList { get; set; }
}
}
三、同步到数据库,控制台代码
using DBSyncTool;
using SqlSugar;
using System.Reflection;
// See https://aka.ms/new-console-template for more information
//数据库账号密码
string user = "sa";
string pwd = "***";
//
//string user = "yfl";
//string pwd = "ydl..yfl";
//打印方法
var Begin = () => Console.WriteLine("┌──────────────────────────────────────────────────────────┐");
var Content = (string context) => Console.WriteLine(@$"│ {context} ");
var End = () => Console.WriteLine("└──────────────────────────────────────────────────────────┘");
var All = (string context) =>
{
Begin();
Content(context);
End();
};
var ReStart = () =>
{
for (int i = 0, m = 1; i < 30; i++)
for (int l = 0; l < new[] { 5, 6, 7, 6, 8, 10, 3, 10, 4, 13, 1, 13, 1, 87, 1, 27, 4, 23, 7, 20, 11, 16, 16, 11, 20, 7, 24, 3, 27, 1 }[i]; l++, m++)
Console.Write((i % 2 > 0 ? "love"[m % 4] : ' ') + (m % 29 > 0 ? "" : "\n"));
Console.WriteLine();
};
//数据库操作类型
string[] _syncarr = { "DBFirst", "CodeFirst", "AddDataBase", "Back" };
//获取的到数据库列表
Fun f = new();
List<string> _dbarr = global::DBSyncTool.Fun.GetDBList(user, pwd);
All("请谨慎做出你的选择,因为这是不可撤销的");
All("CodeFirst需自行代码调整需要同步的Table");
ChooseDB:
Begin();
Content("请选择数据库名称:");
int inx = 0;
foreach (string s in _dbarr)
{
Content(@$"{++inx}:{s}");
}
End();
int? dbstr = 0;
Wdb:
try
{
dbstr = Convert.ToInt32(Console.ReadLine()) - 1;
while (dbstr >= _dbarr.Count || dbstr < 0)
{
All("请输入有效的数据库编号! ");
dbstr = Convert.ToInt32(Console.ReadLine()) - 1;
}
}
catch (Exception)
{
All("请输入有效的数据库编号! ");
goto Wdb;
}
Begin();
Content(@$"【{_dbarr[(int)dbstr]}】");
Content("请选择数据库结构同步方式:");
inx = 0;
foreach (string s in _syncarr)
{
Content(@$"{++inx}:{s}");
}
End();
int? type = 0;
Wsync:
try
{
type = Convert.ToInt32(Console.ReadLine()) - 1;
while (type >= _syncarr.Length || type < 0)
{
Content(@$"【{_dbarr[(int)dbstr]}】");
All("请输入有效的同步方式编号!");
type = Convert.ToInt32(Console.ReadLine()) - 1;
}
}
catch (Exception)
{
Content(@$"【{_dbarr[(int)dbstr]}】");
All("请输入有效的同步方式编号!");
goto Wsync;
}
string? sure = "";
if (_syncarr[(int)type] == "Back")
{
ReStart();
goto ChooseDB;
}
else
{
Begin();
Content($@"请确认您选择的数据库同步方式:【{_dbarr[(int)dbstr]}】【{_syncarr[(int)type]}】");
Content("1 : 确认!开始同步");
Content("2 : 错误!重新选择");
End();
sure = Console.ReadLine();
}
while (string.IsNullOrEmpty(sure) || (sure != "1" && sure != "2"))
{
Content(@$"【{_dbarr[(int)dbstr]}】【{_syncarr[(int)type]}】");
All("请重新输入有效的值!");
sure = Console.ReadLine();
}
if (sure == "1")
{
SqlSugarClient Db = new(new ConnectionConfig()
{
ConnectionString = @$"Server=.;User Id={user};Password={pwd};Database={_dbarr[(int)dbstr]}",//数据库链接字符串
DbType = DbType.SqlServer,//指定数据库类型
IsAutoCloseConnection = true,//链接使用完后是否自动释放
InitKeyType = InitKeyType.Attribute,//从实体特性中读取主键自增列信息
ConfigureExternalServices = new ConfigureExternalServices()
{
EntityNameService = (type, entity) =>
{
entity.IsDisabledDelete = true;//全局禁止删除列
}
}
});
All("开始同步!请勿关闭程序... ");
switch (_syncarr[(int)type])
{
case "DBFirst":
// 应用程序所在目录,即bin/debug
//方法1、Environment.CurrentDirectory 取得或设置当前工作目录的完整限定路径
//方法2、AppDomain.CurrentDomain.BaseDirectory 获取基目录,它由程序集冲突解决程序用来探测程序集
DirectoryInfo exePath = new(Environment.CurrentDirectory);
string pathFullName = exePath.Parent.Parent.Parent.FullName;//项目所在目录,即项目最外层目录
Db.DbFirst.IsCreateAttribute().CreateClassFile(@$"{pathFullName}\\Model\\DBEntity", "Model.DBEntity");//文件地址,指定实体命名空间
break;
case "CodeFirst":
Type[] types = Assembly.LoadFrom("DBSyncTool.dll")//如果 .dll报错,可以换成 xxx.exe 有些生成的是exe
.GetTypes().Where(it => it.FullName.Contains("Model.DBEntity"))//命名空间过滤,当然你也可以写其他条件过滤
.ToArray();//断点调试一下是不是需要的Type,不是需要的在进行过滤
//同步数据表结构
Db.DbMaintenance.CreateDatabase();
Db.CodeFirst.SetStringDefaultLength(1024).InitTables(types);//多表同步
break;
case "AddDataBase":
string? dbs = "";
WDBS:
Content(@$"请输入要新增的数据库名称");
dbs = Console.ReadLine();
if (string.IsNullOrEmpty(dbs))
{
All("请重新输入有效的值!");
goto WDBS;
}
Db = new(new ConnectionConfig()
{
ConnectionString = @$"Server=.;User Id={user};Password={pwd};Database={dbs}",//数据库链接字符串
DbType = DbType.SqlServer,//指定数据库类型
IsAutoCloseConnection = true,//链接使用完后是否自动释放
InitKeyType = InitKeyType.Attribute,//从实体特性中读取主键自增列信息
});
Db.DbMaintenance.CreateDatabase();
break;
}
}
else
{
ReStart();
goto ChooseDB;
}
All("... ... ...");
All("结束同步!已自动关闭程序...");
Console.ReadKey();
!!!请注意如下图中对应的命名空间和文件夹名称