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

簡介

本文主要介紹使用 利用 SqlSugar 來實作多資料庫的維護 ，動态建類CRUD，動态建表 ，全局過濾器 ，跨庫查詢等功能

1、建立表

SqlSugar支援了3種模式的建表(無實體建表、實體建表，實體特性建表)，非常的靈活

可以多個資料庫 MYSQL MSSQL ORACLE SQLITE PGSQL 等用同一文法建立資料庫，不需要考慮資料庫的相容性

中間标準：

string 大文本

5.1.3.44-preview06 推薦

[SugarColumn(ColumnDataType = StaticConfig.CodeFirst_BigString)]

string 設定長度的字元串

[SugarColumn(Length=10)]

public string FieldName{ get; set; }

int 整數

public int FieldName{ get; set; }

short 整數小

public short FieldName{ get; set; }

long 大數字

public long FieldName{ get; set; }

bool 真假

public bool FieldName{ get; set; }

decimal 預設

public decimal FieldName{ get; set; }

decimal 自定義

//18,2 18,4 18,6 這幾種相容性好

[SugarColumn(Length=18,DecimalDigits=2)]

public decimal FieldName{ get; set; }

DateTime 時間

public DateTime FieldName{ get; set; }

枚舉 （資料庫存int）

public 枚舉 FieldName{ get; set; }

byte[] 二進制

public byte[] FileInfo{get;set;}

建議：更新到 SqlSugarCore 5.1.3.46-preview09 及以上

對多庫支援了比較好

SqlServer特殊配置：和他庫不同一般選用Nvarchar，可以使用這個配置讓他和其他資料庫區分（其他庫是varchar）

DbType = SqlSugar.DbType.SqlServer,

ConnectionString ="字元串",

IsAutoCloseConnection = true,

MoreSettings=new ConnMoreSettings() {

SqlServerCodeFirstNvarchar= true,

}

1.1通過特性建表

我們可以通過建立實體來進行建表

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));

建表特性如下

IsIdentity是否建立自增辨別

IsPrimaryKey是否建立主鍵辨別

ColumnName建立資料庫字段的名稱（預設取實體類屬性名稱）

ColumnDataType

建立資料庫字段的類型

用法1： “varchar(20)” 不需要設定長度

用法2: 不設定該參數 系統會根據C#類型自動生成相應的資料庫類型

用法3: 多庫相容可以用 ：看标題9

IsIgnoreORM不處理該列

ColumnDescription備注 表注釋 （新版本支援XML檔案）

Length長度 設成10會生成 xxx類型(10), 沒括号的不設定

IsNullable是否可以為null默為false

DecimalDigits精度 如 decimal(18,2) length=18,DecimalDigits=2

OracleSequenceName設定Oracle序列，設定後該列等同于自增列

OldColumnName修改列名用，這樣不會新增或者删除列

IndexGroupNameList已棄用 ，新用法看文檔4.3

UniqueGroupNameList已棄用， 新用法看文檔4.3

注意：有2個屬性用處不同

DefaultValue

IsOnlyIgnoreInsert

DefaultValue=預設值 用來建表設定字段預設值

IsOnlyIgnoreInsert=true 插入資料時取預設值

很多情況需要2個一起使用

如果隻建表不插入資料用1個

如果建表并且插入資料用2個

名稱描述

　2.2無特性建表

如果我們的實體不需要加特性，那麼我們可以通過特性方式建表

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 相同實體隻會執行一次性不需太操作

1.3 無實體建表

功能與實體建類一模一樣，如果使用SqlSugar中間标準可以支援多個資料庫一套代碼建表

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);

3、資料庫維護

SqlSugar有一套資料庫維護API,并且能夠很好的支援多種資料庫，例如備份資料庫等常用功能

//例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)//驗證表名是否緩存不走緩存

是以API

GetDataBaseList擷取所有資料庫名稱List

GetViewInfoList查詢所有視圖List

GetTableInfoList擷取所有表，查詢所有的表 （GetTableInfoList（是否緩存））List

GetColumnInfosByTableName

擷取列根據表名,擷取字段，字段信

息GetColumnInfosByTableName(表名,是否緩存)

List

GetIsIdentities擷取自增列List

GetPrimaries擷取主鍵List

IsAnyTable表是否存在,判斷表存不存在 （ IsAny(表名，是否緩存)）bool

IsAnyColumn列是否存在bool

IsPrimaryKey主鍵是否存在bool

IsIdentity自增是否存在bool

IsAnyConstraint限制是否存在bool

DropTable删除表bool

TruncateTable清空表bool

CreateTable

看标題 1.1，1.2，1.3

bool

AddColumn添加列bool

UpdateColumn更新列bool

AddPrimaryKey添加主鍵bool

DropConstraint删除限制bool

BackupDataBase備份庫bool

DropColumn删除列bool

RenameColumn重命名列bool

AddDefaultValue添加預設值bool

AddTableRemark添加表描述,表注釋bool

AddColumnRemark添加列描述,表注釋bool

DeleteColumnRemark删除列描述,表注釋bool

RenameTable重命名表bool

CreateIndex建立索引，唯一限制（唯一索引） bool

IsAnyIndex索引是否存在 bool

GetIndexList擷取所有索引

GetProcList擷取所有存儲過程

2、跨庫支援

可以自動識别在哪個庫

實體

[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()　

　隻要實體配置了資料庫，就不要考慮換庫了，直接使用，并且支援事務

3、過濾器

SqlSugar支援了全新的過濾器，可以是接口，內建該接口的類都生效，支援多表查詢

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();//還原過濾器 (适合下面代碼還需要過濾器情況)

4、子查詢更新

1. ToList() 可以直接查詢一個對象

2. First() 可以直接查單個對象

3.ToList(it=>it.Id) 可以查List<int>一個字段集合

4..SelectStringJoin(z => z.Name, ",") 子查詢将一列用逗号拼接成字元串

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();

5、自定義類型支援

5.1 自定義類型轉換器

下面隻是講解怎麼定義轉換器，ORM自帶的功能就包含下面功能，隻是用來講解

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　

現有類型支援

5.1 json類型

https://www.donet5.com/Home/Doc?typeId=1232

5.2 枚舉類型

int存儲:直接用就行了

public DbType DcValue { get; set; }

string存儲：高版本如下寫法

[SugarColumn(ColumnDataType="varchar(20)",SqlParameterDbType=typeof(EnumToStringConvert))]

public DbType DcValue { get; set; }

3.資料庫獨有類型支援

看左邊菜單 【資料庫特性】 該菜單下面有 SqlServer菜單或者MySql菜單等 ， 針對不同資料庫都有專門的介紹

6、動态建類，CRUD

如果用DataTable和字典去操作資料庫，那麼在多庫相容上是不夠用的，很多實體功能不能用，比如過濾器和AOP等等

SqlSugar支援了 動态建類，來實作CRUD

//動态建類可以繼承接口有重載
var type = db.DynamicBuilder().CreateClass("table1", new SugarTable()
    {
    })
    .CreateProperty("Id", typeof(int),new SugarColumn() { IsPrimaryKey = true, IsIdentity = true })
    .CreateProperty("Name",typeof(string), new SugarColumn() { })
    .WithCache()//緩存起來根據表名和字段名組合的KEY
    .BuilderType();

    db.CodeFirst.InitTables(type);
            
    var  value= db.DynamicBuilder().CreateObjectByType(type,new Dictionary<string, object>() { { "Id", 1 }, { "Name", "jack" } });
           
    db.InsertableByObject(value).ExecuteCommand();
    db.UpdateableByObject(value).ExecuteCommand();
    db.DeleteableByObject(value).ExecuteCommand();
    db.StorageableByObject(value).ExecuteCommand();//插入或者更新
    db.Queryable<object>().AsType(type).Filter(type).ToList();           

總結

SqlSugar在2021年到2022年大量的開源應用使用了SqlSugar,帶動了SqlSugar的快速發展，我這邊要感謝大家

Admin.NET通用管理平台

ZrAdminNetCore 背景

管理Yi架構(Ruoyi Vue)

SimpleAdmin (new)

vboot-netmagic.net (Vue2.0)

網關采集系統（Blazor）

RuYiAdmin

CoreShop商城

Blog.Core

YuebonCore

企業級架構Furion

WebFirst

騰訊APIJSON.NET

WaterCloud微服務

ViperFamilyBucket應用架構通用背景

SmartSqlWMS倉庫管理系統a

pevolo-apiFytSoaCms

開源項目位址：https://www.donet5.com/Home/Doc?typeId=1215