Green-field projects should use Microsoft Entity Framework to connect to SQL for data access when possible, especially when a new database will be created and used. The code-first pattern with entity framework should be with new databases.
When an existing database needs to be consumed by an application, Dapper can be used as the ORM.
Dapper also works well when needing to perform read-only or reporting type queries against SQL.
Existing SQL databases can also use Microsoft Entity Framework as the ORM and can use the either the database-first pattern or the code-first pattern.
Both Entity Framework and Dapper can be used to execute/consume SQL statements and Stored Procedures directly.
SQL statements should be stored in resource files in the related Repository project and should not be stored in-line with code. String.Format or Stringbuilder can be used to build a SQL statement. Developers should easily be able to copy the SQL statement and paste into SQL Query analyzer and execute (possibly replacing a variable or two).
Do not use ADO.NET directly.
Avoid cross database queries.
Do not put business logic in the database, data layer, stored procedure, or SQL statement. All business logic should be performed in the services layer of the application. Avoid creating complex stored procedures and SQL statements for application functionality.
Functional tests are not the same as unit tests. Keep in mind that writing unit tests for SQL code is virtual impossible.
For more information about Entity Framework Core, visit https://docs.microsoft.com/en-us/ef/core/
For more information about Dapper, visit https://github.com/StackExchange/Dapper
PREVIOUS: Data Storage
Solution Architecture Guidance
NEXT: Unit Testing and Code Coverage