Solved

Handle db records

Posted on 2015-01-14
4
74 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 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