In the previous article, Getting started with Visual Studio Code (VS Code), we took a detailed overview of the popular code editor. It supports various programming languages t-SQL, Python, PHP, AWS CLI, PowerShell, etc. We need to use extensions in the VS code to work with these languages. For example, if we open the T-SQL script, it recommends you for the below extension.
在上一篇文章“ Visual Studio Code入门(VS Code)”中 ,我们详细介绍了流行的代码编辑器。 它支持各种编程语言,包括t-SQL,Python,PHP,AWS CLI,PowerShell等。我们需要在VS代码中使用扩展名来使用这些语言。 例如,如果我们打开T-SQL脚本,则建议您使用以下扩展名。
We can work on SQL scripts without extension as well, but it does not give added functionality to work on T-SQL. In the below image, we see that you do not get different color codes for the database object. It treats the whole script as a regular file.
我们也可以在不扩展的情况下处理SQL脚本,但是它没有提供在T-SQL上工作的附加功能。 在下图中,我们看到您没有为数据库对象获得不同的颜色代码。 它将整个脚本视为常规文件。
Click on recommendation, and it opens the recommended extension for SQL Server. You can search for a specific extension in the marketplace.
单击推荐,它将打开推荐SQL Server扩展。 您可以在市场中搜索特定的扩展。
Click on Install for SQL Server (mssql) extension. It installs, enables the extension globally along with the SQL tools service for the mssql extension.
单击“安装SQL Server(mssql)扩展”。 它会安装该扩展程序,并与mssql扩展程序SQL工具服务一起全局启用该扩展程序。
Now, let’s add a new connection to SQL Server in VS code. Click on Add Connection.
现在,让我们以VS代码向SQL Server添加新连接。 单击添加连接 。
It opens a prompt to ask for a server name. You can also specify ADO.NET connecting string.
它会提示您输入服务器名称。 您还可以指定ADO.NET连接字符串。
Press Enter, and you get prompt for a database name. It is an optional field. By default, if we do not enter any database name, VS code connects to the master database.
按Enter键,系统提示您输入数据库名称。 这是一个可选字段。 默认情况下,如果我们不输入任何数据库名称,则VS代码将连接到master数据库。
Next, select the authentication mode from either as SQL Login or Integrated. For a Windows authentication, choose an integrated method. If you choose SQL login, it asks for the credential details of SQL login as well.
接下来,从“ SQL登录”或“集成”中选择身份验证模式。 对于Windows身份验证,请选择一种集成方法。 如果选择SQL登录,则还会询问SQL登录的凭据详细信息。
Specify a profile name for this connection to SQL Server.
指定用于此SQL Server连接的配置文件名称。
Press Enter. It connects to SQL Server, and it shows the databases, security, and server objects. You can expand a database to view its objects such as tables, views, functions, stored procedures, etc.
按Enter键。 它连接到SQL Server,并显示数据库,安全性和服务器对象。 您可以扩展数据库以查看其对象,例如表,视图,函数,存储过程等。
(View connection details in User Settings JSON)
VS code stores the connection information in a JSON file. To view JSON file content, navigate to File-> Preferences -> Settings.
VS代码将连接信息存储在JSON文件中。 要查看JSON文件内容,请导航至文件->首选项->设置。
In the settings page, click on MSSQL config… from the left-hand menu. It shows the MSSQL:Connections menu.
在设置页面中,单击左侧菜单中的MSSQL config…。 它显示了MSSQL:Connections菜单。
Click on the Edit in settings.json link. It opens the mssql connections information that we entered earlier in the VS code.
单击在settings.json中编辑链接。 它将打开我们之前在VS代码中输入的mssql连接信息。
Suppose you have a lot of connections profiles in your Visual Studio Code. You can copy these connections profile and paste it into the new VS code setting file. It can save our efforts in setting up profiles on new machines or for your colleagues.
假设您在Visual Studio Code中有很多连接配置文件。 您可以复制这些连接配置文件并将其粘贴到新的VS代码设置文件中。 它可以节省我们在新计算机上或为您的同事设置配置文件的工作。
Let’s explore a few exciting features of VS code SQL Server extension.
让我们探索VS代码SQL Server扩展的一些令人兴奋的功能。
(T-SQL IntelliSense features in Visual Studio Code)
VS code SQL editor supports the t-SQL IntelliSense feature similar to the SSMS and Azure data studio. As soon as you start typing the code in the SQL editor, it gives you suggestions for the keywords, schema object names such as table, views, columns, functions. You also get help for the parameters in stored procedures or functions. It also supports code formatting and style rule inference.
VS代码SQL编辑器支持类似于SSMS和Azure数据工作室的t-SQL IntelliSense功能。 一旦开始在SQL编辑器中键入代码,它就会为您提供有关关键字,架构对象名称(例如表,视图,列,函数)的建议。 您还可以获得有关存储过程或函数中参数的帮助。 它还支持代码格式化和样式规则推断。
(Linting)
Visual Studio code editor highlights any errors and warning in the T-SQL as soon as you write it. For example, if you want to select the records from a table but that table doesn’t exist in the source database, you get an error message stating: Invalid object name.
编写时,Visual Studio代码编辑器会在T-SQL中突出显示任何错误和警告。 例如,如果您要从一个表中选择记录,但是该表在源数据库中不存在,则会收到一条错误消息,指出:无效的对象名。
Similarly, if you want to execute a stored procedure that requires you to specify a parameter value, you can hover your mouse, and it suggests you the parameters in that stored procedure.
同样,如果要执行要求您指定参数值的存储过程,则可以将鼠标悬停在该存储过程上,并建议您使用该存储过程中的参数。
(Peek Definition/Go to Definition in Visual Studio Code)
Many times, you need a reference to check the schema object definitions. It can be objects such as tables, stored procedures, and functions. You do not need to browse the object in the explorer, check the definition, and come back to the editor again.
很多时候,您需要参考来检查架构对象定义。 它可以是表,存储过程和函数之类的对象。 您不需要在资源管理器中浏览对象,检查定义,然后再次返回编辑器。
Suppose you are retrieving records from a SQL table but wanted to check the columns in that table. Select the object, and you get two options – Peek Definition and Go to definition.
假设您正在从SQL表中检索记录,但想检查该表中的列。 选择对象,您将获得两个选项-“窥视定义”和“转到定义”。
(Peek Definition)
Click on Peek-> Peek definition, and you get the object script in the pop-up. You can view the script for your reference and close the script definition to return to the previous window.
单击“ Peek”->“ Peek定义”,然后在弹出窗口中获得对象脚本。 您可以查看脚本以供参考,并关闭脚本定义以返回上一个窗口。
Suppose you want to execute a stored procedure, but you are not sure about its parameters and their datatypes. You can check the stored procedure definition to check its parameters, script, as shown below.
假设您要执行存储过程,但是不确定其参数及其数据类型。 您可以检查存储过程定义以检查其参数脚本,如下所示。
(Go to definition)
Similar to the peek definition, select the object and click on Go to Definition. It opens the object script in a new query window. You can take a reference for the script or save it for your future reference.
与窥视定义类似,选择对象并单击“ 转到定义”。 它在新的查询窗口中打开对象脚本。 您可以参考该脚本,也可以将其保存以备将来参考。
(Code Snippets)
VS code includes several useful codes for performing useful t-SQL statements in the SQL Server database. These code snippets are for adding a new column, creating a new database, a new table, dropping a table.
VS代码包括一些有用的代码,用于在SQL Server数据库中执行有用的t-SQL语句。 这些代码段用于添加新列,创建新数据库,新表,删除表。
You get the list of code snippets once you type the SQL keyword in the editor.
在编辑器中键入SQL关键字后,您将获得代码段列表。
Select the required code snippets, and you get the T-SQL to perform the activity. For example, to create a new database, click ok the sqlCreateDatabase, and it gives the following code. It highlights the inputs required by the user.
选择所需的代码段,您将获得T-SQL执行该活动。 例如,要创建一个新的数据库,请单击ok sqlCreateDatabase ,它提供以下代码。 它突出显示了用户所需的输入。
(Execute SQL query and view results in different formats)
Visual Studio Code provides options to view the result and save it in the CSV, Excel, JSON formats. Write any query and press Execute. You can view query output in the results section and its corresponding message in the messages section. To save the results, click on the required output format, specify a location and it gives you output in that format.
Visual Studio Code提供了查看结果并将其保存为CSV,Excel,JSON格式的选项。 编写任何查询,然后按执行。 您可以在结果部分中查看查询输出,并在消息部分中查看其相应的消息。 要保存结果,请单击所需的输出格式,指定一个位置,然后以该格式输出。
Suppose you execute multiple batches in a single query window. In SQL Server, we separate the batches with the Go keyword. You get the output of both batches in the single results window. It might be challenging to view multiple results together. VS Code provides an option to maximize the results window when we use multiple batches. For example, in the below screenshot, check the maximize button in the second batch output.
假设您在一个查询窗口中执行多个批处理。 在SQL Server中,我们使用Go关键字分隔批处理。 您可以在单个结果窗口中获得两个批次的输出。 一起查看多个结果可能具有挑战性。 当我们使用多个批次时,VS Code提供了一个最大化结果窗口的选项。 例如,在下面的屏幕截图中,检查第二批输出中的最大化按钮。
Click on the Maximize button, and it shows the result of that query batch in the full screen of the results section. You can click on Restore to switch back to the standard output window.
单击最大化按钮,它将在结果部分的全屏中显示该查询批处理的结果。 您可以单击还原以切换回标准输出窗口。
(Script table menu options)
In SSMS and Azure data studio, we can right-click on a table and view the top 1000 records. It also gives the option to generate the script to create and alter objects. Visual Studio Code also supports these features for the SQL Server database.
在SSMS和Azure数据工作室中,我们可以右键单击一个表并查看前1000条记录。 它还提供了生成脚本以创建和更改对象的选项。 Visual Studio Code还支持SQL Server数据库的这些功能。
Right-click on the desired table, and you get these options.
右键单击所需的表,您将获得这些选项。
(SQLCMD mode in Visual Studio Code)
VS code SQL Server extension also supports executing queries in SQLCMD mode. You can define variables, interact with the operating system, and execute the queries.
VS代码SQL Server扩展还支持以SQLCMD模式执行查询。 您可以定义变量,与操作系统交互并执行查询。
For example, in the below query, we define variables to hold the database name, table name, the output format using the nm:Setvar function.
例如,在下面的查询中,我们使用nm:Setvar函数定义变量以保存数据库名称,表名称和输出格式。
:setvar Table Person.Person
:setvar Database AdventureWorks
:setvar Value "FirstName +' '+ LastName AS Name"
USE $(Database)
SELECT $(Value) FROM $(Table)
By default, VS code disables the SQLCMD mode to run the queries. If we try to run the SQLCMD mode query, it does not recognize it and gives you the following error message.
默认情况下,VS代码禁用SQLCMD模式来运行查询。 如果我们尝试运行SQLCMD模式查询,它将无法识别并显示以下错误消息。
Click on the SQLCMD:off to turn it on and execute the query. You get your expected results, as shown below.
单击SQLCMD:off以将其打开并执行查询。 您将获得预期的结果,如下所示。
(Conclusion)
In this article, we explored the integration of Visual Studio Code and SQL Server to help developers writing queries efficiently. Developers can use a single query editor tool for writing codes in multiple languages. It eliminates the headache of using a separate editor for each language. It supports IntelliSense, SQL CMD mode, linting, Peek definition features to give you a better development experience.
在本文中,我们探讨了Visual Studio Code和SQL Server的集成,以帮助开发人员有效地编写查询。 开发人员可以使用单个查询编辑器工具来编写多种语言的代码。 它消除了为每种语言使用单独的编辑器的麻烦。 它支持IntelliSense,SQL CMD模式,lint,Peek定义功能,以为您提供更好的开发体验。
翻译自: https://www.sqlshack.com/visual-studio-code-vs-code-for-sql-server-development/