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

Question

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

Navigation

#1 by (2 votes)

3

I'm having a question in C # and in SQL commands, I'm currently using SQLite as the local database. In my application I make the insertion of records in the Database, so far so good, but I was wondering if there is any way to return the command that was executed, in the form of a 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 inserting data into 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 would be that after running the command, it would catch 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 wanted it to be 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!

sql sqlite c#

asked by anonymous 16.06.2016 / 04:18

1 answer

I update index.html and delete files, but github does not update .click function being executed twice

score 2 · Accepted Answer

I believe your intention is to log a 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;
}