Insertion Using Scope ID As Attribute
On creation, User
I need to give it a system Role
, so I was wondering how best to do this.
I have a working solution, but I'm not sure if this is the best way.
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand(@"INSERT INTO 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 INTO 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 my problem is that if for some reason adding a role goes wrong, I will create a user without a role.
So I was wondering if it is possible to join the two SqlCommand
given what I need Scope_Identity
to insert into User_Role
. Or roll back the last exclusion exception for both inserts?
source to share
Either create a stored procedure that does the job like an atomic block, or if you can't do that, you can wrap the entire block of code in a transaction if you keep using the same connection:
using (SqlConnection con = new SqlConnection(conString))
{
con.Open();
var transaction = con.BeginTransaction();
try
{
//Run first command
//Run second command
//If we have succeeded, commit the transaction
transaction.Commit();
}
catch()
{
//Something went wrong, roll back
transaction.Rollback();
}
}
source to share