[DatabaseLoader] Create higher level convenient methods for DatabaseLoader

@divega

API Proposal

namespace Microsoft.ML
{
    public static class DatabaseLoaderCatalog
    {
        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DbProviderFactory providerFactory,
            string connectionString,
            string commandText,
            params DatabaseLoader.Column[] columns);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DbProviderFactory providerFactory,
            string connectionString,
            string commandText,
            DatabaseLoader.Options options);

        public static IDataView LoadFromDatabase<TInput>(
            this DataOperationsCatalog catalog,
            DbProviderFactory providerFactory,
            string connectionString,
            string commandText);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DatabaseSource source,
            params DatabaseLoader.Column[] columns);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DatabaseSource source,
            DatabaseLoader.Options options);

        public static IDataView LoadFromDatabase<TInput>(
            this DataOperationsCatalog catalog,
            DatabaseSource source);
    }
}

Example usage

using System.Data;                 // DbType
using Microsoft.Data.SqlClient;    // SqlClientFactory; any ADO.NET provider works
using Microsoft.ML;
using Microsoft.ML.Data;           // DatabaseLoader, DatabaseSource

var mlContext = new MLContext();
string connectionString = "<your connection string>";
string commandText = "SELECT CAST(Size as REAL) as Size, CAST(NumBed as REAL) as NumBed, Price FROM House";

// Today (the Learn docs sample): three steps
DatabaseLoader loader = mlContext.Data.CreateDatabaseLoader<HouseData>();
DatabaseSource dbSource = new DatabaseSource(SqlClientFactory.Instance, connectionString, commandText);
IDataView data = loader.Load(dbSource);

// Proposed: one call. Choose one way to give the schema (<TInput>, columns or options)
// and one way to give the source (factory + connection string + command text, or DatabaseSource).

// Explicit schema, for when there is no class (e.g. columns chosen at runtime).
// Result columns 0-1 (Size, NumBed) become one Features vector, and column 2 (Price) becomes Label.
var columns = new[]
{
    new DatabaseLoader.Column("Features", DbType.Single, 0, 1),
    new DatabaseLoader.Column("Label", DbType.Single, 2),
};
var options = new DatabaseLoader.Options { Columns = columns };

// Factory, connection string and command text
data = mlContext.Data.LoadFromDatabase<HouseData>(SqlClientFactory.Instance, connectionString, commandText);
data = mlContext.Data.LoadFromDatabase(SqlClientFactory.Instance, connectionString, commandText, columns);
data = mlContext.Data.LoadFromDatabase(SqlClientFactory.Instance, connectionString, commandText, options);

// DatabaseSource, e.g. for a longer command timeout (the default is 30 s)
var source = new DatabaseSource(SqlClientFactory.Instance, connectionString, commandText,
    commandTimeoutInSeconds: 300);
data = mlContext.Data.LoadFromDatabase<HouseData>(source);
data = mlContext.Data.LoadFromDatabase(source, columns);
data = mlContext.Data.LoadFromDatabase(source, options);

// columns is params, so you can also pass the Column values inline:
data = mlContext.Data.LoadFromDatabase(SqlClientFactory.Instance, connectionString, commandText,
    new DatabaseLoader.Column("Features", DbType.Single, 0, 1),
    new DatabaseLoader.Column("Label", DbType.Single, 2));

// None of these calls query the database. The query runs when the view is enumerated (Preview, Fit, CreateEnumerable).

// Same class as the Learn docs sample. Members map to result columns by name, so no attributes are needed.
public class HouseData
{
    public float Size { get; set; }
    public float NumBed { get; set; }
    public float Price { get; set; }
}

As mentioned by Diego, these additions would help by simplifying the API usage for users even further and it should be pretty easy to implement for us: 👍

@divega commented: dotnet/machinelearning-samples#617 (review)

@CESARDELATORRE, I did a deferred review. The experience seems pretty good.

1:
And I agree with you that it could be even better with some sugar method that loads directly from the arguments of DatabaseSource.

2:
I can also see other possible shortcuts of similar nature. For example, although DbProviderFactory is the all encompassing root concept if you need everything from an ADO.NET provider, a DbConnection can give you everything but DbConnectionStringBuilder, and it is a much more familiar abstraction for most users than the DbProviderFactory. So, unless you need to manipulate connection strings in a provider agnostic way (not commonly an useful thing to do), you could make the sugar Load method generic on the provider's DbConnection type.
All in all, I would would love to meet with you and the devs and walk trough the product code and API. It is likely that more things like this will come up.

@divega

Hi @divega and @CESARDELATORRE

I am interested in this task. Could you please explain what sugar methods you want to have in more details?
You wrote:

you could make the sugar Load method generic on the provider's DbConnection type

What providers type do you mean under

provider's DbConnection type

if I am correctly understand you, it is not the DbProviderFactory.

I can propose such methods:
public static IDataView Load<TData>(this DbConnection connection, string connectionString, string query, IHostEnvironment host)
or
public static IDataView Load<TData>(this DatabaseSource source, string connectionString, string query, IHostEnvironment host)

Thanks.

@CESARDELATORRE

Hi @CESARDELATORRE @eerhardt — I'd like to take this one if it's still open for contribution.

Building on @Pomoinytskyi's suggestion above, my plan is to add convenience "sugar" overloads so users can load directly from a familiar DbConnection without setting up a DbProviderFactory for the common case, e.g.:

public static IDataView Load<TData>(this DatabaseLoader loader, DbConnection connection, string query);

making the method generic over the provider's DbConnection type. I'd include unit tests and XML doc comments, and keep the existing DbProviderFactory-based API intact for the provider-agnostic scenarios.

Does that direction look right? If so, I'll open a PR referencing this issue. Thanks! @avishekhraj9

Add LoadFromDatabase convenience methods (dotnet#4182)

Use a fast in-memory SQLite load test for LoadFromDatabase (dotnet#4182)

@rosebyte

@rosebyte thanks, and that makes total sense to me. I was a bit surprised it was marked as "good first issue" considering the API exposure. Just to clarify, are you saying "leave it to the ML team" or "feel free to submit an API proposal"? I'm assuming the former, but thought it was worth checking.

@adestr

@adestr Feel free to submit an API proposal in a comment here. Then leave it to us to check it and get it through the rest of the API review process.

Take a look for example at dotnet/runtime#27421, to see how an API proposal should look like.

I've extracted the API from the PR (only moved Load methods to extensions), please let me know if there is anything missing.

Allow loading directly from a provider factory or an existing DbConnection, without constructing a DatabaseSource.

namespace Microsoft.ML
{
    public static class DataLoaderExtensions
    {
        public static IDataView Load(
            this IDataLoader<DatabaseSource> loader,
            DbProviderFactory providerFactory,
            string connectionString,
            string commandText);

        public static IDataView Load(
            this IDataLoader<DatabaseSource> loader,
            DbConnection connection,
            string commandText);
    }
}

namespace Microsoft.ML.Data
{
    public sealed class DatabaseSource
    {
        public DatabaseSource(DbConnection connection, string commandText);
        public DatabaseSource(DbConnection connection, string commandText, int commandTimeoutInSeconds);
        public DbConnection Connection { get; }
    }
}

Precedent and differences

Precedent and differences

DataLoaderExtensions.Load(this IDataLoader<IMultiStreamSource> loader, params string[] path) already wraps source construction as loader.Load(new MultiFileSource(path)). These overloads follow the same pattern with DatabaseSource. Unlike instance methods, interface extensions also work with composite loaders returned by loader.Append(transformer).
DatabaseSource already has provider-factory constructors with and without an explicit timeout. The connection constructors mirror that pair. The new Load overloads use the existing 30-second default; custom timeouts still require a source object.
Existing database cursors create and dispose their own connections. A supplied connection remains caller-owned and must stay usable for each enumeration of the lazy data view. The cursor opens it if needed, closes it only if it opened it, and never disposes it. Overlapping reads through the same connection-backed source are rejected; callers must coordinate any other use of the connection.
Connection-backed sources make the existing ProviderFactory and ConnectionString properties return null; Connection returns null for factory-backed sources. Existing factory-created sources are unchanged, but consumers inspecting arbitrary sources must handle the new mode.

Thank you. Given that the spirit of the original request was "make it easier to load from a database", I was also considering whether some extension to DatabaseLoaderCatalog would be useful, similar to the LoadFromFile extensions in TextLoaderSaverCatalog. Given the range of possible method signatures (below), I wasn't sure if an Action<DatabaseLoader.Options> or similar would be easier, but wanted to do some research to figure out how that would work alongside existing command line parsing of DatabaseLoader.Column arguments.

Considering I haven't seen use of Action<T> for configuration, I think it would be better to suggest these extension methods.

namespace Microsoft.ML
{
    public static class DatabaseLoaderCatalog
    {
        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DbProviderFactory factory,
            string connectionString,
            string commandText,
            params DatabaseLoader.Column[] columns);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DbConnection connection,
            string commandText,
            params DatabaseLoader.Column[] columns);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DbConnection connection,
            string commandText,
            DatabaseLoader.Options options);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DatabaseSource source,
            params DatabaseLoader.Column[] columns);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DatabaseSource source,
            DatabaseLoader.Options options);
    }
}

@adestr

@adestr, the latest proposal looks good to me. Thanks for putting it together. We’ll now take it through the API review process.

@rosebyte

@rosebyte Please update the top post to contain the API proposal. We're not good at "scroll down to the proposal, please"

Video

Looks good as proposed

namespace Microsoft.ML
{
    public static class DatabaseLoaderCatalog
    {
        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DbProviderFactory providerFactory,
            string connectionString,
            string commandText,
            params DatabaseLoader.Column[] columns);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DbProviderFactory providerFactory,
            string connectionString,
            string commandText,
            DatabaseLoader.Options options);

        public static IDataView LoadFromDatabase<TInput>(
            this DataOperationsCatalog catalog,
            DbProviderFactory providerFactory,
            string connectionString,
            string commandText);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DatabaseSource source,
            params DatabaseLoader.Column[] columns);

        public static IDataView LoadFromDatabase(
            this DataOperationsCatalog catalog,
            DatabaseSource source,
            DatabaseLoader.Options options);

        public static IDataView LoadFromDatabase<TInput>(
            this DataOperationsCatalog catalog,
            DatabaseSource source);
    }
}

@adestr

@adestr, we have the API approved, are you still happy to propose a PR for the new API? Or you prefer us to take care of it?

@rosebyte

@rosebyte I will get a PR ready shortly.

Navigation Menu

Repository navigation

[DatabaseLoader] Create higher level convenient methods for DatabaseLoader #4182

API Proposal

Example usage

8 remaining items

Precedent and differences

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

[DatabaseLoader] Create higher level convenient methods for DatabaseLoader #4182

Description

API Proposal

Example usage

Activity

Pomoinytskyi commented on Dec 27, 2019

avishekhraj9 commented on Jun 17, 2026

8 remaining items

adestr commented on Sep 30, 2026

svick commented on Sep 30, 2026

rosebyte commented on Oct 1, 2026

Precedent and differences

adestr commented on Oct 1, 2026

rosebyte commented on Oct 13, 2026

bartonjs commented on Oct 16, 2026

bartonjs commented on Nov 3, 2026

rosebyte commented on Nov 4, 2026

adestr commented on Nov 4, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions