MyDevTime.SqlDataProvider 1.7.0

MyDevTime.SqlDataProvider

A config-driven SQL data provider framework that exposes arbitrary database queries through multiple ASP.NET Core protocols. Define SQL queries in appsettings.json and the framework automatically exposes them via GraphQL, OData, SignalR, and gRPC — with schema auto-discovery, typed endpoints, filtering, sorting, and projection.

Supports .NET 8, .NET 9, and .NET 10.

Packages

Package	Version	Description
MyDevTime.SqlDataProvider		Core library — connection factories, SQL dialects, query builder, schema discovery
MyDevTime.SqlDataProvider.AspDotNetCore.GraphQl		HotChocolate GraphQL integration
MyDevTime.SqlDataProvider.AspDotNetCore.OData		OData REST integration
MyDevTime.SqlDataProvider.AspDotNetCore.SignalR		SignalR real-time integration
MyDevTime.SqlDataProvider.AspDotNetCore.gRPC		gRPC integration

Features

• Config-driven — define endpoints in appsettings.json, no code changes needed
• Schema auto-discovery — column names, types, and nullability discovered at startup via SchemaOnly execution
• Multiple protocols — GraphQL (HotChocolate), OData, SignalR, gRPC
• Multi-database — pluggable IDbConnectionFactory with built-in SQL Server support; add any ADO.NET provider (SQLite, PostgreSQL, MySQL, etc.)
• SQL dialect abstraction — ISqlDialect handles paging syntax and column quoting per database (ANSI, SQL Server, PostgreSQL, MySQL built-in)
• SQL injection prevention — ValidColumns whitelist enforced on all filter/sort/projection inputs
• Filtering — per-endpoint opt-in with column-level control
• Sorting — single and multi-column, ASC/DESC
• Projection — auto-projection from GraphQL selection set; explicit column selection for other protocols
• Paging — offset-based with skip/take and totalCount across all protocols (default: 50 rows)
• Hot-reload — OData, SignalR, and gRPC reload endpoint config on appsettings.json changes without restart

Quick Start

1. Install the packages

dotnet add package MyDevTime.SqlDataProvider
dotnet add package MyDevTime.SqlDataProvider.AspDotNetCore.GraphQl   # and/or other protocols

2. Register the provider

// GraphQL
builder.Services
    .AddGraphQLServer()
    .AddQueryType(d => d.Name("Query").Field("_empty").Resolve(""))
    .AddGraphQlSqlDataProvider();

// OData
builder.Services.AddODataSqlDataProvider();

// SignalR
builder.Services.AddSignalRSqlDataProvider();

// gRPC
builder.Services.AddGrpcSqlDataProvider();

3. Register a database connection factory

using MyDevTime.SqlDataProvider.Connection;

// SQL Server (built-in)
builder.Services.AddDbConnectionFactory("mssql", new SqlClientConnectionFactory());

// Custom providers
builder.Services.AddDbConnectionFactory("sqlite", new SqliteConnectionFactory());
builder.Services.AddDbConnectionFactory("postgres", new NpgsqlConnectionFactory());

ProviderName in the profile config selects which factory to use. Duplicate registrations throw at startup.

4. Configure endpoints in appsettings.json

{
  "SqlRetrievalProfiles": [
    {
      "ProfileName": "AdventureWorks",
      "ConnectionString": "Server=...;Database=AdventureWorks;...",
      "ProviderName": "mssql",
      "RetrievalEndpointProfiles": [
        {
          "EndPointName": "Customers",
          "SqlQuery": "SELECT CustomerID, FirstName, LastName, EmailAddress FROM Customer",
          "AllowFiltering": true,
          "AllowSorting": true,
          "AllowProjection": true
        },
        {
          "EndPointName": "Products",
          "SqlQuery": "SELECT ProductID, Name, ListPrice FROM Product WHERE ListPrice > 0",
          "AllowFiltering": true,
          "AllowSorting": true,
          "AllowProjection": false
        }
      ]
    }
  ]
}

Column lists for filtering/sorting/projection are auto-discovered from the query at startup. You can also pin them explicitly:

{
  "EndPointName": "Customers",
  "SqlQuery": "SELECT CustomerID, FirstName, LastName FROM Customer",
  "AllowFiltering": true,
  "FilterableColumns": ["LastName"],
  "AllowSorting": true,
  "AllowProjection": true
}

Explicit lists are never overwritten by discovery.

Protocol Usage

GraphQL

Endpoints are exposed as top-level query fields named {ProfileName}_{EndPointName}. Results are wrapped in a paged response with items and totalCount.

# Basic query — auto-projects from selection set
{
  AdventureWorks_Customers {
    items { customerID firstName lastName }
    totalCount
  }
}

# Filtering (HotChocolate-style where input)
{
  AdventureWorks_Customers(where: { lastName: { eq: "Miller" } }) {
    items { customerID firstName lastName }
    totalCount
  }
}

# Multiple conditions (AND)
{
  AdventureWorks_Customers(
    where: { lastName: { eq: "Miller" }, firstName: { startsWith: "J" } }
  ) {
    items { customerID firstName lastName }
  }
}

# OR conditions
{
  AdventureWorks_Customers(
    where: { or: [{ lastName: { eq: "Miller" } }, { lastName: { eq: "Smith" } }] }
  ) {
    items { customerID firstName lastName }
  }
}

# HC-style sorting
{
  AdventureWorks_Customers(order: [{ lastName: ASC }, { firstName: DESC }]) {
    items { lastName firstName }
  }
}

# Paging
{
  AdventureWorks_Customers(skip: 20, take: 10) {
    items { customerID firstName lastName }
    totalCount
  }
}

Filter field names are camelCased. Supported operators by type:

Note: GraphQL schema is built at startup. Adding or removing endpoints requires a restart.

OData

GET /odata/{ProfileName}/{EndPointName}
GET /odata/AdventureWorks/Customers?$filter=LastName eq 'Miller'
GET /odata/AdventureWorks/Customers?$orderby=LastName asc
GET /odata/AdventureWorks/Customers?$select=CustomerID,FirstName,LastName
GET /odata/AdventureWorks/Customers?$top=10&$skip=20
GET /odata/AdventureWorks/Customers?$count=true

Supported $filter operators:

Logical combinators and / or and parenthesized grouping are supported (and binds tighter than or):

$filter=Color eq 'Red' and ListPrice gt 100
$filter=Color eq 'Red' or Color eq 'Blue'
$filter=(LastName eq 'Miller' or LastName eq 'Smith') and FirstName eq 'John'

SignalR

Hub URL: /sqlDataHub (configurable via MapSqlDataProviderHub("/custom-path"))

Connection

const connection = new signalR.HubConnectionBuilder()
    .withUrl("/sqlDataHub")
    .build();
await connection.start();

Hub Method: ExecuteQuery

ExecuteQuery(profileName, endpointName, filters?, sorting?, projection?, skip?, take?)
  -> { data, totalCount, success } | { error, success }

Supported filter operators: =, !=, <>, >, >=, <, <=, LIKE, NOT LIKE, IN, NOT IN

Multiple filters are combined with AND. Values are auto-unwrapped from JSON.

Examples

// Simple query (returns first 50 rows by default)
const result = await connection.invoke(
    "ExecuteQuery", "AdventureWorks", "Customers",
    null, null, null, null, null
);

// With filter
const filtered = await connection.invoke(
    "ExecuteQuery", "AdventureWorks", "Customers",
    [{ column: "LastName", operator: "=", value: "Miller" }],
    null, null, null, null
);

// With sorting and paging
const page2 = await connection.invoke(
    "ExecuteQuery", "AdventureWorks", "Customers",
    null,
    [{ column: "LastName", ascending: true }],
    null,
    20,   // skip
    20    // take
);
// page2.totalCount = 847, page2.data contains rows 21-40

gRPC

Service Definition

service SqlDataProviderGrpc {
  rpc ExecuteQuery (QueryRequest) returns (QueryResponse);
  rpc GetCatalog (CatalogRequest) returns (CatalogResponse);
}

GetCatalog — Discover Available Endpoints

Returns all profiles, their endpoints, discovered column schemas, and allowed column lists.

var catalog = await client.GetCatalogAsync(new CatalogRequest());
foreach (var profile in catalog.Profiles)
{
    Console.WriteLine($"Profile: {profile.Name}");
    foreach (var endpoint in profile.Endpoints)
    {
        Console.WriteLine($"  Endpoint: {endpoint.Name}");
        foreach (var col in endpoint.Columns)
            Console.WriteLine($"    Column: {col.Name} ({col.ClrType}, nullable={col.Nullable})");
    }
}

ExecuteQuery — Run a Query

Response contains success, error, json_data (JSON-serialized row array), and total_count.

Supported filter operators: =, !=, <>, >, >=, <, <=, LIKE, NOT LIKE, IN, NOT IN

Examples

// Simple query
var response = await client.ExecuteQueryAsync(new QueryRequest {
    ProfileName = "AdventureWorks",
    EndpointName = "Customers"
});

// With filter
var filtered = await client.ExecuteQueryAsync(new QueryRequest {
    ProfileName = "AdventureWorks",
    EndpointName = "Customers",
    Filters = { new FilterDescriptor { Column = "LastName", Operator = "=", Value = "Miller" } }
});

// With sorting and paging
var page2 = await client.ExecuteQueryAsync(new QueryRequest {
    ProfileName = "AdventureWorks",
    EndpointName = "Customers",
    Sorting = { new SortDescriptor { Column = "CustomerID", Ascending = true } },
    Skip = 20,
    Take = 20
});

Multi-Database Setup

Each profile specifies a ProviderName that maps to a registered IDbConnectionFactory. The factory's Dialect property controls SQL generation (paging syntax, column quoting):

{
  "SqlRetrievalProfiles": [
    {
      "ProfileName": "Sales",
      "ConnectionString": "Server=sales-server;...",
      "ProviderName": "mssql",
      "RetrievalEndpointProfiles": [...]
    },
    {
      "ProfileName": "Archive",
      "ConnectionString": "Host=archive-server;Database=archive;...",
      "ProviderName": "postgres",
      "RetrievalEndpointProfiles": [...]
    }
  ]
}

builder.Services.AddDbConnectionFactory("mssql", new SqlClientConnectionFactory());   // SqlServerDialect
builder.Services.AddDbConnectionFactory("postgres", new NpgsqlConnectionFactory());   // custom dialect

Custom Connection Factory

Implement IDbConnectionFactory for any ADO.NET provider:

using MyDevTime.SqlDataProvider.Connection;
using MyDevTime.SqlDataProvider.Dialect;

public class NpgsqlConnectionFactory : IDbConnectionFactory
{
    public ISqlDialect Dialect => PostgreSqlDialect.Instance;

    public DbConnection CreateConnection(string connectionString)
        => new NpgsqlConnection(connectionString);
}

Built-in dialects: AnsiSqlDialect (default), SqlServerDialect, PostgreSqlDialect, MySqlDialect.

Build & Test

dotnet build            # Build entire solution
dotnet test             # Run all 213 tests across 5 projects
dotnet pack             # Package all libraries as NuGet

Target frameworks: net8.0, net9.0, net10.0. Solution uses .slnx format.

License

Apache 2.0 — see LICENSE.