Get source of an call function, invocation or task execution

Question

In my app, I use an class (MySqlClass) to execute and download data from my database.

public class MySqlClass
{
    public void ExecuteQuery(string query) { /* ... */ }

    public DataSet GetDataSet(string query) { /* ... */ }

    public void Transaction(Action queryToCommit, Action whenRollback) { /* ... */ }
}

For example :

public class MyApp
{
    List<MyObjectClass> myList = MyObjectClass.GetMyObjectClass("white");
}

public class MyObjectClass
{
    private static MySqlClass sqlConn = new MySqlClass();

    public static List<MyObjectClass> GetMyObjectClass(string color)
    {
        List<MyObjectClass> obj = new List<MyObjectClass>();

        using (DataSet ds = sqlConn.GetDataSet(" ... my query ... "))
        {
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                obj.Add(ConvertDataRow(dr));
            }
        }

        return obj;
    }

    public static MyObjectClass ConvertDataRow(DataRow dr) { /* ... */ }
}

For some criticals utilizations, I want use transaction WITHOUT modify originals functions. I developed for this "Transaction" function in MySqlClass :

public void Transaction(Action queryToCommit, Action whenRollback)
{
    CancellationTokenSource cts = new CancellationTokenSource();
    try
    {
        Task executeAction = new Task(new Action(() =>
            {
                queryToCommit.Invoke(); // user code executed here
                cts.Token.ThrowIfCancellationRequested();
            }),
            cts.Token
            );

        executeAction.Start();
        executeAction.Wait(cts.Token);

        Commit();
    }
    catch (Exception)
    {
        Rollback();

        whenRollback.Invoke();
        /* ... */
    }
}

In my app :

public class MyApp
{
    MySqlClass sqlConn = new MySqlClass();
    List<MyObjectClass> myList = MyObjectClass.GetMyObjectClass("white");

    private void WithoutTransaction()
    {
        MyObjectClass objA = new MyObjectClass();
        MyObjectClass objB = new MyObjectClass();

        /* ... */

        if (...)
        {
            objA.Insert();
        }
        else
        {
            objA.Delete();
            objB.Update();

            // Exception raised in objB.Update();
            // Data correspond to objA in database are lost
        }
    }

    private void WithTransaction()
    {
        MyObjectClass objA = new MyObjectClass();
        MyObjectClass objB = new MyObjectClass();

        sqlConn.Transaction(
            () => {
                if (...)
                {
                    objA.Insert();
                }
                else
                {
                    objA.Delete();
                    objB.Update(); // Exception raised in objB.Update()
                }
            },
            CallErrorLogFunction()
        );
    }
}

---

My problem : How i can know, when I use GetDataSet(), ExecuteQuery() or whatever, that I'm in a transaction function ?

• StackTrace and StackFrame indicate invocation but not the source function of invocation.
• I can't use Thread (for current thread ID) because my colleagues could make operations on the GUI.
• I thought to lock instruction to check if in a transaction but I no idea to implement the code

If you have an idea ... :)

Thanks !

---

EDIT March 19'21 :

public class MySqlClass
{
    // They are initialized before use "ExecuteQuery()"
    public MyTransactionClass transac; 
    public SqlConnection conn;

    public void ExecuteQuery(string query, Dictionary<string, object> dict = null)
    {
        SqlDataAdapter da;

        try
        {
            if (conn.State = ConnectionState.Closed)
            {
                conn.Open();
            }

            da = new SqlDataAdapter(query, conn);

            /* section of code for parameters queries */
            if (dict != null) { /* ... */ }

            if (IsTransaction())
            {
                da.SelectCommand.Transaction = transac.TransacSql;
            }

            da.SelectCommand.ExecuteNonQuery();
        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (da != null)
            {
                da.Dispose();
            }
        }
    }

    public DataSet GetDataSet(string query) { /* ... */ }

    private bool IsTransaction()
    {
        bool r = false;

        if (transac != null)
        {
            /*
             * Check if in StackTrace, "Transaction" of "MyTransactionClass" is in the list
             */
            StackTrace st = new StackTrace(true);
            StackFrame sf;

            for (int i = 0; i < st.FrameCount; i++)
            {
                sf = st.GetFrame(i);

                if (sf.GetMethod().Name == MyTransactionClass.FunctionName_Transaction)
                {
                    r = true;
                    break;
                }
            }
        }

        return r;
    }
}

public class MyTransactionClass
{
    public static readonly string FunctionName_Transaction = nameof(Transaction);
    public SqlTransaction TransacSql;

    public void Transaction(Action queryToCommit, Action whenRollback)
    {
        CancellationTokenSource cts = new CancellationTokenSource();
        try
        {
            Task executeAction = new Task(new Action(() =>
            {
                queryToCommit.Invoke();
                cts.Token.ThrowIfCancellationRequested();
            }),
                cts.Token
                );

            executeAction.Start();
            executeAction.Wait(cts.Token);

            Commit();
        }
        catch (Exception)
        {
            Rollback();
            whenRollback.Invoke();

            /* ... */
        }
    }

    void Commit() { }
    void Rollback() { }
}

---

EDIT March 31'21 :

I make a unit test, with different use cases :

• with just a transaction
• a transaction and a Action
• a transaction with a Action in a Task

Is the last case (similar to I want), System.Transactions.Transaction.Current is null.

[TestMethod]
public void TestTransactionScope()
{
    Console.WriteLine($"Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");

    using (TransactionScope scope = new TransactionScope())
    {
        Console.WriteLine($"(scope) Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
        MyFunction("direct call");
    }

    // if I use a invocation
    Action<string> fnctTransac = new Action<string>((msg) => MyFunction(msg));
    using (TransactionScope scope = new TransactionScope())
    {
        Console.WriteLine($"(scope) Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
        fnctTransac.Invoke("with invocation");
    }

    // if I use invocation with a Task, similar to my use
    using (TransactionScope scope = new TransactionScope())
    {
        Console.WriteLine($"(scope) Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
        Task t = new Task(() => fnctTransac.Invoke("with invocation, from a Task"));
        t.Start();
        Task.WaitAll(t);
    }
}

public void MyFunction(string msg)
{
    Console.WriteLine($"{msg} - Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
}

Logs :

Transaction ID : 
(scope) Transaction ID : 1c0127fe-44d5-4954-826c-ece6ad261ee5:1
direct call - Transaction ID : 1c0127fe-44d5-4954-826c-ece6ad261ee5:1
(scope) Transaction ID : 1c0127fe-44d5-4954-826c-ece6ad261ee5:2
with invocation - Transaction ID : 1c0127fe-44d5-4954-826c-ece6ad261ee5:2
(scope) Transaction ID : 1c0127fe-44d5-4954-826c-ece6ad261ee5:3
with invocation, from a Task - Transaction ID : 

---

EDIT April 16'21

With TransactionScope :

Source :

public class MySqlClass
{
    public MyTransactionClass transac; 
    public SqlConnection conn;

    public void ExecuteQuery(string query, Dictionary<string, object> dict = null)  { /* ... */ }

    public DataSet GetDataSet(string query) { /* ... */ }

    private bool IsTransaction()
    {
        bool r = false;
        string idTransaction = Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier

        if (
            transac == null &&
            !string.IsNullOrEmpty(idTransaction)
            )
        {
            transac = MyTransactionClass.GetTransaction(idTransaction)
        }

        return r;
    }
}

public class MyTransactionClass
{
    public const int MAX_TIME_TRANSAC_SEC = 5
    
    public static readonly string FunctionName_Transaction = nameof(Transaction);
    public SqlTransaction TransacSql;

    public static void Transaction(Action queryToCommit, Action whenRollback)
    {
        CancellationTokenSource cts = new CancellationTokenSource();
        try
        {
            Task executeAction = new Task(new Action(() =>
            {
                queryToCommit.Invoke();
                cts.Token.ThrowIfCancellationRequested();
            }),
                cts.Token
                );
                
            using (TransactionScope scope = new TransactionScope(
                TransactionScopeOption.Required,
                New TimeSpan(0, 0, MAX_TIME_TRANSAC_SEC),
                TransactionScopeAsyncFlowOption.Enabled
                )
            {
                executeAction.Start();
                executeAction.Wait(cts.Token);
                
                scope.Complete();
                Commit();
            }
        }
        catch (Exception)
        {
            Rollback();
            whenRollback.Invoke();

            /* ... */
        }
    }

    void Commit() { }
    void Rollback() { }
}

Tests :

[TestMethod]
public void E_TransactionScope()
{
    Console.WriteLine($"Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");

    using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        Console.WriteLine($"(scope) Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
        MyFunction("direct call");
    }

    Console.WriteLine();

    // if I use a invocation
    Action<string> fnctTransac = new Action<string>((msg) => MyFunction(msg));
    using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        Console.WriteLine($"(scope) Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
        fnctTransac.Invoke("with invocation");
    }

    Console.WriteLine();

    // if I use invocation with a Task, similar to my use
    using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        Console.WriteLine($"(scope) Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
        Task t = new Task(() => fnctTransac.Invoke("with invocation, from a Task"));
        t.Start();
        Task.WaitAll(t);
    }

    Console.WriteLine();

    // ultimate use case
    Action userCode = () => MyFunction_First("last use case");
    Task tk;
    using (TransactionScope scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
    {
        Console.WriteLine($"(scope) Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");

        // TransactionID not available when userCode is defined out of using instruction and called directly
        // userCode.Start();

        tk = new Task(new Action(() => userCode.Invoke()));
        tk.Start();
        tk.Wait();
    }

    Console.WriteLine("-------------------------");
}

public void MyFunction(string msg)
{
    Console.WriteLine($"{msg} - Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
}

public void MyFunction_First(string msg)
{
    Console.WriteLine($"MyFunction_First - {msg} - Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
    Task t = new Task(() => MyFunction_Second(msg));
    t.Start();
    Task.WaitAll(t);
}

public void MyFunction_Second(string msg)
{
    Console.WriteLine($"MyFunction_Second - {msg} - Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
    Thread th = new Thread(new ThreadStart(() => MyFunction_Third(msg)));
    th.Start();
    th.Join();
}

public void MyFunction_Third(string msg)
{
    using (TransactionScope scope = new TransactionScope())
    {
        Console.WriteLine($"MyFunction_Third - {msg} - Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
        Task t = new Task(() => MyFunction_Fourth(msg));
        t.Start();
        Task.WaitAll(t);
    }
}

public void MyFunction_Fourth(string msg)
{
    Console.WriteLine($"MyFunction_Fourth - {msg} - Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
    Task t = new Task(() => MyFunction_Last(msg));
    t.Start();
    Task.WaitAll(t);
}

public void MyFunction_Last(string msg)
{
    Console.WriteLine($"MyFunction_Last - {msg} - Transaction ID : {System.Transactions.Transaction.Current?.TransactionInformation.LocalIdentifier}");
}

Logs :

Transaction ID : 
(scope) Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:1
direct call - Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:1

(scope) Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:2
with invocation - Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:2

(scope) Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:3
with invocation, from a Task - Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:3

(scope) Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:4
MyFunction_First - last use case - Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:4
MyFunction_Second - last use case - Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:4
MyFunction_Third - last use case - Transaction ID : 99e1d658-27d8-404d-b57e-d6ead2e2308e:4
MyFunction_Fourth - last use case - Transaction ID : 
MyFunction_Last - last use case - Transaction ID : 

Answer 1

Have you already considered using the TransactionScope class?

I'm not sure, but with that, you might not need to know whether you are in a transaction or not inside your ExecuteQuery and GetDataSet methods. It might just simply work. ;)

Your Transaction method could look something like this:

public void Transaction(Action queryToCommit, Action whenRollback = null)
{
    if (queryToCommit == null)
    {
        throw new ArgumentNullException(nameof(queryToCommit));
    }

    CancellationTokenSource cts = new CancellationTokenSource();
    Task executeAction = new Task(new Action(() =>
        {
            queryToCommit.Invoke(); // user code executed here
            cts.Token.ThrowIfCancellationRequested();
        }),
        cts.Token
        );

    try
    {
        using (var scope = new System.Transactions.TransactionScope())
        {
            executeAction.Start();
            executeAction.Wait(cts.Token);

            scope.Complete();
        }
    }
    catch (Exception)
    {
        whenRollback.Invoke();
        /* ... */
    }
}

Note that I haven't tested this. I just based it on your code and the sample in the documentation, so it probably needs some additional work.

Edit:

Based on recent comments, I added a code snippet that shows how I would personally implement your logic:

public class MySqlClass
{
    private string connectionString;

    public MySqlClass(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public int ExecuteQuery(string query, params SqlParameter[] parameters)
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            cmd.Parameters.AddRange(parameters);

            return cmd.ExecuteNonQuery();
        }
    }

    public DataSet GetDataSet(string query, params SqlParameter[] parameters)
    {
        using (SqlConnection con = new SqlConnection(connectionString))
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            con.Open();
            cmd.Parameters.AddRange(parameters);

            DataSet ds = new DataSet();

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(ds);
            }

            return ds;
        }
    }

    public DataTable GetDataTable(string query, params SqlParameter[] parameters)
    {
        DataSet ds = GetDataSet(query, parameters);
        return ds.Tables[0];
    }

    public static void Transaction(Action queryToCommit, Action whenRollback)
    {
        if (queryToCommit == null)
        {
            throw new ArgumentNullException(nameof(queryToCommit));
        }

        CancellationTokenSource cts = new CancellationTokenSource();
        Task executeAction = new Task(new Action(() =>
            {
                queryToCommit.Invoke(); // user code executed here
                cts.Token.ThrowIfCancellationRequested();
            }),
            cts.Token
            );

        try
        {
            using (var scope = new System.Transactions.TransactionScope())
            {
                executeAction.Start();
                executeAction.Wait(cts.Token);

                scope.Complete();
            }
        }
        catch
        {
            whenRollback.Invoke();
            /* ... */
        }
    }

    public static SqlParameter CreateParameter(string parameterName, SqlDbType dbType, object value)
    {
        SqlParameter parameter = new SqlParameter(parameterName, dbType);
        parameter.Value = value;
        return parameter;
    }

    public static SqlParameter CreateParameter(string parameterName, SqlDbType dbType, int size, object value)
    {
        SqlParameter parameter = new SqlParameter(parameterName, dbType, size);
        parameter.Value = value;
        return parameter;
    }
}

/*
CREATE TABLE [MyObjects] (
    [Id] INT IDENTITY NOT NULL,
    [Color] VARCHAR(20) NOT NULL,
    --...
    CONSTRAINT [PK_MyObjects] PRIMARY KEY ([Id])
);
*/

public class MyObjectModel
{
    // Sample private fields
    public int? Id { get; set; }
    public string Color { get; set; }
    /* ... */
}

public class MyObjectRepository
{
    private MySqlClass sqlConn;

    public MyObjectRepository(MySqlClass sqlConn)
    {
        this.sqlConn = sqlConn;
    }

    public List<MyObjectModel> GetMyObjects(string colorFilter)
    {
        List<MyObjectModel> obj = new List<MyObjectModel>();

        string query = (string.IsNullOrWhiteSpace(colorFilter))
            ? "SELECT [Id], [Color] FROM [MyObjects];"
            : "SELECT [Id], [Color] FROM [MyObjects] WHERE [Color] LIKE '%' + @Color + '%';";

        using (DataTable dt = sqlConn.GetDataTable(query,
            MySqlClass.CreateParameter("@Color", SqlDbType.VarChar, 20, colorFilter)))
        {
            foreach (DataRow dr in dt.Rows)
            {
                obj.Add(ConvertDataRow(dr));
            }
        }

        return obj;
    }

    private static MyObjectModel ConvertDataRow(DataRow dr)
    {
        return new MyObjectModel
        {
            /* set class properties here... */
            Id = dr.Field<int>("Id"),
            Color = dr.Field<string>("Color")
            /* ... */
        };
    }

    public void Insert(MyObjectModel model)
    {
        if (model.Id.HasValue)
        {
            return;
        }

        using (DataTable dt = sqlConn.GetDataTable("INSERT INTO [MyObjects] ([Color]) OUTPUT INSERTED.[Id] VALUES (@Color);",
            MySqlClass.CreateParameter("@Color", SqlDbType.VarChar, 20, model.Color)))
        {
            model.Id = (int)dt.Rows[0]["Id"];
        }
    }

    public int? Update(MyObjectModel model)
    {
        if (!model.Id.HasValue)
        {
            return null;
        }

        return sqlConn.ExecuteQuery("UPDATE [MyObjects] SET [Color] = @Color WHERE [Id] = @Id;",
            MySqlClass.CreateParameter("@Id", SqlDbType.Int, model.Id),
            MySqlClass.CreateParameter("@Color", SqlDbType.VarChar, 20, model.Color));
    }

    public int Delete(int id)
    {
        return sqlConn.ExecuteQuery("DELETE FROM [MyObjects] WHERE [Id] = @Id;",
            MySqlClass.CreateParameter("@Id", SqlDbType.Int, id));
    }
}

public class MyApp
{
    private MyObjectRepository repo;

    public MyApp()
    {
        MySqlClass sqlConn = new MySqlClass(" ... connection string here ... ");
        repo = new MyObjectRepository(sqlConn);
    }

    List<MyObjectModel> GetMyObjects(string colorFilter = "white")
    {
        return repo.GetMyObjects(colorFilter);
    }

    private void WithoutTransaction()
    {
        MyObjectModel objA = new MyObjectModel();
        MyObjectModel objB = new MyObjectModel();

        /* ... */

        if (true)
        {
            repo.Insert(objA);
        }
        else
        {
            repo.Delete(objA.Id.Value);
            repo.Update(objB);

            // Exception raised in objB.Update();
            // Data correspond to objA in database are lost
        }
    }

    private void WithTransaction()
    {
        MyObjectModel objA = new MyObjectModel();
        MyObjectModel objB = new MyObjectModel();

        MySqlClass.Transaction(
            () =>
            {
                if (true)
                {
                    repo.Insert(objA);
                }
                else
                {
                    repo.Delete(objA.Id.Value);
                    repo.Update(objB); // Exception still raised now???
                }
            },
            CallErrorLogFunction
        );
    }

    private void CallErrorLogFunction()
    {
        /* ... */
    }
}

From recent edits to your question, I am not sure if this would be a valid solution, however. This is the best I could come up with so far. Perhaps you can extract something useful from it anyway. (I haven't tested it, so despite me being careful, the code might contain some stupid mistakes.)