NXDO.Data.NPostgreSQL 1.0.0

关于 NXDO.Data.NPostgreSQL

为支持 PostgreSQL 对 NXDO.Data 的继承扩展

NuGet 引用

• 引用 Npgsql V9.0.4 支持NetCore6
• 引用 NXDO.Data V4.5.689 最小包版本 >= 4.5.[60|80|90].0

配置

文件名：NXDO.Data.Provider.json
保证该配置文件在 “应用程序环境的根目录”

国产DB（翰高 HighGO，人大金仓 KingBase）均可使用此配置。

{
    "active": 0,
    "providers": [
        {
            "key": 0,

            "providerName": "Npgsql",
            "providerAssemblyFileName": "Npgsql.NpgsqlFactory, Npgsql.dll",
            "providerAssemblyQualifiedName": "Npgsql.NpgsqlFactory, Npgsql, Version=9.0.4.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7",
            "dbType": "NpgsqlTypes.NpgsqlDbType",

            "connectionString": "Host=localhost;Username=root;Password=***;Database=postgres;Port=5431;",
            //需继承 NXDO.Data.Linq.Formatter.DbAnyFormatter 自行实现，一些常用的功能 DbMySqlFormatter 也支持 postgreSQL
            "linqFormatterType": "NXDO.Data.Linq.Formatter.DbMySqlFormatter, NXDO.Data.N6.dll", 

            "parameter": "@",

            //支持自增列，NXDO.Data.N[6|8|9].dll 版本必须 >= 4.5.[60|80|90].0 。否则请设置成空。
            "syncAutoID": "select currval(pg_get_serial_sequence('$table', '$autofield'))",
            //"syncAutoID": "returning $autofield", //不支持，insert执行后进行查询，应使用完整的sql进行查询， insert into table(...) values(...) returning 自增列名称

            "quotePrefix": "",
            "quoteSuffix": "",

            "usedAS": true,
            "usedJSON": false,
            "usedSnowId": false,
            "snowIdTables": "",

            //支持基于 limit .. offset .. 分页
            //"pageRowCount": "pageIndex * pageSize",
            //"pageMaxCount": "",
            //"alias": "t1", //分页时的别名,用于$SelectFields
            //"pageSQL": "SELECT $SelectFields FROM $Table t1 join (select $Field from $BaseTable WHERE $Filter $Order LIMIT $PageSize offset $PageRowCount) t2 on t1.$Field=t2.$Field",


            "pageRowCount": "pageIndex * pageSize",
            "pageMaxCount": "pageIndex * pageSize + pageSize",
            "alias": "",
            "pageSQL": "SELECT $SelectFields FROM (SELECT *, ROW_NUMBER() OVER ($Order) AS RN FROM $Table WHERE $Filter) WHERE RN > $PageRowCount AND RN <= $PageMaxCount"
        }
    ]
}

低级别API - 导出/导入

using NXDO.Data.DbClient.Transfer;

//导出
var exp = BulkExport.GetExport("table", DataFileMode.Csv, "savePath");
exp.RegisterExportService<ExportPostgreSQL>();
exp.InculdeCsvFields = true;
exp.Execute();

//导入
var imp = BulkImport.GetImportWithDirectory(false, "savePath");
imp.RegisterImportService<ImportPostgreSQL>(pg => { pg.InculdeCsvFields = false; /*true|false*/
                                                    pg.IsSupportUpdate = true;   /*true|false*/ });
imp.InculdeCsvFields = true; //最终为 false, Execute执行时：注册的 Action 将被执行
imp.Execute();

NXDO.WebAPI - 导出/导入控制器

using NXDO.Data.DbClient.Transfer;
using NXDO.WebAPI.Controllers;

public class PgOutController : ApiDbExportController {
    protected override DbTransfer CreateDbTransferInstance() {
        var dbTrf = new DbTransfer();

        //修改默认的导出类
        dbTrf.ChangeBulkExporter<ExportPostgreSQL>(pg => { pg.InculdeCsvFields = true; });
        return dbTrf;
    }
}

public class PgInController : ApiDbImportController {
    protected override DbTransfer CreateDbTransferInstance() {
        var dbTrf = new DbTransfer();

        //修改默认的导入类
        dbTrf.ChangeBulkImporter<ImportPostgreSQL>(pg => { pg.InculdeCsvFields = true; pg.IsSupportUpdate = true; });
        return dbTrf;
    }
}

PostgreSQL 隐性事务服务 for NXDO.Data

var factory = NXDO.Data.DbClient.AnyDbProviderFactory.GetFactory(2);
using var conn = factory.CreateConnection("...");

//涉及内部使用隐性事务
conn.AddImplicitTransactionFindService(new PostgreTransactionFindService());

//关闭内部隐性事务的支持 
AnyDbProviderFactory.AllowImplicitTransactionNull = true;

conn.Open();
var dbHelper = NXDO.Data.Extension.DbHelperExtension.CreateDbHelper(conn);
dbHelper.SetCommand("...").[OtherMethod]