SQL to Insert data into multiple tables from one POST in WebMatrix Razor Syntax

Question

I've got two form fields from which the user submits a 'category' and an 'item'.

The following code inserts the category fine (I modified it from the WebMatrix intro PDF) but I've no idea how to then insert the 'item' into the Items table. I'll also need to add the Id of the new category to the new item row.

This is the code that's working so far

@{ var db = Database.OpenFile("StarterSite.sdf");
var Category = Request["Category"]; //was name
var Item = Request["Item"]; //was description
if (IsPost) {
// Read product name.
Category = Request["Category"];
if (Category.IsEmpty()) {
Validation.AddFieldError("Category", "Category is required");
}
// Read product description.
Item = Request["Item"];
if (Item.IsEmpty()) {
Validation.AddFieldError("Item",
"Item type is required.");
}
// Define the insert query. The values to assign to the
// columns in the Products table are defined as parameters
// with the VALUES keyword.
if(Validation.Success) {
var insertQuery = "INSERT INTO Category (CategoryName) " +
"VALUES (@0)";
db.Execute(insertQuery, Category);
// Display the page that lists products.
Response.Redirect(@Href("~/success"));
}
}
}

I'm guessing/hoping this is a very easy question to answer so hopefully there isn't much more detail required - but please let me know if there is. Thanks.

Answer 1

There's a Database.GetLastInsertId method within WebMatrix which returns the id of the last inserted record (assuming it's an IDENTITY column you are using). Use that:

db.Execute(insertQuery, Category);
var id = (int)db.GetLastInsertId(); //id is the new CategoryId
db.Execute(secondInsertQuery, param1, id);