How to execute pl sql block using oledb in c#

Question

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.

Well your string starts with BEGINSTART - not a great starting point. You're also putting your parameters directly into your SQL string, which is a really bad idea. Use parameterized SQL instead. Do you really need to execute a block instead of just one command? It's not clear what you're trying to achieve here. — Jon Skeet
– Jon Skeet, Commented Aug 16, 2014 at 8:10
My task is: i am searching the string 'a1' from Discom column name and trying to retrieve its count in integer 'var1' using SQL&ROWCOUNT.. — Parag Chaudhari
– Parag Chaudhari, Commented Aug 16, 2014 at 8:16
This block will not work as intended even if you correct the syntax errors. You're executing a SELECT statement where you should open a cursor. Furthermore, you're attempting to assign SQL%ROWCOUNT to C# variable var1 from inside a PL/SQL block - this won't work as PL/SQL has no knowledge of your C# (or VB or whatever) variables. (And - use the assignment operator :=). Please - have a look at this question which shows HOW to get values back from an anonymous block. Best of luck. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Aug 16, 2014 at 20:01

Jon Skeet · Accepted Answer · 2014-08-16 08:21:48Z

1

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.

answered Aug 16, 2014 at 8:21

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sathish · Accepted Answer · 2014-08-16 08:03:53Z

1

Remove semicolon near a1

Discom ='" + a1 + "';"+ to Discom ='" + a1 + "'"+

"BEGIN"+
"SELECT * from MasterCompliant WHERE Discom ='" + a1 + "'"+
"" + var1 + " = SQL%ROWCOUNT;"+
"END;"

answered Aug 16, 2014 at 8:03

Sathish

4,4854 gold badges36 silver badges60 bronze badges

1 Comment

Parag Chaudhari Over a year ago

Its not working.. similar exception even after removing the semicolon.

Collectives™ on Stack Overflow

How to execute pl sql block using oledb in c#

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related