What is the best way for passing parameters to select command?

[orenr]

Hi

We have a web site for 100 users using SQL Server.
In our DAL all the selections when we need to pass parameters are using
the SqlCommand and they are something like:

SqlCommand com = new SqlCommand();
com.Connection = MyConnection;
com.Transaction = MyTransaction;
com.CommandText = ""SELECT CustomerID, CompanyName FROM Customers "

+ "WHERE Country = "+ MyCountryVal.ToString() + " AND City = "
+ MyCityVal;
dataReader = com.ExecuteReader();

I want to know if in this kind of commads i will have performace
issues?
Does it better to pass the parameters to the SqlCommand with the
SqlCommand.Parameters command as follow:

command.CommandText =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Parameters.Add(paramArray);

for (int j=0; j<paramArray.Length; j++)
{
command.Parameters.Add(paramArray[j]) ;
}

Thanks in advance.
Oren.

[Karl Seguin [MVP]]

It's better 'cuz it's about 100000x more secure. Performance isn't an issue
either way.

Karl
--
http://www.openmymind.net/
http://www.codebetter.com/
<or***@tici.co.ilwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

Hi

We have a web site for 100 users using SQL Server.
In our DAL all the selections when we need to pass parameters are using
the SqlCommand and they are something like:

SqlCommand com = new SqlCommand();
com.Connection = MyConnection;
com.Transaction = MyTransaction;
com.CommandText = ""SELECT CustomerID, CompanyName FROM Customers "

+ "WHERE Country = "+ MyCountryVal.ToString() + " AND City = "
+ MyCityVal;
dataReader = com.ExecuteReader();

I want to know if in this kind of commads i will have performace
issues?
Does it better to pass the parameters to the SqlCommand with the
SqlCommand.Parameters command as follow:

command.CommandText =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Parameters.Add(paramArray);

for (int j=0; j<paramArray.Length; j++)
{
command.Parameters.Add(paramArray[j]) ;
}

Thanks in advance.
Oren.

[Karl Seguin [MVP]]

Err...the command.Parameters is better is what i mean :)

karl

--
http://www.openmymind.net/
http://www.codebetter.com/
<or***@tici.co.ilwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

Hi

We have a web site for 100 users using SQL Server.
In our DAL all the selections when we need to pass parameters are using
the SqlCommand and they are something like:

SqlCommand com = new SqlCommand();
com.Connection = MyConnection;
com.Transaction = MyTransaction;
com.CommandText = ""SELECT CustomerID, CompanyName FROM Customers "

+ "WHERE Country = "+ MyCountryVal.ToString() + " AND City = "
+ MyCityVal;
dataReader = com.ExecuteReader();

I want to know if in this kind of commads i will have performace
issues?
Does it better to pass the parameters to the SqlCommand with the
SqlCommand.Parameters command as follow:

command.CommandText =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Parameters.Add(paramArray);

for (int j=0; j<paramArray.Length; j++)
{
command.Parameters.Add(paramArray[j]) ;
}

Thanks in advance.
Oren.

[paul.thomas]

Convert your command into a stored procedure and then pass in
parameters.

If you have optional parameters e.g Search on First name or Last name
you can change you
SQL like this.

CREATE PROCEDURE spgUser
{
@firstName nvarchar(50) = null,
@lastName nvarchar(50) = null
}
AS

SELECT

userID

FROM

userTable

WHERE

(@firstName IS NULL OR firstName = @firstName )
AND
(@lastName IS NULL OR lastName = @lastName)

This will also allow this procedure to bring back ALL users if no
params are passed.
If you don't want that simply do an IF test for both being null at the
start or put that
logic in you code.

Hope this helps.



Karl Seguin [MVP] wrote:

Err...the command.Parameters is better is what i mean :)

karl

--
http://www.openmymind.net/
http://www.codebetter.com/
<or***@tici.co.ilwrote in message
news:11**********************@s13g2000cwa.googlegr oups.com...

Hi

We have a web site for 100 users using SQL Server.
In our DAL all the selections when we need to pass parameters are using
the SqlCommand and they are something like:

SqlCommand com = new SqlCommand();
com.Connection = MyConnection;
com.Transaction = MyTransaction;
com.CommandText = ""SELECT CustomerID, CompanyName FROM Customers "

+ "WHERE Country = "+ MyCountryVal.ToString() + " AND City = "
+ MyCityVal;
dataReader = com.ExecuteReader();

I want to know if in this kind of commads i will have performace
issues?
Does it better to pass the parameters to the SqlCommand with the
SqlCommand.Parameters command as follow:

command.CommandText =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Parameters.Add(paramArray);

for (int j=0; j<paramArray.Length; j++)
{
command.Parameters.Add(paramArray[j]) ;
}

Thanks in advance.
Oren.

[Mark Rae]

<pa*********@corpoflondon.gov.ukwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

WHERE
(@firstName IS NULL OR firstName = @firstName )
AND
(@lastName IS NULL OR lastName = @lastName)

I find the following much easier to read:

WHERE
firstName = COALESCE(@firstName, firstName)
AND
lastName = COALESCE(@lastName, lastName)

[Paul]

Agreed but is there a performance hit?

When we initially went for the solution I wrote many queries like this

(@firstName = firstName OR @firstName IS NULL )

This is ALOT slower than

( @firstName IS NULL OR .....

When @firstName is indeed NULL.
Mark Rae wrote:

<pa*********@corpoflondon.gov.ukwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...

WHERE
(@firstName IS NULL OR firstName = @firstName )
AND
(@lastName IS NULL OR lastName = @lastName)

I find the following much easier to read:

WHERE
firstName = COALESCE(@firstName, firstName)
AND
lastName = COALESCE(@lastName, lastName)

[Mark Rae]

"Paul" <pa*********@corpoflondon.gov.ukwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

Agreed but is there a performance hit?

I don't know - is there...?

When we initially went for the solution I wrote many queries like this

(@firstName = firstName OR @firstName IS NULL )

This is ALOT slower than

( @firstName IS NULL OR .....

When @firstName is indeed NULL.

OK - I'll have to take your word for that, as I've never benchmarked it.

Thanks for the tip.

[Karl Seguin [MVP]]

Yes, there is a performance tip...and putting it in a sproc isn't
necessarily the best answer.

While I'm a big fan of sprocs (really big), they aren't any more secure, and
most developers don't realize that in many cases, they can run considerably
slower than inline SQL.

I disagree wth the blanket statement of putting it in an sproc - though I do
agree that it should be considered.

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:ue**************@TK2MSFTNGP04.phx.gbl...

"Paul" <pa*********@corpoflondon.gov.ukwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

>Agreed but is there a performance hit?

I don't know - is there...?

>When we initially went for the solution I wrote many queries like this

(@firstName = firstName OR @firstName IS NULL )

This is ALOT slower than

( @firstName IS NULL OR .....

When @firstName is indeed NULL.

OK - I'll have to take your word for that, as I've never benchmarked it.

Thanks for the tip.

[Karl Seguin [MVP]]

*tip* --*hit*

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
netwrote in message news:e7**************@TK2MSFTNGP04.phx.gbl...

Yes, there is a performance tip...and putting it in a sproc isn't
necessarily the best answer.

While I'm a big fan of sprocs (really big), they aren't any more secure,
and most developers don't realize that in many cases, they can run
considerably slower than inline SQL.

I disagree wth the blanket statement of putting it in an sproc - though I
do agree that it should be considered.

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:ue**************@TK2MSFTNGP04.phx.gbl...

>"Paul" <pa*********@corpoflondon.gov.ukwrote in message
news:11**********************@i3g2000cwc.googlegr oups.com...

>>Agreed but is there a performance hit?

I don't know - is there...?

>>When we initially went for the solution I wrote many queries like this

(@firstName = firstName OR @firstName IS NULL )

This is ALOT slower than

( @firstName IS NULL OR .....

When @firstName is indeed NULL.

OK - I'll have to take your word for that, as I've never benchmarked it.

Thanks for the tip.

[JT]

I agree with you. However, some people might point out that an
advantage of stored procedures is that they can be modified without a
code recompile. That may or may not be a concern. I haven't found
that to be a high priority and like the inline parameter approach.

JT

Karl Seguin [MVP] wrote:

Yes, there is a performance tip...and putting it in a sproc isn't
necessarily the best answer.

While I'm a big fan of sprocs (really big), they aren't any more secure, and
most developers don't realize that in many cases, they can run considerably
slower than inline SQL.

I disagree wth the blanket statement of putting it in an sproc - though I do
agree that it should be considered.

Karl

--
http://www.openmymind.net/
http://www.fuelindustries.com/
"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:ue**************@TK2MSFTNGP04.phx.gbl...

"Paul" <pa*********@corpoflondon.gov.ukwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

Agreed but is there a performance hit?

I don't know - is there...?

When we initially went for the solution I wrote many queries like this

(@firstName = firstName OR @firstName IS NULL )

This is ALOT slower than

( @firstName IS NULL OR .....

When @firstName is indeed NULL.

OK - I'll have to take your word for that, as I've never benchmarked it.

Thanks for the tip.