Calling oracle procedures using C#

June 2, 2010 in C#, Oracle


I have this procedure stored in oracle database.

-- procedure to to calculate student cgpa/grade letter of whole semester
create or replace procedure calc_sem_cgpa(regIn in number, semIn varchar2)
as
    cursor result is
    select cgpa.gpa, syllabus.credit
    from cgpa,syllabus,student
    where
    cgpa.reg = regIn
    and cgpa.semester = semIn
    and cgpa.course = syllabus.course
    and cgpa.semester = syllabus.semester
    and student.reg = cgpa.reg
    and student.d ept = syllabus.dept
    and student.ses = syllabus.ses;

    eachResult result%rowtype;

    sum_cgpa   number(5,-2)  := 0;
    sum_credit  number(5,2) := 0;
    final_cgpa number(4,2)  := 0;
    countRow number(2)      := 0;

begin
    open result;
    loop
        fetch result into eachResult;
        exit when result%notfound;

        if eachResult.gpa > 0.0 then
            sum_cgpa  := sum_cgpa + eachResult.gpa * eachResult.credit;
            sum_credit := sum_credit + eachResult.credit;
         end if;

    end loop;

    if sum_credit > 0 then
        final_cgpa := round(sum_cgpa/sum_credit, 2);
    else
        final_cgpa := 0;
    end if;

    select count(*) into countRow from cgpa_sem
    where cgpa_sem.reg = regIn
    and cgpa_sem.semester = semIn;

    if countRow > 0 then
        update cgpa_sem set cgpa = final_cgpa, credits_comp = sum_credit
        where reg = regIn and semester = semIn;
    else
        insert into cgpa_sem(reg, semester, cgpa, credits_comp)
        values(regIn, semIn, final_cgpa, sum_credit);
    end if;
end;
/

Now I will call this procedure using C#

try
{
    con = new oracleConnection("Data Source=localhost;Persist Security Info=True;User ID=rp;Password=rp;Unicode=True");
    con.Open();
    // call oracle procedure to calculate semester cgpa
    cmd = new OracleCommand("calc_sem_cgpa", con);
    cmd.CommandType = CommandType.StoredProcedure;
    prm1 = new OracleParameter("regIn", OracleType.Number);
    prm2 = new OracleParameter("semIn", OracleType.VarChar);
    prm1.Value = new OracleNumber(Convert.ToInt32(stdReg.Text));
    prm2.Value = new OracleString(semester.Text);
    cmd.Parameters.Add(prm1);
    cmd.Parameters.Add(prm2);
    cmd.ExecuteNonQuery();
    con.Close();
}
catch (OracleException ex)
{
    MessageBox.Show(ex.Message);
}

In prm1 and prm2 i am sending to parameters to the procedure from c#

Related posts:

Tags: , , ,

To make money we lose our health, and then to restore our health we lose our money.... We live as if we are never going to die, and we die as if we never lived!

Leave a Reply