通过

SqlCommandFilters–a tool for every toolbox

  • 项目

SqlCommandFilters is a utility assembly that will automatically parse your SqlCommand.CommandText and parameterize it for you.

Why would you want to do this?

 

Picture this 

You want to leverage SQL Server Always Encrypted, but you your queries are not currently parameterized Sad smile

You have a web application that builds all SQL input from elements on the user page. SQL Injection anyone?

You want better performance so you know you should parameterize your queries… but there are thousands of them.

 

What if…

You could by adding one using statement and one line of code accomplish all of the above? Smile

You can with SqlCommandFilters. The source code is all posted on CodePlex.

 

How did you do this?

By using the Microsoft.SqlServer.TransactSql.ScriptDom namespace I was able to parse the SQL command text and automatically create and add parameters to the SqlParameters collection of the SqlCommand object. I used the excellent information provided by Arvind Shyamsundar  found here: https://blogs.msdn.microsoft.com/arvindsh/tag/scriptdom/ as my starting point.

 

What constructs does it support?

There is a test / driver program that will allow you to easily test with over 20 different T-SQL constructs. The tool supports non-parameterized, partially parameterized and fully parameterized queries.

 

Is it hard to use? You be the judge. The important statement is line 18 – that is where all the magic happens.

    1: SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();

    2: sb.InitialCatalog = "AdventureWorks2014";

    3: sb.IntegratedSecurity = true;

    4: sb.DataSource = @"SQLBOBT\SQL2k16CTP3";

    5: SqlConnection con = new SqlConnection(sb.ConnectionString);

    6: con.Open();

    7: SqlCommand cmd = new SqlCommand();

    8: cmd.Connection = con;

    9: // Pick one of the TestStatements and assign it to the CommandText property of the SqlCommand object

   10: cmd.CommandText = TestStatements.existentialSubQueryStmt;

   11:  

   12: // Parameterize supports a reparse parameter

   13: // by default it is true. It will reparse and format the resultant SQL to ensure we have good code

   14: // if you feel that the performance suffers you can turn off by calling this instead

   15: // Parameters.Parameterize(ref cmd, false);

   16: // Parameterize will parse, parameterize and create the parameter collection and modify the CommandText and Parameters collection

   17: // appropriately

   18: SqlCommandFilters.Parameters.Parameterize(ref cmd);

Comments

  • Anonymous
    August 24, 2016
    Could it be made into an extension method of SqlCommand ?
    • Anonymous
      June 20, 2018
      Sorry for the late reply. That would be a great idea! I will take a look at doing that and post here if I do!
    • Anonymous
      July 12, 2018
      I am posting the code changes for this today.
  • Anonymous
    October 12, 2016
    This is very useful, thanks boB!