当前位置：首页 > news >正文

随笔20250721 PostgreSQL实体类生成器

news 2025/7/22 10:32:07

我来帮你创建一个C#程序，从PostgreSQL数据库读取表结构并生成对应的实体类文件。我已经创建了一个完整的PostgreSQL实体类生成器。这个程序包含以下主要功能：

主要特性

数据库连接: 使用Npgsql连接PostgreSQL数据库
表结构读取: 自动读取所有表的结构信息
类型映射: 将PostgreSQL数据类型映射为C#数据类型
实体生成: 为每个表生成对应的C#实体类
文件导出: 将生成的类保存到指定文件夹

使用方法

安装NuGet包:
```
Install-Package Npgsql
```

配置连接字符串: 修改Main方法中的连接字符串：

var connectionString = "Host=localhost;Database=your_database;Username=your_username;Password=your_password;";

设置输出路径:

var outputPath = @"C:\GeneratedEntities";

运行程序: 执行后会自动生成所有表的实体类

生成的实体类特点

包含完整的Data Annotations特性
主键字段标记为[Key]
字段映射使用[Column]特性
支持可空类型
字符串长度限制
帕斯卡命名法（下划线转换）

支持的数据类型

整数类型：int, long, short
浮点类型：float, double, decimal
文本类型：string
日期时间：DateTime, DateTimeOffset, TimeSpan
其他：bool, Guid, byte[]

生成的实体类可以直接用于Entity Framework Core或其他ORM框架。

1.try cath 写在哪里? 写在 ViewModel层 Servace层最多使用自定义异常业务异常进行处理(传给ViewModel层处理), DAO层进行数据库操作流水账式,但是要做好资源释放操作
2.DbHelp 是什么模式?单例?释放需要手动释放?单例模式(和项目的整个声明周期一起,关了就完蛋了),不需要关闭(和项目的整个声明周期一起,关了就完蛋了),无法批量插入是因为 表结构还没完全构建完成,解决方法,先插入第一条来“预热”连接,如果还有后续的数据,就直接进行批量插入.public async Task InitTestOrdersAsync(){await _db.DeleteTableAsync<Orders>();await _db.CreateTableAsync<Orders>();var mockOrders = new List<Orders>();// ... (生成 mockOrders 的代码) ...if (mockOrders.Any()){// 1. 先插入第一条来“预热”连接await _db.InsertAsync(mockOrders.First());// 2. 再批量插入剩下的数据var remainingOrders = mockOrders.Skip(1).ToList();if(remainingOrders.Any()){await _db.InsertAllAsync(remainingOrders);}}}3.弄清 异步和非异步方法的区别,以及使用场景
只要底层（DAO 和 HttpClient）是异步的，Service 层 和 ViewModel 层也都必须是异步的！也就是说：要一路 async 到顶层。
| 层级                            | 是否 async | 原因                    |
| ----------------------------- | -------- | --------------------- |
| DAO（使用 SQLiteAsyncConnection） | ✅ 必须异步   | SQLite 是 IO，使用异步 API  |
| 调用 DAO 的 Service              | ✅ 必须异步   | 调用异步方法 → 必须用 `await`  |
| 调用 Service 的 ViewModel        | ✅ 必须异步   | 继续 `await` service 结果 || 情况               | 是否 async    |
| ---------------- | ----------- |
| 方法内部没有任何异步调用     | ❌ 不需要 async |
| 方法只是封装计算逻辑、纯内存操作 | ❌ 不需要 async |你用的是 SQLiteAsyncConnection + HttpClient 这样的异步库，整个调用链（DAO → Service → ViewModel）都必须使用 async/await 才是正确做法。🎯 为什么 .Result 或 .Wait() 会“把线程卡住”？
原因是：
.Result 和 .Wait() 是同步阻塞方法，会强制当前线程“原地等到任务完成”，不能做别的事，也不会释放线程。4.创建一个读取postSQL数据库的工具,生成实体类5432

PostgreSQLEntityGenerator.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using Npgsql;namespace ConsoleApp1
{public class EntityGenerator{private readonly string _connectionString;private readonly string _outputPath;public EntityGenerator(string connectionString, string outputPath){_connectionString = connectionString;_outputPath = outputPath;}public void GenerateEntities(){try{// 确保输出目录存在if (!Directory.Exists(_outputPath)){Directory.CreateDirectory(_outputPath);}using (var connection = new NpgsqlConnection(_connectionString)){connection.Open();// 获取所有表名var tables = GetTableNames(connection);foreach (var tableName in tables){Console.WriteLine($"正在生成实体类: {tableName}");GenerateEntityForTable(connection, tableName);}}Console.WriteLine($"实体类生成完成！输出路径: {_outputPath}");}catch (Exception ex){Console.WriteLine($"生成实体类时发生错误: {ex.Message}");throw;}}private List<string> GetTableNames(NpgsqlConnection connection){var tables = new List<string>();const string sql = @"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE'ORDER BY table_name";using (var command = new NpgsqlCommand(sql, connection))using (var reader = command.ExecuteReader()){while (reader.Read()){tables.Add(reader.GetString("table_name"));}}return tables;}private void GenerateEntityForTable(NpgsqlConnection connection, string tableName){var columns = GetTableColumns(connection, tableName);var entityCode = GenerateEntityCode(tableName, columns);var fileName = $"{ToPascalCase(tableName)}.cs";var filePath = Path.Combine(_outputPath, fileName);File.WriteAllText(filePath, entityCode, Encoding.UTF8);}private List<ColumnInfo> GetTableColumns(NpgsqlConnection connection, string tableName){var columns = new List<ColumnInfo>();const string sql = @"SELECT c.column_name,c.data_type,c.is_nullable,c.column_default,c.character_maximum_length,c.numeric_precision,c.numeric_scale,CASE WHEN pk.column_name IS NOT NULL THEN true ELSE false END as is_primary_keyFROM information_schema.columns cLEFT JOIN (SELECT ku.column_nameFROM information_schema.table_constraints tcJOIN information_schema.key_column_usage kuON tc.constraint_name = ku.constraint_nameAND tc.table_schema = ku.table_schemaWHERE tc.constraint_type = 'PRIMARY KEY'AND tc.table_name = @tableNameAND tc.table_schema = 'public') pk ON c.column_name = pk.column_nameWHERE c.table_name = @tableNameAND c.table_schema = 'public'ORDER BY c.ordinal_position";using (var command = new NpgsqlCommand(sql, connection)){command.Parameters.AddWithValue("@tableName", tableName);using (var reader = command.ExecuteReader()){while (reader.Read()){columns.Add(new ColumnInfo{Name = reader.GetString("column_name"),DataType = reader.GetString("data_type"),IsNullable = reader.GetString("is_nullable") == "YES",DefaultValue = reader.IsDBNull("column_default") ? null : reader.GetString("column_default"),MaxLength = reader.IsDBNull("character_maximum_length") ? (int?)null : reader.GetInt32("character_maximum_length"),Precision = reader.IsDBNull("numeric_precision") ? (int?)null : reader.GetInt32("numeric_precision"),Scale = reader.IsDBNull("numeric_scale") ? (int?)null : reader.GetInt32("numeric_scale"),IsPrimaryKey = reader.GetBoolean("is_primary_key")});}}}return columns;}private string GenerateEntityCode(string tableName, List<ColumnInfo> columns){var sb = new StringBuilder();var className = ToPascalCase(tableName);// 添加using语句sb.AppendLine("using System;");sb.AppendLine("using System.ComponentModel.DataAnnotations;");sb.AppendLine("using System.ComponentModel.DataAnnotations.Schema;");sb.AppendLine();// 添加命名空间sb.AppendLine("namespace Entities");sb.AppendLine("{");// 添加Table特性sb.AppendLine($"    [Table(\"{tableName}\")]");sb.AppendLine($"    public class {className}");sb.AppendLine("    {");// 生成属性foreach (var column in columns){GenerateProperty(sb, column);}sb.AppendLine("    }");sb.AppendLine("}");return sb.ToString();}private void GenerateProperty(StringBuilder sb, ColumnInfo column){var propertyName = ToPascalCase(column.Name);var csharpType = GetCSharpType(column);// 添加注释sb.AppendLine();sb.AppendLine($"        /// <summary>");sb.AppendLine($"        /// {column.Name}");sb.AppendLine($"        /// </summary>");// 添加特性if (column.IsPrimaryKey){sb.AppendLine("        [Key]");}sb.AppendLine($"        [Column(\"{column.Name}\")]");if (!column.IsNullable && csharpType != "string"){sb.AppendLine("        [Required]");}if (column.MaxLength.HasValue && csharpType == "string"){sb.AppendLine($"        [MaxLength({column.MaxLength.Value})]");}// 生成属性sb.AppendLine($"        public {csharpType} {propertyName} {{ get; set; }}");}private string GetCSharpType(ColumnInfo column){var baseType = column.DataType.ToLower() switch{"integer" or "int4" => "int","bigint" or "int8" => "long","smallint" or "int2" => "short","boolean" or "bool" => "bool","real" or "float4" => "float","double precision" or "float8" => "double","numeric" or "decimal" => "decimal","text" or "varchar" or "character varying" or "char" or "character" => "string","timestamp" or "timestamp without time zone" => "DateTime","timestamp with time zone" or "timestamptz" => "DateTimeOffset","date" => "DateTime","time" or "time without time zone" => "TimeSpan","uuid" => "Guid","json" or "jsonb" => "string","bytea" => "byte[]",_ => "object"};// 如果字段可为空且不是引用类型，添加?if (column.IsNullable && baseType != "string" && baseType != "byte[]" && baseType != "object"){return baseType + "?";}return baseType;}private string ToPascalCase(string input){if (string.IsNullOrEmpty(input))return input;var parts = input.Split('_', '-');var sb = new StringBuilder();foreach (var part in parts){if (!string.IsNullOrEmpty(part)){sb.Append(char.ToUpper(part[0]));if (part.Length > 1)sb.Append(part[1..].ToLower());}}return sb.ToString();}}public class ColumnInfo{public string Name { get; set; }public string DataType { get; set; }public bool IsNullable { get; set; }public string DefaultValue { get; set; }public int? MaxLength { get; set; }public int? Precision { get; set; }public int? Scale { get; set; }public bool IsPrimaryKey { get; set; }}
}

Program.cs

using ConsoleApp1;try
{// 配置数据库连接字符串var connectionString = "Host=localhost;Database=postgres;Username=postgres;Password=123456;";// 配置输出路径var outputPath = @"C:\MySoft\PostgreSQL\Models";// 创建生成器实例var generator = new EntityGenerator(connectionString, outputPath);// 生成实体类generator.GenerateEntities();Console.WriteLine("按任意键退出...");Console.ReadKey();
}
catch (Exception ex)
{Console.WriteLine($"程序执行出错: {ex.Message}");Console.WriteLine("按任意键退出...");Console.ReadKey();
}
Console.WriteLine("Hello, World!");

查看全文

http://www.lryc.cn/news/595204.html