Need help to verify the accuracy of Odbc transaction rollback

Question

Hi,

I've 2 tables as following. Parent and Child

1. teststudent (id,firstname, lastname, enrollmentdate)
2. teststudentdetail (teststudentid, remark)

teststudent (id) is auto number and identity

teststudentdetail (teststudentid) is a foreign key teststudent (id)

So far, this is my Odbc transaction rollback. All inserted successfully

public teststudent StudentAdd(teststudent model)
        {           
            using (OdbcConnection conn = new OdbcConnection(_connectionString))
            {
                teststudent response = new teststudent();
                OdbcCommand command = new OdbcCommand();
                OdbcTransaction transaction = null;

                command.Connection = conn;

                try
                {                                     
                    conn.Open();

                    // Start a local transaction
                    transaction = conn.BeginTransaction();

                    // Assign transaction object for a pending local transaction.
                    command.Connection = conn;
                    command.Transaction = transaction;

                    // Execute the commands.
                    string TSQL1 = "INSERT INTO teststudent" +
                              " (firstname, lastname, enrollmentdate)" +
                              " VALUES" +                         
                              " ('" + model.firstname + "', '"
                              + model.lastname + "', '" + model.enrollmentdate.ToString("MM-dd-yyyy").ToString() + "')";

                    command.CommandText = TSQL1;
                    command.ExecuteNonQuery();

                    string TSQL2 = "select dbinfo('sqlca.sqlerrd1') as newid from sysmaster:sysdual";

                    int newid = 0;
                    command.CommandText = TSQL2;
                    OdbcDataReader reader;
                    reader = command.ExecuteReader();
                    if (reader.Read())
                    {
                        newid = Convert.ToInt32(reader["newid"]);
                    }

                    // Always call Close when done reading.
                    reader.Close();


                    // Execute the commands.

                    string TSQL3 = "INSERT INTO teststudentdetail" +
                             " (teststudentid, remark)" +
                             " VALUES" +
                             " (" + newid + ", '" + model.remark + "')";

                    command.CommandText = TSQL3;
                    command.ExecuteNonQuery();

                    // Commit the transaction.
                    transaction.Commit();

                    response.firstname = "0"; // No Error

                }
                catch (Exception ex)
                {
                    response.firstname = ex.Message;
                    try
                    {
                        // Attempt to roll back the transaction.
                        transaction.Rollback();
                    }
                    catch
                    {
                        // Do nothing here; transaction is not active.
                    }

                }


                return response;

            }
        }

Is my code accurate, correct and best practice ?

Please help

Answer 1

Not a best practice for sure. First of all, you need to use parameters when inserting values. Secondly, I'm wondering if you can send all statements as one command. I did a quick google search in regards to Oracle identity columns, this is what I found https://stackoverflow.com/questions/34811283/retrieve-oracle-last-inserted-identity I like the solution which uses RETURNING keyword.