Select Delete commands

Question

Hello,

I've been given this samples for insert into and update:

Using command As New OleDbCommand("INSERT INTO Table (Column1) VALUES (?)", connection)
   command.Parameters.AddWithValue("@param1", field1)
   command.ExecuteNonQuery()
   command.Parameters.Clear()
End Using

Using command As New OleDbCommand("UPDATE [Table] SET Column1 = ? WHERE ConditionColumn = ?", connection)
     command.Parameters.AddWithValue("@param1", newValue)
     command.Parameters.AddWithValue("@param2", conditionValue)
     command.ExecuteNonQuery()
   command.Parameters.Clear()
End Using

Can I use parameterized queries for SELECT and DELETE commands as well?

If so, anyone can give a sample like above?

Thanks.

Answer 1

Hi @StewartBW , Welcome to Microsoft Q&A,

Yes, you can use parameterized queries with SELECT and DELETE commands. Here is the sample code:

Example of SELECT command using parameterized query

Using command As New OleDbCommand("SELECT * FROM [Table] WHERE Column1 = ?", connection)

     command.Parameters.AddWithValue("@param1", searchValue)

     Using reader As OleDbDataReader = command.ExecuteReader()

         While reader.Read()

             ' Process query results

         End While

     End Using

End Using

Example of DELETE command using parameterized query

Using command As New OleDbCommand("DELETE FROM [Table] WHERE ConditionColumn = ?", connection)

     command.Parameters.AddWithValue("@param1", conditionValue)

     command.ExecuteNonQuery()

End Using

The searchValue and conditionValue in these examples are the values you need to query or delete.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As per my reply in your other question this uses Dapper.

using Dapper;
using System.Data;
using System.Data.OleDb;

internal class Table1Operations
{
    private IDbConnection _cn = new OleDbConnection(
        @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb");
    public void Add(Table1 sender)
    {
        const string statement =
            """
            INSERT INTO Table1
            (
                Column1
            )
            VALUES
            (
                @Column1
            )
            """;
        _cn.Execute(statement, new { sender.Column1 });
    }

    public void Delete(Table1 sender)
    {
        const string statement =
            """
            DELETE FROM Table1 
            WHERE Id = @Id
            """;
        _cn.Execute(statement, new { Id = sender.Id });

    }

    public List<Table1> ReadAllRecords()
    {
        return _cn.Query<Table1>(
                """
                SELECT Id, Column1 
                FROM Table1
                """)
            .AsList();
    }
}

public class Table1
{
    public int Id { get; set; }
    public string Column1 { get; set; }
}