asked on

storing a sql query a more efficient way?

i'm using visual basic with asp.net

i'm using asp to perform a long query. it spans about 30 lines and i want the code to be clean. i am trying to store this as a string.

what is the best way to do this?

ASKER CERTIFIED SOLUTION

samtran0331

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

samtran0331

...or if that is not an option and you just want something that looks cleaner, you can use a stringbuilder

' Import system.text
Dim sb As StringBuilder = New StringBuilder
sb.Append("some text ")
sb.Append("some more text ")

which looks much cleaner than concat string
dim str as string
str = "some text "
str &= "some more text "

weeb0

or try toad and write your query in the editor and use the export to vb key. ;-)

rnagli

I usually build a template first and then replace template variables with real ones:
for example:
string template = "select #fld1, #fld2 from #Table where #fld3='#val1'"

and then
template = template.Replace("#fld1", "RealField1");
template = template.Replace("#fld2", "RealField1");
template = template.Replace("#fld3", "RealField3");
and so one.

kssaran

You might wannna break your query like this

strSQL = "SELECT columnA, " & _
" columnB, " & _
" columnC, " & _
"FROM tableA , " & _
" tableB " & _
"WHERE columnA = columnB " & _
"ORDER BY columnA"

Rgds
Sara