SqlMarshal and other type-safe database access layers

[charlesroddie]

Really interesting project here. I've explored some libraries that take an sql database and generate access functions. (We currently use FSharp.Data.SqlClient which generally works well but slows down the IDE so we prefer the idea of doing a manual process to generate types so are looking for an alternative.)

https://github.com/fsprojects/FSharp.Data.SqlClient

An F# type provider. Good type safety (not only 'a option but 'a option option has meaning in some contexts - probably not that essential though). To use, point the provider to the database using type AdventureWorks = SqlProgrammabilityProvider<connectionString> and then static methods of AdventureWorks are generated and ready to use. Not usable outside F# since the type provider is "erasing", so the thing generated is not a dotnet library. Doesn't scale very well for large databases. Somewhat legacy as hasn't been updated for a few years, and F# type providers themselves have an uncertain future.

https://www.sqlplus.net/

Closed-source, extremely well documented. Doesn't use new features like nullability annotations. Fast (ADO.Net-like, faster than dapper). To use, install the visual studio extension which enables a build command to generate the types (as csharp code).

https://github.com/ErikEJ/EFCorePowerTools

Not exactly in this category as it has an EF dependency, but it does generate types (csharp code) from stored procedures and has nullability annotations. Use via visual studio extension. Owing to EF dependency there is a (performance overhead)[https://github.com/ErikEJ/EFCorePowerTools/issues/713#issuecomment-761553523].

SqlMarshal

Seems to generate types (csharp code) by pointing to a database.
Type safety: good as it has nullability annotations
How to use: unknown. Probably run code with some settings and some C# files get created? Grateful if you could share some information so I can update this!
Performance: probably very fast similar to ADO.Net

[kant2002]

For now, this project is out of scope for F# since I use source generators approach. Did you look at https://github.com/kant2002/SqlMarshal#dbconnection-examples ? What specifically do you need? I may create sample app if you want

[charlesroddie]

For now, this project is out of scope for F# since I use source generators approach

Not a problem for F# as the compiled code can be used by F#.

But now I understand that cod in README.md is written by the user and generated code done behind the scenes by source generators.

In var items = connection.GetResult(); , do you mean dataContext.GetResult() ?

[SqlMarshal("persons_search")]
public partial IList<Item> GetResults(string name, string city);

Am I right in assuming it works like this: Stored procedures are searched for "persons_search" (with some error if not found) and the inputs types and names entered (with an error if there the inputs don't match the names and types). Item is any word which then becomes a class created by SqlMarshal?

This all seems usable. It would be ideal if everything were generated, so you could access SqlMarshal.Persons_SearchAsync.

[kant2002]

Am I right in assuming it works like this

No, it works much simpler.

1. Stored procedure just passed to the SqlCommand and that's responsibility of the developer to type proper name
2. Parameters passed in same order with simple rules for transformation of names. Let's say order based.
3. Mapping from resultset to entity is happens right now in the declaration order by names. So that can be a problem for complex entities.

It would be ideal if everything were generated

I'm opposed to this, since this is killing inner loop productivity, or there should be separate tool which can perform reverse-engeneering based on the library. That's possible.