How to execute different multiple SQL statements using OracleCommand C#

0
votes

I am trying to execute different SQL statements using OracleCommand.

var conn = new OracleConnection("User Id=SYSTEM;Password=mw;Data Source=SampleDataSource");
// Open the connection
if (conn.State != ConnectionState.Open)
    conn.Open();

string sql = @"
    DROP TABLE CUSTOMERS;

    CREATE TABLE CUSTOMERS (
        ID INT NOT NULL PRIMARY KEY,
        NAME VARCHAR(12) NOT NULL,
        AGE INT,
        ADDRESS VARCHAR(12)
    );

    INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (1, 'Mark', 28, 'NY');

    INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (2, 'John', 39, 'LA');

    INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (3, 'Andy', 48, 'CA');

    INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (4, 'Allan', 53, 'LA');
";

var cmd = new OracleCommand(sql, conn);
try
{
    cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
    error = e.Message;
}
finally
{
    if (conn.State == ConnectionState.Open)
    {
        conn.Close();
    }
}

I am getting the following exception.

ORA-00933: SQL command not properly ended

When I enclose the multiple statements in BEGIN, END block then I am getting the following exception.

ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array

3
c#.netoracleoracle.manageddataaccess
Could you create a stored procedure and use execute immediate? You would have to use execute immediate to drop the table. – gmdev86
You could also try this stackoverflow.com/questions/31917301/… – gmdev86
execute immediate is what you will need in order to call multiple statements – gmdev86
Might be irrelevant to Oracle but in MS SQL Server you can't do a drop and a create table and a query in the same batch. If Oracle has a batch separator like MSSQL's GO try putting that in between. – Crowcoder
Typically you don't drop or create tables at runtime. What is your purpose? – Wernfried Domscheit

3 Answers

1
votes

execute immediate will give you the ability to execute more than one statement.

OracleConnection con = new OracleConnection("User Id=SYSTEM;Password=mw;Data Source=SampleDataSource");  

if (con.State != ConnectionState.Open)
    con.Open();

OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText =
    "begin " +
    "  execute immediate 'DROP TABLE CUSTOMERS';" +
    "  execute immediate 'CREATE TABLE CUSTOMERS (ID INT NOT NULL PRIMARY KEY,NAME VARCHAR(12) NOT NULL, AGE INT, ADDRESS VARCHAR(12))';" +
    "  execute immediate 'INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (1, 'Mark', 28, 'NY')';" +
    "  execute immediate 'INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (2, 'John', 39, 'LA')';" +
    "  execute immediate 'INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (3, 'Andy', 48, 'CA')';" +
    "  execute immediate 'INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (4, 'Allan', 53, 'LA')';" +
    "end;"
cmd.CommandType = CommandType.Text;
try
{
    cmd.ExecuteNonQuery();
}
catch (OracleException e)
{
    error = e.Message;
}
finally
{
    if (con.State == ConnectionState.Open)
    {
        con.Close();
    }
}
0
votes

Maybe it's better when you use a command for each statement. Below is an example with dapper.

  const string dropTableCustomers = "DROP TABLE CUSTOMERS";

  const string createTableCustomers = @"    
    CREATE TABLE CUSTOMERS (
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(12) NOT NULL,
    AGE INT,
    ADDRESS VARCHAR(12))";

  const string insertCustomerMark = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (1, 'Mark', 28, 'NY')";
  const string insertCustomerJohn = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (2, 'John', 39, 'LA')";

  using (var connection = new OracleConnection("User Id=SYSTEM;Password=mw;Data Source=SampleDataSource")) {
    connection.Open();

    connection.Execute(dropTableCustomers);
    connection.Execute(createTableCustomers);
    connection.Execute(insertCustomerMark);
    connection.Execute(insertCustomerJohn);
  }

If you need a transaction, you can do following:

  public static class DbConnectionFactory {
    public static IDbConnection Create(string connectionString) {
      var connection = new OracleConnection(connectionString);
      connection.Open();

      return connection;
    }
  }


  const string dropTableCustomers = "DROP TABLE CUSTOMERS";

  const string createTableCustomers = @"    
    CREATE TABLE CUSTOMERS (
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(12) NOT NULL,
    AGE INT,
    ADDRESS VARCHAR(12))";

  const string insertCustomerMark = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (1, 'Mark', 28, 'NY')";
  const string insertCustomerJohn = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS) VALUES (2, 'John', 39, 'LA')";

  using (var connection = DbConnectionFactory.Create("User Id=SYSTEM;Password=mw;Data Source=SampleDataSource"))
  using (var transaction = connection.BeginTransaction()) {

    connection.Execute(dropTableCustomers);
    connection.Execute(createTableCustomers);
    connection.Execute(insertCustomerMark);
    connection.Execute(insertCustomerJohn);

    transaction.Commit();
  }
0
votes
strSQL = @"Insert all into NEW1(a,b) VALUES(1,2) "
                + "INTO NEW1(a,b) VALUES(2, 3) "
                + "INTO NEW1(a,b) VALUES(3, 4) "
                + "INTO NEW2(a,b) VALUES(9, 9) "
                + "INTO NEW2(a,b) VALUES(6, 6) "
                + "SELECT * FROM DUAL";