如何使用C#解析固定格式文本文件并连接SQL Server数据库

核心实现思路 #

• 采用行遍历+状态标记的方案，不需要一次性加载全量文件，大文件也能兼容
• 预定义3种内容状态+默认无匹配状态，识别到目标标题后切换对应状态，后续行自动归入当前状态对应的存储列表
• 遇到下一个目标标题/文件结束时，自动重置状态，避免内容归属错误

前置模型定义 #

你已有的Columns模型可以保留，补充另外两个业务模型和解析状态枚举即可：

// 已有的字段定义模型，可按你的需求扩展
public class ColumnDefinition
{
    public string TableName { get; set; }
    public string ColumnName { get; set; }
    public string ColumnType { get; set; }
    public bool IsPrimaryKey { get; set; }
}

// 测试记录模型
public class RecordItem
{
    public string TableName { get; set; }
    public List<string> FieldValues { get; set; } = new();
}

// 表间关系模型
public class RelationshipItem
{
    public string SourceTable { get; set; }
    public string SourceColumn { get; set; }
    public string TargetTable { get; set; }
    public string TargetColumn { get; set; }
}

// 全局解析结果容器
public class DbSchemaParseResult
{
    public string DbType { get; set; }
    public string ConnectionString { get; set; }
    public string DbName { get; set; }
    public List<ColumnDefinition> Columns { get; set; } = new();
    public List<RecordItem> Records { get; set; } = new();
    public List<RelationshipItem> Relationships { get; set; } = new();
}

// 解析状态枚举，用于标记当前读取的内容归属
internal enum ParseState
{
    None,
    InColumns,
    InRecords,
    InRelationships
}

核心解析代码 #

public static DbSchemaParseResult ParseSchemaFile(string filePath)
{
    var result = new DbSchemaParseResult();
    var currentState = ParseState.None;
    string currentTableName = string.Empty; // 标记当前读取内容所属的表名

    // 逐行读取，兼容大文件场景
    foreach (var line in File.ReadLines(filePath))
    {
        var cleanLine = line.Trim();
        if (string.IsNullOrWhiteSpace(cleanLine)) continue; // 跳过空行

        // 第一步：匹配目标标题，切换解析状态
        if (cleanLine.StartsWith("Columns:"))
        {
            currentState = ParseState.InColumns;
            continue;
        }
        if (cleanLine.StartsWith("Records:"))
        {
            currentState = ParseState.InRecords;
            continue;
        }
        if (cleanLine.StartsWith("Relationships:"))
        {
            currentState = ParseState.InRelationships;
            continue;
        }

        // 第二步：读取三个标题外的全局基础信息
        if (currentState == ParseState.None)
        {
            if (cleanLine.StartsWith("DatabaseType:"))
                result.DbType = cleanLine.Split(":", StringSplitOptions.TrimEntries)[1];
            else if (cleanLine.StartsWith("ConnectionString:"))
                result.ConnectionString = cleanLine.Split(":", StringSplitOptions.TrimEntries)[1];
            else if (cleanLine.StartsWith("DbName:"))
                result.DbName = cleanLine.Split(":", StringSplitOptions.TrimEntries)[1];
            else if (cleanLine.StartsWith("Table:")) // 表名匹配规则可按你的文本格式调整
                currentTableName = cleanLine.Split(":", StringSplitOptions.TrimEntries)[1];
            continue;
        }

        // 第三步：按当前状态把内容存入对应列表
        switch (currentState)
        {
            case ParseState.InColumns:
                // 字段行分割规则可按你的文本格式调整，示例为逗号分割
                var columnParts = cleanLine.Split(',', StringSplitOptions.TrimEntries);
                result.Columns.Add(new ColumnDefinition
                {
                    TableName = currentTableName,
                    ColumnName = columnParts[0],
                    ColumnType = columnParts[1],
                    IsPrimaryKey = columnParts.Length > 2 && columnParts[2].Equals("PK", StringComparison.OrdinalIgnoreCase)
                });
                break;
            case ParseState.InRecords:
                // 记录行分割规则可按你的文本格式调整
                var recordParts = cleanLine.Split(',', StringSplitOptions.TrimEntries);
                result.Records.Add(new RecordItem
                {
                    TableName = currentTableName,
                    FieldValues = recordParts.ToList()
                });
                break;
            case ParseState.InRelationships:
                // 关系行分割规则可按你的文本格式调整，示例为->分割
                var relParts = cleanLine.Split("->", StringSplitOptions.TrimEntries);
                var sourcePart = relParts[0].Split('.');
                var targetPart = relParts[1].Split('.');
                result.Relationships.Add(new RelationshipItem
                {
                    SourceTable = sourcePart[0],
                    SourceColumn = sourcePart[1],
                    TargetTable = targetPart[0],
                    TargetColumn = targetPart[1]
                });
                break;
        }
    }
    return result;
}

使用说明 #

调用ParseSchemaFile方法拿到解析结果对象后，就可以分别遍历Columns、Records、Relationships三个列表，生成对应的建表、插入数据、创建外键约束的SQL语句，完成数据库初始化操作。所有的行分割、匹配规则都可以根据你实际的文本格式灵活调整。

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