Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > What is the best way for passing parameters to select command?

Reply
Thread Tools

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

 
 
orenr@tici.co.il
Guest
Posts: n/a
 
      07-20-2006
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.

 
Reply With Quote
 
 
 
 
Karl Seguin [MVP]
Guest
Posts: n/a
 
      07-20-2006
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/


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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.
>



 
Reply With Quote
 
 
 
 
Karl Seguin [MVP]
Guest
Posts: n/a
 
      07-20-2006
Err...the command.Parameters is better is what i mean

karl

--
http://www.openmymind.net/
http://www.codebetter.com/


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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.
>



 
Reply With Quote
 
paul.thomas@corpoflondon.gov.uk
Guest
Posts: n/a
 
      07-20-2006
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/
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) 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.
> >


 
Reply With Quote
 
Mark Rae
Guest
Posts: n/a
 
      07-20-2006
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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)


 
Reply With Quote
 
Paul
Guest
Posts: n/a
 
      07-20-2006
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:

> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) 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)


 
Reply With Quote
 
Mark Rae
Guest
Posts: n/a
 
      07-20-2006
"Paul" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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.


 
Reply With Quote
 
Karl Seguin [MVP]
Guest
Posts: n/a
 
      07-20-2006
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" <(E-Mail Removed)> wrote in message
news:uemWRy$(E-Mail Removed)...
> "Paul" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) 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.
>



 
Reply With Quote
 
Karl Seguin [MVP]
Guest
Posts: n/a
 
      07-20-2006
*tip* --> *hit*

--
http://www.openmymind.net/
http://www.fuelindustries.com/


"Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
net> wrote in message news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:uemWRy$(E-Mail Removed)...
>> "Paul" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed) 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.
>>

>
>



 
Reply With Quote
 
JT
Guest
Posts: n/a
 
      07-21-2006
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" <(E-Mail Removed)> wrote in message
> news:uemWRy$(E-Mail Removed)...
> > "Paul" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed) 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.
> >


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing parameters to an executable vs. passing them to a server Ramon F Herrera C++ 8 09-13-2009 02:48 AM
best way to publish webmethod parameters and return data Enda Mannion ASP .Net Web Services 0 06-15-2009 02:37 PM
Passing parameters best practice tshad ASP .Net 3 08-09-2006 06:52 PM
Passing multiple parameters with select-option to controller serbulentunsal@gmail.com Ruby 1 12-18-2005 08:08 PM
select of select box will select multiple in another box palmiere Javascript 1 02-09-2004 01:11 PM



Advertisments