SQL Analyzer

A Roslyn-based analyzer for SQL related stuff in .NET

Analyzers

SQL001: SQL type is not specified

Noncompliant Code Example:

Query<Thing>("select * from Thing where Name = @Name", new { Name = abcde });

Compliant Solution:

Query<Thing>("select * from Thing where Name = @Name", new {Name = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true }});

https://github.com/StackExchange/Dapper/blob/master/Readme.md#ansi-strings-and-varchar

SQL002: SQL parameters mismatch

Noncompliant Code Example:
Dapper

var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Id = guid });

SqlCommand

var sql = new SqlCommand("select Age = @Age, Id = @Id");
sql.Parameters.AddWithValue("@Id", guid);
sql.ExecuteNonQuery();

Compliant Solution:
Dapper

var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

SqlCommand

var sql = new SqlCommand("select Age = @Age, Id = @Id");
sql.Parameters.AddWithValue("@Id", guid);
sql.Parameters.AddWithValue("@Age", 42);
sql.ExecuteNonQuery();

SQL003: Using 'Query' method is not optimal here

Noncompliant Code Example:

var dog = connection.Query<Dog>("select * from dogs").Single();

Compliant Solution:

var dog = connection.QuerySingle<Dog>("select * from dogs");

https://github.com/StackExchange/Dapper#performance

SQL004: Using 'QueryMultiple' method is not optimal here

Noncompliant Code Example:

var multi = connection.QueryMultiple("select * from dogs");
var dogs = multi.Read<Dog>();

Compliant Solution:

var dogs = connection.Query<Dog>("select * from dogs");