Dapper.Sharding 3.0.30

#sharding for mysql、postgresql、sqlserver、sqlite、clickhouse、oracle

var config = new DataBaseConfig { Server = "127.0.0.1", UserId = "root", Password = "123", Port = 3306 };

//client must be singleton mode(必须是单例模式)
static IClient client = ShardingFactory.CreateClient(DataBaseType.MySql, config);
//static IClient client = ShardingFactory.CreateClient(DataBaseType.SqlServer, config, DataBaseVersion.SqlServer2012);
//client.AutoCreateDatabase = true; //自动创建数据库
//client.AutoCreateTable = true; //自动创建表
//client.AutoCompareTableColumn = false; //是否自动对比列
//client.AutoCompareTableColumnLength = false; //是否自动对比字段长度
//client.AutoCompareTableColumnDelete = false; //是否自动对比删除列

var db = client.GetDatabase("test");
//var db2 = client.GetDatabase("test2"); //this will create test2 database(自由分库)

var table = db.GetTable<Student>("student"); //自由分表
table.Insert(new Student { Id = ShardingFactory.NextObjectId(), Name = "lina" });

var table2 = db.GetTable<Student>("student2");
table2.Insert(new Student { Id = ShardingFactory.NextObjectId(), Name = "lina2" });

var table3 = db.GetTable<Student>("student3");
table3.Insert(new Student { Id = ShardingFactory.NextObjectId(), Name = "lina3" });

//sharding query on all table(分片查询)
var query = new ShardingQuery(table, table2, table3); 
var total = await query.CountAsync();
or
var data = await query.QueryAsync("SELECT * FROM $table"); //$table is each table name

//Transaction(分布式事务)
var tran = new DistributedTransaction();
try
{
    table.Insert(new Student { Id = ShardingFactory.NextObjectId(), Name = "lina" }, tran);
    table.Delete("1", tran);
    table2.Delete("2", tran);
    table3.Delete("3", tran);
    tran.Commit();
}
catch
{
    tran.Rollback();    
}

//Transaction CAP
//https://gitee.com/znyet/dapper.sharding.cap

namespace ConsoleApp
{
    [Table("Id", false, "学生表")]
    public class Student
    {
        [Column(24, "主键id")]
        public string Id { get; set; }

        [Column(50, "名字")]
        public string Name { get; set; }

        [Column(20, "年龄")]
        public long Age { get; set; }

        [Ignore]
        public string NoDatabaseColumn { get; set; }
    }
}

//custom column type
[Column(columnType: "text")]
[Column(columnType: "geometry")]

//IQuery
var query = table.AsQuery("a")
     .LeftJoin(table2, "b", "a.bid=b.id")
     .Where("a.name=@name")
     .OrderBy("a.id")
     .ReturnFields("a.*,b.name as bname")
     //.Limit(10)
     .Page(1, 10)
     .Param(new { name = "lili" });

var data = query.Query<T>();
var data2 = query.QueryPageAndCount<T>();

//IUnion
var union = query.Union(query2)
.Union(query3)
.Where("name=@name")
.OrderBy("id")
.Page(1, 10)
.Param(new { name = "lili" });

var data = union.Query<T>();

//client must singleton mode(必须是单例模式)

/*===mysql need MySqlConnector≥2.2.6===*/
public static IClient Client = ShardingFactory.CreateClient(DataBaseType.MySql, new DataBaseConfig { Server = "127.0.0.1", UserId = "root", Password = "123", Port = 3306 })

/*===sqlite need Microsoft.Data.Sqlite≥8.0.6===*/
//public static IClient Client = ShardingFactory.CreateClient(DataBaseType.Sqlite, new DataBaseConfig { Server = "D:\\DatabaseFile" })

/*===sqlserver need Microsoft.Data.SqlClient≥2.1.7 ===*/
//public static IClient Client = ShardingFactory.CreateClient(DataBaseType.SqlServer2008, new DataBaseConfig { Server = ".\\express", UserId = "sa", Password = "123456", Database_Path = "D:\\DatabaseFile" })

/*===postgresql need Npgsql≥6.0.11===*/
//public static IClient Client = ShardingFactory.CreateClient(DataBaseType.Postgresql, new DataBaseConfig { Server = "127.0.0.1", UserId = "postgres", Password = "123" })

/*===duckdb need DuckDB.NET.Data.Full≥1.0.1===*/
//public static IClient Client = ShardingFactory.CreateClient(DataBaseType.DuckDB, new DataBaseConfig { Server = "127.0.0.1" })

/*===clickhouse need ClickHouse.Client≥6.7.1 ===*/
//public static IClient ClientHouse = ShardingFactory.CreateClient(DataBaseType.ClickHouse, new DataBaseConfig { Server = "192.168.0.200" });

/*===oracle need Oracle.ManagedDataAccess.Core===*/
static DataBaseConfig oracleConfig = new DataBaseConfig
{
    Server = "127.0.0.1",
    UserId = "test",
    Password = "123",
    Oracle_ServiceName = "xe",
    Oracle_SysUserId = "sys",
    Oracle_SysPassword = "123",
    Database_Path = "D:\\DatabaseFile",
    Database_Size_Mb = 1,
    Database_SizeGrowth_Mb = 1
};
//public static IClient Client = ShardingFactory.CreateClient(DataBaseType.Oracle, oracleConfig)

//json or json string field
namespace ConsoleApp1
{
    [Table("Id", true, "人类")]
    public class School
    {
        public int Id { get; set; }

        public string Name { get; set; }
        
        //[JsonString]
        //[Column(columnType: "varchar(8000)")]
        [Column(columnType: "jsonb")]
        public Student Stu { get; set; }  //json or json string
    }

    public class Student
    {
        public string Name { get; set; }

        public int Age { get; set; }
    }
}

//add Type
ShardingFactory.AddJsonType(typeof(Student));

//add Assembly
ShardingFactory.AddJsonType(typeof(Student).Assembly);

GeneratorClassFile(can create class entity file from database) //代码生成器

db.GeneratorTableFile("D:\\Class"); //生成表实体类

db.GeneratorDbContextFile("D:\\Class"); //生成请求上下文文件

//Npgsql GeoJson
NpgsqlConnection.GlobalTypeMapper.UseGeoJson();
NpgsqlGeoJsonFactory.UseGeoJson();

//Npgsql NetTopologySuite
NpgsqlConnection.GlobalTypeMapper.UseNetTopologySuite();
NpgsqlGeoFactory.UseGeo();

//Npgsql DateTimeOffset
//https://www.npgsql.org/doc/types/basic.html
AppContext.SetSwitch("Npgsql.EnableLegacyTimestampBehavior", true);
AppContext.SetSwitch("Npgsql.DisableDateTimeInfinityConversions", true);

//json config
{
    "Id": "node1",
    "DbType": "mysql",
    "DbVersion": "default",
    "Server": "127.0.0.1",
    "UserId": "root",
    "Password": "123456",
    "AutoCreateDatabase": true,
    "AutoCreateTable": true,
    "AutoCompareTableColumn": true,
    "AutoCompareTableColumnLength": true,
    "AutoCompareTableColumnDelete": false,
    "Database": "test",
    "DatabaseList": [ "db1", "db2", "db3" ],
    "TimeOut": 15,
    "CommandTimeout": 30,
    "OtherConfig": "",
    "node": {
        "Id": "node2",
        "DbType": "sqlserver",
        "DbVersion": "sqlserver2012",
        "Server": "127.0.0.1",
        "UserId": "sa",
        "Password": "1234567",
        "AutoCreateDatabase": true,
        "AutoCreateTable": true,
        "AutoCompareTableColumn": true,
        "AutoCompareTableColumnLength": true,
        "AutoCompareTableColumnDelete": false,
        "Database": "test2",
        "DatabaseList": [ "db1", "db2", "db3" ],
        "TimeOut": 15,
        "CommandTimeout": 30,
	"TrustServerCertificate": true,
        "OtherConfig": ""
    },
    "list": [
        {
            "Id": "node3",
            "DbType": "pgsql",
            "DbVersion": "default",
            "Server": "127.0.0.1",
            "UserId": "postgres",
            "Password": "123456",
            "AutoCreateDatabase": true,
            "AutoCreateTable": true,
            "AutoCompareTableColumn": true,
            "AutoCompareTableColumnLength": true,
            "AutoCompareTableColumnDelete": false,
            "Database": "test",
            "DatabaseList": [ "db1", "db2", "db3" ],
            "TimeOut": 15,
            "CommandTimeout": 30,
            "OtherConfig": ""
        },
        {
            "Id": "node4",
            "DbType": "sqlite",
            "DbVersion": "default",
            "Server": "127.0.0.1",
            "AutoCreateDatabase": true,
            "AutoCreateTable": true,
            "AutoCompareTableColumn": true,
            "AutoCompareTableColumnLength": false,
            "AutoCompareTableColumnDelete": false,
            "Database": "test",
            "DatabaseList": [ "db1", "db2", "db3" ],
            "CommandTimeout": 30,
            "OtherConfig": ""
        },
        {
            "Id": "node5",
            "DbType": "duckdb",
            "DbVersion": "default",
            "Server": "127.0.0.1",
            "AutoCreateDatabase": true,
            "AutoCreateTable": true,
            "AutoCompareTableColumn": true,
            "AutoCompareTableColumnLength": false,
            "AutoCompareTableColumnDelete": false,
            "Database": "test",
            "DatabaseList": [ "db1", "db2", "db3" ],
	    "Duck_Memory": 10, //10GB
	    "Duck_Threads": 8
            "OtherConfig": ""
        }
    ]
}

//load json config
var client1 = ShardingFactory.CreateClient("db.json");
var client2 = ShardingFactory.CreateClient("db.json", "node");
var clientList = ShardingFactory.CreateClientList("db.json", "list");
var clientDict = ShardingFactory.CreateClientDict("db.json", "list");