Executing multiple insert into table commands in sing Oracle Command C#

Question

I want to execute all following insert into table statement in single Oracle Command is this possible?

OracleCommand cmd = new OracleCommand();
var parameter = cmd.Parameters;
string insrtPInfo = "Insert into PersonalInfo(Name, ContactNum, EmailID, Address, Gender, DOB) VALUES(:pName, :contactNum, :emailId, :address, :gender, :dob )";
string insrtEdu = "Insert into PersonalInfo(Degree, Institution, Year, CGPA_Marks) VALUES(:degree, :Instituition, :year, :marks)";
string insrtExprnce = "Insert into PersonalInfo(Organization, Organization, Desigination) VALUES(:organization, :duration, :desigination)";
string insrtSkils = "Insert into PersonalInfo(Programming_languages, Softwares, OS) VALUES(:progLang, :softwares, OS)";
string insrtProj = "Insert into PersonalInfo(FYP, Other_Projects) VALUES(:fyp, otherProj)";
// T1
parameter.Add("pName", pName);
parameter.Add("contactNum", contactNum);
parameter.Add("emailId", emailId);
parameter.Add("address", address);
parameter.Add("gender", gender);
parameter.Add("dob", dob);
// T2             
parameter.Add("degree", degree);
parameter.Add("Instituition", Instituition);
parameter.Add("year", year);
parameter.Add("marks", marks);
// T3                             
parameter.Add("organization", organization);
parameter.Add("duration", duration);
parameter.Add("desigination", desigination);
// T4           
parameter.Add("progLang", progLang);
parameter.Add("softwares", softwares);
parameter.Add("OS", OS);
// T5
parameter.Add("fyp", fyp);
parameter.Add("otherProj", otherProj);
cmd.CommandText = insrtPInfo;
cmd.ExecuteNonQuery();

How I can execute all these statements in single Oracle Command. Or any other best way to do it. I am using Visual Studio 2013 and Oracle 11g for database.

possible duplicate of [Bulk Insert to Oracle using .NET](http://stackoverflow.com/questions/343299/bulk-insert-to-oracle-using-net) — Artem Kulikov, Aug 19 '15 at 18:48

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

One option is using Oracle's INSERT ALL syntax:

string sql = @"Insert all
                into PersonalInfo(Name, ContactNum, EmailID, Address, Gender, DOB) VALUES(:pName, :contactNum, :emailId, :address, :gender, :dob )
                into PersonalInfo(Degree, Institution, Year, CGPA_Marks) VALUES(:degree, :Instituition, :year, :marks)
                into PersonalInfo(Organization, Organization, Desigination) VALUES(:organization, :duration, :desigination)
                into PersonalInfo(Programming_languages, Softwares, OS) VALUES(:progLang, :softwares, :OS)
                into PersonalInfo(FYP, Other_Projects) VALUES(:fyp, :otherProj)
               select * from dual";

Documentation: multi_table_insert

ALL into_clause

Specify ALL followed by multiple insert_into_clauses to perform an unconditional multitable insert. Oracle Database executes each insert_into_clause once for each row returned by the subquery.

Another option is wrapping all your INSERT statements inside an anonymous PL/SQL block:

string sql = @"begin
                insert into PersonalInfo(Name, ContactNum, EmailID, Address, Gender, DOB) VALUES(:pName, :contactNum, :emailId, :address, :gender, :dob );
                insert into PersonalInfo(Degree, Institution, Year, CGPA_Marks) VALUES(:degree, :Instituition, :year, :marks);
                insert into PersonalInfo(Organization, Organization, Desigination) VALUES(:organization, :duration, :desigination);
                insert into PersonalInfo(Programming_languages, Softwares, OS) VALUES(:progLang, :softwares, :OS);
                insert into PersonalInfo(FYP, Other_Projects) VALUES(:fyp, :otherProj);
               end;";

I get error " Operation is not valid due to current state of object " at `cmd.executenonquery();` @sstan — Hanni, Aug 20 '15 at 16:57
This error has nothing to do with the statement itself. Based on the code you posted, it looks like you forgot to set the connection on the `OracleCommand` object. If that's not it, consider editing your post with your complete code + full error and stack trace. Or better yet, consider asking a new question, since this would no longer have anything to do with rewriting your insert statements. — sstan, Aug 20 '15 at 17:45

Executing multiple insert into table commands in sing Oracle Command C#

1 Answers1