Home » Infrastructure » Windows » VS, ODAC, Procedures, & SELECT statement (12C)
VS, ODAC, Procedures, & SELECT statement [message #654420] Tue, 02 August 2016 09:33 Go to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
Let me start by saying I have an MS SQL background and am new to Oracle. Normally I write all my SQL code in Stored Procedures. I am learning Oracle procedures are not exactly the same as MS.

Especially when coding a SELECT statement...so from what I have found you don't use procedure to get a result set.

Using ODAC the foolowing code seems to be the way I have to do it or am I missing something. I can see this getting pretty hairy if I have a large routine with cursors, etc and then ending with a Select.

Am I missing something or is this my only option?

public static List<HWL_Variance> GetCycle(string yearMonth)
        {
            List<HWL_Variance> cycleList = new List<HWL_Variance>();

            OracleConnection connection = HWLOracleDB.GetConnection();
            string sql = "SELECT DISTINCT Cycle FROM HWL_Variance Where YearMonth = " + ":YearMonth" + " ORDER BY Cycle";
            OracleCommand selectCommand = new OracleCommand(sql, connection);
            selectCommand.CommandType = CommandType.Text;

            OracleParameter p_YearMonth = new OracleParameter();
            p_YearMonth.OracleDbType = OracleDbType.Varchar2;
            p_YearMonth.Value = yearMonth;
            selectCommand.Parameters.Add(p_YearMonth);

            OracleDataAdapter da = new OracleDataAdapter(selectCommand);
            OracleCommandBuilder cb = new OracleCommandBuilder(da);
            DataSet ds = new DataSet();

            try
            {
                connection.Open();
                da.Fill(ds);

                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    HWL_Variance cycle = new HWL_Variance();
                    cycle.Cycle = Convert.ToInt32(dr["Cycle"]);
                    cycleList.Add(cycle);
                }
            }
            catch (Exception ex)
            {
                frmSQLError errorForm = new frmSQLError();
                errorForm.error = ex.ToString();
                DialogResult selectedButton = errorForm.ShowDialog();
            }
            finally
            {
                connection.Close();
            }

            return cycleList;
        }
Re: VS, ODAC, Procedures, & SELECT statement [message #654422 is a reply to message #654420] Tue, 02 August 2016 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read How to use [code] tags and make your code easier to read.

What do you expect from us?
Maybe you can start with this page reading articles and watching videos.

[Edit: typo]

[Updated on: Tue, 02 August 2016 11:59]

Report message to a moderator

Re: VS, ODAC, Procedures, & SELECT statement [message #654423 is a reply to message #654422] Tue, 02 August 2016 10:00 Go to previous messageGo to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
public static List<HWL_Variance> GetCycle(string yearMonth)
 {
 List<HWL_Variance> cycleList = new List<HWL_Variance>();

 OracleConnection connection = HWLOracleDB.GetConnection();
 string sql = "SELECT DISTINCT Cycle FROM HWL_Variance Where YearMonth = " + ":YearMonth" + " ORDER BY Cycle";
 OracleCommand selectCommand = new OracleCommand(sql, connection);
 selectCommand.CommandType = CommandType.Text;

 OracleParameter p_YearMonth = new OracleParameter();
 p_YearMonth.OracleDbType = OracleDbType.Varchar2;
 p_YearMonth.Value = yearMonth;
 selectCommand.Parameters.Add(p_YearMonth);

 OracleDataAdapter da = new OracleDataAdapter(selectCommand);
 OracleCommandBuilder cb = new OracleCommandBuilder(da);
 DataSet ds = new DataSet();

 try
 {
 connection.Open();
 da.Fill(ds);

 foreach (DataRow dr in ds.Tables[0].Rows)
 {
 HWL_Variance cycle = new HWL_Variance();
 cycle.Cycle = Convert.ToInt32(dr["Cycle"]);
 cycleList.Add(cycle);
 }
 }
 catch (Exception ex)
 {
 frmSQLError errorForm = new frmSQLError();
 errorForm.error = ex.ToString();
 DialogResult selectedButton = errorForm.ShowDialog();
 }
 finally
 {
 connection.Close();
 }

 return cycleList;
 } 


Re: VS, ODAC, Procedures, & SELECT statement [message #654424 is a reply to message #654422] Tue, 02 August 2016 10:00 Go to previous messageGo to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
Thanks, that does look better.
Re: VS, ODAC, Procedures, & SELECT statement [message #654428 is a reply to message #654424] Tue, 02 August 2016 13:34 Go to previous messageGo to next message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
It might help if you told us exactly what it is that you're trying to do rather than just posting some code and asking if "that's how you do it"
Re: VS, ODAC, Procedures, & SELECT statement [message #654431 is a reply to message #654428] Tue, 02 August 2016 19:25 Go to previous messageGo to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
I wasn't asking if that was how you do it....what I posted works. In fact I have even refined it since this post. What I was asking was is this the best way to handle Select Statements. If I was using MS SQL I would be doing this in a stored procedure. But from trying that in Oracle that doesn't seem to be possible when trying to get a multi-record result set.

I have some MS SQL procedures that are screens long with multiple cursors which end by returning a result set, etc which I am going to attempt to convert to Oracle. To be honest, it seems kinda cheesy to me to have to do this on the front end and then send it to Oracle. But if that is the only way it can be done then so be it.

I am interested in what other people might have been able to come up with that accomplish the same thing.

Re: VS, ODAC, Procedures, & SELECT statement [message #654432 is a reply to message #654431] Tue, 02 August 2016 19:51 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+ref+cursor+example+c%23
Re: VS, ODAC, Procedures, & SELECT statement [message #654433 is a reply to message #654432] Tue, 02 August 2016 21:07 Go to previous messageGo to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
Thanks, I'll check this out.
Re: VS, ODAC, Procedures, & SELECT statement [message #654836 is a reply to message #654433] Mon, 15 August 2016 14:06 Go to previous messageGo to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
Just got back to this...thought I would share what I came up with. After looking at many examples...some of them being very bizarre and way to much work...I narrowed it down to exactly what I needed to retrieve data using a stored procedure. I have found most Oracle documentation gives you way more information than you need. It is sometimes not easy to pick out exactly what you do need.

Hope this helps someone out. I felt like I spent way to much time looking for an answer on this one. I know it took me a few tries to get it to work.

Stored Procedure...
create or replace PROCEDURE SPVARIANCE_GETROUTE
(
  p_YearMonth VARCHAR2,
  p_Cycle int,
  p_RefCursor OUT SYS_REFCURSOR
)
AS 
BEGIN
  OPEN p_RefCursor FOR
    SELECT DISTINCT Route
    FROM HWL_Variance
    WHERE YearMonth = p_YearMonth AND Cycle = p_Cycle
    ORDER BY Route;
  
END SPVARIANCE_GETROUTE;

C# Procedure...
public static List<HWL_Variance> GetRoute(string yearMonth, int cycle)
        {
            List<HWL_Variance> routeList = new List<HWL_Variance>();

            OracleConnection connection = HWLOracleDB.GetConnection();
            OracleCommand selectCommand = new OracleCommand("SPVARIANCE_GETROUTE", connection);
            selectCommand.CommandType = CommandType.StoredProcedure;

            selectCommand.Parameters.Add(new OracleParameter("p_YearMonth", yearMonth));
            selectCommand.Parameters.Add(new OracleParameter("p_Cycle", cycle));

            OracleParameter p_RefCursor = new OracleParameter();
            p_RefCursor.OracleDbType = OracleDbType.RefCursor;
            p_RefCursor.Direction = System.Data.ParameterDirection.Output;
            selectCommand.Parameters.Add(p_RefCursor);

            try
            {
                connection.Open();

                OracleDataReader reader = selectCommand.ExecuteReader();

                while (reader.Read())
                {
                    HWL_Variance route = new HWL_Variance();
                    route.Route = Convert.ToInt32(reader["Route"]);
                    routeList.Add(route);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                connection.Close();
            }

            return routeList;
        }

Re: VS, ODAC, Procedures, & SELECT statement [message #657496 is a reply to message #654836] Thu, 10 November 2016 03:50 Go to previous messageGo to next message
Flyby
Messages: 181
Registered: March 2011
Location: Belgium
Senior Member
Don't forget to set BindByName=true for the OracleCommand object or you're in for a surprise when the parameters are not in order.
selectCommand.BindByName=true
The oracle deemed it wise to set as default "false" (bind by position)

[Updated on: Thu, 10 November 2016 03:51]

Report message to a moderator

Re: VS, ODAC, Procedures, & SELECT statement [message #657504 is a reply to message #654836] Thu, 10 November 2016 08:39 Go to previous message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
Yes..I had already figured that out...thanks
Previous Topic: [windows] srvctl : how to change the output language.
Next Topic: Difference between 32 and 64-bit Windows
Goto Forum:
  


Current Time: Sat Dec 16 16:54:34 CST 2017

Total time taken to generate the page: 0.19927 seconds