PocoClassGenerator：RDBMS所有表/视图生成Dapper POCO类代码

目录

介绍

开始

第一

第二

演示图片

域逻辑

域代码

---

下载Github链接

介绍

制作PocoClassGenerator的原因是我经常需要“ 生成大量RDBMS的表/视图到dapper poco类代码”。这不仅适用于SQL服务器，也适用于其他数据库。

所以我写了PocoClassGenerator：

• 支持当前DataBase所有表和视图生成POCO类代码
• 支持多种RDBMS： sqlserver， oracle ，mysql，postgresql
• 轻量且速度更快（仅在5秒内生成100个表格代码）
• 为每个数据库查询使用适当的专业用语架构表SQL

开始

第一

将PocoClassGenerator.cs代码复制并粘贴到项目或LINQPad，或从NuGet安装：

PM> install-package PocoClassGenerator

第二

使用Connection调用GenerateAllTables，然后打印出来。

using (var connection = Connection)
{
    Console.WriteLine(connection.GenerateAllTables());
}

在线演示：POCO类生成器GenerateAllTables | .NET Fiddle

演示图片

域逻辑

• ExecuteReader和CommandBehavior.KeyInfo和GetSchemaTable方法用来获取RDBMS的表/列的数据。
• 用where 1=2查询获取0数据以提高查询效率

域代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;

public static class PocoClassGenerator
{
	private static readonly Dictionary<Type, string> TypeAliases = 
                                          new Dictionary<Type, string> {
		{ typeof(int), "int" },
		{ typeof(short), "short" },
		{ typeof(byte), "byte" },
		{ typeof(byte[]), "byte[]" },
		{ typeof(long), "long" },
		{ typeof(double), "double" },
		{ typeof(decimal), "decimal" },
		{ typeof(float), "float" },
		{ typeof(bool), "bool" },
		{ typeof(string), "string" }
	};

	private static readonly Dictionary<string, string> QuerySqls = 
                                               new Dictionary<string, string> {
		{"sqlconnection", "select  *  from [{0}] where 1=2" },
		{"sqlceserver", "select  *  from [{0}] where 1=2" },
		{"sqliteconnection", "select  *  from [{0}] where 1=2" },
		{"oracleconnection", "select  *  from \"{0}\" where 1=2" },
		{"mysqlconnection", "select  *  from `{0}` where 1=2" },
		{"npgsqlconnection", "select  *  from \"{0}\" where 1=2" }
	};

	private static readonly Dictionary<string, string> SchemaSqls = 
                                               new Dictionary<string, string> {
		{"sqlconnection", "select TABLE_NAME from INFORMATION_SCHEMA.TABLES 
                           where TABLE_TYPE = 'BASE TABLE'" },
		{"sqlceserver", "select TABLE_NAME from INFORMATION_SCHEMA.TABLES 
                         where TABLE_TYPE = 'BASE TABLE'" },
		{"sqliteconnection", "SELECT name FROM sqlite_master where type = 'table'" },
		{"oracleconnection", "select TABLE_NAME from USER_TABLES 
                         where table_name not in (select View_name from user_views)" },
		{"mysqlconnection", "select TABLE_NAME from  information_schema.tables 
                             where TABLE_TYPE = 'BASE TABLE';" },
		{"npgsqlconnection", "select table_name from information_schema.tables 
                              where table_type = 'BASE TABLE'" }
	};

	private static readonly HashSet<Type> NullableTypes = new HashSet<Type> {
		typeof(int),
		typeof(short),
		typeof(long),
		typeof(double),
		typeof(decimal),
		typeof(float),
		typeof(bool),
		typeof(DateTime)
	};	
	
	public static string GenerateAllTables
        (this System.Data.Common.DbConnection connection,bool containsView = false)
	{
		if (connection.State != ConnectionState.Open)
			connection.Open();
		
		var conneciontName = connection.GetType().Name.ToLower();
		var tables = new List<string>();
		using (var command = connection.CreateCommand())
		{
			command.CommandText = containsView ? 
              Regex.Split(SchemaSqls[conneciontName],"where")[0]:SchemaSqls[conneciontName];
			using (var reader = command.ExecuteReader())
			{
				while(reader.Read())
					tables.Add(reader.GetString(0));
			}
		}
		
		var sb = new StringBuilder();
		sb.AppendLine("namespace Models { ");
		tables.ForEach(table=> sb.Append(connection.GenerateClass
                              (string.Format(QuerySqls[conneciontName],table))));
		sb.AppendLine("}");
		return sb.ToString();
	}

	public static string GenerateClass
            (this IDbConnection connection, string sql,string className = null)
	{
		if (connection.State != ConnectionState.Open)
			connection.Open();

		var cmd = connection.CreateCommand();
		cmd.CommandText = sql;


		using (var reader = cmd.ExecuteReader
              (CommandBehavior.KeyInfo | CommandBehavior.SingleRow ))
		{
			var builder = new StringBuilder();
			do
			{
				if (reader.FieldCount <= 1) continue;

				var schema = reader.GetSchemaTable();
				foreach (DataRow row in schema.Rows)
				{
					if (string.IsNullOrWhiteSpace(builder.ToString()))
					{
						var tableName = string.IsNullOrWhiteSpace(className) ? 
                              row["BaseTableName"] as string ?? "Info" : className;
						builder.AppendFormat("	public class {0}{1}", 
                                                tableName, Environment.NewLine);
						builder.AppendLine("	{");
					}

					var type = (Type)row["DataType"];
					var name = TypeAliases.ContainsKey(type) ? TypeAliases[type] : type.Name;
					var isNullable = (bool)row["AllowDBNull"] && NullableTypes.Contains(type);
					var collumnName = (string)row["ColumnName"];

					builder.AppendLine(string.Format("		public {0}{1} {2} 
                     {{ get; set; }}", name, isNullable ? "?" : string.Empty, collumnName));
					builder.AppendLine();
				}

				builder.AppendLine("	}");
				builder.AppendLine();
			} while (reader.NextResult());

			return builder.ToString();
		}
	}
}

 

原文地址：https://www.codeproject.com/Articles/4025476/PocoClassGenerator-RDBMS-All-Table-View-Generate-D