Solved

Handle db records

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now