Tools

MOBZQuery

AOT!

MOBZQuery started life as an experiment. I'd been trying to build fast, compact, native command line utiities using .NET, so I paid a lot of attention to Ahead Of Time (AOT) compilation. Before .NET 10, AOT was possible, but not very widely supported, particularly by thrird-party libraries. And the ones I was intending to use were System.CommandLine and Spectre.Console. Both has issues with AOT - until recently with .NET 10!

So I thought I could finally write that 10 MB .exe that used the full power of .NET to leverage MOBZystems.Data, our tiny data access library, to query data from OleDb sources. OleDb is very powerful: you install a provider on your PC for your type of database (say Microsoft Sql Server, or MySql, or even Access) and .NET allows you to write generic database code against it. The trick is that the connection to the database is specified through a connection string that contains a reference to the provider to use. In theory: great. You can write a utility that queries your OleDb data source and displays the result, using nothing more than a connection string (and a Sql query of course).

The first results were very encouraging. A simple console project with both System.CommandLine and Spectre.Console (and MOBZystems.Data) compiled AOT just fine to about 10 MB. Great!

... or not.

Then I added System.Data.OleDb and tried to connect to an Access database. I got a message saying that COM was disabled by a feature flag. I had to Google that: it turns out that you need to add a property BuiltInComInteropSupport to your project to allow (experimental!) COM access - which is required for OleDb, apparently. Then it turned out I couldn't use AOT with System.Data.OleDb (probably for the same reason). So that was out the door. But publishing as a single file application and enabling trimming helped a lot and produced roughly the same results, as far as self-contained application size was concerned.

Next: use OleDb to access a Sqlite database! First, find and install an OleDb provider for Sqlite. Turns out there isn't a non-commercial one. So I added a reference to Microsoft.Data.Sqlite, which enables the application to connect to Sqlite database directly. Did this kill the original motion to build a generic OleDb query tool? Yes it did. Yes it did, indeed.

For MySql, the situation is similar. There is a MySql.Data package that allows direct connections to MySql databasee, but not through OleDb. All right, fine, whatever: added that too.

MOBZQuery usage

So now I have the following command structure:

mobzquery v0.9.0 by MOBZystems - https://www.mobzystems.com (x64)

Description:
  Query OleDb, MySql and Sqlite data sources

Usage:
  mobzquery [command] [options]

Options:
  -t, --type <ace|jet|mysql|sqlite>  The data source type. If provided, the source is
                                     simply a file name (except for MySql)
  -v, --verbose                      Show detailed output
  -?, -h, --help                     Show help and usage information
  --version                          Show version information

Commands:
  info       Show column information about a query
  table      Query a data source and show contents as a table
  json       Query a data source and show contents as JSON
  csv        Query a data source and show contents as CSV
  scalar     Query a data source for a single value
  update     Check for updates

Basically, you can do a few things with a data source and a query: display the results as a table on the terminal, as a CSV file, of as JSON. You specify which as the command (table, csv or json), plus the data source, plus the query:

mobzquery table "Provider=...;Data Source=..." "select * from someTable"

But to know which type of connection to open (an OleDbConnection, a SqliteConnection or a MySqlConnection) I need to have the --type specified (or not, for plain OleDb connections). The possible values are ace, jet, mysql and sqlite. 'ace' and 'jet' are also special cases: MOBZQuery builds the OleDb connection string for them itself, so you can just specify the name of the database as the data source, as in:

mobzquery --type jet "\path\to\database.mdb" "select ..."

'jet' use the Jet 4.0 OleDb provider, 'ace' Ace 12.0 version. You need the correct provider installed on your PC (and for the right bitness, too! If you have a 32-bit Access provider installed but are using a 64-bit version of MOBZQuery, it will not find the provider)

The next unpleasant surprise is that MySql.Data doesn't work well with trimming. So we can make a single-file executable but we cannot trim off unused code, so the resulting executable is more than 75 MB in size! That's a far cry from the nice 10 MB we started out with...

What else? You can specify info for the command and it'll display a list of column names and their .NET data types for the query. (I never know what type of result to expect from booleans or bits or even integers. Int32? Int64? Decimal?)

The scalar command gets a single value from the data source and prints it on the terminal. Intended for use in batch files as the result from a Count(*) or Exists() result.

And the update command checks for updates.

Specify --verbose to see a bit more information (like the actual connection string used).

Query parameters

To prevent you from having to construct your own query by hand (and making yourself vulnerable to Sql Injection) MOBZQuery supports parameters. If the query contains placeholders like @1, @2, etc. you can specify values after the query:

mobzquery <data source> "Select * From Table Where Thing Between @1 and @2" 15 183

will create parameters on the query and provide the values 15 and 183 for them, effectively (and safely!) returning the same as "Select * From Table Where Thing Between 15 and 183". Note that there are no parameter names: by convention they are just numbered from 1.

Releases

Because OleDb is Windows-only, MOBZQuery is only available for Windows. And since the self-contained version is so much larger than the framework-dependent version (which requires .NET 10 to be installed), I've decided to publish both. And because why not, also for arm64. So there you have it: six releases per version.

Because or their size, the self-contained versions are only available on the Github Releases page.