C#,SQL,SQLITE How to return SQL command from an action

Asked

Viewed 261 times

3

I have a question in C# and SQL commands, I am currently using Sqlite as a local database. In my application I enter entries in the database, so far so good, but I wanted to know if there is any way to return the command that was executed, in the form of string, for example:

This is the method of receiving the parameters:

        public void Inserir(Curso curso)
    {
        acessoaoDB.LimparParametros();
        acessoaoDB.AdicionarParametros("@NomeCurso", curso.NomeCurso);
        acessoaoDB.AdicionarParametros("@SiglaCurso", curso.SiglaCurso);
        acessoaoDB.AdicionarParametros("@Descricao", curso.Descricao);
        acessoaoDB.ExecutarManipulacao(CommandType.Text, "INSERT INTO cursos (nome_curso,sigla_curso,descricao) VALUES (@NomeCurso,@SiglaCurso,@Descricao)");
    }

This is the method of entering data in the table:

        public void ExecutarManipulacao(CommandType commandType, string SQL)
    {
        SQLiteConnection sqlConnection = CriarConexao();
        sqlConnection.Open();
        SQLiteCommand sqlCommand = sqlConnection.CreateCommand();
        sqlCommand.CommandType = commandType;
        sqlCommand.CommandText = SQL;
        sqlCommand.CommandTimeout = 7200;

        foreach (SQLiteParameter sqliteParameter in sqliteParameterCollection)
        {
            sqlCommand.Parameters.Add(new SQLiteParameter(sqliteParameter.ParameterName, sqliteParameter.Value));
        }

        sqlCommand.ExecuteNonQuery();

    }

What I wanted to do was after executing the command, capture it the way it was sent to the bank, for example:

Command with variables:

"INSERT INTO cursos (nome_curso,sigla_curso,descricao) VALUES (@NomeCurso,@SiglaCurso,@Descricao)"

Command as I wish you were captured:

"INSERT INTO cursos (nome_curso,sigla_curso,descricao) VALUES ('Matematica','MAT123','Curso de Matematica do Ensino Medio')"

Thank you very much for your attention!

  • What is the purpose of this capture?

  • The goal is to make a versioning system by directly saving the lines.

1 answer

2


I believe that your intention is to log the query, so I suggest the following implementation:

public string ExecutarManipulacao(CommandType commandType, string SQL)
{
    SQLiteConnection sqlConnection = CriarConexao();
    sqlConnection.Open();
    SQLiteCommand sqlCommand = sqlConnection.CreateCommand();
    sqlCommand.CommandType = commandType;
    sqlCommand.CommandText = SQL;
    sqlCommand.CommandTimeout = 7200;

    foreach (SQLiteParameter sqliteParameter in sqliteParameterCollection)
    {
        sqlCommand.Parameters.Add(new SQLiteParameter(sqliteParameter.ParameterName, sqliteParameter.Value));
    }

    if (sqlCommand.ExecuteNonQuery() > 0)
        return LogQuery(sqlCommand);

    return "";
}

public static string LogQuery(SQLiteCommand sqlCommand)
{
    string query = sqlCommand.CommandText;

    foreach (SQLiteParameter p in sqlCommand.Parameters)
    {
        query = query.Replace(p.ParameterName, p.ParameterValueForSQL());
    }

    return query;
}

public static string ParameterValueForSQL(this SQLiteParameter sp)
{
    string strReturn = "";

    switch (sp.DbType)
    {
        case DbType.AnsiString:
        case DbType.AnsiStringFixedLength:
        case DbType.String:
        case DbType.StringFixedLength:
            strReturn = "'" + sp.Value.ToString().Replace("'", "''") + "'";
            break;
        case DbType.Boolean:
            strReturn = Convert.ToBoolean(sp.Value) ? "1" : "0";
            break;
        default:
            strReturn = sp.Value.ToString().Replace("'", "''");
            break;
    }

    return strReturn;
}

Browser other questions tagged

You are not signed in. Login or sign up in order to post.