In most my applications I have a list of existing objects along with a linkbutton ("Edit this") - and last a button on its own - "Add new". Both should go to the same form (asp.net page) and in that code (when clicking on a "Save" button ) I want to update my underlying database.
If you like me prefer using stored procedures for most your database work (there are pretty good reasons for doing so - security is one (not allowing dynamic SQL string building) and performance another reason.
However SP:s means an extra layer, which needs to developed - and maintained. To mimimize the number of SP:s needing modifications when your database changes I often try to use the same SP for insert and update.
As I in my application sure can tell if we are insering or updating a record one easy way of accomplishing it is just to send -1 or something into the stored procedure as a marker to say "we are currently inserting a record".
Example of SP code
CREATE PROCEDURE cust_SaveCustomer(@id int, @name varchar(50))
AS
if @id=-1
BEGIN
insert into cust_Customer(custname) select @name
select @@identity
END
ELSE
BEGIN
update cust_Customer set custname=@name where id=@id
select @id
END
So calling it like this
cust_SaveCustomer -1,'John Doe'
would create a new customer with customer_name set to 'John Doe'. We also get the new id back to our code by using the select @@identity call in the end.
However when called with
cust_SaveCustomer 1,'John Doe'
the stored procedure will update the customer with id=1 and set the name to John Doe.
