c# - Multiple Inserting to Oracle DBMS via Oracle Store Procedure -
i want insert multiple record @ once oracle database. create store procedure inserting because want insert multiple tables , 1 table primary key foreign key in table. below c# code insert list
public void inserttodatabase(list<employeedata> allemployee) { checkconnection(); oracletransaction trans = con.begintransaction(); try { oraclecommand com = new oraclecommand(); foreach (var item in allemployee) { com.commandtype = commandtype.storedprocedure; com.commandtext = "insert_wages"; com.transaction = trans; com.parameters.add(new oracleparameter("year", item.year)); com.parameters.add(new oracleparameter("employer_tax_payer_no", item.employer_tax_payer_no)); com.parameters.add(new oracleparameter("tax_payer_no", item.tax_payer_no)); com.parameters.add(new oracleparameter("employment_no", item.employment_no)); com.parameters.add(new oracleparameter("enterprise_no", item.enterprise_no)); com.parameters.add(new oracleparameter("total_base_salary", item.total_base_salary)); com.parameters.add(new oracleparameter("withh_decl_month", item.withh_decl_month)); com.parameters.add(new oracleparameter("payment_date", item.payment_date)); com.parameters.add(new oracleparameter("amount", item.amount)); com.parameters.add(new oracleparameter("calculated_tax", item.calculated_tax)); com.parameters.add(new oracleparameter("tax_period_no", item.tax_period_no)); com.parameters.add(new oracleparameter("wdr_tax_account_no", item.wdr_tax_account_no)); com.parameters.add(new oracleparameter("credited_tax_period_no", item.credited_tax_period_no)); com.parameters.add(new oracleparameter("wdee_tax_account_no", item.wdee_tax_account_no)); com.parameters.add(new oracleparameter("enter_user", item.enter_user)); com.parameters.add(new oracleparameter("ird_employee_no", item.ird_employee_no)); com.parameters.add(new oracleparameter("tax_centre_no", item.tax_centre_no)); com.executenonquery(); } } catch (exception e) { trans.rollback(); return; } trans.commit(); } and below store procedure in oracle database
create or replace procedure insert_wages( p_year in tax_withheld.year%type, p_employer_tax_payer_no in tax_withheld.employer_tax_payer_no%type, p_tax_payer_no in tax_withheld.tax_payer_no%type, p_employment_no in tax_withheld.employment_no%type, p_enterprise_no in tax_withheld.enterprise_no%type, p_total_base_salary in tax_withheld.total_base_salary%type, p_withh_decl_month in tax_withheld.withh_decl_month%type, p_payment_date in tax_withheld.payment_date%type, p_amount in tax_withheld_detail.amount%type, p_calculated_tax in tax_withheld_detail.calculated_tax%type, p_tax_period_no in tax_withheld_detail.tax_period_no%type, p_wdr_tax_account_no in tax_withheld_detail.wdr_tax_account_no%type, p_credited_tax_period_no in tax_withheld_detail.credited_tax_period_no%type, p_wdee_tax_account_no in tax_withheld_detail.wdee_tax_account_no%type, p_enter_user in tax_withheld_detail.enter_user%type, p_ird_employee_no in tax_transaction.ird_employee_no%type, p_tax_centre_no in tax_transaction.tax_centre_no%type ) --varaible sequence of 4 table , store in varbile v_tax_withheld_no tax_withheld.tax_withheld_no%type; v_tax_withheld_detail_no tax_withheld_detail.tax_withheld_detail_no%type; v_tax_trans_no tax_transaction.tax_trans_no%type; v_tax_sub_trans_no tax_sub_trans.tax_sub_trans_no%type; begin select tax_withheld_no_seq.nextval v_tax_withheld_no dual; select tax_withheld_detail_no_seq.nextval v_tax_withheld_detail_no dual; select tax_trans_no_seq.nextval v_tax_trans_no dual; select tax_trans_type_no_seq.nextval v_tax_sub_trans_no dual; insert tax_withheld ( tax_withheld_no, year, tax_payer_no, employer_tax_payer_no, date_entered, employment_no, enterprise_no, total_base_salary, mar_stat_no, calculated_family_divider, withh_decl_month, capture_tab, payment_date ) values (v_tax_withheld_no, p_year, p_tax_payer_no , p_employer_tax_payer_no, sysdate, p_employment_no, p_enterprise_no, p_total_base_salary , 2, 1 , p_withh_decl_month , 72 , p_payment_date ); insert tax_withheld_detail ( tax_withheld_detail_no, tax_withheld_no, amount, tax_type_no, tax_payer_no, calculated_tax, tax_period_no, wdr_tax_account_no, credited_tax_type_no, credited_tax_period_no, wdee_tax_account_no, taxable_income, enter_user, enter_date ) values ( v_tax_withheld_detail_no, v_tax_withheld_no, p_amount, 1, p_tax_payer_no, p_calculated_tax, p_tax_period_no, p_wdr_tax_account_no, 2, p_credited_tax_period_no, p_wdee_tax_account_no, p_total_base_salary, p_enter_user, sysdate ); --insert data tax_transection table insert tax_transaction ( tax_trans_no, ird_employee_no, tax_centre_no, trans_date, enter_date, enter_time, amount, tax_account_no, tax_payer_no, tax_type_no ) values ( v_tax_trans_no, p_ird_employee_no, p_tax_centre_no, sysdate, sysdate, systimestamp, p_amount, p_wdee_tax_account_no, p_tax_payer_no, 2); --insert data sub transetion table insert tax_sub_trans( tax_sub_trans_no, charge_type_no, tax_trans_type_no, tax_trans_no, amount, enter_date, enter_time, tax_period_no, sub_trans_comment, tax_account_no, tax_centre_no, tax_type_no, credit_source_type_no, collection_location_no, arrears_flag, reverse_flag, appeal_deposit_fl ) values ( v_tax_sub_trans_no, 15, 1, v_tax_trans_no, p_amount, sysdate, systimestamp, p_tax_period_no, 'asp.net insert', p_wdee_tax_account_no, p_tax_centre_no, 2, 1, 2, 'n', 'n', 'n'); update tax_withheld_detail set tax_trans_no=v_tax_trans_no tax_withheld_detail_no=v_tax_withheld_detail_no; --if exception occur in these 4 insertion database roolback end; it give exception operation not valid due current state of object if 1 know problem solve or suggest method insert multiple record different tables in c# using oracle transaction. highly appreciated
Comments
Post a Comment