如何在VB.NET中编写可读的SQL [英] How to write readable SQL in VB.NET

问题描述

当在 VB.NET 中编写 Sql 时，由于 VB 缺少多行字符串，经常会得到一些非常不可读的东西.

When writing Sql in VB.NET one often ends up with something quite unreadable due to VB's lack of multi-line strings.

例如:

Dim sql As String = "SELECT t1.Name, t1.Description, t1.Address, t1.PhoneNumber, t2.RegistrationID, t2.Date, t2.Description, t2.RegistrationStatus FROM Users t1 JOIN Registrations t2 ON t1.UserID = t2.UserID WHERE t2.RegistrationID = @RegistrationID"

您可以使用行继续符来拆分字符串，但是额外的引号和行继续符会使这更难阅读.也使得在代码和 SSMS 之间传输查询变得困难.

You could break the string up using line-continuation characters, but the extra quote marks and line-continuation characters make this harder to read. Also it makes transferring the query between code and SSMS difficult.

是否有一种解决方案可以使 SQL 在 VB 中可读，并且还允许在 VB 代码和 SSMS(或任何其他 SQL 编辑器/IDE)之间轻松传输查询(通过复制/粘贴)?

Is there a solution that makes SQL readable within VB and also allows easy transfer of queries (via copy/paste) between VB code and SSMS (or any other SQL editor/IDE)?

推荐答案

是否可以升级到 Visual Studio 2015?
您可以在 Visual Basic 14 中使用新的多行字符串文字:

Is it possible for you to upgrade to Visual Studio 2015?
You can use new multi-line string literals in Visual Basic 14:

Dim sql As String = "
    SELECT t1.Name, 
           t1.Description, 
           t1.Address, 
           t1.PhoneNumber, 
           t2.RegistrationID, 
           t2.Date, 
           t2.Description, 
           t2.RegistrationStatus 
    FROM   Users t1 
           JOIN Registrations t2 ON t1.UserID = t2.UserID 
    WHERE   t2.RegistrationID = @RegistrationID
"

没有以前从 XML 多行程序已知的限制(<、& 等问题).您需要在字符串中转义的唯一内容是 "(将其替换为 "").

There are no limitations previously known from XML multi-liners (problems with <, &, ...). The only thing you need to escape inside the string is " (replace it with "").

出色的新字符串插值功能可帮助您使字符串具有前所未有的可读性:

And great new string interpolation feature helps you make your strings readable like never before:

Dim tableName As String = "Registrations"
Dim currentOrderByColumn As String = "t2.Date"

Dim sql = $"SELECT t1.Name, t1.Description FROM {tableName} ORDER BY {currentOrderByColumn}"

Dim sql2 = $"
    SELECT t1.Name, t1.Description
    FROM {tableName}
    ORDER BY {currentOrderByColumn}
"

插值字符串内部的表达式也完全支持变量重命名，因此将tableName重命名为mainTableName也会在字符串内部进行重命名.

Expressions inside interpolated strings also fully support variable renaming, so renaming tableName to mainTableName will also perform renaming inside the string.

您需要在此类字符串中处理的其他字符是 { 和 } - 您必须将它们替换为 {{ 或}} 分别.但在 T-SQL 中它们只有一个特定目的.

Additional characters you need to take care of in this type of string are { and } - you must replace them with {{ or }} respectively. But in T-SQL they have only one specific purpose.

更多信息:1, 2

注意:如果您希望暂时继续使用已弃用的 XML 解决方法，请不要使用表单
<tag attribute="text"/>.Attribute("attribute").Value
因为它删除换行符导致奇怪的 SQL 单行字符，例如
SELECT t1.Name, t1.Description, t2.Date FROM Users t1.

Notice: If you wish to temporarily keep using deprecated XML workaround, then DON'T use form
<tag attribute="text" />.Attribute("attribute").Value
because it removes new line characters what leads to strange SQL single-liners like
SELECT t1.Name, t1.Description, t2.Date FROM Users t1.

改为使用表单
text.Value
保留行尾，所以你看到的(在代码编辑器中)就是你得到的(在 SQL 命令处理器的输入中).SQL 可读性是此问答的主要目标，此细节是其中的一部分.

Instead, use form
<tag>text</tag>.Value
which preserves line endings so what you see (in code editor) is what you get (at input of SQL command processor). SQL readability is the main goal of this Q/A and this detail is part of it.

(但请记住，XML 中这种改进的 SQL 形式也已弃用 = 它有效，但请尽快放弃.)

(But remember this improved form of SQL in XML is deprecated, too = it works, but abandon it as soon as possible.)

这篇关于如何在VB.NET中编写可读的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！