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);
}
}
