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

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 = @dotnet.itags.org.Country AND City = @dotnet.itags.org.City";
command.Parameters.Add(paramArray);

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

Thanks in advance.
Oren.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/
<orenr@.tici.co.ilwrote in message
news:1153394531.973683.183090@.s13g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

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.
>

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

karl

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

Quote:

Originally Posted by

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:

Quote:

Originally Posted by

Err...the command.Parameters is better is what i mean :)
>
karl
>
--
http://www.openmymind.net/
http://www.codebetter.com/
>
>
<orenr@.tici.co.ilwrote in message
news:1153394531.973683.183090@.s13g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

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@.corpoflondon.gov.ukwrote in message
news:1153396850.145845.319360@.m79g2000cwm.googlegr oups.com...

Quote:

Originally Posted by

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)
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:

Quote:

Originally Posted by

<paul.thomas@.corpoflondon.gov.ukwrote in message
news:1153396850.145845.319360@.m79g2000cwm.googlegr oups.com...
>

Quote:

Originally Posted by

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" <paul.thomas@.corpoflondon.gov.ukwrote in message
news:1153399319.323432.186430@.i3g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

Agreed but is there a performance hit?

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

Quote:

Originally Posted by

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.
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" <mark@.markNOSPAMrae.comwrote in message
news:uemWRy$qGHA.4944@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

"Paul" <paul.thomas@.corpoflondon.gov.ukwrote in message
news:1153399319.323432.186430@.i3g2000cwc.googlegro ups.com...
>

Quote:

Originally Posted by

>Agreed but is there a performance hit?

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

Quote:

Originally Posted by

>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.
>

*tip* --*hit*

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

Quote:

Originally Posted by

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" <mark@.markNOSPAMrae.comwrote in message
news:uemWRy$qGHA.4944@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

>"Paul" <paul.thomas@.corpoflondon.gov.ukwrote in message
>news:1153399319.323432.186430@.i3g2000cwc.googlegro ups.com...
>>

Quote:

Originally Posted by

>>Agreed but is there a performance hit?

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

Quote:

Originally Posted by

>>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.
>>

>
>

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.

Karl Seguin [MVP] wrote:

Quote:

Originally Posted by

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" <mark@.markNOSPAMrae.comwrote in message
news:uemWRy$qGHA.4944@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

"Paul" <paul.thomas@.corpoflondon.gov.ukwrote in message
news:1153399319.323432.186430@.i3g2000cwc.googlegro ups.com...

Quote:

Originally Posted by

Agreed but is there a performance hit?

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

Quote:

Originally Posted by

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

Thanks for the tip.

Saturday, March 24, 2012

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

0 comments:

Post a Comment

using IIS

Blog Archive

About Me