by Andreas

Free .DBF Query Manager

A recent request from a client included digging into some data from an old CRM system, which is using the .DBF format. A quick search online showed that there are actually people charging money for query managers supporting this format – which, by the way, is obsolete and slow. I therefore sacrificed a couple of hours of my precious time (..) and put together a very simple DBF Query Manager, written in managed C# code.

This might be developed further, but I honestly doubt it. It solved my issues, and might solve yours as well.

1. Choose a directory where the DBF files are stored (DBF is a flat file database system where a table is represented as a file within a directory). In my example the .DBF files are stored in Q:DBFDemo:

Login

2. Click Connect to open the Main Query Window:

QueryManager

3. Run your queries as you would in an overpriced, commercial, dishonest version! Including nested queries – any syntactically correct query will work.

Note: this thing is free. Free like it used to be in the old days – with no strings attached, or a “max 30 days use before we rip you off”-scheme hidden somewhere in the code. It took me a couple of hours to build. This blog post included! But for that very reason there is hardly any error handling! It will display a “Table not found” error message whenever it detects something wrong – like if you screw up the query. There is no intellisense (or intelligence..), and it is READ-ONLY! At least for now..

But it works! Like a charm! And THAT’s the main point!

Click here to download (requires .NET Framework 4.0)

by Andreas

Generisk databaseklient

Nylig oppstod et behov for å hente ut informasjon fra et sett med .DBF-filer som stammet fra en kundes eldre backendsystem. Dette dreide seg om en databasestrukur hvor tabellene var lagret i separate filer, og sannsynligvis lå det nok en relasjonsoversikt godt gjemt inne i det gamle backendsystemet et sted. Kort oppsummert: Kunden trengte en daglig dump av disse dataene, i et format som passet et nyere 3-partssystem.

Kjappeste vei til mål var å lage en generisk OLEDB-klient som kunne hente ut det jeg trengte fra de tabellene jeg var interessert i. Gjenbruk var derfor av høy pri, og løsningen jeg kom frem til er noe som lett kan pakkes i en assembly og brukes mot nyere eller eldre databasesystemer når behovene er enkle og tiden er knapp.

Logging og utvidet feilhåndtering er fjernet for å øke lesbarhet. OleDbConnection kan byttes ut med for eksempel SqlConnection om måldatabasen er SQL Server:

OLEDBClient.cs

public class OLEDBClient
{
private OleDbConnection _conn = null;
private string _connectionString = null;

public OLEDBClient(string connectionString)
{
_connectionString = connectionString;
}

private void connect()
{
// throws exception if connection fails
if (_conn == null)
{
_conn = new OleDbConnection(_connectionString);
}
}

public bool testConnection()
{
try
{
if (_conn == null)
connect();

return true;
}
catch (Exception e)
{
_log.Fatal("testConnection() error: '" + e.Message + "'");
return false;
}
}

public DataSet runGenericSQL(string sql)
{
try
{
if (_conn == null)
connect();

OleDbDataAdapter adapter = new OleDbDataAdapter(sql, _conn);
DataSet resultSet = new DataSet();
adapter.Fill(resultSet);
return resultSet;

}
catch (Exception e)
{
return null;
}
}
}

Denne klassen kan da kalles med en connection string som peker på en .dbf, i dette formatet:
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TEST\DB.DBF”:

SomeApp.cs

private void GetSomeDataFromOleDB()
{
OLEDBClient client = new OLEDBClient(_DBConnectionString);

// ** SQL QUERY DEFINED HERE ** //
DataSet result = client.runGenericSQL("SELECT * FROM someTable WHERE someField IS NOT NULL");

if (result == null)
throw new Exception("Unable to retrieve data from database connection '" + _DBConnectionString + "'");

// loop through all records
foreach (DataRow row in result.Tables[0].Rows)
{
if (row["someField"] != null && !row["someField"].Equals(""))
{
// do something

}

......