Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to write easy to debug SQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Preface

Compared with high-level language debugging such as C #, debugging SQL is a painful thing. Especially those thousands of lines of stored procedures, is my programmer's nightmare.

After decomposing the SQL of thousands of lines of stored procedures into C # management, debugging is not smooth, so how to make debugging more smooth?

two。 Common debugging

2.1 usually go down a breakpoint in Dapper and catch a SQL similar to the following:

SELECT a.* FROM dbo.ptype an INNER JOIN dbo.PType_Price b ON a.typeId=b.PTypeID LEFT JOIN dbo.PType_Units c ON a.typeId=c.UnitsId WHERE a.typeId=@typeid AND a.CreateDate=@Area AND preprice1=@preprice1 AND deleted=@deleted

Various @ symbols need to be manually replaced before debugging (trouble). It would be nice to catch the final SQL.

2.2 fortunately, the final SQL can be caught through SQLServer Profiler.

But in the SQLServer in the production environment, there are a lot of SQL executed concurrently. As can be seen in the picture above, it is also troublesome to find the SQL you have just executed in a large pile of SQL. Even if you can Ctrl + F call up the search box to search, you have to think of a good keyword to search, trouble.

3. Solution

Since we want the final SQL, why is it that Mao is already the final SQL before throwing it to Dapper for execution? add the tool code:

Public class SqlHelper {public Dictionary Param = new Dictionary (); public string ReplaceParam (ref string sql) {if (Param.Count = = 0) {return sql;} StringBuilder sb = new StringBuilder (); sb.Append (sql) Foreach (var item in Param) {var paramName = item.Key; var paramValue = item.Value; var type = paramValue.GetType () If (type = = typeof (string) | | type = = typeof (DateTime)) {/ / string sb.Replace ($"@ {paramName}", $"{paramValue}'") } else if (type = = typeof (bool)) {/ / bool type if (paramValue.ToString () = = "True") {sb.Replace ($"@ {paramName}", "1") } else {sb.Replace ($"@ {paramName}", "0") } else {/ / numeric sb.Replace ($"@ {paramName}", paramValue.ToString ());} sql = sb.ToString (); return sql }}

Call example:

Public IEnumerable GetPtypeDetail () {var sql = @ "SELECT a.* FROM dbo.ptype an INNER JOIN dbo.PType_Price b ON a.typeId=b.PTypeID LEFT JOIN dbo.PType_Units c ON a.typeId=c.UnitsId WHERE a.typeId=@Typeid AND a.CreateDate=@CreateDate AND preprice1=@preprice1 AND deleted=@deleted"; var sqlHelper = new SqlHelper (); sqlHelper.Param.Add ("Typeid", "001") SqlHelper.Param.Add ("CreateDate", DateTime.Now); sqlHelper.Param.Add ("preprice1", 3.62m); sqlHelper.Param.Add ("deleted", true); sqlHelper.ReplaceParam (ref sql); IEnumerable plist = new List () Using (var con = SQLServerHelper.GetConnection ()) {plist = con.Query (sql);} return plist;}

In this way, the SQL thrown to Dapper is always the final SQL, so you don't have to take great pains to catch it.

PS: some people may question the efficiency of this replacement. Don't worry about having been tested. String replacement for C # is very fast. The test results of the above invocation example are subtle and nanosecond. Interested caretakers can test again.

4. *

Now what is thrown to Dapper is no longer a SQL full of @ parameters, but a replacement of the final SQL.

So when the boss yells at you from afar, "Xiao Jiang, you have a XX bug in tm, take a look."

You can take your time to make a breakpoint at Dapper Query.

Mouse over the SQL variable, easily get the final SQL for debugging, instead of manually replacing the @ parameter, or looking for a needle in a haystack in SQLServer Profiler!

Thank you for reading this article carefully. I hope the article "how to write an easy-to-debug SQL" shared by the editor will be helpful to everyone. At the same time, I also hope you can support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.