Solved

Handle db records

Posted on 2015-01-14
4
75 Views
Last Modified: 2015-01-18
I'm working on a .net web application using Oracle store procedure to get db records. I need to handle the records returned from store procedures with C#, e.g. get db records data, loop through to get value list.

I do not use entity framework. I use ODP.Net to connect to the Oracle db and call store procedures.

Let's say an employee list is returned from store procedure which contain contains empID and empName. I need help code samples in detail on
1. Employee class definition
2. How an Employee object gets record list value returned from store procedures

Thanks.
0
Comment
Question by:minglelinch
  • 2
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
anarki_jimbel earned 500 total points
ID: 40550352
May be I do not understand the question but this should be quite straightforward.
I suggest you know how to retrieve data using ODP and stored procedures. So my code will be just a "sample" code.

Definition for the Employee class:

    public class Employee
    {
        //private fields
        private int _id;
        private string _name;
        //public properties
        public int ID
        {
            get{return _id;}
        }

        public string Name
        {
            get { return _name; }
        }
        //constructor
        public Employee(int id, string name)
        {
            _id = id;
            _name = name;
        }
    }

Open in new window

Creating Employee objects:

        private void button1_Click(object sender, EventArgs e)
        {
            // Create a list of employees to add to:
            List<Employee> employees = new List<Employee>();

            // Now all DB stuff to retrieve data from storage
            // I have no Oracle DB, so - only an idea I can't really test :)

            OracleConnection conn = new OracleConnection("Data Source=Oracle8i;Integrated Security=yes");
            Conn.Open;
            OracleCommand cmd = conn.CreateCommand();
            cmd.CommandText = "sp_getemp.getdata";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new OracleParameter("a1", OracleType.Cursor)).Direction = ParameterDirection.Output;
            cmd.Parameters.Add(new OracleParameter("a2", OracleType.Cursor)).Direction = ParameterDirection.Output;
            DataSet ds = new DataSet();
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(ds);
            dt = ds.Tables["EmployeeTable"];

            foreach (DataRow dr in dt.Rows)
            {
                Employee emp = new Employee((int)dr["empID"], dr["empName"].ToString());
                employees.Add(emp);
            }

        }

Open in new window

0
 
LVL 1

Author Comment

by:minglelinch
ID: 40556421
Thanks for the comment. That's what I need. For the Employee class definition, there's get for each field, but about set? We do not need set, do we?
0
 
LVL 29

Expert Comment

by:anarki_jimbel
ID: 40556472
This depends. I.e., some properties may need 'set', others - not.
For example, date of birth. You may pass this value into a constructor when creating an object (not in my example but you may add).
I'd personally prefer this approach and have 'get' only property. The reason is - we never change the date of birth, so why would we have 'set'?!
Same for ID and Name. But even for name it's a bit more complex. Say, an Employee is a female, she gets married and takes a new family name... We may need 'set' property for this.
Other properties like 'Address' (or, better 'AddressID' - address is a separate class and DB record) - need to be "replaceable". People may move etc. Same with a position, phone number etc.
Just use common sense  - what property values are to be set once, when creating an object (db record), and what values may change over the time.
0
 
LVL 1

Author Closing Comment

by:minglelinch
ID: 40556494
Thanks for the solution and comments.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question