I have many commits described as some variation of "fixed error in SQL statement". Too many dumb mistakes like modifying a WHERE clause and leaving an extra AND keyword in it, things like that.
Is there a Roslyn analyzer that validates T-SQL syntax in string literals? Not looking for validation against the actual database, just clearing up syntax warnings would probably help us avoid some problems.
If not, does anybody have some good solutions for this? Not interested in switching to EF or recommendations like that, but for projects using micro ORM tools like Dapper that make use of SQL literals in code, if there are solutions to validate syntax at compile time or in unit testing, I'd be interested in hearing them.
These aren't database projects in Visual Studio, but like web or API projects where SQL statements are strings.
Yes! Dapper.Advisor / Dapper.AOT already include a full T-SQL analyzer (that uses Microsoft.SqlServer.TransactSql.ScriptDom internally), which automatically detects both Dapper usage and use of CommandText on SqlCommand - and has both general T-SQL checking and additional usage guidance. If you want something more bespoke, the code is on GitHub.
Example validator that shows a simple detected failure scenario here (although now I notice that the constructor usage isn't being validated - I've logged that to be fixed)