Working with stored procedures
Stored procedure is a native way of interacting with most relational databases. Lot of legacy systems I have worked on used stored procedures extensively. On the application side, we would use ADO.NET to prepare SQL commands that execute stored procedures and return result sets. ADO.NET is well capable of executing any valid SQL that you send down the wire. So instead of sending commands to execute stored procedures, you can dynamically build a SQL and execute it. That is what NHibernate does. But most DBAs that I have come across prefer stored procedures. There were two main reasons for this preferred choice. They are as follows:
- Stored procedures let DBAs control access to databases in a better way. Instead of letting application users write over all database tables, and letting rogue users do anything with your data under the context of an application user, you can limit the access to only stored procedures and thus control the damage. If stored procedures...
