Ad Hoc Query in C#

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ok...here we go...

I am trying to manually run an SQL server back up job from c#. Here is a
snippet of my code to begin the process:

string strSQL = "USE msdb " +
"\n if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
"\n EXEC sp_delete_job @job_name = 'TempBackupJob' " +
"\n GO";
newDB.Command(strSQL);

This command exceptions with an "Incorrect syntax near 'GO'".
Now, when I put the EXACT same query into Query Analyzer like this:

USE msdb
if exists(select * from sysjobs_view where name = 'TempBackupJob')
EXEC sp_delete_job @job_name = 'TempBackupJob'
GO

the command executes without any errors.

Is there something I'm missing with newline characters? I also tried using
\r\n instead of just \n to separate the lines...that didn't work either.

Any help would be greatly appreciated.
 
take out the carriage returns and line breaks

The /+/'s will let you break the lines in your c# code

Use an @ in front of the first " (double quote)

string strSQL = @"USE msdb " +
" if exists(select * from sysjobs_view where name = 'TempBackupJob') " +
" EXEC sp_delete_job @job_name = 'TempBackupJob' " +
" GO";
 
Why do you need the \n and \r ? It is not like you have to print out the sql
string to make it readable. You just need to execute it and considering that
it is SQL all you need is good spacing between your SQL statements and
commands.

Try it with no \r and no \n and see what happens..

Good luck

Amadelle
 
When I take out the \r\n the command exceptions too.( Incorrect syntax near
'GO')
I also tested something in Query Anaylzer..if the SQL statment is all on one
line...it fails...if everything else EXCEPT GO is on the same line...the
query runs successfully. So...I am under the assumption that GO has to be on
its own line when it is evaluated. That's why I was wondering about line
breaks.

Got anything else ??
 
Thanks for the feedback...I took the carriage returns and line breaks out and
added the @ at the beginning of the string before the parenthesis...just like
your example..but that command failed too.

I also replied to Amadelle saying that when the command I'm trying to run is
all on one line in Quyery Anaylzer...it fails there as well. But if the
command is all on one line except for 'GO' it runs successfully. So if GO is
on its own line the command is good.

Any other ideas??
 
Rather than using SQL like this just build a stored procedure that contains
the logic. Then call the stored procedure from your code. Always makes for
cleaner and safe code this way.
 
He could do that, or just parameterize his string sql query.

You should never put values in quotes in a string.

Use @vars and replace them with sql params, just like in a stored procedure.

Putting logic like this in a stored procedure isn't alway to my liking
(although it seems to be Gospel in the MS camp).

It's perfectly reasonable to use queries in ado, and it gives the
programmer far more control than having to go back and forth to sprocs
and code.


Jim said:
Rather than using SQL like this just build a stored procedure that contains
the logic. Then call the stored procedure from your code. Always makes for
cleaner and safe code this way.
Click to expand...
 
I suppose it is a little different for me, since I have always had access to
the database as well as the application code.

I always like to put as much logic in the database as possible, simply
because I can change the stored procedure without needing to repackage and
deploy the application. It also makes for much cleaner code, although the
tradeoff is the programmer cannot see what is going on in the database.
This last piece can be either a pro or a con, depending on how knowledgeable
and involved the programmer is with the database code.

The other benefit is that it is much simpler to debug a single stored
procedure call than multiple lines of concatenated text.

John A. Bailo said:
He could do that, or just parameterize his string sql query.

You should never put values in quotes in a string.

Use @vars and replace them with sql params, just like in a stored procedure.

Putting logic like this in a stored procedure isn't alway to my liking
(although it seems to be Gospel in the MS camp).

It's perfectly reasonable to use queries in ado, and it gives the
programmer far more control than having to go back and forth to sprocs
and code.
Click to expand...
 
You know -- that really rings true.

I wish -- or maybe I could invent through operator overloading -- a
better way to put in long multiline strings in c# code.

Maybe with an XML comment insert ?

So the code would look like


<DEFINE id="mysqlquery>
SELECT * FROM TABLE
WHERE FIELD1=@VALUE
ORDER BY @ARG1
</DEFINE>

and I could put that right in a method, and then say something like

MySqlCommand.CommandText = SpecialXMLFunction("mysqlquer");
 
Being able to write readable SQL within the application code itself would be
nice, as you would be able to see everything in one place at the same time,
rather than going from the code to the database and back.
 
I wonder if it can be treated as an "attribute".

Somehow it doesn't seem exactly a match, but clearly I can add in XML
attributes that are attached to classes and use Reflection to utilize them.

I'm going to have to review Reflectin/Attributes to see if it can be done.
 
John A. Bailo said:
I wonder if it can be treated as an "attribute".

Somehow it doesn't seem exactly a match, but clearly I can add in XML
attributes that are attached to classes and use Reflection to utilize them.

I'm going to have to review Reflectin/Attributes to see if it can be done.
Click to expand...

I think what you're really after is NHibernate...
http://www.nhibernate.org

(You might also want to look at DLINQ...)
 
Back
Top