在C#中将DataTable转换为SQL的CREATE TABLE + INSERT脚本

public static string DataTableToCreateTableScript(DataTable table)
{
    StringBuilder sb = new StringBuilder();
    sb.Append("CREATE TABLE " + table.TableName + " (");
    foreach (DataColumn column in table.Columns)
    {
        sb.Append(column.ColumnName + " " + GetSQLDataType(column.DataType) + ", ");
    }
    sb.Remove(sb.Length - 2, 2); // 移除最后一个逗号和空格
    sb.Append(");");
    return sb.ToString();
}

public static string DataTableToInsertScript(DataTable table)
{
    StringBuilder sb = new StringBuilder();
    foreach (DataRow row in table.Rows)
    {
        sb.Append("INSERT INTO " + table.TableName + " (");
        foreach (DataColumn column in table.Columns)
        {
            sb.Append(column.ColumnName + ", ");
        }
        sb.Remove(sb.Length - 2, 2); // 移除最后一个逗号和空格
        sb.Append(") VALUES (");
        foreach (DataColumn column in table.Columns)
        {
            sb.Append("'" + row[column].ToString().Replace("'", "''") + "', ");
        }
        sb.Remove(sb.Length - 2, 2); // 移除最后一个逗号和空格
        sb.Append(");");
    }
    return sb.ToString();
}

private static string GetSQLDataType(Type dataType)
{
    if (dataType == typeof(string))
    {
        return "VARCHAR(MAX)";
    }
    else if (dataType == typeof(int))
    {
        return "INT";
    }
    else if (dataType == typeof(float))
    {
        return "FLOAT";
    }
    else if (dataType == typeof(double))
    {
        return "FLOAT";
    }
    else if (dataType == typeof(decimal))
    {
        return "DECIMAL";
    }
    else if (dataType == typeof(DateTime))
    {
        return "DATETIME";
    }
    else if (dataType == typeof(bool))
    {
        return "BIT";
    }
    else
    {
        return "VARCHAR(MAX)";
    }
}

DataTable table = new DataTable("MyTable");
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Rows.Add(1, "John Doe");
table.Rows.Add(2, "Jane Doe");

string createTableScript = DataTableToCreateTableScript(table);
string insertScript = DataTableToInsertScript(table);

CREATE TABLE MyTable (
    ID INT,
    Name VARCHAR(MAX)
);

INSERT INTO MyTable (ID, Name) VALUES (1, 'John Doe');
INSERT INTO MyTable (ID, Name) VALUES (2, 'Jane Doe');