How to write SQL queries in txt file C# transaction

To start with, you'll need some code to format a SqlCommand object to an executable script. The code to do that is quite long, so I've put it on GitHub:

SQL Query Formatter · GitHub[^]

Next, change your addQueryToDb method so that it takes the command object instead of a string. I'd also recommend renaming it to more closely match what it's doing.

public static void LogDatabaseQuery(IDbCommand command)
{
    if (command is null) throw new ArgumentNullException(nameof(command));
    
    const string userName = "work_";
    string logFileName = DateTime.Today.ToString("d_M_yyyy");
    string virtualPath = "~/userWork\\" + userName + "/" + logFileName + ".txt";
    string physicalPath = HostingEnvironment.MapPath(virtualPath);
    
    File.AppendAllText(physicalPath, SqlQueryFormatter.FormatCommand(command));
}

Get rid of your static DBConnection.Connection property. Connection objects should not be shared or stored. Instead, create a new connection object when you require it, and dispose of it as soon as you're finished with it.

public static class DBConnection
{
    public static SqlConnection CreateConnection()
    {
        string connectionString = WebConfigurationManager.ConnectionStrings["yourConnectionStringName"].ConnectionString;
        return new SqlConnection(connectionString);
    }
}

Update your ExecuteNonQuery method to allow you to pass parameters:

public static SqlCommand BuildCommand(SqlCommand command, FormattableString commandText)
{
    if (commandText.ArgumentCount == 0)
    {
        throw new InvalidOperationException("Parameterless command detected: review for SQL Injection.\n" + commandText);
    }
    
    int parameterIndex = 0;
    var parameterNames = new object[commandText.ArgumentCount];
    foreach (object parameter in commandText.GetArguments())
    {
        string parameterName = $"@p{parameterIndex}";
        parameterNames[parameterIndex] = parameterName;

        var p = command.CreateParameter();
        p.ParameterName = parameterName;
        p.Value = parameter;
        command.Parameters.Add(p);

        parameterIndex++;
    }

    command.CommandText = string.Format(CultureInfo.InvariantCulture, commandText.Format, parameterNames);
}

public static int ExecuteNonQuery(FormattableString commandText)  
{
    using (SqlConnection con = DBConnection.CreateConnection())
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = con;
        cmd.CommandTimeout = 0;
        BuildCommand(cmd, commandText);
        
        con.Open();
        int i = cmd.ExecuteNonQuery();
        if (i != 0) LogDatabaseQuery(cmd);
        return i;
    }
}

Usage:

string accId = "1";
string productId = "1";
string memCode = "3";
string memberName = "Prasanta";

DataAccess.ExecuteNonQuery($"insert into dbo.repayment_collection_master (AccId, productId, mem_code, memberName) Values ({accId}, {productId}, {memCode}, {memberName})");

NB: Note the use of FormattableString to convert an interpolated string to a properly parameterized query. If you don't pass it properly, or you don't include any parameters, you will get an exception.

Now, when you execute your commands within a transaction, you can log them:

using (SqlConnection connection = DBConnection.CreateConnection())
{
    connection.Open();
    
    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        var command1 = new SqlCommand("", connection, transaction);
        DataAccess.BuildCommand(command1, $"insert into dbo.repayment_collection_master (AccId, productId, mem_code, memberName) Values ({accId}, {productId}, {memCode}, {memberName})");
        command1.CommandTimeout = 0;
        command1.ExecuteNonQuery();
        
        var command2 = new SqlCommand("SP_Loan_Repayment", connection, transaction);
        command2.CommandType = CommandType.StoredProcedure;
        command2.CommandTimeout = 0;
    
        command2.Parameters.Add("@branchId", SqlDbType.VarChar).Value = branchId;    
        command2.Parameters.Add("@centerId", SqlDbType.VarChar).Value = centerId;    
        command2.Parameters.Add("@centerName", SqlDbType.VarChar).Value = centerName;    
        command2.Parameters.Add("@repayDate", SqlDbType.VarChar).Value = repayDate;    
        command2.Parameters.Add("@repayPrinc", SqlDbType.Decimal).Value = repayPrinc;    
        command2.Parameters.Add("@repayInt", SqlDbType.Decimal).Value = repayInt;    
        command2.Parameters.Add("@transactionId", SqlDbType.VarChar).Value = transactionId;    
        command2.Parameters.Add("@approvedBy", SqlDbType.VarChar).Value = approvedBy;    
    
        SqlParameter voucharReset = command2.Parameters.Add("@VoucharReset", SqlDbType.Int, 2);    
        VoucharReset.Direction = ParameterDirection.Output;    
        SqlParameter returnIndex = command2.Parameters.Add("@ReturnIndex", SqlDbType.Int, 2);    
        ReturnIndex.Direction = ParameterDirection.Output;    
        SqlParameter returnMsg = command2.Parameters.Add("@ReturnMsg", SqlDbType.VarChar, 4000);    
        ReturnMsg.Direction = ParameterDirection.Output;    
        SqlParameter returntransactionId = command2.Parameters.Add("@ReturntransactionId", SqlDbType.VarChar, 10);    
        ReturntransactionId.Direction = ParameterDirection.Output;
        
        command2.ExecuteNonQuery();
        
        transaction.Commit();
        
        DataAccess.LogDatabaseQuery(command1);
        DataAccess.LogDatabaseQuery(command2);
    }
}

NB: To log both commands, you will need to use separate SqlCommand objects. But you shouldn't be reusing them anyway.