Clear Measure

WEBINAR: You Have What It Takes to Oversee a Software Project. REGISTER TODAY!


How to Use Roslyn and Alleviate Mismatched Parameters

Share This Article:

With all the good sample Roslyn analyzers out there, I didn’t want to write yet another how-to-write-an-analyzer tutorial, so this post explores some of the more interesting bits of the first analyzer I wrote. I assume you have at least run the Analyzer with Code Fix template in Visual Studio and are familiar with the terminology. See the links below for more tutorials.

With the latest versions of Visual Studio, Microsoft opened up the compiler black box with the Roslyn compiler. With it, the developer has full access to the syntax and semantic models of C# and VB code. You can use your imagination about what you can do with that information, and how to surface it in the IDE.

As a brief digression, one thing that I don’t think the blogs and examples emphasize enough is to use the Syntax Visualizer (View->Other Windows->Syntax Visualizer) in Visual Studio. As the image below shows, you get to see exactly how Roslyn is parsing the current file. As you write your analyzer, this will be invaluable. Even if you’re not writing an analyzer, it’s interesting to see how your code is parsed. (If you don’t have the visualizer, install the Visual Studio extension development workload to VS.)

Roslyn Analyzer Syntax Visualizer

The Problem

There once was a .NET application that made extensive use of SQL Server stored procs (no need to digress into their merits or lack thereof). As developers called the procs from C#, they would not always get the names or types of the parameter correct. For example, the proc may take a parameter num that is an int, but the developer added the parameter as a BigInt. It would compile ok, but not until runtime would SQL Server throw a truncation error.

sqlCommand.Parameters.Add(new SqlParameter("@num", SqlDbType.BigInt));

To help the developers avoid this problem, I created a Roslyn analyzer that would detect those errors while the developer was in Visual Studio, and even correct some cases.

Roslyn Analyzer

There are many ways to add parameters to a SQLCommand but for this project, I tackled the two most common. (Some problems are impossible to detect via an analyzer.) If the devs wanted to take advantage of the analyzer, they may have to tweak their code. The two cases are as follows:

sqlCommand.Parameters.Add(new SqlParameter("@int", SqlDbType.Int));
sqlCommand.Parameters.AddWithValue("@int", 123); // second param is object

One thing you may notice is that at the time the parameter needs to be checked, the sqlCommand will have already been initialized with the proc name. My first cut at the analyzer detected the sqlCommand's assignment in one analyzers, then the parameter adding in a second. This was brittle and had many holes, so I rewrote it. When finding a parameter add, the analyzer’s flow will be this:

  1. Detect a parameter is being added to a SqlCommand
  2. Find the sqlCommand variable for it
  3. Validate that the CommandType is set to CommandType.StoredProcedure (I assume the properties are set in the current scope, and not set on a SqlCommand passed into this scope)
  4. Validate that the CommandText has been set
  5. Find the proc name set as CommandText.
  6. Validate the proc name.
  7. Validate the parameter name and type.
To get the proc and parameter data, I could have retrieved it from the database at runtime, but that has auth issues, and are they using the correct database, etc.  Instead, I extracted the procs and parameters from a valid database and saved them in a JSON file that the analyzer reads into a collection. 

To detect the parameter being set I registered an invocation expression action with Roslyn.

context.RegisterSyntaxNodeAction(AnalyzeProcType, SyntaxKind.InvocationExpression);

Since the commands must be of the syntax shown above, I can get the syntax node for the SqlCommand with this code where the fnCall is the InvocationExpressionSyntax passed into my handler:

var sqlCmdNode = ((fnCall.Expression as MemberAccessExpressionSyntax)
                    ?.Expression as MemberAccessExpressionSyntax)
                    ?.Expression as IdentifierNameSyntax;

Given the syntax, I need the semantic model’s symbol info for it to make sure the object is a SqlCommand. To find the assignment calls that involve the SqlCommand in this scope I found this method to work.

  1. Get the Parent object until it finds a MethodDeclaration to get the root scope we’ll use.
  2. Get the DescendantNodes of the root where there is a an assignment involving the SqlCommand that’s getting the parameter set on it. (Since setting the CommandType and CommandText are assignments.)
  3. Validate the CommandType assignment is setting to StoredProcedure
  4. Validate the CommandText is being assigned a value, and get the proc name.

Now that the SqlCommand is valid, and I have a proc name, I can look it up in my collection. The next step is to validate the name and type (or value) used when adding the parameter match the name of a proc parameter, and the its type.

  • CommandType is not StoredProcedure
  • CommandText isn’t have a know proc name
  • Parameter name doesn’t exist on proc
  • Parameter value is the incorrect type

In the case where the type is incorrect, I add the value of the expected type to messageArgs, so the fix code knows how to fix it.

diagnostic = Diagnostic.Create(WrongTypeRule, value.GetLocation(),
    new Dictionary<string, string> {
                            { "ExpectedType", expected }
    }.ToImmutableDictionary(), paramName, procName, current, expected);

The happy path does nothing. If any of the following issues are detected, Diagnostic.Create is called that will put the squiggles under the offending code, and give the dev the light bulb to see and possibly fix the issue.

One issue I had was that by going up and down the syntax tree, the same error could be reported multiple times for the SqlCommand. To solve this problem, I registered a semantic model action, added the errors to a collection when they were found, and at the end of the semantic model action, call ReportDiagnostic for each unique error.

The Code Fixer

Compared to the analyzer, the code fix code was trivial, and varied little from the template code. Although the analyzer could report several issues, only the following case could be fixed automatically by changing the type.

sqlCommand.Parameters.Add(new SqlParameter("@int", SqlDbType.Int));

The analyzer passes in the expected type, so to change the type of the parameter, all was needed was to replace that node in the syntax tree.

private async Task<Document> ChangeTypeAsync(Document document, MemberAccessExpressionSyntax sqlDbType, string expectedType, CancellationToken cancellationToken)
    var newSqlDbType = sqlDbType.WithName(SyntaxFactory.IdentifierName(expectedType));

    // Indicate to format the list with the current coding style
    var formattedSqlDbType = newSqlDbType.WithAdditionalAnnotations(Formatter.Annotation);

    // Replace the old local declaration with the new local declaration.
    var oldRoot = await document.GetSyntaxRootAsync(cancellationToken);
    var newRoot = oldRoot.ReplaceNode(sqlDbType, formattedSqlDbType).WithLeadingTrivia(sqlDbType.GetLeadingTrivia()).WithTrailingTrivia(sqlDbType.GetTrailingTrivia());

    return document.WithSyntaxRoot(newRoot);

Final Thoughts – Roslyn Analyzer

In this case an analyzer helped devs find errors that normally would not be found until runtime right in the editor. And it could even fix the problem with a couple clicks. If the dev did not fix the problem the CI process could be configured to reject a build that had any of the specific compiler warnings kicked out by the analyzer.


Roslyn OverView in official SDK github
Roslyn Succinctly free e-book from Syncfusion
Microsoft Tutorial
Writing a Roslyn analyzer blog from Meziantou
Working with types in a Roslyn analyzer blog from Meziantou
Microsoft.CodeAnalysis Namespace doc
Roslyn Analyzers on GitHub FxCop Analyzers
Roslyn SDK on GitHub has many samples

We hope you found this post informational. If there are specific challenges you and your team face, the architects and engineers here at Clear Measure would be happy to work with you. Reach out to us now to find out all the ways we can help you!

Originally published September 17, 2019. Information refreshed August 31, 2022.

Related Articles

Need Help with an Upcoming Project

Terms of Use | Privacy Policy | Cookies Policy

Copyright © Clear Measure. All rights reserved.