| VS, ODAC, Procedures, & SELECT statement [message #654420] |
Tue, 02 August 2016 09:33  |
 |
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 #654836 is a reply to message #654433] |
Mon, 15 August 2016 14:06  |
|
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;
}
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
