I have the following SQL command (simplified) assigned to the var sql
:
INSERT ALL
INTO ORACLETBL (COL_A,COL_B) VALUES ('A','B')
INTO ORACLETBL (COL_A,COL_B) VALUES ('C','D')
SELECT * FROM dual;
When I do a conn.Execute(sql);
in a void
method, the command works correctly.
When I do an await conn.ExecuteAsync(sql);
in an async Task
method, against the exact same SQL command, I get the exception "ORA-00933: SQL command not properly ended".
Is it obvious to anyone what I'm doing wrong?
Remove the semicolon at the end of the statement.
Edit:
Maybe that's a solution, too.
string sql = "INSERT INTO Customers (CustomerName) Values (@CustomerName);";
using (var connection = new SqlConnection("")) {
connection.Open();
var affectedRows = await connection.ExecuteAsync(sql,
new[] {
new {CustomerName = "John"},
new {CustomerName = "Andy"},
new {CustomerName = "Allan"}
}
);
}
Or try
SELECT 1 FROM DUAL
instead of
SELECT * FROM DUAL
I'm not sure why it throws an exception in one situation but not the other, but it may be related to your using ExecuteAsync
when you mean to use QueryAsync
.
Execute is not intended for use with selects, but instead returns the number of affected rows when an operation is performed.
In the ExecuteAsync documentation you'll notice they use ExecuteAsync and only receive back the number of rows affected, but they use Query when they want to retrieve actual rows.
This stack post contains a more detailed explanation of the difference between Execute
and Query
.