C#解析mdb文件

在 ASP.NET 应用程序中解析 mdb 文件，将 mdb 文件从前端上传，在后台解析，并将解析结果返回前端。

mdb是一种文件格式，它是Access数据库的一种文件存储格式，由于对数据操作的方便性，常用在一些中小型程序中；对于mdb格式的文件可以用Access打开。

我使用到的一个mdb文件如下：

编码：

创建一个工具类 MdbHelp ，用于封装对mdb文件的操作。

MdbHelp

public class MdbHelp
{
    private string fileName;
    private string connectionString;
    private OleDbConnection connection;

    public MdbHelp(string fileName)
    {
        this.fileName = fileName;
        this.connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";";
    }

    /// <summary>  
    /// 建立连接（打开数据库文件）  
    /// </summary>  
    public void Open()
    {
        try
        {
            // 建立连接  
            connection = new OleDbConnection(connectionString);

            // 打开连接  
            connection.Open();
        }
        catch (Exception)
        {
            throw new Exception("尝试打开 " + this.fileName + " 失败, 请确认文件是否存在！");
        }
    }

    /// <summary>  
    /// 断开连接（关闭据库文件）  
    /// </summary>  
    public void Close()
    {
        connection.Close();
    }

    /// <summary>  
    /// 根据sql命令返回一个DataSet  
    /// </summary>  
    /// <param name="sql">sql命令</param>  
    /// <returns>以DataTable形式返回数据</returns>  
    public DataTable GetDataSetBySql(string sql)
    {
        var dt = new DataTable();

        try
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connection);
            adapter.Fill(dt);
        }
        catch (Exception)
        {
            return null;
        }

        return dt;
    }

    /// <summary>
    /// 获取当前连接的mdb中的所有表名
    /// </summary>
    /// <returns></returns>
    public List<string> GetTableNames()
    {
        DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        List<string> tableNameList = new List<string>();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            var tableName = dt.Rows[i]["TABLE_NAME"].ToString();
            tableNameList.Add(tableName);
        }
        return tableNameList;
    }
}

service层代码：

MdbService

public class MdbService : IMdbService
{
    private readonly IWebHostEnvironment webHostEnvironment;

    public MdbService(IWebHostEnvironment webHostEnvironment)
    {
        this.webHostEnvironment = webHostEnvironment;
    }

    public void Dispose()
    {
        GC.SuppressFinalize(this);
    }

    /// <summary>
    /// 解析mdb文件
    /// </summary>
    /// <param name="file"></param>
    /// <returns></returns>
    public async Task<Result<List<TableResult>>> ResolvingMdb(IFormFile file)
    {
        if (file == null || file.Length == 0)
        {
            return Result.BadRequest<List<TableResult>>("未找到文件");
        }

        if (!file.FileName.EndsWith(".mdb"))
        {
            return Result.BadRequest<List<TableResult>>("文件格式不支持");
        }

        // 拼接文件路径
        var directoryPath = Path.Combine(webHostEnvironment.ContentRootPath, "Files/Temp");
        // 检验路径是否存在
        var directoryInfo = new DirectoryInfo(directoryPath);
        if (!directoryInfo.Exists)
        {
            directoryInfo.Create();
        }
        // 文件名
        var fileName = $"{DateTime.Now:yyyyMMddHHmmss}-{file.FileName}";
        // 文件全路径
        var fileFullPath = Path.Combine(directoryPath, fileName);
        using var stream = File.Create(fileFullPath);
        // 将上传的文件复制到本地
        await file.CopyToAsync(stream);
        stream.Close();

        try
        {
            // 打开连接
            var mdbHelp = new MdbHelp(fileFullPath);
            mdbHelp.Open();

            // 获取所有表名
            var tableNames = mdbHelp.GetTableNames();

            var tableResultList = new List<TableResult>();
            foreach (var tableName in tableNames)
            {
                // 查询 sql
                var sql = "select * from " + tableName;

                var tableResult = new TableResult();
                tableResult.TableName = tableName;

                // 执行sql
                var dataTable = mdbHelp.GetDataSetBySql(sql);

                // 遍历DataTable
                var rowValueList = new List<List<string>>();
                foreach(DataRow row in dataTable.Rows)// 行
                {
                    var rowValues = new List<string>();
                    for (int i = 0; i < dataTable.Columns.Count; i++)// 列
                    {
                        if (row[i] != null)
                        {
                            rowValues.Add(row[i].ToString());
                        }
                        else
                        {
                            rowValues.Add("");
                        }
                    }
                    rowValueList.Add(rowValues);
                }

                // 获取列名
                var colNames = new List<string>();
                for (int i = 0; i < dataTable.Columns.Count; i++)
                {
                    colNames.Add(dataTable.Columns[i].ColumnName);
                }

                tableResult.RowNames = colNames;
                tableResult.Rows = rowValueList;
                tableResultList.Add(tableResult);
            }

            // 关闭连接
            mdbHelp.Close();

            return Result.Ok(tableResultList);
        }
        catch (Exception ex)
        {
            return Result.BadRequest<List<TableResult>>(ex.Message);
        }
        finally
        {
            // 删除导入文件
            if (File.Exists(fileFullPath))
            {
                File.Delete(fileFullPath);
            }
        }
    }
}

其中用到的两个工具类如下：

Result

public class Result<T>
{
    /// <summary>
    /// 状态码，0：失败，1：成功
    /// </summary>
    public int Code { get; set; }

    /// <summary>
    /// 消息
    /// </summary>
    public string Msg { get; set; }

    /// <summary>
    /// 数据
    /// </summary>
    public T Data { get; set; }
}

public class Result : Result<object>
{
    public static Result Succeed(string msg = "请求成功")
    {
        return new Result
        {
            Code = 1,
            Msg = msg
        };
    }

    public static Result<T> Succeed<T>(T data, string msg = "请求成功")
    {
        return new Result<T>
        {
            Code = 1,
            Msg = msg,
            Data = data
        };
    }

    public static Result Fail(string msg = "请求失败")
    {
        return new Result
        {
            Code = 0,
            Msg = msg
        };
    }

    public static Result<T> Fail<T>(string msg = "请求失败")
    {
        return new Result<T>
        {
            Code = 0,
            Msg = msg
        };
    }

    public static Result Ok()
    {
        return Succeed();
    }

    public static Result Ok(string msg)
    {
        return Succeed(msg);
    }

    public static Result<T> Ok<T>(T data)
    {
        return Succeed(data);
    }

    public static Result<T> Ok<T>(T data, string msg)
    {
        return Succeed(data, msg);
    }

    public static Result BadRequest()
    {
        return Fail();
    }

    public static Result BadRequest(string msg)
    {
        return Fail(msg);
    }

    public static Result<T> BadRequest<T>()
    {
        return Fail<T>();
    }

    public static Result<T> BadRequest<T>(string msg)
    {
        return Fail<T>(msg);
    }

    public static Result NotFound(string msg = "未找到对象")
    {
        return Fail(msg);
    }

    public static Result<T> NotFound<T>(string msg = "未找到对象")
    {
        return Fail<T>(msg);
    }
}

TableResult

/// <summary>
/// 收集一张表的数据
/// </summary>
public class TableResult
{
    /// <summary>
    /// 表名
    /// </summary>
    public string TableName { get; set; }
    /// <summary>
    /// 所有列名
    /// </summary>
    public List<string> RowNames { get; set; }
    /// <summary>
    /// 所有行的值，按列名顺序排列
    /// </summary>
    public List<List<string>> Rows { get; set; }
}

controller层：

MdbsController

[Route("api/mdb")]
[ApiController]
public class MdbsController : ControllerBase
{
    private readonly IMdbService mdbService;

    public MdbsController(IMdbService mdbService)
    {
        this.mdbService = mdbService;
    }

    /// <summary>
    /// 解析mdb文件，返回文件中的所有数据
    /// </summary>
    /// <param name="formFile"></param>
    /// <returns></returns>
    [HttpPost("resolving-mdb")]
    public async Task<ActionResult<Result<List<TableResult>>>> ResolvingMdb(IFormFile formFile)
    {
        var res = await mdbService.ResolvingMdb(formFile);
        return Ok(res);
    }
}