why we use stored procedure in c# .net application
ADO.NET consists of a set of Classes that interact to provide the required functionality. Stored procedures (sprocs) are generally an ordered series of Transact-SQL statements bundled into a single logical unit. They allow for variables and parameters, as well as selection and looping constructs. A key point is that sprocs are stored in the database rather than in a separate file.
You normally write SQL statements, like select, inserts, updates to access your data from database. If you find yourself using the same query over and over again, it would make sense to put it into a stored procedure.
Every time you write a query it is parsed in database. If you have written a stored procedure for it, it will be parsed once and can be executed N number of times.
Stored procedures can also improve performance. All the conditional logic and is written into a stored procedure which is a single execution block on the database server.
It is pre compiled entity i.e. it is compiled at once and can be used again and again. Stored procedures provide faster code execution and reduce network traffic. Faster execution: Stored procedures are parsed and optimised as soon as they are created and the stored procedure is stored in memory.
Faster execution: Stored procedures are parsed and optimised as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimise your SQL code every time it run
Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.
Users can execute a stored procedure without needing to execute any of the statements directly.
Stored procedure can provide advanced database functionality for users who wouldn't normally have access to these tasks, but this functionality is made available in a tightly controlled way.
Advantages of Using Stored Procedures
The following are not all inclusive reasons in regards to advantages of using Stored Procedures.
These advantages are subjective to developers, database administrators, business and/or security requirements.
- Maintainability: Because scripts are in a single location updates and tracking of dependencies based on schema changes become easier.
- Testing: Can be tested independent of an application.
- Isolation of Business Rules: Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application.
- Speed/Optimization: Stored Procedures are cached on the server. The first execution may take longer while later calls will be shorter. This means the query is optimized along with proper indexing server side.
- Security:
- Limit direct access to tables via defined roles in the database
- Securing just the data and the code that accesses it is easier than applying that security within the application code itself
Disadvantages of Using Stored Procedures
- Someone must keep track of changes between database environments, make a test changes in development environment and promote, then test in both test and production environments.
- Testing:
- Stored Procedures tend to be utilized on more than one application. This means there are chances all affected applications do not get tested which may lead to unexpected results.
- Any data errors in handling Stored Procedures are not generated until runtime
- Security: When done for the wrong reasons can lead to longer time to work with a specific Stored Procedure where time will vary dependent on the process to gain access to a Stored Procedure followed by time to work on the SQL.
- Stored procedure code is not as robust as application code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)
Alternatives to Stored Procedures
- Conventional inline queries using SqlClient data provider utilizing parameters rather than string concatenation when creating SQL statements.
- Entity Framework classic
Click Here for More Details