Solved

Handle db records

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 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