none
Executing Stored Proecdures - CommandType.Text vs CommandType.StoredProcedure RRS feed

  • Question

  • Preamble

    I am upgrading one of our web services to use Stored Procedures rather than imbedded SQL in the C# code.

    The DB is SQL Server 2008. The target .NET framework is 2.0 but if any differences using 3.5 I would like to know also.

    We have a helper class (Database.cs) which wraps our database connections. This class has convenience methods (ExecuteReader, ExecuteNonQuery, etc.) which take in an sql string and constructs an OdbcCommand with .


    Question

    Is there much difference between executing a Stored Procedure using CommandType == Text and CommandText == "EXEC SP_NAME @Param = 'value'", and using CommandType == StoredProcedure and CommandText == "SP_NAME"?
    Thursday, August 27, 2009 12:20 AM

Answers

  • As you indicate, there is reduced risk of sql injection.

    But you will also get, for example, type checking of paramters and increased maintainability


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by Yichun_Feng Wednesday, September 2, 2009 1:38 AM
    Thursday, August 27, 2009 7:57 AM

All replies

  • As you indicate, there is reduced risk of sql injection.

    But you will also get, for example, type checking of paramters and increased maintainability


    This posting is provided "AS IS" with no warranties.
    • Marked as answer by Yichun_Feng Wednesday, September 2, 2009 1:38 AM
    Thursday, August 27, 2009 7:57 AM
  • Yes, there is a difference. Using StoredProcedure is faster. Using a Parameter instead of a literal value is also faster.

    Here are two are great blog posts about the difference

    http://rhartskeerl.wordpress.com/2009/06/01/commandtype-text-and-commandtype-storedprocedure/

    http://sqlblog.com/blogs/tibor_karaszi/archive/2010/01/11/is-there-and-overhead-to-rpc-events.aspx

    Wednesday, July 30, 2014 5:46 PM