C# 動態建立類，動態建立表，支援多庫的資料庫維護方案

public class CodeFirstTable1
{
        [SugarColumn(IsIdentity = true, IsPrimaryKey = true)]
        public int Id { get; set; } 
        public string Name { get; set; }
        //ColumnDataType 自定格式的情況 length不要設定 （想要多庫相容看4.2和9）
        [SugarColumn(ColumnDataType = "Nvarchar(255)")]
        public string Text { get; set; }
        [SugarColumn(IsNullable = true)]//可以為NULL
        public DateTime CreateTime { get; set; }
}
 
/***建立單個表***/
db.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(CodeFirstTable1));//這樣一個表就能成功建立了
/***手動建多個表***/
db.CodeFirst.SetStringDefaultLength(200)
.InitTables(typeof(CodeFirstTable1),typeof(CodeFirstTable2)); 

SugarClient db = new SqlSugarClient(new ConnectionConfig()
{
    DbType = DbType.SqlServer,
    ConnectionString = Config.ConnectionString3,
    InitKeyType = InitKeyType.Attribute,
    IsAutoCloseConnection = true,
    ConfigureExternalServices = new ConfigureExternalServices()
    {
        EntityService = (s, p) =>
        {
            //如果是Order實體進行相關設定
            p.IfTable<Order>()
            .UpdateProperty(it => it.id, it =>
            {
                it.IsIdentity = true;
                it.IsPrimarykey = true;
            })
            .UpdateProperty(it => it.Name, it => {
                it.Length = 100;
                it.IsNullable = true;
  
            })
            .OneToOne(it => it.Item, nameof(Order.ItemId));
              
            //如果Custom實體進行相關設定
             p.IfTable<Custom>()
             .UpdateProperty(it => it.id, it =>
             {
                it.IsIdentity = true;
                it.IsPrimarykey = true;
             })
              .UpdateProperty(it => it.Text, it => {
                it.DataType= StaticConfig.CodeFirst_BigString;//支援多庫的MaxString用法
              })
              
                          
            //可以結合全域性邏輯一起使用，下面的和上面的有衝突的話，下面會覆蓋上面的
               
              
            //統一設定 nullable等於isnullable=true
            //低版本C#看標題2.2 
             if(p.IsPrimaryKey==false&&new NullabilityInfoContext()
                         .Create(c).WriteState is NullabilityState.Nullable)
             {
                           p.IsNullable = true;
             }
              
               
               
        }
    }
});
//效能說明：
//EntityService 相同實體只會執行一次性不需太操作

 var type = db.DynamicBuilder().CreateClass("UnitEntityA",
                new SugarTable()
                {
                    TableDescription = "表備註",
                    //DisabledUpdateAll=true 可以禁止更新只建立
                }
            )
           .CreateProperty("Id", typeof(int), new SugarColumn() { IsPrimaryKey = true, IsIdentity = true, ColumnDescription = "列備註" }) 
           .CreateProperty("Name", typeof(string), new SugarColumn() {Length=200, ColumnDescription = "列備註" })
           .BuilderType();
 
            db.CodeFirst.InitTables(type);

//例1 獲取所有表
var tables = db.DbMaintenance.GetTableInfoList(false);//true 走快取 false不走快取
foreach (var table in tables)
{
     Console.WriteLine(table.Description);//輸出表資訊
      
     //獲取列資訊
     //var columns=db.DbMaintenance.GetColumnInfosByTableName("表名",false);
}
 
//例2
db.DbMaintenance.IsAnyTable("tablename",false)//驗證表名是否快取不走快取

 [TenantAttribute("1")]//對應ConfigId
  public class C1Table
  {
     public string Id { get; set; }
  }
    
  [TenantAttribute("2")]
  public class C2Table
  {
      public string Id { get; set; }
  }

//通過ConfigId進行資料庫區分
var db = new SqlSugarClient(new List<ConnectionConfig>()
{
 //這兒聲名所有上下文都生效
 new ConnectionConfig(){ConfigId="0",DbType=DbType.SqlServer,ConnectionString=..,IsAutoCloseConnection=true},
 new ConnectionConfig(){ConfigId="1",DbType=DbType.MySql,ConnectionString=..,IsAutoCloseConnection=true  }
});
//自動跨庫聯表查詢
var query5 = db.QueryableWithAttr<Order>()//如果MySql和SqlServer自動支援同伺服器的跨庫聯表查詢
         .LeftJoin<Custom>   ((o, cus ) => o.CustomId == cus.Id)//多個條件用&&
         .LeftJoin<OrderDetail> ((o, cus, oritem) => o.Id == oritem.OrderId)
         .Where(o => o.Id == 1)  
         .Select((o, cus , oritem) => new ViewOrder { Id = o.Id, CustomName = cus.Name })
         .ToList();  
//手動跨庫聯表查詢 ，這種方式結合dblink可以跨伺服器
var query5 = db.Queryable().As("xxx.表名")
         .LeftJoin<Custom>   ((o, cus ) => o.CustomId == cus.Id ,"yyyy.表名") 
         .LeftJoin<OrderDetail> ((o, cus, oritem) => o.Id == oritem.OrderId ,"zzzz.名錶")
         .Where(o => o.Id == 1)  
         .Select((o, cus , oritem) => new ViewOrder { Id = o.Id, CustomName = cus.Name })
         .ToList(); 

db.InsertableWithAttr(list).Execommand()

db.UpdateableWithAttr(list).Execommand()

db.UpdateableWithAttr(list).Execommand()　

db.QueryFilter
.AddTableFilter<IDeletedFilter>(it => it.IsDeleted==false)//IDeletedFilter是自定義介面,繼承這個介面的實體有效
.AddTableFilterIF<ITenantFilter>(isAdmint==false,it=>it.OrgId==使用者OrgId);//ITenantFilter自定義介面
 
//用例1：單條語句清空，隻影響當前語句
db.Queryable<Order>().ClearFilter().ToList();//所有過濾器都無效
db.Queryable<Order>().ClearFilter<IDeletedFilter>().ToList();//只有IDeletedFilter過濾器無效
db.Queryable<Order>().ClearFilter<IDeletedFilter,ITenantFilter>().ToList();//IDeletedFilter+ITenantFilter無效
   
//用例2：當前上下文清空 ，不會影響其他請求，只是當前請求清空
db.QueryFilter.Clear();
db.QueryFilter.Clear<IDeletedFilter>(); 
 
//用例3：清空並還原 ，不會影響其他請求，只是當前請求清空
db.QueryFilter.ClearAndBackup();//有多個過載 ClearAndBackup<T,T2>();
db.Queryable<Order>().ToList();
db.QueryFilter.Restore();//還原過濾器 (適合下面程式碼還需要過濾器情況)

 var list=db.Queryable<Order>()
 .Select(it => new
         {
           CustomId = it.CustomId,
           OrderId = it.Id,
           OrderName = it.Name,
           CustomList = SqlFunc.Subqueryable<Custom>().Where(c => c.Id == it.CustomId).ToList()
         })
        .ToList();

public class DictionaryConvert : ISugarDataConverter
{
public SugarParameter ParameterConverter<T>(object value, int i)
{
//該功能ORM自帶的IsJson就能實現這裡只是用這個用例來給大家學習
var name = "@myp" + i;
var str = new SerializeService().SerializeObject(value);
return new SugarParameter(name, str);
}

public T QueryConverter<T>(IDataRecord dr, int i)
{
//打斷點偵錯
//該功能ORM自帶的IsJson就能實現這裡只是用這個用例來給大家學習
var str = dr.GetValue(i) + "";
return new SerializeService().DeserializeObject<T>(str);
}
}
//使用自定義轉換器
[SugarColumn(ColumnDataType="varchar(2000)",SqlParameterDbType=typeof(DictionaryConvert))]
public Dictionary<string, object> DcValue { get; set; }//5.1.3.53-preview08　

public DbType DcValue { get; set; }

[SugarColumn(ColumnDataType="varchar(20)",SqlParameterDbType=typeof(EnumToStringConvert))]
public DbType DcValue { get; set; }