Working with arbitrary queries?

[isaacabraham]

Is there a way to use SqlHydra for arbitrary queries e.g. if you have a folder with .sql queries, can it generate a type and / or strongly-typed datareader field for each query file it finds?

[JordanMarr]

I'm assuming you mean table schema / TSQL queries (not regular data queries).
But either way -- it currently cannot do that.

You would have to put them into a DB instance first so that SqlHydra can query it.

[isaacabraham]

Yes, I'm thinking like an ad-hoc SQL query that you call from the app which doesn't correspond to a specific table etc.

[JordanMarr]

While it would be possible to create a new provider that could read in the sql files and extract the the column names, the problem that I see is that the queries themselves would not be able to provide the column data types.

[isaacabraham]

Yes, that makes sense. It may be worth looking at how Facil does this (or the old SQL Client type provider) - my understanding is that they use SQL itself to provide the type information for the query.

[JordanMarr]

I see. This would use an existing toml config file to get connection string to look up column metadata.

Having a folder of query files is a novel approach; I can see how that could be a nice workflow: Design queries using SSMS (which provides compile errors), then generate types.

Alternatively, the Query library does provide most of what you would need for most queries in terms of joins, aggregates, etc. It’s even let’s you select a subset of columns as a tuple.

If this were only SQL Server, the dacpac parsing lib could be used to extract table names and columns from the sql files to be used for the lookup. I think this would require a custom parser that could handle the different dialects.