Format SQL statement into C# string. - C# Discussion Boards

19-Oct-09 5:15

Hello,

I frequently write many SQLs and would like to embed in C# code (SSMS 2005/VS.NET 2005). There was macro which would let me select the code and hit a shortcut to format it into C# code. Has anyone seen where it is now?

Thanks,

Raghu.

You can never try. You either do it or you don't.

Not Active19-Oct-09 5:51

19-Oct-09 5:51

Do I understand that you want a macro to copy SQL statements into a C# file?

I thought it was Ctrl+C, Ctrl+V

only two letters away from being an asset

RK KL19-Oct-09 6:01

19-Oct-09 6:01

Hmm... haven't heard that before, but I'll give it a try... Poke tongue | ;-P

I was referencing converting a SQL statement to C# string...

But, here's what I came up with in case someone needs this... Assign this macro a short cut of of Ctl+Shift+F3

It converts

select *
From Status
From ActivityStatus ActStsId=StsId

string sql = "   	    select * "+
"	    From Status "+
"	    From ActivityStatus ActStsId=StsId";

Macro is as follows.

Sub SQL2CSharp()
    Dim selection As EnvDTE.TextSelection
    Dim startPoint As EnvDTE.EditPoint
    Dim endPoint As TextPoint
    Dim quoteStart As String

    selection = DTE.ActiveDocument.Selection()
    startPoint = selection.TopPoint.CreateEditPoint()
    endPoint = selection.BottomPoint
    quoteStart = """"

    DTE.UndoContext.Open("SQL 2 CSharp")
    Try
        startPoint.Insert("string sql = """)

        Do While (True)
            Dim line As Integer

            line = startPoint.Line
            startPoint.Insert(quoteStart)
            startPoint.EndOfLine()
            If (line = endPoint.Line) Then
                startPoint.Insert(quoteStart & ";")
            Else
                startPoint.Insert(quoteStart & "+")
            End If
            startPoint.LineDown()
            startPoint.StartOfLine()
            If (line = endPoint.Line) Then
                Exit Do
            End If
        Loop
    Finally
        ' If an error occurred, then make sure that the undo context is cleaned up.
        ' Otherwise, the editor can be left in a perpetual undo context.
        DTE.UndoContext.Close()
    End Try
End Sub

You can never try. You either do it or you don't.

Not Active19-Oct-09 6:17

19-Oct-09 6:17

Why would anyone need or want this? You SHOULD NOT be using inline SQL at all.

only two letters away from being an asset

RK KL19-Oct-09 7:01

19-Oct-09 7:01

Are you suggesting I should use the alternatives for SQL like SPs, functions, views etc?

You can never try. You either do it or you don't.

Not Active19-Oct-09 7:19

19-Oct-09 7:19

Only if you want your applications to perform better, be more maintainable and more secure

only two letters away from being an asset

EliottA19-Oct-09 8:17

EliottA

19-Oct-09 8:17

And why the hell would we want that?!

Check out the CodeProject forum Guidelines[^]
The original soapbox 1.0 is back![^]

RK KL19-Oct-09 9:06

19-Oct-09 9:06

Why would this be insecure if there is no WHERE clause associated with the SQL statement?

Depending on if it is a reusable/non-reusable SQL statement, I think its a personal preference to keep the SQL in code or move it to database.

You can never try. You either do it or you don't.

Not Active19-Oct-09 9:19

19-Oct-09 9:19

If you have to ask it shows you really have no idea what your doing. Help make this profession better, drop out now.

only two letters away from being an asset

RK KL19-Oct-09 9:25

19-Oct-09 9:25

I never would've thought this kind of comment would come from a CodeProject supporter. I'm just thinking about this kind of response in general is what makes a good or bad forum.

Now, if you can please answer the question (which was a genuine one), everyone who reads this thread now and a few years from now will learn something.

You can never try. You either do it or you don't.

Not Active19-Oct-09 9:58

19-Oct-09 9:58

Even if I explained the concepts of good software design, i.e. no such thing as non-reusable code, or tried to have you understand SQL Injection attack profiles, it is clear you don't posses the capacity to understand it or you would never have made the previous comments.

If you think this, or the previous, was a harsh reply you need to get a thicker skin. Read some of the other responses from MVPs and Supporters and others. A reply from Christian will send you whimpering to your mother. http://www.codeproject.com/Messages/3239105/Re-how-to-concatenate-two-dropdownlist-items.aspx[^]
http://www.codeproject.com/Messages/3239048/Re-File-upload-using-batch-file-in-asp-net.aspx[^]

only two letters away from being an asset

RK KL19-Oct-09 10:13

19-Oct-09 10:13

Mark,

We are going on a tangent here.

I understand the questions in the threads you posted are coming from novice programmers, they posted in an entirely irrelevent forum, and that they didn't do their homework. People do that all the time, every minute, every hour, every day.

I see no relation to the question on hand.

You can never try. You either do it or you don't.

Brady Kelly19-Oct-09 11:21

Brady Kelly

19-Oct-09 11:21

No, you should be using LINQ, but short of that, there is nothing wrong with parametrised inline SQL.

Not Active19-Oct-09 11:51

19-Oct-09 11:51

A hearty debate but the general consensus is to use stored procs for a number of benefits.

only two letters away from being an asset

PIEBALDconsult19-Oct-09 15:37

PIEBALDconsult

19-Oct-09 15:37

How will you call them?

AspDotNetDev19-Oct-09 10:25

AspDotNetDev

19-Oct-09 10:25

string sql = @"
   	    select *
	    From Status 
	    From ActivityStatus ActStsId=StsId";

Putting an @ in front of a string makes it a multi-line string. Whitespace is preserved (including newlines). If you had a quote character in your SQL, you'd have to escape it by typing a two of them (you could do this with a quick find-replace in Notepad before pasting into C#).

But like others have said, typically a bad idea to put SQL in C# code. Some environments don't have stored procedures (like SQL Server CE), in which case I'd recommend storing the SQL in your settings as a string setting. You can then reference it by the setting name in your code.

Visual Studio is an excellent GUIIDE.