How to execute pl sql block using oledb in c#

I am executing Pl/Sql block using OLEDB in C#. The code is:

        string a1;
        a1 = discom1.Text.ToString();

        my_querry10 = "BEGIN"+
                      "SELECT * from MasterCompliant WHERE Discom ='" + a1 + "';"+
                      "" + var1 + " = SQL%ROWCOUNT;"+
                      "END;";
        OleDbCommand cmd12 = new OleDbCommand(my_querry10, conn);
        conn.Open();

        cmd12.ExecuteNonQuery();

The exception is coming to statement:

"cmd12.ExecuteNonQuery" as "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

MasterCompliant is table name; Discom is column name and var1 is integer.

Jon Skeet
people
quotationmark

It seems to me that you don't need a separate block for this at all - you can just use SELECT COUNT(1) and ExecuteScalar:

string sql = "SELECT COUNT(1) FROM MasterCompliant WHERE Discom = ?";
int count;

// Open and close a connection each time you need one - let the connection pool
// handle making that efficient.
using (var connection = new OleDbConnection(...))
{
    connection.Open();
    using (var command = new OleDbCommand(sql, conn))
    {
        command.Parameters.Add("@v", OleDbType.VarChar).Value = discom1.Text;
        count = (int) command.ExecuteScalar();
    }
}

It's possible that you need to cast to long instead of int - you should basically try it and see.

Note how using a parameterized query makes the SQL simpler to read and prevents SQL Injection attacks.

people

See more on this question at Stackoverflow