none
CommandType text or storedprocedure

    Question

  • Hi

    In my Windows Form application I may need to create a simple table in an existing SQL database if it does not already exist.

    That table will hold a single record only

    Once that record has been created it will only rarely need to be updated. I will only need to read the record as a simple "select *" without a need to set relationship to other tables in the database

    I plan to use ADO.NET to read and write data to the table.

    As I need to create this table from within my application and it will only contain a single record and be rarely modified I am considering using CommandType of text and passing the query as a CommandText from my application ("select * from ...", "update..." etc). In addition to the size of the data another reasoning for doing this is it will also removes the need to create stored procedures in the database for the select/insert/update.

    Any thoughts as to why this may not be a good idea and why I should look to create stored procedures?

    Many thanks in advance

    Friday, March 21, 2014 4:58 PM

Answers

  • I will recomend you to use SP in case if you are using DML commands together or batch and if it contain logical statements associated with the batch. The another advantages of SP is that it supports parameters and can be called multiple time from the application by varying the parameters and processing or executing the same steps repetatively. It sometime makes life easier to manage, processing speed, precise, code reusability, transactions etc.


    Regards, RSingh

    • Marked as answer by bob132 Monday, March 24, 2014 3:52 PM
    Friday, March 21, 2014 5:21 PM
  • Any thoughts as to why this may not be a good idea and why I should look to create stored procedures?

    Below are some of the benefits of using stored procedures.  Not all apply to your trivial case, except perhaps security.

    • Well-defined database interface: The underlying database schema can be changed and refactored without application code changes.
    • Security: Only execute permissions on the stored procedure is needed.  Permissions on the indirectly referenced objects are not needed, assuming the ownership chain is unbroken.  SQL injection is not possible with CommandType.StoredProcedure, assuming no dynamic SQL in the proc.
    • Performance: Avoids the performance impact of improperly typed parameters in application code and improves the likelihood of execute plan reuse.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by bob132 Monday, March 24, 2014 3:52 PM
    Saturday, March 22, 2014 6:52 PM
    Moderator

All replies

  • I will recomend you to use SP in case if you are using DML commands together or batch and if it contain logical statements associated with the batch. The another advantages of SP is that it supports parameters and can be called multiple time from the application by varying the parameters and processing or executing the same steps repetatively. It sometime makes life easier to manage, processing speed, precise, code reusability, transactions etc.


    Regards, RSingh

    • Marked as answer by bob132 Monday, March 24, 2014 3:52 PM
    Friday, March 21, 2014 5:21 PM
  • Any thoughts as to why this may not be a good idea and why I should look to create stored procedures?

    Below are some of the benefits of using stored procedures.  Not all apply to your trivial case, except perhaps security.

    • Well-defined database interface: The underlying database schema can be changed and refactored without application code changes.
    • Security: Only execute permissions on the stored procedure is needed.  Permissions on the indirectly referenced objects are not needed, assuming the ownership chain is unbroken.  SQL injection is not possible with CommandType.StoredProcedure, assuming no dynamic SQL in the proc.
    • Performance: Avoids the performance impact of improperly typed parameters in application code and improves the likelihood of execute plan reuse.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by bob132 Monday, March 24, 2014 3:52 PM
    Saturday, March 22, 2014 6:52 PM
    Moderator