473,574 Members | 2,927 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

A general question about SQL statement

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
9 2353
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.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #2
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.co m> wrote in message
news:MP******** *************** *@msnews.micros oft.com...
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.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Nov 16 '05 #3
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******** ******@TK2MSFTN GP09.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
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******* *******@TK2MSFT NGP09.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
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************ ****@sportairme dia.ca> wrote in message
news:79******** *************** ***@posting.goo gle.com...
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******* *******@TK2MSFT NGP09.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
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.co m>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 16 '05 #7
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.Substri ng(0,sqlStmt.Le ngth-1);
// strip off last AND
sqlWhere = sqlWhere.Substr ing(0,sqlWhere. Length-4);

// build the where clause

sqlStmt += sqlWhere;

sqlCommand = new SqlCommand(sqlS tmt,this.sqlCon nect);

// add the parameters

foreach (string colName in tableColumns)
{
sqlParam = new SqlParameter();
sqlParam.Parame terName = "@"+colName ;
sqlParam.Source Column = colName;

sqlCommand.Para meters.Add(sqlP aram);

sqlParam = new SqlParameter();
sqlParam.Parame terName = "@old"+colN ame;
sqlParam.Source Column = colName;
sqlParam.Source Version = DataRowVersion. Original;
sqlCommand.Para meters.Add(sqlP aram);

}
this.daClassAda pter.UpdateComm and = 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******* ******@TK2MSFTN GP11.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
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******** ******@TK2MSFTN GP09.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
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************ ****@sportairme dia.ca> wrote in message
news:79******** *************** ***@posting.goo gle.com...
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.Substri ng(0,sqlStmt.Le ngth-1);
// strip off last AND
sqlWhere = sqlWhere.Substr ing(0,sqlWhere. Length-4);

// build the where clause

sqlStmt += sqlWhere;

sqlCommand = new SqlCommand(sqlS tmt,this.sqlCon nect);

// add the parameters

foreach (string colName in tableColumns)
{
sqlParam = new SqlParameter();
sqlParam.Parame terName = "@"+colName ;
sqlParam.Source Column = colName;

sqlCommand.Para meters.Add(sqlP aram);

sqlParam = new SqlParameter();
sqlParam.Parame terName = "@old"+colN ame;
sqlParam.Source Column = colName;
sqlParam.Source Version = DataRowVersion. Original;
sqlCommand.Para meters.Add(sqlP aram);

}
this.daClassAda pter.UpdateComm and = 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3223
by: gcook | last post by:
Hi, I've got an old perl program running on my webserver - so old that I haven't used a perl programmer in about two years :) (we've gone all php for a variety of reasons). Anyway, I'm stumbling on a problem that I am almost certain is a problem with my inputs to the script. The program has a debug mode that is giving me some very weird...
11
10823
by: galina betz | last post by:
Please help! Getting this error intermittently: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at...
11
5843
by: CSN | last post by:
Is it possible to iterate over an array in plpgsql? Something like: function insert_stuff (rel_ids int) .... foreach rel_ids as id insert into table (rel_id, val) values (id, 5);
7
2234
by: Felix Kater | last post by:
Hi, when I need to execute a general clean-up procedure (inside of a function) just before the function returns -- how do I do that when there are several returns spread over the whole function? My first approach: Use "while(1)" and "break", however this doesn't work if there is another loop inside (since I can't break two loops at the...
39
2365
by: Hareth | last post by:
C# 2005 express & vb 2005 express: 1. During runtime, I can edit my codes in C#..... How come this cannot be done in VB? it says ...."read-only" during runtime...... 2. Why does vb automatically show the errors, but C# i have to build the app b4 errors are underlined in my code?
9
5154
by: VenuGopal | last post by:
Hi, why n++ executes faster than n+1..... or does it realli execute faster? thanks Venugopal.B
3
2263
by: Yong | last post by:
I get a general network error when I try to make asynchronously call ExecuteNonQuery on long sql statements that run in parallel. Here is the background info on what I'm trying to accomplish: I have a sql file with two long T-SQL batches, each of which contains about 50 thousand simple insert statments into 2 non-indexed tables. Batch 1...
66
3664
by: KimmoA | last post by:
Hey! Some questions about C that have been bugging me for a while... 1) Is inline a valid C keyword or not? I was kind of surprised of not finding it in C, to be honest. My "The C Programming Language" book doesn't mention it. 2) I understand that C doesn't care about whitespace that much, but why did they make it impossible to use the...
11
2637
by: dhan | last post by:
please give answer
0
7805
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8053
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8237
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7817
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8098
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5622
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5301
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3752
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1060
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.