How to pass c# object to Oracle?

Question

I have my c# object

public class Person
{
    
    public string Name { get;set; }
    public string Surname{ get;set; }
    public string Phone { get;set; }
    public string Address { get;set; }
    // More properties here
}

So, i am passing it to oracle stored procedure this way

var con = new OracleConnection("connection string");
var com = con.CreateCommand("create_user");
com.Parameters.Add("name", OracleDbType.Varchar2, Name , ParameterDirection.Input);
com.Parameters.Add("surname", OracleDbType.Varchar2, Surname, ParameterDirection.Input);
com.Parameters.Add("phone", OracleDbType.Varchar2, Phone , ParameterDirection.Input);
// More adds here

my oracle procedure

CREATE OR REPLACE PROCEDURE Person(
       name IN Person.name%TYPE,
       surname IN Person.surname%TYPE,
       phone IN Person.phone%TYPE,
       address IN Person.address%TYPE
       -- more parameters
       )
IS
BEGIN

  INSERT INTO Person ("name", "surname", "phone", "address")
  VALUES (name, surname,phone, address);

  COMMIT;

END;
/

The problem is: Is the any other ways to pass object to oracle? cuz i think its little weird to create procedure with 10+ input parameters. Or better use xml(clob) communication?

There are *better* ways, but they still involve passing the field parameters independently. — Robert Harvey, Jul 12 '20 at 14:26
Why do you create a procedure to run an `INSERT` query when you could run the `INSERT` query directly? — Progman, Jul 12 '20 at 14:53
If you don't want 10+ input parameters in procedure, you can create UDT in oracle and use it. But from C#, you still need to map all the properties. You can check this [link](https://stackoverflow.com/a/12748619/1705895) for reference. — Rajeev, Jul 12 '20 at 15:21
@Rajeev thanks, it works. If you will write your answer to the answers block, i will mark it as solution — superhacker, Jul 20 '20 at 05:03

score 0 · Answer 1 · answered Jul 12 '20 at 14:34

Using Dapper.Contrib, you could simply write this as

connection.Insert(Person);

Dapper will map all of your parameters for you.

Of course, this only works if you're willing to forego stored procedures. If you insist on stored procedures, you will still have to populate each parameter individually, even with Dapper.

score 0 · Accepted Answer · answered Jul 22 '20 at 06:17

0

If you don't want 10+ input parameters in procedure, you can create UDT in oracle and use it.

But from C#, you still need to map all the properties.

You can check this link for reference

answered Jul 22 '20 at 06:17

Rajeev

843
2
11
22

How to pass c# object to Oracle?

2 Answers2