👇👇👇👇 Check out Software Architecture with C# 12 and .NET 8: amzn.to/3UNYCSo 👆👆👆👆 💡 Learn how to program in C#: - dometrain.com/course/getting-started-csharp?affcode=1115529_nl-teyzg 🧠Deep dive on C#: - dometrain.com/course/deep-dive-csharp?affcode=1115529_nl-teyzg 🎁Zero to Hero C# Bundle: - dometrain.com/bundle/from-zero-to-hero-csharp/?affcode=1115529_nl-teyzg 💪 Skill up your refactoring: - dometrain.com/course/from-zero-to-hero-refactoring-for-csharp-developers?affcode=1115529_nl-teyzg ✉ Subscribe to my free software engineering newsletter: - subscribe.devleader.ca
I also like SQL in my code, but to be honest, also having been a DBA, I also recommend putting even simple SELECTs into stored procedures or at least calling prepare on your embedded SQL statements, this is because SQL servers can cache stored procedure execution plans, as well as prepared statements. This leads to faster executions, especially in the long term, as the same query gets called more and more times. Waits times accumulate and preparing a query every time would slow down the server and consume more resources than needed.
Interesting - do we have to do anything explicit on the commands? I know this is a basic SQLite example, but I thought for MySQL (for example) the database itself handles caching the command if your command text isn't altered? I guess the question is: what do you do in C# to "prepare" the command? Thanks for the comments 💪💪
This is true of SQL Server. It really comes down to where the 'demarcation lines' sit. Many organisations have DBA's who manage database schemas and models. Others additionally mandate the use of stored procs and encoding business rules into them. This is where things become problematic because business rules end up being all over the place when they should be contained in business layers of code, not databases. On the other hand, we can't have developers writing code that changes the database otherwise the DBA can't manage the integrity of the database.
@DevLeader however, we can have two EF contexts and in the read-only context, just enable no tracking, so it won't track any changes as it's as fast as Dapper!
Haha, I'm just the opposite: I really dislike having SQL cluttering up my pristine C# code. 🙂 For us, EF works great but we're not doing any heavy DB related stuff. We're essentially using it as a convenient storage medium for specialized document storage where each document is it's own SQLite file.
Thanks for this nice content! What about stored procs,views,functions and migrations ? How do you store them on application ? Asking for learning best practices and to benefit from your experience
I almost exclusively use databases for purely storage, so functions generally aren't something I've had to do much of. Essentially, it would involve moving things to the schema creation though. But that brings us to migrations 😅 I haven't had to do my own large scale migrations in YEARS so I probably need to put some effort into thinking through how that might look and feel in Dapper, for example. In short: I don't have great examples (yet) to refer you to on these with respect to best practices
I am also not a fan of EF Core. Honestly, we keep choosing initial speed only to live through a maintenance nightmare. We ultimately choose to make things more complex than they need to be, just because we don't want to write a few lines of SQL or mapping code. All the decisions to use EF that I've seen have increased maintenance costs and bugs. Job security I guess.
My opinion, SQL does not belong in c#. Using schemas, sprocs, views, functions, etc are the way to go. You get better performance plus having these in the db makes reusability so much easier and you know the db items are good because you have already executed the code. You can create a db project and put it in source control. I understand for showing classic vs and ORM in this example it is fine, but for production, keep it clean and separated.
👇👇👇👇
Check out Software Architecture with C# 12 and .NET 8:
amzn.to/3UNYCSo
👆👆👆👆
💡 Learn how to program in C#:
- dometrain.com/course/getting-started-csharp?affcode=1115529_nl-teyzg
🧠Deep dive on C#:
- dometrain.com/course/deep-dive-csharp?affcode=1115529_nl-teyzg
🎁Zero to Hero C# Bundle:
- dometrain.com/bundle/from-zero-to-hero-csharp/?affcode=1115529_nl-teyzg
💪 Skill up your refactoring:
- dometrain.com/course/from-zero-to-hero-refactoring-for-csharp-developers?affcode=1115529_nl-teyzg
✉ Subscribe to my free software engineering newsletter:
- subscribe.devleader.ca
Amen bro!
I also like SQL in my code, but to be honest, also having been a DBA, I also recommend putting even simple SELECTs into stored procedures or at least calling prepare on your embedded SQL statements, this is because SQL servers can cache stored procedure execution plans, as well as prepared statements. This leads to faster executions, especially in the long term, as the same query gets called more and more times. Waits times accumulate and preparing a query every time would slow down the server and consume more resources than needed.
Interesting - do we have to do anything explicit on the commands? I know this is a basic SQLite example, but I thought for MySQL (for example) the database itself handles caching the command if your command text isn't altered?
I guess the question is: what do you do in C# to "prepare" the command?
Thanks for the comments 💪💪
This is true of SQL Server.
It really comes down to where the 'demarcation lines' sit. Many organisations have DBA's who manage database schemas and models. Others additionally mandate the use of stored procs and encoding business rules into them. This is where things become problematic because business rules end up being all over the place when they should be contained in business layers of code, not databases. On the other hand, we can't have developers writing code that changes the database otherwise the DBA can't manage the integrity of the database.
We can use Dapper and EF simultaneously. For reading, it's better to use Dapper as it's faster, but for CRUD operations, EF is preferable.
Hey, maybe that's the solution to the question around migrations too 🙂
@DevLeader however, we can have two EF contexts and in the read-only context, just enable no tracking, so it won't track any changes as it's as fast as Dapper!
Haha, I'm just the opposite: I really dislike having SQL cluttering up my pristine C# code. 🙂 For us, EF works great but we're not doing any heavy DB related stuff. We're essentially using it as a convenient storage medium for specialized document storage where each document is it's own SQLite file.
Hehe yeah I didn't want to frame it as right or wrong, just happens to be the tool that aligns with my style the most 🙂
Yep, stuff like this isn't right or wrong; more personal preference and what works best for the domain and team.
@HarleyPebley I find I have to separate my C# from my Blazor, too - it really muddies the water.
Thanks for this nice content! What about stored procs,views,functions and migrations ? How do you store them on application ? Asking for learning best practices and to benefit from your experience
I almost exclusively use databases for purely storage, so functions generally aren't something I've had to do much of. Essentially, it would involve moving things to the schema creation though.
But that brings us to migrations 😅 I haven't had to do my own large scale migrations in YEARS so I probably need to put some effort into thinking through how that might look and feel in Dapper, for example.
In short: I don't have great examples (yet) to refer you to on these with respect to best practices
I am also not a fan of EF Core. Honestly, we keep choosing initial speed only to live through a maintenance nightmare. We ultimately choose to make things more complex than they need to be, just because we don't want to write a few lines of SQL or mapping code. All the decisions to use EF that I've seen have increased maintenance costs and bugs. Job security I guess.
What about Dapper.FluentMap? Isn't that a bit less to maintain when things change..?
Not sure - haven't seen it yet 🙂
My opinion, SQL does not belong in c#. Using schemas, sprocs, views, functions, etc are the way to go. You get better performance plus having these in the db makes reusability so much easier and you know the db items are good because you have already executed the code. You can create a db project and put it in source control. I understand for showing classic vs and ORM in this example it is fine, but for production, keep it clean and separated.
You're welcome to develop software however you'd like, and I respect that 🙂