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 (0)

Skip to main content