How can I save information to my database when information is to be saved in different tables?

Question

I have to save the following variables to a database:

        string PoliciaID = Session["username"];
        string PlacaAuto = Page.Request.QueryString["placaauto"];
        string Carnet = Page.Request.QueryString["carnet"];
        string Fecha = DateTime.Now;
        string TipoInfraccion = Page.Request.QueryString["tipo"];

PoliciaID is the primary key of another table called Policia and Carnet is primary key of a table called Persona.

How could I save information in this situation using C#?

Thank you for your time!

Answer 1

Two INSERT or UPDATE commands, one per table, executed as a single unit of work in a transaction context. Either both should succeed or both should fail and rollback.

This might help you learn about transactions.

Answer 2

As @duffymo said

DECLARE @FirstTab TABLE (Col1 INT, Col2 INT)
DECLARE @SecondTab TABLE (Col3 INT, Col4 INT)

BEGIN TRANSACTION
BEGIN TRY
    INSERT INTO @FirstTab ( Col1, Col2 ) VALUES ( 1,2 )
    INSERT INTO @SecondTab ( Col3, Col4 ) VALUES ( 3,4 )
    COMMIT TRANSACTION 
END TRY 
BEGIN CATCH 
    ROLLBACK TRANSACTION
END CATCH 

(you'll probably also want to add in some mechanism to communicate to the caller whether it succeeded (and committed the changes) or failed (rolled back) )

So the idea here is that the two inserts will both happen, or neither. If there's an exception thrown along the way, it'll jump into the catch block to roll back anything that's already been done. But normally it'll just do the two INSERTS, commit the work, and continue on its way.