Best Practice for using Stored Procedures in asp.net for inserting Data

Question

I was wondering if anyone can give me a link on following a best practice pattern for inserting data in a SQL db using stored procedures. I mean, what procedures should we take to ensure validated data to be passed through stored procedures.

Answer 1

There are many different ways to do this depending on if you are using an ORM or using direct database access. One thing is you should use the SqlParameter object to create SP parameters. There are a wide variety of ways to create a SqlParameter with various options but the simplest form is

 SqlParameter[] prms = new SqlParameter[1];
 prms[0] = new SqlParameter("@userid", "Bob");

Answer 2

I would recommend that you embed any logic for validation at a layer above the stored procedure implementation. In this way you can have much more flexibility in your implementation and exposure. A simple example is that you can reuse a validation library across multiple projects. Most ORM libraries support validation in its simplest form by verifying appropriate type assignment at compile time.

I would also recommend that you implement your transactional logic in a layer above the stored procedures.