search

stuarta0 / dbqf

A library and application for providing rich UX for querying complex databases
MIT License
16 stars 8 forks source link
application library sql winforms wpf

readme

dbqf

Database Query Framework

TL;DR

dbqf is a set of libraries to help you make searching, reporting and data mining easy for users in .NET applications. Oh and it's easy for you too.

Requirements

The long version

dbqf is a library primarily aimed at simplifying presentation of complex data mining for the end user in .NET applications. It's a full stack solution that gives you the boring SQL building components right up to the UI controls to dump into your application.

The project comes with the building blocks for you to construct whatever you need with tight integration into your application. In addition it has a standalone application that you can use side-by-side with your application with a single XML project file of your making.

Construction of the UI is modular with use of factories, adapters and (in the example Standalone project) dependency injection. The code is database engine agnostic allowing you to create whatever SQL is required to suit your engine (currently tested: MSSQL, SQLite). Note this is not an ORM but could conceivably have an adapter to an ORM's mapping.

The library employs many patterns from Gang of Four and the UI is abstracted using a variety of the Presentation Model (and subsequently MVVM). By doing this, the core behaviour can be reused across many UI toolkits with two provided out-of-the-box: WinForms and WPF. This project has unit tests for the core libraries, but less so for the UI. It also employs fluent patterns throughout the code to make things easier to work with.

No external libraries are needed for the core and WinForms libraries. The Standalone applications do require a number of external libraries but can be updated with automatic package restore.

Why would I want to use it?

Examples!

Standalone Application

Provided standalone application in .NET 4.5 WinForms that uses inversion of control, loads XML projects, has custom parsing and threaded behaviour.

Example-Standalone

Preset Search Control

Fully customisable list of field / value pairs.

Example-Preset

Standard Search Control

Fully customisable list of field / operator / value combinations.

Example-Standard

Advanced Search Control

Complete control over parameters with AND/OR from any field or path from within the database as well as selection of operator / value.

Example-Advanced

Custom Output Fields Control

Choice of fields to retrieve using drag-drop from hierarchical representation of data structure.

Example-Output

Generated SQL

Example of generated parameterised SQL (in psuedo form) created from core components in the library.

Example-Output

Detailed Component Explanation (Preset Search Control)

Preset

Documentation

Full documentation TBA. Code has XML documentation, test cases show usage, and Standalone project shows real-world use.

Sample Code

Using the core, Sql and WinForms libraries, here's sample usage in a WinForms application:

  var config = new dbqf.Sql.Configuration.MatrixConfiguration()
      .Subject(new dbqf.Configuration.Subject("Test")
          .Sql("SELECT * FROM [Test]")
          .FieldId(new dbqf.Configuration.Field("Id", typeof(int)))
          .FieldDefault(new dbqf.Configuration.Field("Name", typeof(string)))
          .Field(new dbqf.Configuration.Field("Total", typeof(int)))
          .Field(new dbqf.Configuration.Field("Date Created", typeof(DateTime))));

  var preset = new dbqf.WinForms.PresetView(new dbqf.Display.Preset.PresetAdapter<Control>(
      new dbqf.WinForms.UIElements.WinFormsControlFactory(), 
      new dbqf.Display.ParameterBuilderFactory()));

  preset.Adapter.SetParts(new dbqf.Display.FieldPathFactory().GetFields(config[0]));
  preset.Dock = DockStyle.Fill;
  this.Controls.Add(preset);

We create a configuration fluently, which contains one subject called Test with Sql that selects from a table named Test and has four fields; an id, name, total and date created.

Once we have our configuration set up, we want to display it. We'll use the PresetView in the WinForms library which requires a PresetAdapter<Control>. The generic type of the PresetAdapter (in this case Control) tells us that we need to provide a ControlFactory that dynamically creates UI elements of type Control. This should make sense since we're writing a WinForms application, so we want WinForms controls. In the example we use the built in WinFormsControlFactory class to do this for us.

Next we need to provide a ParameterBuilderFactory. The purpose of this class is to provide defaults for operators used for each field. What we mean by this is if you have a field Name (like in the sample code above) that is a typeof(string) then the most common operator an end user would want is Name CONTAINS "foo". This is what the built in ParameterBuilderFactory does - it looks at a field's type and determines the most common operator to use.

Once we've got our PresetView instantiated, we initialise it by calling SetParts. We pass it a list of fields that we want to show in the PresetView. If we use the built in FieldPathFactory it'll get us all fields for a subject excluding the Id field.

Then we add the PresetView to our Form and the user will see the fields.

Note: in terms of execution the flow will actually be:

License

This project has an MIT license to allow the most freedom of use. That said, myself and other users of the library would greatly appreciate any contributions you can make.