Conditional Insert?

This is a kind of follow-up to my other question: Delimited string of ids as a field or a separate table? I have a database containing a table of products, one of categories, and o

Solution 1:

I worked out a way to put a kind of conditional in the insert-query. This is how:

INSERTINTO productsInCategories (categoryId, productId)
SELECT id,@0FROM Categories WHERE userId=@1AND id=@2@0=Lastinsert id (the product inserted prior to this query)
@1=The user id
@2=The category id supplied by the client

If the client supplies a spooked category-id then no record will be found by the select statement(unless that category is owned by the same user, which in case we don't care about) and nothing will be inserted. Then we'll just check how many rows were inserted(the execute-function of WebMatrix.Data.Database which I use returns the count of records affected as an int which will be 0 then) and if none then roll back.

The INSERT with the nested SELECT looks funny, at least to me. With no "Values()" and the second unrelated parameter is in the middle of it. I haven't really looked up anything about that and don't have any facts but I think that's because the select-query basically returns a Values()-object so we'll have to omit it, and that's why we can't put additional parameters after the select-query too. Instead we pass them to the select-query as constants so it'll just put them in the Values-object it returns.

Solution 2:

Generally you always validate data coming from a client before storing into the database. Only after the data is checked and found correct you do the database operation. In this case you have two inserts. So you have to do it in one transaction with a commit statement at the end. I must admit I am not familiar with sql-server-ce but I am sure that it has a transaction commit/rollback feature as any other sql database.