DataQuery.Net 1.0.3

.NET Core 3.1 .NET Standard 2.0
There is a newer version of this package available.
See the version list below for details.
Install-Package DataQuery.Net -Version 1.0.3
dotnet add package DataQuery.Net --version 1.0.3
<PackageReference Include="DataQuery.Net" Version="1.0.3" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add DataQuery.Net --version 1.0.3
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: DataQuery.Net, 1.0.3"
#r directive can be used in F# Interactive, C# scripting and .NET Interactive. Copy this into the interactive tool or source code of the script to reference the package.
// Install DataQuery.Net as a Cake Addin
#addin nuget:?package=DataQuery.Net&version=1.0.3

// Install DataQuery.Net as a Cake Tool
#tool nuget:?package=DataQuery.Net&version=1.0.3
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

Build status Nuget

DataQuery.Net

The data query is an ASP.Net Core library for querying dynamically huge database using a basic querying language similar to Google analytics's API Explorer querying language (dimensions, metrics, filters...) . This tool was particularly useful for building a custom analytic tools on a bug database using millions of lines.

Prerequisite

You need an SQL database on SQL Server 2012+ ASP.Net Core 3.1 You'll need an sql database structured as a star : (https://en.wikipedia.org/wiki/Star_schema)[https://en.wikipedia.org/wiki/Star_schema]

Démarrage rapide

Installer le package nuget

package-install DataQuery.Net

Sample configuration in Startup.cs ConfigureServices() method :

services.RegisterSqlDataQueryServices(options => {
    options.ConnectionString = "{your SQL Server connection string here}";
});
services.RegisterDataQueryProvider<MyAwesomeDataQueryProvider>();

Implement the IDataQueryProvider interface to provide the metrics and dimensions lists to query :


  public class MyAwesomeDataQueryProvider : IDataQueryProvider
  {
    public DataQueryCollections Provide()
    {
      var cnx = "Ma chaine de connexion à la BDD ici";
      var config = new DataQueryCollections() { };

      config.Tables["User"] = new Table()
      {
        // The table name, it must match the key name
        Name = "User",
        // The AS alias "select from table AS {alias}"
        Alias = "U",
        // The properties you would like to query (Just the columns you need to query or used in relationships)
        Props = new List<DatabaseProp>
        {
          new DatabaseProp()
          {
			// ALias : The unique name of the dim or metric
            Alias = "UserId",
			// La colonne : correspond à ce qui va être sélectionné par le requêteur. If it's a metric, you must use the proper aggregation operator : e.g. SUM(), AVG(), COUNT()...
            Column = "U.Id",
			// Field description
            Description = "User's id",
            Label="Userid",
            // Le type SQL du champ sera utile pour parser les dimensions sélectionnés.
            SqlType = SqlDbType.Int,
            // Ce flag permet de déterminer si c'est une dimension visible ou non
            Displayed = true,
            // A false par défaut, cette variable permet de déterminer si c'est une métrique ou non. Si s'en est une elle sera exclue automatiquement de la clause groupby. Si elle n'aggrège rien, il y aura une erreur
            IsMetric = false,
            // SQL join. The key is the "Name" of the target table, the value is the name of the prop (IN SQL, do not take the alias).
            // The sql join must be done in the both side. In this use case, in "User_Stat" => to User.id and "User" => to User_State.UserId.
            SqlJoin = new Dictionary<string, string>
            {
              {"User_Stat", "UserId" }
            }
          },
          new DatabaseProp()
          {
            Alias = "Name",
            Column = "U.Name",
            Description = "User's name",
            Label="Username",
            Displayed = true
          },
          new DatabaseProp()
          {
            Alias = "Email",
            Column = "U.Email",
            Description = "Email",
            Label="Email",
            Displayed = true
          }
        }
      };

      config.Tables["User_Stat"] = new Table()
      {
        Name = "User_Stat",
        Alias = "US",
        Props = new List<DatabaseProp>
        {
          new DatabaseProp()
          {
            Alias = "UserRef",
            Column = "US.UserId",
            Displayed = true,
            SqlJoin = new Dictionary<string, string>
            {
              {"User", "UserId" }
            }
          },
          new DatabaseProp()
          {
            Alias = "Date",
            Column = "US.Date",  
			// This dimension will be used to filter date by default
			UsedToFilterDate = true,
            Description = "Date",
            SqlType = System.Data.SqlDbType.Date,
            Displayed = true
          },
          new DatabaseProp()
          {
            Alias = "NbConnexion",
            Column = "SUM(U.NbConnexion)",
            Description = "NbConnexion",
            Label="NbConnexion",
            IsMetric = true,
            Displayed = true
          }
        }
      };


      return config;
    }
  }

In this sample, we have configured two tables :

  • User: Name, Email, UserId
  • User_Stat: Date, NbConnexion, UserRef

Important note The metric's alias must be unique, because it will be used for querying data

Querying the data

For executing the data in a sample webapp :


public TestController : Controller
{
  public IDataQuery _dataQuery;
	public TestController(IDataQuery dataQuery){
		_dataQuery = dataQuery;
	}

	[HttpGet]
	public IActionResult GetStats(DataQueryFilterParam params){
	    var results =	_dataQuery.Query(params);
		return Ok(results);
	}

}

Here is a sample query to get the nb connexions per date on 2 weeks for Jean-Marc :

/Test?dimensions=Date&metrics=NbConnexion&period=2w&asc=false&sort=Date&filters=Name%3DJean-Marc

Paramètre des requêtes

Query params list :

  • aggregate : wether the data are grouped or not
  • size : size of the recordset (paginated results)
  • page : page index
  • query: full text query (using FullText index)
  • queryConstraint : for limiting field used in full text query.
  • start : start date for period filtering
  • end : end date for period filtering
  • period : Perdiods : 1w = 1 week, 3m = 3 months
  • sort : name of the metric or dimension used to sort data
  • dimensions : comma separated dimensions to select, ex: UserName, Email
  • metrics : list of metrics to query, ex: NbConnexions
  • filters : filter used, ex: (Name==Toto,Name!=Titi);NbViews>12;Date>01/01/2020 , = OR / ; = AND / == = equal / != = different / =~ = LIKE (with a % on the value)
Product Versions
.NET net5.0 net5.0-windows net6.0 net6.0-android net6.0-ios net6.0-maccatalyst net6.0-macos net6.0-tvos net6.0-windows
.NET Core netcoreapp2.0 netcoreapp2.1 netcoreapp2.2 netcoreapp3.0 netcoreapp3.1
.NET Standard netstandard2.0 netstandard2.1
.NET Framework net461 net462 net463 net47 net471 net472 net48
MonoAndroid monoandroid
MonoMac monomac
MonoTouch monotouch
Tizen tizen40 tizen60
Xamarin.iOS xamarinios
Xamarin.Mac xamarinmac
Xamarin.TVOS xamarintvos
Xamarin.WatchOS xamarinwatchos
Compatible target framework(s)
Additional computed target framework(s)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.0.16 1,226 1/28/2021
1.0.14 163 1/28/2021
1.0.13 160 1/28/2021
1.0.11 148 1/28/2021
1.0.10 144 1/28/2021
1.0.6 155 1/28/2021
1.0.3 187 1/25/2021