SQL Statement in C#

[=?Utf-8?B?Q2hyaXM=?=]

I always have trouble writing a SQL statement when it comes to the handling
of the quotes. Can someone please help me with this line of ocde? I know I
need to have "Armed Away" in quotes too (I believe)
string sCommand = "SELECT CommandString FROM Commands WHERE CommandName =
Armed Away";

[Peter Bradley]

Don't do it. Use a stored procedure.

But if you must use a command string, you want escaped single quotes. Or
you can use unescaped single quotes in your string literal, with the @ sign
preceding it.

So, either:

"SELECT CommandString FROM Commands WHERE CommandName = \'Armed Away\'";

or:

@"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

HTH
Peter

"Chris" <Ch***@discussions.microsoft.comwrote in message
news:30**********************************@microsof t.com...

>I always have trouble writing a SQL statement when it comes to the handling
of the quotes. Can someone please help me with this line of ocde? I know
I
need to have "Armed Away" in quotes too (I believe)
string sCommand = "SELECT CommandString FROM Commands WHERE CommandName =
Armed Away";

[PokerMan]

string sCommand = "SELECT CommandString FROM Commands WHERE CommandName =
'Armed Away' ";
"Chris" <Ch***@discussions.microsoft.comwrote in message
news:30**********************************@microsof t.com...

>I always have trouble writing a SQL statement when it comes to the handling
of the quotes. Can someone please help me with this line of ocde? I know
I
need to have "Armed Away" in quotes too (I believe)
string sCommand = "SELECT CommandString FROM Commands WHERE CommandName =
Armed Away";

[Jon Skeet [C# MVP]]

Peter Bradley <pb******@uwic.ac.ukwrote:

Don't do it. Use a stored procedure.

Personally I disagree with the idea of using stored procedures for
everything, unless it's for security reasons. With ORM systems like
Hibernate, you can perform much richer queries directly than are easily
feasible with stored procs unless those procs end up generating dynamic
SQL (which is messy and prone to bugs).

The better alternative is to use a parameterised query - you get the
benefits of the parameters being passed without risk of SQL injection
attacks etc, but without the development/maintenance overhead of having
to add a stored proc for every query.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Chris Dunaway]

On Feb 16, 8:45 am, Jon Skeet [C# MVP] <[e-mail]:

Peter Bradley <[e-mail]:

Don't do it. Use a stored procedure.

Personally I disagree with the idea of using stored procedures for
everything, unless it's for security reasons. With ORM systems like
Hibernate, you can perform much richer queries directly than are easily
feasible with stored procs unless those procs end up generating dynamic
SQL (which is messy and prone to bugs).

The better alternative is to use a parameterised query - you get the
benefits of the parameters being passed without risk of SQL injection
attacks etc, but without the development/maintenance overhead of having
to add a stored proc for every query.

--
Jon Skeet - <[e-mail]>http://www.pobox.com/~skeet Blog:http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

I also tend to use parameterized queries but I think this is one of
those "religious" type things with one side saying that stored procs
are "the only true way" and the other side saying they are not.

Here's an interesting article that *doesn't* promote the use of
sprocs:

http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

I'm not sure I agree with it 100%, but it is an interesting read.

[PokerMan]

oops lol yes don't forget the @ to make the string literal.

But i agree with Peter, stored procs are 'best' but not the only way.

To add more, i originally had all my code with a class acting as a data
layer and it had all my sql statements in. I eventually moved all my swl
statements however small, and it does become a bit of overkill, to the
database.

The advantage became, that i could work on the database problems and make
big adjustsments to my db without ever touching my code or recompiling. Not
to mention the code side became neater to read (assuming storedprocedue
names were neat). A big help was a method i made that allowed me to just
pass in the parameter names and values and nothing else to run any stored
procedure. Until then it gets tiring with all the parameter code for stored
procs.

So i would always advise from my experience of both methods, to go the
stored proc way. I found it very advantageous and the only downsides have
been when i need a sql statement changed that i have to fire up the db. But
the advantages and organisation as well as scalability make it by far the
better option in my opinion.
"PokerMan" <no****@pokercat.co.ukwrote in message
news:OS**************@TK2MSFTNGP06.phx.gbl...

>
string sCommand = "SELECT CommandString FROM Commands WHERE CommandName =
'Armed Away' ";
"Chris" <Ch***@discussions.microsoft.comwrote in message
news:30**********************************@microsof t.com...

>>I always have trouble writing a SQL statement when it comes to the
handling
of the quotes. Can someone please help me with this line of ocde? I know
I
need to have "Armed Away" in quotes too (I believe)
string sCommand = "SELECT CommandString FROM Commands WHERE CommandName =
Armed Away";

[Peter Bradley]

Yes. Both of you guys are correct. I shouldn't have inferred that Stored
Procedures are the One True Way. My apologies. And I agree with your
comments about parameterised queries. They are often a better solution, and
I should have included that in my response.

Thanks guys.
Peter

"Chris Dunaway" <du******@gmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...

On Feb 16, 8:45 am, Jon Skeet [C# MVP] <[e-mail]:

>Peter Bradley <[e-mail]:

Don't do it. Use a stored procedure.

Personally I disagree with the idea of using stored procedures for
everything, unless it's for security reasons. With ORM systems like
Hibernate, you can perform much richer queries directly than are easily
feasible with stored procs unless those procs end up generating dynamic
SQL (which is messy and prone to bugs).

The better alternative is to use a parameterised query - you get the
benefits of the parameters being passed without risk of SQL injection
attacks etc, but without the development/maintenance overhead of having
to add a stored proc for every query.

--
Jon Skeet - <[e-mail]>http://www.pobox.com/~skeet
Blog:http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

I also tend to use parameterized queries but I think this is one of
those "religious" type things with one side saying that stored procs
are "the only true way" and the other side saying they are not.

Here's an interesting article that *doesn't* promote the use of
sprocs:

http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

I'm not sure I agree with it 100%, but it is an interesting read.

[Dylan Parry]

Peter Bradley wrote:

Don't do it. Use a stored procedure.

I've heard people saying this many times in the past, and while I don't
want to get into the politics of it, I've never used them simply because
I don't know how to! Any pointers for a self-confessed "n00b"?

--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk

Programming, n: A pastime similar to banging one's head
against a wall, but with fewer opportunities for reward.

[Peter Bradley]

Enw Cymreig, da.

Assuming you're using SQL Server, try Transact SQL help in Query Analyser?

or you could try this:

http://msdn2.microsoft.com/en-us/lib...9(SQL.80).aspx

Please bear in mind what others have said about paramaterised queries.

The main point is that you shouldn't pass "tainted" strings (i.e. strings
built from input outside of the method that have not been sanitised) as
queries against databases. Users are able to inject malicious SQL
statements with frightening ease if you do this. Using parameters (whether
via paramaterised queries or stored procs) will ensure that any input is
treated as a literal string and not as executable statements - assuming I'm
reading Microsoft's literature correctly.
Peter
"Dylan Parry" <us****@dylanparry.comwrote in message
news:45*********************@news.gradwell.net...

Peter Bradley wrote:

>Don't do it. Use a stored procedure.

I've heard people saying this many times in the past, and while I don't
want to get into the politics of it, I've never used them simply because
I don't know how to! Any pointers for a self-confessed "n00b"?

--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk

Programming, n: A pastime similar to banging one's head
against a wall, but with fewer opportunities for reward.

[=?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=]

Agreed. The key point (besides your noted protection from SQL injection
attacks) is that in both the case of a stored procedure and a parameterized
SQL text statement, the SQL Server is able to use the prepared statement and
compile it, and the execution path, only "one time". After that, it only
needs to replace the typed parameter values.
Peter

--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net


"Jon Skeet [C# MVP]" wrote:

Peter Bradley <pb******@uwic.ac.ukwrote:

Don't do it. Use a stored procedure.

Personally I disagree with the idea of using stored procedures for
everything, unless it's for security reasons. With ORM systems like
Hibernate, you can perform much richer queries directly than are easily
feasible with stored procs unless those procs end up generating dynamic
SQL (which is messy and prone to bugs).

The better alternative is to use a parameterised query - you get the
benefits of the parameters being passed without risk of SQL injection
attacks etc, but without the development/maintenance overhead of having
to add a stored proc for every query.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Dylan Parry]

Peter Bradley wrote:

Enw Cymreig, da.

Heh. I'm Welsh, but I don't speak (or read) a word of it ;)

Assuming you're using SQL Server, try Transact SQL help in Query Analyser?

or you could try this:

http://msdn2.microsoft.com/en-us/lib...9(SQL.80).aspx

Thanks for both pointers.

Please bear in mind what others have said about paramaterised queries.

Ok, well I don't really know the terminology, but I just code stuff :)
At the moment I have code that looks like:

this.command = this.connection.CreateCommand();
this.command.CommandText = "SELECT foo, bar FROM foobar WHERE foo=@foo";
this.command.Parameters.Add("@foo", SqlDbType.Int).Value = myInt;
this.dataReader = this.command.ExecuteReader();

Is that what you mean by parameterised query?

--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk

Programming, n: A pastime similar to banging one's head
against a wall, but with fewer opportunities for reward.

[Jon Skeet [C# MVP]]

Dylan Parry <us****@dylanparry.comwrote:

Please bear in mind what others have said about paramaterised queries.

Ok, well I don't really know the terminology, but I just code stuff :)
At the moment I have code that looks like:

this.command = this.connection.CreateCommand();
this.command.CommandText = "SELECT foo, bar FROM foobar WHERE foo=@foo";
this.command.Parameters.Add("@foo", SqlDbType.Int).Value = myInt;
this.dataReader = this.command.ExecuteReader();

Is that what you mean by parameterised query?

Yup, that's right.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

There is no reason to escape apostrophes in a string literal.

So:

"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

or

@"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"
Peter Bradley wrote:

Don't do it. Use a stored procedure.

But if you must use a command string, you want escaped single quotes. Or
you can use unescaped single quotes in your string literal, with the @ sign
preceding it.

So, either:

"SELECT CommandString FROM Commands WHERE CommandName = \'Armed Away\'";

or:

@"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

HTH
Peter

"Chris" <Ch***@discussions.microsoft.comwrote in message
news:30**********************************@microsof t.com...

>I always have trouble writing a SQL statement when it comes to the handling
of the quotes. Can someone please help me with this line of ocde? I know
I
need to have "Armed Away" in quotes too (I believe)
string sCommand = "SELECT CommandString FROM Commands WHERE CommandName =
Armed Away";

--
Göran Andersson
_____
http://www.guffa.com

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

PokerMan wrote:

oops lol yes don't forget the @ to make the string literal.

The @ doesn't make a string literal, it makes it @-quoted.

A literal string is a string that is known at compile time.

--
Göran Andersson
_____
http://www.guffa.com

[Jon Skeet [C# MVP]]

Göran Andersson <gu***@guffa.comwrote:

PokerMan wrote:

oops lol yes don't forget the @ to make the string literal.

The @ doesn't make a string literal, it makes it @-quoted.

A literal string is a string that is known at compile time.

To use the C# spec terminology, it makes it a "verbatim string
literal" instead of just a "string literal".

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Mark Rae]

"Göran Andersson" <gu***@guffa.comwrote in message
news:On**************@TK2MSFTNGP03.phx.gbl...

There is no reason to escape apostrophes in a string literal.

So:

"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

or

@"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

Or even...

"SELECT CommandString FROM Commands WHERE CommandName = \'Armed Away\'"

or

@"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Mark Rae wrote:

"Göran Andersson" <gu***@guffa.comwrote in message
news:On**************@TK2MSFTNGP03.phx.gbl...

>There is no reason to escape apostrophes in a string literal.

So:

"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

or

@"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

Or even...

"SELECT CommandString FROM Commands WHERE CommandName = \'Armed Away\'"

or

@"SELECT CommandString FROM Commands WHERE CommandName = 'Armed Away'"

As I said, there is no reason to escape apostrophes in a string literal.

--
Göran Andersson
_____
http://www.guffa.com

[PokerMan]

Woah picky picky now now lol

"Jon Skeet [C# MVP]" <sk***@pobox.comwrote in message
news:MP************************@msnews.microsoft.c om...
Göran Andersson <gu***@guffa.comwrote:

PokerMan wrote:

oops lol yes don't forget the @ to make the string literal.

The @ doesn't make a string literal, it makes it @-quoted.

A literal string is a string that is known at compile time.

To use the C# spec terminology, it makes it a "verbatim string
literal" instead of just a "string literal".

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

[Will]

This is mainly MS-SQL thinking, with other databases(Oracle, db2,etc)
they do not think this way dynamic SQL is the prime method. The
people who have a problem with stored procedures have it with CRUD
statements, if you are doing something that multiple database pulls,
process the data, asking for more database info and then puts out a
simple string or number use a stored procedure the performance is
better then anything dynamic SQL on the client will give.

Now for the prime reason they give for using stored procedures for
CRUD.

Speed. Back in the old days of MS-SQL 6.5 stored procedures would be
compiled and kept around while dynamic would not so you gained a
decent speed increase. Since MS-SQL 7 this has not been the case
parameterized dynamic SQL and stored procedures are treated the same.
Where stored procedures can give a speed boost is that there is a
better chance that the same plan will be use. However stored
procedures use alot of coalease and isnull statements and those are
very CPU intensive.

Security. Compared to other database the security in MS-SQL is
garbage. However with stored procedures you do start to add security
because you only have to give users access to the stored procedures
and not the table themselves. the stored procedures have the
permissions to the tables and that is given internally. For client/
server applications this can be useful since you don't have to any
chance of the user of digging up the password, however with web and
other 3-physical tier designs this is not the case since the user
would not have the direct connections to the database. Also the
previously mentioned SQL injection attacks

That is the basis of it check of the following links for more detailed
talks

http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx
http://www.codinghorror.com/blog/archives/000117.html


On Feb 16, 5:25 pm, Dylan Parry <[e-mail]:

Peter Bradley wrote:

Don't do it. Use astoredprocedure.

I've heard people saying this many times in the past, and while I don't
want to get into the politics of it, I've never used them simply because
I don't know how to! Any pointers for a self-confessed "n00b"?

--
Dylan Parryhttp://electricfreedom.org|http://webpageworkshop.co.uk

Programming, n: A pastime similar to banging one's head
against a wall, but with fewer opportunities for reward.

[Jay]

Much of the discussion in the link you gave is based on how SQL Server pre-compiles/caches.

Is this discussion about whether or not to use stored procedures mainly applicable to SQL Server, or
is it applicable to other database too?
"Chris Dunaway" <du******@gmail.comwrote in message
news:11**********************@m58g2000cwm.googlegr oups.com...
On Feb 16, 8:45 am, Jon Skeet [C# MVP] <[e-mail]:

Peter Bradley <[e-mail]:

Don't do it. Use a stored procedure.

Personally I disagree with the idea of using stored procedures for
everything, unless it's for security reasons. With ORM systems like
Hibernate, you can perform much richer queries directly than are easily
feasible with stored procs unless those procs end up generating dynamic
SQL (which is messy and prone to bugs).

The better alternative is to use a parameterised query - you get the
benefits of the parameters being passed without risk of SQL injection
attacks etc, but without the development/maintenance overhead of having
to add a stored proc for every query.

--
Jon Skeet - <[e-mail]>http://www.pobox.com/~skeet Blog:http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too

I also tend to use parameterized queries but I think this is one of
those "religious" type things with one side saying that stored procs
are "the only true way" and the other side saying they are not.

Here's an interesting article that *doesn't* promote the use of
sprocs:

http://weblogs.asp.net/fbouma/archiv.../18/38178.aspx

I'm not sure I agree with it 100%, but it is an interesting read.

[Jon Skeet [C# MVP]]

<"Jay" <nospam>wrote:

Much of the discussion in the link you gave is based on how SQL
Server pre-compiles/caches.

Is this discussion about whether or not to use stored procedures
mainly applicable to SQL Server, or is it applicable to other
database too?

It would be down to the individual database - consult the docs or run
performance tests against the exact database you're interested in.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet Blog: http://www.msmvps.com/jon.skeet
If replying to the group, please do not mail me too