Is there a proper way to use Sql Helper class for Parent-Child tables

Question

The following code works fine for a single sql statement, what if I need to handle parent-child tables, for example,

product table
product details table

please help ~

public static void ExecuteNonQuery(string commandText)
{
using (SqlCommand command = new SqlCommand())
{
// Set up the connection
SqlConnection connection = DBConnection.SqlConn;
command.Connection = connection;

            // Start a local transaction
            SqlTransaction transaction = connection.BeginTransaction();
            command.Transaction = transaction;

            try
            {
                command.CommandType = CommandType.Text;
                command.CommandText = commandText;
                command.ExecuteNonQuery();

                transaction.Commit();
            }
            catch (SqlException e)
            {
                transaction.Rollback();
                MessageBox.Show(e.ToString());
            }
        }
    }

Answer 1

We can add an output clause in the first sql statement.

Then use ExecuteScalar to get its id, and then use it for the second SQL statement.

Please try if this can meet your needs:

            string connString = @"";  
            using (SqlConnection connection = new SqlConnection(connString))  
            {  
                connection.Open();  
                using (SqlCommand command = new SqlCommand("insert into ScoreTable OUTPUT Inserted.ID values(4,'Tom',84,54,65)", connection))  
                {  
                    SqlTransaction transaction = connection.BeginTransaction();  
                    command.Transaction = transaction;  
  
                    try  
                    {  
                        int insertedRowID = (int)command.ExecuteScalar();  
                        command.CommandText = "update ScoreTable set Name = @name where id = @id";  
                        command.Parameters.Add("@name", System.Data.SqlDbType.NVarChar).Value = "UpdatedName";  
                        command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = insertedRowID;  
                        command.ExecuteNonQuery();  
                        transaction.Commit();  
                    }  
                    catch (Exception ex)   
                    {  
                        Console.WriteLine(ex.Message);  
                        transaction.Rollback();  
                    }  
                          
                }  
            }  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

oh yes, I think it works, but I have to create another method to handle parent-child tables, right?

--- Method 1 (for typical sql statements (insert, delete, update) ---
try
{
......
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.ExecuteNonQuery();
transaction.Commit();
}

--- Method 2 (for parent-child sql statements) ---
try
{
int insertedRowID = (int)command.ExecuteScalar();
......
command.CommandType = CommandType.Text;
command.CommandText = commandText;
command.ExecuteNonQuery();
transaction.Commit();
}

it also looks like it's impossible to handle parent-child sql statements (due to insertedRowID) by the Method 1, right? : )

Answer 3

yup, got it, thx a lot !