C# .NET Core：基于DataTable动态创建SQL表，求Pandas to_sql等效方法

Absolutely! You can replicate the "create table + bulk insert" workflow you get with Pandas' to_sql in .NET Core/MVC. The key is to generate a CREATE TABLE statement from your DataTable's schema first, then use SqlBulkCopy to load the data—here's a practical, reusable approach:

Step 1: Map .NET Data Types to SQL Server Types #

First, you need a helper method to translate your DataTable's column types into SQL Server-compatible data types. This covers common types, but you can expand it based on your data:

private string MapDotNetTypeToSqlType(Type dotNetType)
{
    return dotNetType switch
    {
        Type t when t == typeof(int) => "INT",
        Type t when t == typeof(long) => "BIGINT",
        Type t when t == typeof(decimal) => "DECIMAL(18,2)",
        Type t when t == typeof(DateTime) => "DATETIME2(7)",
        Type t when t == typeof(string) => "NVARCHAR(MAX)",
        Type t when t == typeof(bool) => "BIT",
        Type t when t == typeof(float) => "FLOAT",
        _ => throw new NotSupportedException($"Unsupported data type: {dotNetType.Name}")
    };
}

Step 2: Generate the CREATE TABLE SQL Statement #

Next, build the CREATE TABLE command by iterating over your DataTable's columns and applying the type mappings:

private string GenerateCreateTableSql(DataTable dataTable, string targetTableName)
{
    var columnDefinitions = new List<string>();
    
    foreach (DataColumn column in dataTable.Columns)
    {
        string sqlDataType = MapDotNetTypeToSqlType(column.DataType);
        string nullableConstraint = column.AllowDBNull ? "NULL" : "NOT NULL";
        columnDefinitions.Add($"[{column.ColumnName}] {sqlDataType} {nullableConstraint}");
    }

    return $"CREATE TABLE [{targetTableName}] ({string.Join(", ", columnDefinitions)})";
}

Step 3: Combine Table Creation + Bulk Insert #

Put it all together in a single method that checks if the table exists (to avoid errors), creates it if needed, then uses SqlBulkCopy for fast data insertion:

using System.Data;
using System.Data.SqlClient;

public void DataTableToSql(DataTable dataTable, string targetTableName, string sqlConnectionString)
{
    using (var connection = new SqlConnection(sqlConnectionString))
    {
        connection.Open();

        // Check if table exists; create it if it doesn't
        string createTableSql = GenerateCreateTableSql(dataTable, targetTableName);
        string checkAndCreateSql = $@"
            IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = N'{targetTableName}' AND schema_id = SCHEMA_ID(N'dbo'))
            BEGIN
                {createTableSql}
            END";

        using (var createCommand = new SqlCommand(checkAndCreateSql, connection))
        {
            createCommand.ExecuteNonQuery();
        }

        // Bulk insert the DataTable data
        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = targetTableName;
            bulkCopy.BatchSize = 1000; // Adjust based on your data volume

            // Auto-map columns (works if DataTable column names match the new SQL table)
            foreach (DataColumn column in dataTable.Columns)
            {
                bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
            }

            bulkCopy.WriteToServer(dataTable);
        }
    }
}

Key Notes & Customizations #

• Type Mapping Flexibility: Adjust the MapDotNetTypeToSqlType method to match your needs—for example, use VARCHAR(255) instead of NVARCHAR(MAX) for short strings, or specify a different precision for decimals.
• SQL Injection Safety: If targetTableName comes from user input, avoid string interpolation. Instead, validate/escape the table name (SQL Server doesn't support parameterizing table names directly).
• Indexes & Keys: If you need primary keys or indexes, modify the GenerateCreateTableSql method to append clauses like PRIMARY KEY ([Id]) after the column definitions.
• Error Handling: Add try/catch blocks to handle connection issues, type mapping failures, or bulk copy errors. You can also use SqlBulkCopy events like RowErrorOccurred to log problematic rows.

This workflow gives you the exact convenience you were looking for—just pass your parsed DataTable, target table name, and connection string, and it handles both table creation and data import.

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