Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SqlMarshal and other type-safe database access layers #4

Open
charlesroddie opened this issue Mar 29, 2022 · 3 comments
Open

SqlMarshal and other type-safe database access layers #4

charlesroddie opened this issue Mar 29, 2022 · 3 comments
Labels
enhancement New feature or request

Comments

@charlesroddie
Copy link

charlesroddie commented Mar 29, 2022

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

@charlesroddie charlesroddie changed the title This and other type-safe database access layers SqlMarshal and other type-safe database access layers Mar 29, 2022
@kant2002
Copy link
Owner

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
Copy link
Author

charlesroddie commented Mar 30, 2022

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
Copy link
Owner

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.

@kant2002 kant2002 added the enhancement New feature or request label Aug 15, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

2 participants