C# method with Procedure name and other parameters

Question

I will be as soon as possible in my question which is the following: I am working on a scheme where my C# program will list the names of the procedures and their respective parameters that were executed during the process and will be saved using the log4net in a table. Well, I created a method in the class RequisicaoDTO that will keep the name of the trial, but I am trying to pass inside this method the for with the values of the parameters, because this procedure I will put in other places too, follows below everything I did:

Class:

public class RequisicaoDTO
{
    public List<string> procedures = new List<string>();

    public string ProcedureAuxiliar { get; set; }
        
    public List<string> ProceduresAdd(string comando)
    {
        procedures.Add(comando);

        return procedures;
    }
}

First method the program executes the information:

public long GerarNumeracaoPI(int tipo, DTO.RequisicaoDTO requisicao)
        {
            var numeroPI = new long();

            //System.Data.SqlClient.SqlConnectionStringBuilder builder = new System.Data.SqlClient.SqlConnectionStringBuilder();

            //string server = builder.DataSource;
            //string database = builder.InitialCatalog;

            using (var scope = new System.Transactions.TransactionScope())
            {
                using (var comando = db.GetStoredProcCommand("dbo.PR_P_GERA_PROXIMO_NUMERO_PI"))
                {
                    db.AddInParameter(comando, "@TIP_PI", System.Data.DbType.String, tipo);
                    db.AddOutParameter(comando, "@PROXIMO_NUMERO", System.Data.DbType.Decimal, 10);
                    db.AddOutParameter(comando, "@CD_ERRO", System.Data.DbType.Decimal, 10);
                    db.AddOutParameter(comando, "@DS_ERRO", System.Data.DbType.String, 2060);

                    db.ExecuteScalar(comando);
                    
                    var auxNomeBase = comando.Connection.Database;
                    
                    requisicao.ProceduresAdd(comando.CommandText);

                    requisicao.ProcedureAuxiliar = $"Procedure: {comando.CommandText}" +
                                                  $"\nParâmetros: ";

                    for (int i = 0; i < comando.Parameters.Count; i++)
                    {
                        requisicao.ProcedureAuxiliar += $"\nNome:{comando.Parameters[i].ParameterName}, Tipo: {comando.Parameters[i].DbType}, Valor: {comando.Parameters[i].Value.ToString()}, Direção: {comando.Parameters[i].Direction}";
                    }

                    requisicao.procedures.Add(requisicao.ProcedureAuxiliar);

                    if (Convert.ToInt16(db.GetParameterValue(comando, "@CD_ERRO")) != 0)
                    {
                        throw new Exception($"Não fois possível obter número de apólice/endosso: {db.GetParameterValue(comando, "@DS_ERRO").ToString()} ");
                    }

                    numeroPI = Convert.ToInt64(db.GetParameterValue(comando, "@PROXIMO_NUMERO"));

                };

                scope.Complete();
            };

            return numeroPI;
        }

This is the for which reads the 4 parameters of proc and forms the result by concatenating the names:

for (int i = 0; i < comando.Parameters.Count; i++)
{
    requisicao.ProcedureAuxiliar += $"\nNome:{comando.Parameters[i].ParameterName}, Tipo: {comando.Parameters[i].DbType}, Valor: {comando.Parameters[i].Value.ToString()}, Direção: {comando.Parameters[i].Direction}";
}

In this my method I pass to requisicao.ProceduresAdd(comando.CommandText); only the name of the previous in the class method, would there be a way to access the previous files with C# in my class within the method or to pass everything at once? So I would only call my method in all the places they call procs, in public List<string> ProceduresAdd(string comando) I already traded string command by Object command, when debugging shows the objects but did not scroll access the information of the Parameters.

Result would look like this in my method:

Procedure: dbo.PR_P_GERA_PROXIMO_NUMERO_PI
Parâmetros: 
Nome:@TIP_PI, Tipo: String, Valor: 2, Direção: Input
Nome:@PROXIMO_NUMERO, Tipo: Decimal, Valor: 2120000110, Direção: Output
Nome:@CD_ERRO, Tipo: Decimal, Valor: 0, Direção: Output
Nome:@DS_ERRO, Tipo: String, Valor: , Direção: Output

Thanks guys, I hope I’ve been clear, a hug and I appreciate any help.

Answer 1

If I understand the problem correctly, you need access to the name and parameters of a Stored Procedure in different places. I believe that the simplest solution would be to pass the variable comando for the method (receiving a Idbcommand in it):

public List<string> ProceduresAdd(IDbCommand comando) 
{
    // comando.CommandText...
    for (int i = 0; i < comando.Parameters.Count; i++)
    {
        /* ... */
    } 
}

This being the case, from the implementation you receive in your method, you will have access both to the command name with comando.CommandText as to their parameters with comando.Parameters.

Answer 2

Thanks Ruan for the support, you were right, just call even by parameter the Dbcommand, was a great learning, follows below as was my method:

public void ProceduresAdd(DbCommand comando, SqlDatabase database)
    {
            var auxAgrupamento = new StringBuilder();

        
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(database.ConnectionString);

            auxAgrupamento.AppendLine($" Procedure: {comando.CommandText} - ")
                          .AppendLine($" Nome da Base: {builder.InitialCatalog} - ")
                          .AppendLine($" Parâmetros: ");

            for (int i = 0; i < comando.Parameters.Count; i++)
            {
                auxAgrupamento.AppendLine($"Nome:{comando.Parameters[i].ParameterName}")
                              .AppendLine($", Tipo: {comando.Parameters[i].DbType}")
                              .AppendLine($", Valor: {comando.Parameters[i].Value.ConvertToString()}")
                              .AppendLine($", Direção: {comando.Parameters[i].Direction}");
            }

            procedures.Add(auxAgrupamento.ToString());
    }

Ball show, hugs and hope this helps anyone who needs it one day.