How can one get code colorization of SQL strings in a C# project within Visual Studio? And for a bonus, add syntax checking and intellisense? Despite some time spent searching the web, I couldn't find any way to do this and am not sure where to go next. Note: this question is not looking for "preferred" or "best" or seeking people's opinions on how they like to do it, it's seeking any way at all to achieve the above concrete goal(s). My wild ideas were: Create a new language that is in reality C#, but has just enough differences to support inline SQL somehow (not inside double quotes). Make a Visual Studio plugin (or modify the attendant VS code some way) that at some point manages to get code colorization in. I get that achieving my objective could be very costly in terms of effort, time, resources, etc. I'm asking from the perspective of, if you had those resources, what is any possible way to get code colorization? The use case is Dapper, if that helps.

c#visual-studiosyntax-highlightingcolorize

Reputation: 50201

Colorize SQL strings in Visual Studio

How can one get code colorization of SQL strings in a C# project within Visual Studio? And for a bonus, add syntax checking and intellisense?

Despite some time spent searching the web, I couldn't find any way to do this and am not sure where to go next.

Note: this question is not looking for "preferred" or "best" or seeking people's opinions on how they like to do it, it's seeking any way at all to achieve the above concrete goal(s).

My wild ideas were:

Create a new language that is in reality C#, but has just enough differences to support inline SQL somehow (not inside double quotes).
Make a Visual Studio plugin (or modify the attendant VS code some way) that at some point manages to get code colorization in.

I get that achieving my objective could be very costly in terms of effort, time, resources, etc. I'm asking from the perspective of, if you had those resources, what is any possible way to get code colorization?

The use case is Dapper, if that helps.

Upvotes: 5

Answers (3)

Alternatex

Reputation: 1552

This extension did the job for me:

Sql Tools

It supports Visual Studio 2017 and 2019, unlike the one in the currently accepted answer. In contrast, it's a very simple extension, but it worked well for my PostgreSQL/Dapper project.

Upvotes: 3

Flydog57

Reputation: 7111

My solution is to put my queries in *.sql files and then load them on demand. My loader (somewhat) minifies the SQL (removes single line comments (either // or /* comment */ as long as it's on a single line), and then removes leading and trailing spaces as well as blank lines). Finally, it caches the minified SQL. I leave line-ends in the SQL (for readability, at very little cost).

Since you are editing SQL files, you get full syntax coloring, etc. In addition, you aren't using SQL as strings (which tends to remove programmer's temptation to use interpolated strings, or string concatenation, or... things that can lead to SQL injection). It also tends to make your SQL code much easier to find (bringing joy to any DBAs looking over your shoulder).

Because of the minification, it reduces the query cache space needed on the server.

One version of this that I've written (I've done it for several employers) includes a SQL fragment syntax, so we could have common fragments cleanly inserted into the resulting SQL (kinda-sorta a macro language).

The downside is that you end up deploying the SQL files to the server - which may not be desirable from a security standpoint.

Upvotes: 0

Icemanind

Reputation: 48686

There is already a Visual Studio Add In for doing this. Check it out!

From their site:

This Visual Studio 2010 extension adds basic SQL syntax highlighting (keywords, functions and variables) to string literals.

In addition, you can get the source code and customize it anyway you feel fit to (including adding specific Dapper keywords).

Upvotes: 10

Colorize SQL strings in Visual Studio

Answers (3)

Related Questions