C#•2y ago

❔ Load *.sql files (How to handle them?)

Hi, Im creating a backend in NET Core and I do not want to use EF Core as I need to extend my SQL knowledge, so I thought it's good to use SqlDataClient and raw SQL. So far so good. My solution structure is like this: - Domain (contains Models and SQL scripts) - Infrastructure (contains Repositories, Services, SQLManager [classes to create database, tables, call sql scripts, etc) - Web API (controllers that use the services in the infrastructure) My idea was to store the SQL scripts along the models as resource files and have them in a *.resx file or maybe call the files programmatically, but I think the first option is good for resolving compile errors. I wonder if I can reference the *.resx files from another project (infrastructure)? How do you typically deal with *.sql files that you call in your programs?

8 Replies

Angius•2y ago

Why do you need separate SQL files in the first place?

var query = """
    SELECT * FROM stuff s
    WHERE s.Count > 10
    ORDER BY s.Added
    LIMIT 10
    """;
var data = await _db.Query<SomeDto>(query);

var query = """
    SELECT * FROM stuff s
    WHERE s.Count > 10
    ORDER BY s.Added
    LIMIT 10
    """;
var data = await _db.Query<SomeDto>(query);

chimera•2y ago

As Z's is saying, just have your queries in code. That way you can parametize them easily

Davaaron•2y ago

I know but we decided to go for the other way, it has some advantages so we have placed some sql files there

Angius•2y ago

Then sure, store them as resource files in whatever project will be doing the actual querying

MarkPflug•2y ago

I also prefer to keep my SQL files separate from my C# code. Mainly for syntax highlighting of the .sql files, but also makes the C# code feel cleaner (my opinion). I've built this nuget package to make it super-easy: https://github.com/MarkPflug/Sylvan.BuildTools.Resources#static-string-code-generation Basically, you designate a folder as a "StaticResourceFolder " folder, then my package generates source code from the contents of the files in that folder as if they were defined as C# string constants. You can then access them as "FolderName.MySqlScript" in C#.

GitHub

GitHub - MarkPflug/Sylvan.BuildTools.Resources: Build-time resource...

Build-time resource file support in C# projects. Contribute to MarkPflug/Sylvan.BuildTools.Resources development by creating an account on GitHub.

ChucklesTheBeard•2y ago

mainly for syntax highlighting

Rider has language injections; you can tell it a string is SQL (or it can guess based on "SELECT ... FROM ..." etc) and you get highlighting and syntax checking.

MarkPflug•2y ago

That's nice. I don't use rider.

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Load *.sql files (How to handle them?)