Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Handle db records

Posted on 2015-01-14
4
Medium Priority
?
92 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 30

Accepted Solution

by:
anarki_jimbel earned 2000 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 30

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

636 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