Help with formatting SQL string in C#

[markknowsley]

What I mean is: when I use a SELECT statement in my C# (this is in Visual Studio 2003) I currently have to paste the string for the SELECT statement in one long line which is a nightmare for editing if I want to make changes.

Can someone recommend a tutorial for how I can divide the string down several lines in a manner that the compiler will appreciate?

 

[litton1]

is this what you mean?

string sqlSelect = "SELECT staff_id, salutation, first_name, surname, job_ID, time_Recording,"
				+ " NI_Number, address1, address2, address3, city, county, country,"
				+ " postcode, home_Tel, mobile_Tel, emerg_Contact_Name, emerg_Contact_Num,"
				+ " passport_Number, passport_Expire, Date_Of_Birth, company_Start_Date,"
				+ " company_Finish_Date, notes, holiday_Allocation, lunch, driving_Licence"
				+ " FROM Database.MyTable";

Age is a consequence of experience

 

[markknowsley]

That's exactly what I meant. Much appreciated - thanks!

 

[cappmgr]

Take a look at the StringBuilder class.
Marty

 

[litton1]

If there is only one string then the string builder is a bad idea!
The string builder is used when there are a number of add on’s to the string but on this string there are none.
e.g.

string myString = "one";

string myString += "Two"; // there are now two strings

string myString += "Three"; //there are now three strings

//should have used the StringBuilder Class

string myNewString = "One" + "Two"
+ "Three"; //One string So dont use StringBuilder Class (Takes To Long)

Age is a consequence of experience

 

[markknowsley]

Is there any way of making it easier to edit SQL statements inside Visual Studio? Some way of referencing a file that I can edit using a proper editor with colours and things?

 

[litton1]

you can have the environment set so that strings are one colour and vars are another and operators are another etc, if thats what you mean go to
"Tools>Options>Fonts And Colors" and have a mess

Age is a consequence of experience

 

[ddiamond]

For large sql strings I normally store them in a separate text file and then load them with the following code:

StreamReader sr = new StreamReader("MySelect.sql");
sqlSelect = sr.ReadToEnd();
sr.close();

That way you can edit MySelect.sql with your editor of choice. You can also add MySelect.sql to your C# project and then edit it with visual studio. As long as you don't put the above code in a loop, performance should be ok.

 

[markknowsley]

Great answers - thanks!

 

[markknowsley]

Sorry to have to reopen this - if I add a sql file to the project and access it via StreamReader I get a message saying that the file cannot be located in c:\windows\system32.

How do I change this location to c:\inetpub\

http://wwwroot\projectfolder?

And if I copy the project to a web server will it take the file with it and put it in the right place?

 

[litton1]

where is the file? try adding a path

string thePath = @"c:\somepath\"
string theFile = " MySelect.sql
string aFileAndPath  = thePath + theFile;
StreamReader sr = new StreamReader(aFileAndPath)
sqlSelect = sr.ReadToEnd();
sr.close();

Age is a consequence of experience

 

[markknowsley]

Thanks for your help.

I used:
string filename = Server.MapPath("nameoffile.sql");
and then used StreamReader() as above to process the text.

and the SQL files seemed to copy across OK when using the 'Copy Project' feature in Visual Studio.

 

[stevexff]

Of course, you could always put the large and complicated SQL in a stored procedure. Then you could edit it in VS...

 

[markknowsley]

Yes, granted, but i started trying to use sprocs and they kept returning no data (i'm sure it was a simple and obvious reason, but that's another discussion altogether...)