Home > database >  Convert Oracle PL/SQL code to C# use ado.net
Convert Oracle PL/SQL code to C# use ado.net

Time:01-25

I use .net 4 and oracle sql and I execution this block code:

DECLARE
Wresult  NUMBER;
BEGIN
Wresult :=tis.fuc(1, 3, 5, 44, '3311');
DBMS_OUTPUT.put_line(Wresult);
END;

When I run incorrect input I get "1".

enter image description here

When I run correct input I get "0".

enter image description here

how to show "0" or "1" result into c# use ado.net?

This is my code ado.not:

string str1 = "declare Wresult number;"  
              "begin "  
              "w:=tis.fuc(:pre1, :pre2, :pre3, :pre4, :pre5);"  
              "DBMS_OUTPUT.put_line(Wresult);"  
              "end;";

          using (OracleConnection connection = new OracleConnection(OracleServer))
          {
           connection.Open();
           using (OracleCommand command = new OracleCommand(str1, connection))
           {
            command.CommandType = CommandType.Text;

            command.Parameters.Add("pre1", OracleDbType.Int32).Value = 1;
            command.Parameters.Add("pre2", OracleDbType.Int32).Value = in1;
            command.Parameters.Add("pre3", OracleDbType.Int32).Value = in2;
            command.Parameters.Add("pre4", OracleDbType.Int32).Value = in3;
            command.Parameters.Add("pre5", OracleDbType.Int32).Value = in4;
            //command.Parameters.Add("Wresult", OracleDbType.Int32, ParameterDirection.Output);
            var result1 = command.ExecuteNonQuery();
            //var result1 = command.Parameters["Wresult"].Value;
            //vv = Convert.ToInt32(command.Parameters["Wresult"].Value);
                 }
                  connection.Close();
           }

and I use this to get output but not work .

//command.Parameters.Add("Wresult", OracleDbType.Int32, ParameterDirection.Output);

and finally

var result1 = command.ExecuteNonQuery();

the out put result1 incorrect or correct input is -1 and when put correct output is work but not show on C# only background on database.

CodePudding user response:

string str1 = "declare Wresult number;"  
              "begin "  
              "w:=tis.fuc(:pre1, :pre2, :pre3, :pre4, :pre5);"  
              "DBMS_OUTPUT.put_line(Wresult);"  
              "end;";

An OUT value is still a binded variable, so this should look like:

string str1 = "begin "  
              ":wout := tis.fuc(:pre1, :pre2, :pre3, :pre4, :pre5);"  
              "end;";

at which point you would then have:

command.Parameters.Add("wout", OracleDbType.Int32, ParameterDirection.Output);
  • Related