博客 (3)

public static class EntityFrameworkCoreExtension
{
    private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection connection, params object[] parameters)
    {
        var conn = facade.GetDbConnection();
        connection = conn;
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = sql;
        cmd.Parameters.AddRange(parameters);
        return cmd;
    }

    public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
    {
        var command = CreateCommand(facade, sql, out DbConnection conn, parameters);
        var reader = command.ExecuteReader();
        var dt = new DataTable();
        dt.Load(reader);
        reader.Close();
        conn.Close();
        return dt;
    }

    public static List<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
    {
        var dt = SqlQuery(facade, sql, parameters);
        return dt.ToList<T>();
    }

    public static List<T> ToList<T>(this DataTable dt) where T : class, new()
    {
        var propertyInfos = typeof(T).GetProperties();
        var list = new List<T>();
        foreach (DataRow row in dt.Rows)
        {
            var t = new T();
            foreach (PropertyInfo p in propertyInfos)
            {
                if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
                {
                    p.SetValue(t, row[p.Name], null);
                }
            }
            list.Add(t);
        }
        return list;
    }
}

调用示例：

var data = db.Database.SqlQuery<List<string>>("SELECT 'title' FROM `table` WHERE `id` = {0};", id);

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace xxx.xxx.xxx.Controllers
{
    public class DiscuzTinyintViewerController : Controller
    {
        public IActionResult Index()
        {
            using var context = new Data.xxx.xxxContext();

            var conn = context.Database.GetDbConnection();
            conn.Open();
            using var cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT `TABLE_NAME` FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();";
            Dictionary<string, List<FieldType>?> tables = new();

            using var r = cmd.ExecuteReader();
            while (r.Read())
            {
                tables.Add((string)r["TABLE_NAME"], null);
            }
            conn.Close();

            foreach (var table in tables)
            {
                var conn2 = context.Database.GetDbConnection();
                conn2.Open();
                using var cmd2 = conn2.CreateCommand();
                cmd2.CommandText = "DESCRIBE " + table.Key;
                using var r2 = cmd2.ExecuteReader();
                List<FieldType> fields = new();
                while (r2.Read())
                {
                    if (((string)r2[1]).Contains("tinyint(1)"))
                    {
                        fields.Add(new()
                        {
                            Field = (string)r2[0],
                            Type = (string)r2[1],
                            Null = (string)r2[2],
                        });
                    }
                }
                conn2.Close();
                tables[table.Key] = fields;
            }

            foreach (var table in tables)
            {
                foreach (var f in table.Value)
                {
                    var conn3 = context.Database.GetDbConnection();
                    conn3.Open();
                    using var cmd3 = conn3.CreateCommand();
                    cmd3.CommandText = $"SELECT {f.Field} as F, COUNT({f.Field}) as C FROM {table.Key} GROUP BY {f.Field}";
                    using var r3 = cmd3.ExecuteReader();
                    List<FieldType.ValueCount> vs = new();
                    while (r3.Read())
                    {
                        vs.Add(new() { Value = Convert.ToString(r3["F"]), Count = Convert.ToInt32(r3["C"]) });
                    }
                    conn3.Close();
                    f.groupedValuesCount = vs;
                }
            }

            return Json(tables.Where(c => c.Value != null && c.Value.Count > 0));
        }

        private class FieldType
        {
            public string Field { get; set; }
            public string Type { get; set; }
            public string Null { get; set; }
            public List<ValueCount> groupedValuesCount { get; set; }
            public class ValueCount
            {
                public string Value { get; set; }
                public int Count { get; set; }
            }
            public string RecommendedType
            {
                get
                {
                    if (groupedValuesCount == null || groupedValuesCount.Count < 2)
                    {
                        return "无建议";
                    }
                    else if (groupedValuesCount.Count == 2 && groupedValuesCount.Any(c => c.Value == "0") && groupedValuesCount.Any(c => c.Value == "1"))
                    {
                        return "bool" + (Null == "YES" ? "?" : "");
                    }
                    else
                    {
                        return "sbyte" + (Null == "YES" ? "?" : "");
                    }
                }
            }
        }
    }
}

[{
	"key": "pre_forum_post",
	"value": [{
		"field": "first",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "0",
			"count": 1395501
		}, {
			"value": "1",
			"count": 179216
		}],
		"recommendedType": "bool"
	}, {
		"field": "invisible",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "-5",
			"count": 9457
		}, {
			"value": "-3",
			"count": 1412
		}, {
			"value": "-2",
			"count": 1122
		}, {
			"value": "-1",
			"count": 402415
		}, {
			"value": "0",
			"count": 1160308
		}, {
			"value": "1",
			"count": 3
		}],
		"recommendedType": "sbyte"
	}, {
		"field": "anonymous",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "0",
			"count": 1574690
		}, {
			"value": "1",
			"count": 27
		}],
		"recommendedType": "bool"
	}, {
		"field": "usesig",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "0",
			"count": 162487
		}, {
			"value": "1",
			"count": 1412230
		}],
		"recommendedType": "bool"
	}, {
		"field": "htmlon",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "0",
			"count": 1574622
		}, {
			"value": "1",
			"count": 95
		}],
		"recommendedType": "bool"
	}, {
		"field": "bbcodeoff",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "-1",
			"count": 935448
		}, {
			"value": "0",
			"count": 639229
		}, {
			"value": "1",
			"count": 40
		}],
		"recommendedType": "sbyte"
	}, {
		"field": "smileyoff",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "-1",
			"count": 1359482
		}, {
			"value": "0",
			"count": 215186
		}, {
			"value": "1",
			"count": 49
		}],
		"recommendedType": "sbyte"
	}, {
		"field": "parseurloff",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "0",
			"count": 1572844
		}, {
			"value": "1",
			"count": 1873
		}],
		"recommendedType": "bool"
	}, {
		"field": "attachment",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "0",
			"count": 1535635
		}, {
			"value": "1",
			"count": 2485
		}, {
			"value": "2",
			"count": 36597
		}],
		"recommendedType": "sbyte"
	}, {
		"field": "comment",
		"type": "tinyint(1)",
		"null": "NO",
		"groupedValuesCount": [{
			"value": "0",
			"count": 1569146
		}, {
			"value": "1",
			"count": 5571
		}],
		"recommendedType": "bool"
	}]
}]

private static List<string> GetTableNames(DbContext context)
{
    context.Database.OpenConnection();

    var connection = context.Database.GetDbConnection();

    using var command = connection.CreateCommand();
    command.CommandText = @"SELECT `TABLE_NAME`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();";
    var tableNames = new List<string>();

    using var reader = command.ExecuteReader();
    while (reader.Read())
    {
        tableNames.Add((string)reader["TABLE_NAME"]);
    }

    return tableNames;
}