Click here to Skip to main content
15,891,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

Hi i have one person model and relation model

C#
public class Person{

public int personid;
public int personname;
public List<Relation> relationlist;
}

public class Relation{

public int relationid;
public int relationtype;
}


here how i can pass relationlist into oracle procedure?
relationlist may contain more than one result....
Please help me........
Posted
Updated 29-Nov-15 18:24pm
v3
Comments
Krunal Rohit 30-Nov-15 0:40am    
Oracle follows the SQL and SQL doesn't have a concept of List nor an Array. Hence you'd be requiring some different approach like passing one object at a time from list to SP.

-KR

1 solution

I assume that you have two tables in your database, one called Persons and another called Relations, PersonID is a Foreign Key in the Relations table.

One approach is to create two stored procedures, one to store the person and one that stores the relations one by one.
From the first SP must return the primary key of the Persons table, and that variable is then used as a parameter together with the relation data into the second SP.
This way will require several transactions, but from an implementation point of view it is pretty clean.

Example
SQL
CREATE FUNCTION AddPerson(IN personID INT, IN personName VARCHAR(45)) RETURNS INT
BEGIN
    -- Do the insertion
    RETURN lastInsertedPersonID;
END

CREATE PROCDURE AddRelation(IN personID INT, IN relationID INT, IN relationType INT)
BEGIN
    -- Do the insertion
END


You could also serialize the object Person as XML, Examples of XML Serialization[^], and send into the SP as a string and then use XMLPARSE[^] inside the SP to extract the data.

This will require a loop for extracting the data, see LOOP Statements[^]

The advantage of the second method is that you will only have one transaction, but on the other hand the code will be more complex.
 
Share this answer
 
Comments
Baskar Gs 30-Nov-15 7:23am    
Yes this way will require several transactions, that why want to send list directly into procedure then iterate list with in procedure. it will minimize no.of database calls.
I have already did such task in java projects with oracle types and Array Descriptors

create or replace type person
AS
object
(
FIRSTNAME VARCHAR2(20),
LASTNAME VARCHAR2(20),
ID NUMBER
) ;

create or replace type RELATIONSHIPS
AS
object
(

ID NUMBER,
RELATED_ID NUMBER,
RELATIONSHIP VARCHAR2(40) ) ;

create or replace type RELATIONSHIPS_AARRAY as table of RELATIONSHIPS;

Then iterate like
FOR l_row IN 1 .. Relationship.COUNT
LOOP
DBMS_OUTPUT.put_line (parents (l_row));
END LOOP;


only problem is how to match oracle type(array) with C# list

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900