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

Popular posts from this blog

angular - Ionic slides - dynamically add slides before and after -

hi i'm using ngfor create set of 3 slides while starting in middle i'm guaranteed able slide left or right on start. when slide right can simple listen reachedend , push slide array i'm looping. but have problem adding slide beginning. if same above , use e.g. array.unshift() or spread add item beginning, view think it's on position 0 , snaps view new slide. the code below work animates slide change index 1. slide = [0,1,2] //example loop slidechanged(event) { if(this.slides.isbeginning()){ this.slide = [this.slide[0]-1, ...this.slide]; this.slides.update(); this.slides.slideto(1) } } <ion-slides [initialslide]="1" (ionslidedidchange)="slidechanged($event)"> <ion-slide *ngfor="let item of slide"> <h1>slide {{item}}</h1> </ion-slide> </ion-slides> any appreciated! you can using ionslidenextend , ionslideprevend events slides . please...
Read more

Add a dynamic header in angular 2 http provider -

i have http interceptor built via tutorial https://scotch.io/@kashyapmukkamala/using-http-interceptor-with-angular2 the problem need have dynamic header changes automatically once changes 'mode' in app. service able tell interceptor change header, or have interceptor pull header (which string)from service each request. able throwing away patch method, call http.patch('new header string') set header string. call in service controls 'mode' setting. wondering if there way interceptor pull recent value service each request or if can add method http provider via interceptor? i've tried call service in interceptor each request returns undefined. because creating new instance of service or copying value before service fired , loaded/changed data. i don't want pass header string every http provider call because defeats purpose of having interceptor, id able set once changes or have provider fetch service before makes request. tl;dr : need add autom...
Read more

minify - Minimizing css files -

i want know how css files can minimized. currently, using external sites css minifier compress css files. however, after removing whitespaces, comments, etc, file sizes reduced 10-15%. how frameworks bootstrap manage make minimized versions kilobytes. as example, css is: body { background-color: #ff0; } the reduction goes 28 bytes 22 bytes other css files have lot more characters still smaller this. actually minifying code means it's remove thing code white space characters new line characters comments block delimiters it reduces amount of code has transferred on web , speed site load fastly.but unreadable format.
Read more