c# - Insert using Scope Identity as Attribute -

when creating user, need give system role, wondering best way that.

i have working solution, i'm unsure if it's best way.

using (sqlconnection con = new sqlconnection(constring)) {     con.open();      using (sqlcommand cmd = new sqlcommand(@"insert users (name, email, username, password, active) values (@name, @email, @username, @password, @active); select scope_identity();", con))     {         try         {             cmd.parameters.addwithvalue("@name", user._name);             cmd.parameters.addwithvalue("@email", user._email);             cmd.parameters.addwithvalue("@username", user._email);             cmd.parameters.addwithvalue("@password", user.password);             cmd.parameters.addwithvalue("@active", 1);              user_id = convert.toint32(cmd.executescalar());             //cmd.executenonquery();         }         catch (sqlexception)         {                                //handle exception         }     } }  using (sqlconnection con = new sqlconnection(constring)) {     using (sqlcommand cmd = new sqlcommand("insert user_role (user_role_user_id, user_role_role_id) values (@user_id, @role_id)", con))     {         try         {             cmd.parameters.addwithvalue("@user_id", user_id);             cmd.parameters.addwithvalue("@role_id", user.role_id);              cmd.executenonquery();         }         catch (sqlexception)         {             //handle exception         }      } } 

now problem is, if adding role goes wrong whatever reason, i'll have user created without role.

so wondering if it's possible join 2 sqlcommand, considering need scope_identity insert user_role. or rollback on last exception catch both insert's?

either make stored procedure work atomic unit, or if unable that, can wrap entire code block in transaction if yo keep using same connection, this:

using (sqlconnection con = new sqlconnection(constring)) {     con.open();     var transaction = con.begintransaction();      try     {         //run first command          //run second command          //if have succeeded, commit transaction         transaction.commit();     }     catch()     {         //something went wrong, roll         transaction.rollback();     } }