- A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.
- So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.
- You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
- Performance: SQL Server compiles and optimizes the execution plan for the stored procedure once and caches it. This is often faster than sending raw SQL strings from your C# code every time.
- Security:
- Access Control: You can grant a user permission to execute a stored procedure without giving them SELECT or UPDATE access to the underlying tables.
- SQL Injection: Using parameters in stored procedures (like @UserId) automatically handles input sanitization, preventing SQL injection attacks.
- Maintainability: If your database schema changes (e.g., a column name changes), you only need to update the Stored Procedure in the database, rather than finding and updating every SQL string scattered throughout your C# code.
- Network Traffic: Instead of sending a long SQL query string over the network, you only send the name of the procedure and parameters.
- a table that exists temporarily on the database server. They are stored in the system database tempdb and are useful for storing intermediate results during the execution of a complex query or stored procedure.
- Local Temp Tables (#TableName): Visible only to the connection that created them. They are automatically deleted when the connection closes.
- Global Temp Tables (##TableName): Visible to all connections. They are deleted when the last connection referencing them is closed.
- Performance: Instead of calculating the average salary for the department inside a subquery for every single user (which can be computationally expensive), you calculate it once for all departments, store it, index it, and then join it.
- Readability: It breaks a complex logic flow into distinct steps: "First, figure out department averages. Second, get user data and attach those averages."
- An Upsert is a database operation that combines UPdate and inSERT. It ensures that a record exists in the database by updating it if it is already there, or inserting a new one if it is not.
- Idempotency: You can run this procedure multiple times with the same data without creating duplicate rows or causing errors.
- Simplified Application Logic: Your API code (C#) doesn't need to query the database first to see if a user exists before deciding whether to call an Add or Edit method. It just calls Upsert.
- Dynamic parameters is a Dapper class that allows us to pass parameters to our SQL query in a safe way (prevents SQL injection)
- sql injection is a security vulnerability that allows an attacker to execute arbitrary SQL code on the database by manipulating the input parameters. By using parameterized queries (like DynamicParameters in Dapper), we can ensure that user input is treated as data and not executable code, thus preventing SQL injection attacks.