[C#]統計Visual FoxPro資料庫總筆數
紀錄一下過程
問題描述
最近接手的系統使用Visual FoxPro開發，之前完全沒有接觸過，想要統計資料庫表格(DBF檔)總筆數，用開發工具要這樣看：
很麻煩，survey下列幾套
發現沒有此功能，自行開發看看…
程式架構
基本流程如下
1. 連線到foxpro資料庫檔案所在的資料夾。
2. 針對每個資料表格檔案(*.DBF)，統計總筆數。
3. 統計最後的結果。
FoxPro資料庫連線
using System.Data;
using System.Data.OleDb;
using System.IO;
/// <summary>
/// ForPro 資料存取層
/// 要先下載Data Provider for FoxPro
/// http://www.microsoft.com/en-us/download/details.aspx?id=14839
/// </summary>
public class DaoFoxPro
{
private string connectString = string.Empty;
public DaoFoxPro(string dbfdir)
{
DirectoryInfo di;
if (Directory.Exists(dbfdir) == false)
{
throw new DirectoryNotFoundException("找不到DBF檔案資料夾");
}
di = new DirectoryInfo(dbfdir);
connectString = @"Provider=vfpoledb;Data Source=" + di.FullName + "\\;Collating Sequence=machine;";
}
public DataTable GetQueryResult(string sqlString)
{
DataTable myTable = new DataTable();
using (OleDbConnection myConnection = new OleDbConnection(connectString))
{
try
{
OleDbDataAdapter adpt = new OleDbDataAdapter(sqlString, myConnection);
adpt.Fill(myTable);
}
catch (OleDbException ex)
{
throw ex;
}
}
return myTable;
}
}
讀取每個DBF檔案資訊統計總筆數
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
namespace DisplayDBFInfo.Model
{
/// <summary>
/// 管理DBF資料庫檔案相關的資訊
/// </summary>
class DBfMgr
{
//指定DBF檔案路徑位置
private string filefolder = string.Empty;
private DaoFoxPro fxDao;
public DBfMgr(string dbfdir)
{
if (fxDao == null)
{
fxDao = new DaoFoxPro(dbfdir);
filefolder = dbfdir;
}
}
/// <summary>
/// 取得資料夾所有的DBF檔案名稱
/// </summary>
/// <returns></returns>
public List<string> GetDBFFileList()
{
List<string> dbfFileList = new List<string>();
string[] extension = new string[] { ".DBF" };
dbfFileList = GetAllFilesNameStringWithPositiveFilter(filefolder, extension);
return dbfFileList;
}
/// <summary>
/// Gets all table total num.
/// </summary>
/// <returns></returns>
public DataTable GetAllTableTotalNum()
{
//該資料夾所有的DBF檔案名稱
List<string> dbfFileList = GetDBFFileList();
string sqlcmd = string.Empty;
//建立總筆數表格
DataTable totalTableNum = GetDefaultTotalNumTable();
DataTable dtResult = null;
foreach (string dbfFile in dbfFileList)
{
sqlcmd = "select '" + dbfFile + "' as tablename,count(1) as number ,'正常' as status from " + dbfFile;
try
{
dtResult = fxDao.GetQueryResult(sqlcmd);
}
catch (OleDbException ex)
{
dtResult = CreateErrorTable(dbfFile, ex.Message);
}
finally
{
MergeSearchDataToTable(ref totalTableNum, dtResult);
}
}
return totalTableNum;
}
#region DataTable操作
/// <summary>
/// 將DataRow加入DataTable.
/// </summary>
/// <param name="dtSource">The dt source.</param>
/// <param name="dr">The dr.</param>
/// <returns></returns>
public void MergeSearchDataToTable(ref DataTable dtDest, DataTable dtSource)
{
int i = 0; //讀取過濾的資料
foreach (DataRow item in dtSource.Rows)
{
DataRow row = dtDest.NewRow();
i = 0;
foreach (DataColumn dc in dtSource.Columns)
{
//建立DataRow的資料
row[dc.ToString()] = item.ItemArray[i];
i++;
}
dtDest.Rows.Add(row);
}
}
/// <summary>
/// 建立空白的統計表.
/// </summary>
/// <returns></returns>
private static DataTable GetDefaultTotalNumTable()
{
DataTable totalTableNum = new DataTable();
DataColumn tablename = new DataColumn("tablename", typeof(string));
totalTableNum.Columns.Add(tablename);
DataColumn number = new DataColumn("number", typeof(string));
totalTableNum.Columns.Add(number);
DataColumn status = new DataColumn("status", typeof(string));
totalTableNum.Columns.Add(status);
return totalTableNum;
}
/// <summary>
/// 當DBF讀取發生異常建立相關資訊.
/// </summary>
/// <param name="tablename">The tablename.</param>
/// <param name="message">The message.</param>
/// <returns></returns>
public DataTable CreateErrorTable(string tablename, string message)
{
DataTable errorTable = GetDefaultTotalNumTable();
DataRow dr = errorTable.NewRow();
dr["tablename"] = tablename;
dr["number"] = "NaN";
dr["status"] = message;
errorTable.Rows.Add(dr);
return errorTable;
}
#endregion
#region 過濾檔案
/// <summary>
/// 取得資料夾所有檔案名稱包括子資料夾下的檔案.(正面表列)
/// </summary>
/// <param name="dir"></param>
/// <param name="extentsions">正面表列</param>
/// <returns></returns>
public List<string> GetAllFilesNameStringWithPositiveFilter(String dir, string[] extentsions)
{
List<string> fileNameString = new List<string>();
if (Directory.Exists(dir))
{
DirectoryInfo di = new DirectoryInfo(dir);
foreach (FileInfo fi in di.GetFiles())
{
if (CheckFileExtensionPositive(fi.FullName, extentsions) == true)
{
fileNameString.Add(fi.Name);
}
}
foreach (DirectoryInfo item in di.GetDirectories())
{
fileNameString.AddRange(GetAllFilesNameStringWithPositiveFilter(item.FullName, extentsions).ToArray());
}
}
return fileNameString;
}
/// <summary>
/// 過濾檔案副檔名(正面列表)
/// </summary>
/// <param name="fileName">檔案名稱</param>
/// <param name="exceptExtension">副檔名</param>
/// <returns></returns>
public bool CheckFileExtensionPositive(string fileName, string[] exceptExtension)
{
FileInfo fi = new FileInfo(fileName);
if (fi.Exists == false)
{
return false;
}
else
{
foreach (string extName in exceptExtension)
{
if (fi.Extension == extName)
{
return true;
}
}
return false;
}
}
#endregion
}
}
主程式
using System;
using System.Windows.Forms;
namespace DisplayDBFInfo
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string dir = txt_DbfDir.Text.Trim();
DisplayDBFInfo.Model.DBfMgr dbMgr = new Model.DBfMgr(dir);
dataGridView1.DataSource = dbMgr.GetAllTableTotalNum();
}
}
}
執行畫面
後續問題
1. 有些檔案讀取失敗，必須找出原因排除
2. 學習有關Visual FoxPro的開發技巧
參考資料
Connection strings for Visual FoxPro / FoxPro 2.x
Microsoft OLE DB Provider for Visual FoxPro 9.0
The 'vfpoledb' provider is not registered on the local machine