By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,180 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,180 IT Pros & Developers. It's quick & easy.

A general question about SQL statement

P: n/a
Hi all,

I would like to get people's opinion about executing SQL statements in C#
(or any other .NET language really). I used to create my SQL statement by
building a string and replacing single quote with two single quotes.
Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL
string. Should I always use SqlParameters? What are the
advantages/disadvantages between building SQL string and using SqlParameter?
Does one perform better than the other?

Thanks.
Michael
Nov 16 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Michael <m@m.com> wrote:
I would like to get people's opinion about executing SQL statements in C#
(or any other .NET language really). I used to create my SQL statement by
building a string and replacing single quote with two single quotes.
Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL
string. Should I always use SqlParameters? What are the
advantages/disadvantages between building SQL string and using SqlParameter?
Does one perform better than the other?


Always use parameters for anything other than a throwaway test.
Advantages:

o SQL injection attack becomes impossible
o Formatting is done for you (string escaping, date/time formatting
etc)
o The command can be reused with different values, increasing
performance

Disadvantages:
o It takes a bit more code to sort out the parameters to start with

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #2

P: n/a
Yes, by the way you can use PowerToys for ADO.NET to simplify use of
parameters, it can generate wrappers for calling stored procedures...

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
Michael <m@m.com> wrote:
I would like to get people's opinion about executing SQL statements in C# (or any other .NET language really). I used to create my SQL statement by building a string and replacing single quote with two single quotes.
Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL string. Should I always use SqlParameters? What are the
advantages/disadvantages between building SQL string and using SqlParameter? Does one perform better than the other?


Always use parameters for anything other than a throwaway test.
Advantages:

o SQL injection attack becomes impossible
o Formatting is done for you (string escaping, date/time formatting
etc)
o The command can be reused with different values, increasing
performance

Disadvantages:
o It takes a bit more code to sort out the parameters to start with

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

Nov 16 '05 #3

P: n/a
SQL Server caches commands and precompiles them. If the command string does
not change from one call to the next, as is the case with a parameterized
query, performance is significantly increased. But if you build the values
into the command string, it will change virtually every time, defeating this
optimization.

I haven't seen any benchmarks, and one's mileage probably varies, but
parameterized SQL statements probably approach the performance level of
stored procedures in some cases, particularly batch processes. The main
performance advantage of stored procedures would be that in most cases there
would be no server-side compiles at all, not even on the first call.

--Bob

"Michael" <m@m.com> wrote in message
news:O6**************@TK2MSFTNGP09.phx.gbl...
Hi all,

I would like to get people's opinion about executing SQL statements in C#
(or any other .NET language really). I used to create my SQL statement by
building a string and replacing single quote with two single quotes.
Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL
string. Should I always use SqlParameters? What are the
advantages/disadvantages between building SQL string and using SqlParameter? Does one perform better than the other?

Thanks.
Michael

Nov 16 '05 #4

P: n/a
Michael,

I agree with Jon, parameters are the way to go if at all possible.

One other tip, when using parameters with MS SQL remember to set
ANSI_NULLS off (SET ANSI_NULLS OFF) to make sure that NULL values
compare correctly. With ANSI_NULLS set to ON, NULL = NULL always
returns FALSE. This means that you may not get all the rows returned
or it can cause havoc if you are using full row optimistic concurrency
on UPDATES and DELETES.

This was one of those things I learned 4 years ago working in a
different development environment, and forgot to incorporate when
moving to C#. Took me half a day to figure out why I could not update
a row!

Best regards,

Tim

"Michael" <m@m.com> wrote in message news:<O6**************@TK2MSFTNGP09.phx.gbl>...
Hi all,

I would like to get people's opinion about executing SQL statements in C#
(or any other .NET language really). I used to create my SQL statement by
building a string and replacing single quote with two single quotes.
Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL
string. Should I always use SqlParameters? What are the
advantages/disadvantages between building SQL string and using SqlParameter?
Does one perform better than the other?

Thanks.
Michael

Nov 16 '05 #5

P: n/a
Actually, your problem is not the setting of ANSI_NULLS, but your SQL
syntax. Use IS NULL or IS NOT NULL instead of the = operator, and it won't
matter what the ANSI_NULLS setting is.

It is actually correct that a database NULL is not equal to anything --
including itself. That's because it has a different meaning in a database
context than it does in the context of, say, an object reference. A null
object reference means "uninitialized", which is a specific special value,
but a database NULL means "unknown". Something that's unknown cannot be
equal or not equal to anything else, or to itself. That is why the ANSI
standard specifies this behavior.

On the other hand if you are asking if a value IS or IS NOT NULL, (in other
words, is the value known or not) *now* you are asking the right question
and will get the answer you expect. It may seem like fussbudget semantics,
but semantics are important because they help you think accurately about
what you're doing.

--Bob

"Tim Holgerson" <tl****************@sportairmedia.ca> wrote in message
news:79**************************@posting.google.c om...
Michael,

I agree with Jon, parameters are the way to go if at all possible.

One other tip, when using parameters with MS SQL remember to set
ANSI_NULLS off (SET ANSI_NULLS OFF) to make sure that NULL values
compare correctly. With ANSI_NULLS set to ON, NULL = NULL always
returns FALSE. This means that you may not get all the rows returned
or it can cause havoc if you are using full row optimistic concurrency
on UPDATES and DELETES.

This was one of those things I learned 4 years ago working in a
different development environment, and forgot to incorporate when
moving to C#. Took me half a day to figure out why I could not update
a row!

Best regards,

Tim

"Michael" <m@m.com> wrote in message

news:<O6**************@TK2MSFTNGP09.phx.gbl>...
Hi all,

I would like to get people's opinion about executing SQL statements in C# (or any other .NET language really). I used to create my SQL statement by building a string and replacing single quote with two single quotes.
Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL string. Should I always use SqlParameters? What are the
advantages/disadvantages between building SQL string and using SqlParameter? Does one perform better than the other?

Thanks.
Michael

Nov 16 '05 #6

P: n/a
Bob Grommes <bo*@bobgrommes.com> wrote:
Actually, your problem is not the setting of ANSI_NULLS, but your SQL
syntax. Use IS NULL or IS NOT NULL instead of the = operator, and it won't
matter what the ANSI_NULLS setting is.

It is actually correct that a database NULL is not equal to anything --
including itself. That's because it has a different meaning in a database
context than it does in the context of, say, an object reference. A null
object reference means "uninitialized", which is a specific special value,
but a database NULL means "unknown". Something that's unknown cannot be
equal or not equal to anything else, or to itself. That is why the ANSI
standard specifies this behavior.

On the other hand if you are asking if a value IS or IS NOT NULL, (in other
words, is the value known or not) *now* you are asking the right question
and will get the answer you expect. It may seem like fussbudget semantics,
but semantics are important because they help you think accurately about
what you're doing.


Agreed with all of this. It seems odd to me that Tim was finding
updates and deletes failing, however - those should be using the
primary key, which should never be null in the first place!

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #7

P: n/a
Bob,

I think that if you look at my post you will see that I indicated the
problem becomes evident when you are attempting to use all of the
columns in a row to insure concurrency i.e. if any of the columns in a
row have been changed since the record was pulled from the server, the
update fails. This approach requires a WHERE clause that incorporates
all of the columns.

Here is the problem when using parameters with nulls.

Let's say you are creating an UPDATE statement for a DataAdapter using
parameters. The UPDATE statement is built the same way everytime.
The construction of the UPDATE statement is not dependant on the
actual data being passed to the server. A simple example follows:
// build update statement for dataadapter
string sqlStmt = "Update mytablename Set";
string sqlWhere = " Where ";
foreach (string colName in tableColumns) // where tableColumns is
an array of columns
{
sqlStmt +=" "+ colName + "= @"+colName+",";
sqlWhere += colName + "= @old"+colName+" AND ";
}
// strip off the last comma
sqlStmt = sqlStmt.Substring(0,sqlStmt.Length-1);
// strip off last AND
sqlWhere = sqlWhere.Substring(0,sqlWhere.Length-4);

// build the where clause

sqlStmt += sqlWhere;

sqlCommand = new SqlCommand(sqlStmt,this.sqlConnect);

// add the parameters

foreach (string colName in tableColumns)
{
sqlParam = new SqlParameter();
sqlParam.ParameterName = "@"+colName;
sqlParam.SourceColumn = colName;

sqlCommand.Parameters.Add(sqlParam);

sqlParam = new SqlParameter();
sqlParam.ParameterName = "@old"+colName;
sqlParam.SourceColumn = colName;
sqlParam.SourceVersion = DataRowVersion.Original;
sqlCommand.Parameters.Add(sqlParam);

}
this.daClassAdapter.UpdateCommand = oCommand;

Assuming a simple update with three columns, the resulting command
sent to the SQL server would look something like this:

exec sp_executesql N'Update mytable Set ColOne= @ColOne, ColTwo=
@ColTwo, ColThres= @ColThree Where ColOne= @oldColOne AND ColTwo=
@oldColTwo AND ColThree= @oldColThree ', N'@ColOne
nvarchar(4000),@oldColOne nvarchar(4000),@ColTwo
nvarchar(4000),@oldColTwo nvarchar(4000),@ColThree
nvarchar(4000),@oldColThree nvarchar(4000), @ColOne = N'New Value',
@oldColOne = N'Old Value', @ColTwo = N'New Value', @oldColTwo = N'Old
Value', @colThree = NULL, @oldcolThree = NULL

In the SQL statement shown above, I am assuming that the user has
changed the values of colOne and colTwo. The value of colThree was
initially NULL and has not changed - still is null. With SET
ANSI_NULLS set to ON, this UPDATE statement will not update the row
because the WHERE clause will always return a FALSE value. This is
because the NULL = NULL comparision on colThree returns a false.

If you construct your WHERE clause only on the primary key as Jon
suggests you must either figure out another way to test to see if the
underlying data has changed since the result set was returned, or you
must live with the fact that you may be overwriting recent changes
with out giving the user the option of canceling the update. This may
be an acceptable solution in some circumstances.

I agree that SET ANSI_NULLS off is a workaround and if someone know a
better way to get around the problem describe above, I would be happy
to adopt it.

Best regards,

Tim

"Bob Grommes" <bo*@bobgrommes.com> wrote in message news:<e3*************@TK2MSFTNGP11.phx.gbl>...
Actually, your problem is not the setting of ANSI_NULLS, but your SQL
syntax. Use IS NULL or IS NOT NULL instead of the = operator, and it won't
matter what the ANSI_NULLS setting is.

It is actually correct that a database NULL is not equal to anything --
including itself. That's because it has a different meaning in a database
context than it does in the context of, say, an object reference. A null
object reference means "uninitialized", which is a specific special value,
but a database NULL means "unknown". Something that's unknown cannot be
equal or not equal to anything else, or to itself. That is why the ANSI
standard specifies this behavior.

On the other hand if you are asking if a value IS or IS NOT NULL, (in other
words, is the value known or not) *now* you are asking the right question
and will get the answer you expect. It may seem like fussbudget semantics,
but semantics are important because they help you think accurately about
what you're doing.

--Bob

Nov 16 '05 #8

P: n/a
Using SqlParameter is better.

First off, you are less susceptible to a small set of common coding
mistakes. Not only the single-quote-double-quote issue you mentioned, but
also date conversions, GUID conversions, and handling of large strings. All
of these can be different, and have to be handled with care, when composing
strings.

Secondly, and this is more important by far, if you compose your sql strings
as a string, you are very prone to sql injection errors. At my company, we
have tools that inspect the code and draw the attention of professional
security reviewers to every place in the code where a sql statement is being
executed. The reviewer will flag any place where sql statements are
composed in strings, and will prevent the app from going into production.
It is punative, true, but it was done because the good advice of the
security people was being ignored by lazy programmers (myself included).

Third, since the code inside ADODB is fast and well debugged, using SQL
parameters is very likely to be as fast or faster than your best code, with
fewer bugs.

Always use SQL Parameters.

--- Nick

"Michael" <m@m.com> wrote in message
news:O6**************@TK2MSFTNGP09.phx.gbl...
Hi all,

I would like to get people's opinion about executing SQL statements in C#
(or any other .NET language really). I used to create my SQL statement by
building a string and replacing single quote with two single quotes.
Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL
string. Should I always use SqlParameters? What are the
advantages/disadvantages between building SQL string and using SqlParameter? Does one perform better than the other?

Thanks.
Michael

Nov 16 '05 #9

P: n/a
Tim,

I avoid this by simply having a column of type timestamp in every table
where I need concurrency handling, and checking for changes in the
timestamp. This way your WHERE clause is only going to have to check one
field for changes, which is much more efficient.

--Bob

"Tim Holgerson" <tl****************@sportairmedia.ca> wrote in message
news:79**************************@posting.google.c om...
Bob,

I think that if you look at my post you will see that I indicated the
problem becomes evident when you are attempting to use all of the
columns in a row to insure concurrency i.e. if any of the columns in a
row have been changed since the record was pulled from the server, the
update fails. This approach requires a WHERE clause that incorporates
all of the columns.

Here is the problem when using parameters with nulls.

Let's say you are creating an UPDATE statement for a DataAdapter using
parameters. The UPDATE statement is built the same way everytime.
The construction of the UPDATE statement is not dependant on the
actual data being passed to the server. A simple example follows:
// build update statement for dataadapter
string sqlStmt = "Update mytablename Set";
string sqlWhere = " Where ";
foreach (string colName in tableColumns) // where tableColumns is
an array of columns
{
sqlStmt +=" "+ colName + "= @"+colName+",";
sqlWhere += colName + "= @old"+colName+" AND ";
}
// strip off the last comma
sqlStmt = sqlStmt.Substring(0,sqlStmt.Length-1);
// strip off last AND
sqlWhere = sqlWhere.Substring(0,sqlWhere.Length-4);

// build the where clause

sqlStmt += sqlWhere;

sqlCommand = new SqlCommand(sqlStmt,this.sqlConnect);

// add the parameters

foreach (string colName in tableColumns)
{
sqlParam = new SqlParameter();
sqlParam.ParameterName = "@"+colName;
sqlParam.SourceColumn = colName;

sqlCommand.Parameters.Add(sqlParam);

sqlParam = new SqlParameter();
sqlParam.ParameterName = "@old"+colName;
sqlParam.SourceColumn = colName;
sqlParam.SourceVersion = DataRowVersion.Original;
sqlCommand.Parameters.Add(sqlParam);

}
this.daClassAdapter.UpdateCommand = oCommand;

Assuming a simple update with three columns, the resulting command
sent to the SQL server would look something like this:

exec sp_executesql N'Update mytable Set ColOne= @ColOne, ColTwo=
@ColTwo, ColThres= @ColThree Where ColOne= @oldColOne AND ColTwo=
@oldColTwo AND ColThree= @oldColThree ', N'@ColOne
nvarchar(4000),@oldColOne nvarchar(4000),@ColTwo
nvarchar(4000),@oldColTwo nvarchar(4000),@ColThree
nvarchar(4000),@oldColThree nvarchar(4000), @ColOne = N'New Value',
@oldColOne = N'Old Value', @ColTwo = N'New Value', @oldColTwo = N'Old
Value', @colThree = NULL, @oldcolThree = NULL

In the SQL statement shown above, I am assuming that the user has
changed the values of colOne and colTwo. The value of colThree was
initially NULL and has not changed - still is null. With SET
ANSI_NULLS set to ON, this UPDATE statement will not update the row
because the WHERE clause will always return a FALSE value. This is
because the NULL = NULL comparision on colThree returns a false.

If you construct your WHERE clause only on the primary key as Jon
suggests you must either figure out another way to test to see if the
underlying data has changed since the result set was returned, or you
must live with the fact that you may be overwriting recent changes
with out giving the user the option of canceling the update. This may
be an acceptable solution in some circumstances.

I agree that SET ANSI_NULLS off is a workaround and if someone know a
better way to get around the problem describe above, I would be happy
to adopt it.

Best regards,

Tim

Nov 16 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.