How to write long string sentences

[Felipe Blin]

Hi,
Sorry for this newbie question.
I usually have to write long string sentence like this:

strSQL= " select ........." _
+ " from ......." _
+ " where ....." _
+ " and....." _
+ " and ..." _
+ " group by..."
etc....
(Note: I can not use store procedure)
This kind of sentence is hard to maintain and to write.
Is there a way to do it better??
Thanks
Felipe Blin

 

[Rich Wallace]

Have a look at System.IO.StringBuilder.

HTH
-Rich

 

[Jay B. Harlow [MVP - Outlook]]

Felipe,
My first choice would be a Stored Procedure.

Whether or not a stored procedure was an option, within my code I would use
a data command object placed on an Component to manage my SQL statements
(either stored procedure calls or straight SQL statements such as your
select).

You use "Project - Add Component" to add a new component to your project.

Using the visual designer of the Component you can then drag the Connection,
Command, and DataAdapter objects that you want to it from the Toolbox.
Alternatively you can drag & drop items from the Server Explorer. You can
set properties of these with the properties window. Using the visual
designer & property window of the component will allow you to use a visual
query designer on the SQL statement itself (similar to the query builder in
Access).

Then from the Code Behind I add any methods that I want exposed via the
Component. Such as a Fill & Update that calls all of the contained
DataAdapters to fill or update the selected DataSet.

Hope this helps
Jay

 

[Felipe Blin]

Hi jay,
I know what your are talking about. The problem if for those cases (they
always exist in complex systems) where that kind of solution is not good
enough or definitely not possible.
So what I want is a way to manage long string manipulation.
Thanks anyway

 

[Cor]

Hi Felipe,

In addition to Jay B.

Have a look at the "command parameters"

http://msdn.microsoft.com/library/d.../vbtsksettinggettingdatacommandparameters.asp

The can also be used direct on an adapter.

I hope this helps?

Cor

 

[Jay B. Harlow [MVP - Outlook]]

Felipe,
Do you want to manage long SQL statements or do you want to manage long
string manipulation. I see them as two largely different problems.

Personally anything other then a Component or Stored Procedures for Long SQL
statements is not good enough! As you are finding you run into "problems"
trying to manipulate the SQL statement as a string.

Also if you happen to be concatenation in any parameters (instead of using
parameter markers) then you are opening yourself for even more pain, as you
will need to manually "quote" the parameters, which is a pain, why reinvent
the wheel, using parameter markers & parameter object will properly handle
all quoting for you, also you avoid "injection attacks" especially on web
pages where you allow an id to be requested (maybe indirectly via
QueryString) and simply concatenate this QueryString value into the SQL,
what happens when someone modifies the QueryString such that it includes
other SQL statements? Remember SQL Server supports multiple statements
separated via a semicolon.

So what I want is a way to manage long string manipulation.

I still question if you are wanting long string manipulation or simply
building long string constants.

If you are truly manipulating long strings, consider using a StringBuilder.
If performance & GC profiling suggest it is a performance issue.

If your (non SQL) string is a constant I would stick with what you had
originally had, as the constant will be created at compile time, with no
runtime performance issues.

If for some reason you don't want to use Visual Designers for managing your
SQL statements I've seen others suggest storing the SQL statements in an
external file. I would use either an external text file or an XML file, if I
used an XML file I would consider using an embedded Resource file (.resx) to
avoid the file itself from being modified.

However I would hope once you fully understand what the Component is buying
you will change your opinion on using Component...

Hope this helps
Jay