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

Discussion in 'ASP .Net' started by orenr@tici.co.il, Jul 20, 2006.

  1. Guest

    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.
     
    , Jul 20, 2006
    #1
    1. Advertising

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


    <> wrote in message
    news:...
    > 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], Jul 20, 2006
    #2
    1. Advertising

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

    karl

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


    <> wrote in message
    news:...
    > 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], Jul 20, 2006
    #3
  4. Guest

    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/
    >
    >
    > <> wrote in message
    > news:...
    > > 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.
    > >
     
    , Jul 20, 2006
    #4
  5. Mark Rae Guest

    <> wrote in message
    news:...

    > 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, Jul 20, 2006
    #5
  6. Paul Guest

    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:

    > <> wrote in message
    > news:...
    >
    > > 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, Jul 20, 2006
    #6
  7. Mark Rae Guest

    "Paul" <> wrote in message
    news:...

    > 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.
     
    Mark Rae, Jul 20, 2006
    #7
  8. 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" <> wrote in message
    news:uemWRy$...
    > "Paul" <> wrote in message
    > news:...
    >
    >> 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], Jul 20, 2006
    #8
  9. *tip* --> *hit*

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


    "Karl Seguin [MVP]" <karl REMOVE @ REMOVE openmymind REMOVEMETOO . ANDME
    net> wrote in message news:...
    > 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" <> wrote in message
    > news:uemWRy$...
    >> "Paul" <> wrote in message
    >> news:...
    >>
    >>> 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], Jul 20, 2006
    #9
  10. JT Guest

    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" <> wrote in message
    > news:uemWRy$...
    > > "Paul" <> wrote in message
    > > news:...
    > >
    > >> 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, Jul 21, 2006
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jonck van der Kogel
    Replies:
    2
    Views:
    1,008
    Jonck van der Kogel
    May 27, 2004
  2. Replies:
    4
    Views:
    440
    Steven Bethard
    Sep 24, 2007
  3. Replies:
    1
    Views:
    108
    Eric Hodel
    Dec 18, 2005
  4. Jeff Leeman
    Replies:
    8
    Views:
    153
    Sebastian Hungerecker
    Apr 17, 2009
  5. palmiere
    Replies:
    1
    Views:
    465
    Erwin Moller
    Feb 9, 2004
Loading...

Share This Page