基于C#与Entity Framework实现MySQL运行时动态创建表

Got it, let's get that AddTableIntempDb method working to create your MySQL table from the dynamic type. First, I'll point out a quick correction in your existing code, then walk through the implementation step by step.

Important Correction in CreateTempTable #

Right now, you're passing constructedListType (which is List<YourDynamicType>) to AddTableIntempDb—but we need the underlying dynamic object type, not the List type. Otherwise, your code will try to create a table from List properties (like Count or Capacity) instead of your data source's attributes. Update that line to:

AddTableIntempDb(tableName, objectType); 

Full Implementation of AddTableIntempDb #

This method will map your dynamic type's properties to MySQL data types, build a CREATE TABLE statement, and execute it against your database:

using System.Reflection;
using MySqlConnector; // Or MySQL.Data.MySqlClient depending on your library

private void AddTableIntempDb(string tableName, Type type)
{
    // Get all public instance properties from the dynamic type
    var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
    
    if (!properties.Any())
        throw new InvalidOperationException("The dynamic type has no properties to use as table columns.");

    // Map .NET types to corresponding MySQL data types
    var typeMappings = new Dictionary<Type, string>
    {
        { typeof(string), "VARCHAR(255)" },
        { typeof(int), "INT" },
        { typeof(int?), "INT NULL" },
        { typeof(long), "BIGINT" },
        { typeof(long?), "BIGINT NULL" },
        { typeof(decimal), "DECIMAL(18,2)" },
        { typeof(decimal?), "DECIMAL(18,2) NULL" },
        { typeof(DateTime), "DATETIME" },
        { typeof(DateTime?), "DATETIME NULL" },
        { typeof(bool), "BOOLEAN" },
        { typeof(bool?), "BOOLEAN NULL" },
        { typeof(float), "FLOAT" },
        { typeof(float?), "FLOAT NULL" },
        { typeof(double), "DOUBLE" },
        { typeof(double?), "DOUBLE NULL" }
        // Add more mappings here for any other data types your source uses
    };

    // Build column definitions for the table
    var columns = new List<string>();
    foreach (var prop in properties)
    {
        var propertyType = prop.PropertyType;
        // Handle nullable value types (e.g., int?)
        var underlyingNullableType = Nullable.GetUnderlyingType(propertyType);
        if (underlyingNullableType != null)
            propertyType = underlyingNullableType;

        if (!typeMappings.TryGetValue(propertyType, out var mySqlType))
            throw new NotSupportedException($"Unsupported data type: {propertyType.Name}");

        // Ensure nullable columns are marked correctly (for reference types like string)
        var isNullable = !propertyType.IsValueType || underlyingNullableType != null;
        if (isNullable && !mySqlType.EndsWith("NULL"))
            mySqlType += " NULL";

        // Escape column names to avoid conflicts with MySQL reserved words
        columns.Add($"`{prop.Name}` {mySqlType}");
    }

    // Optional: Add a primary key if your dynamic type has an ID property (adjust as needed)
    var idProperty = properties.FirstOrDefault(p => p.Name.Equals("Id", StringComparison.OrdinalIgnoreCase));
    if (idProperty != null)
    {
        columns.Add($"PRIMARY KEY (`{idProperty.Name}`)");
    }

    // Build the final CREATE TABLE SQL statement
    var createTableSql = $"CREATE TABLE IF NOT EXISTS `{tableName}` ({string.Join(", ", columns)})";

    // Execute the SQL against your MySQL database
    // Replace with your actual connection string (fetch from config/Unit of Work instead of hardcoding!)
    using var connection = new MySqlConnection("Your_MySQL_Connection_String");
    connection.Open();
    
    using var command = new MySqlCommand(createTableSql, connection);
    command.ExecuteNonQuery();
}

Key Notes #

1. Connection String: Replace "Your_MySQL_Connection_String" with your actual database connection string—ideally fetch this from your app's configuration or Unit of Work instead of hardcoding it.
2. Type Mappings: Extend the typeMappings dictionary to cover any additional data types your dynamic source might use (e.g., byte[] for BLOBs).
3. Error Handling: Wrap the database logic in a try-catch block if you want to handle exceptions (like duplicate tables or connection issues) and return an error DTO from CreateTempTable instead of throwing.
4. Table Names: Using backticks around table/column names ensures compatibility with MySQL reserved words.

内容的提问来源于stack exchange，提问作者GOPAL SHARMA