wcf调用oracle存储过程

 1 public IList<ACCP_RAIN> QueryAll(string beginTime, string endTime, string type)
 2         {
 3             beginTime = "2012-1-1"; endTime = "2014-10-10"; type = "ZQ";
 4             List<ACCP_RAIN> list = new List<ACCP_RAIN>();
 5             DbDataReader reader = null;
 6 
 7             try
 8             {
 9                 OracleParameter[] paras = new OracleParameter[4];
10                 OracleParameter para1 = new OracleParameter("ptm1", OracleType.VarChar);
11                 para1.Value = beginTime;
12                 OracleParameter para2 = new OracleParameter("ptm2", OracleType.VarChar);
13                 para2.Value = endTime;
14                 OracleParameter para3 = new OracleParameter("retp", OracleType.VarChar);
15                 para3.Value = type;
16                 OracleParameter para4 = new OracleParameter("p_cur", OracleType.Cursor);
17                 para4.Direction = ParameterDirection.Output;
18                 paras[0] = para1;
19                 paras[1] = para2;
20                 paras[2] = para3;
21                 paras[3] = para4;
22                 
23                 DbConnection conn = DbFactory.GetDbCon();
24                 conn.Open();
25                 DbTransaction trans = conn.BeginTransaction();
26                 reader = (DbHelper as OracleHelper).ExecuteReader(conn, trans, CommandType.StoredProcedure, "GETDRP.PROC_GETDRP", paras);
27                 while (reader.Read())
28                 {
29                     list.Add(SqlDataToEntity.ToEntity<ACCP_RAIN>(reader));
30                 }
31                 trans.Dispose();
32                 conn.Close();
33                 conn.Dispose();
34             }
35             catch (Exception ex)
36             {
37                 return null;
38             }
39             finally
40             {
41                 if (reader != null) reader.Close();
42             }
43             return list;
44         }

View Code

  public DbDataReader ExecuteReader(DbConnection connection, DbTransaction trans, CommandType commandType, string commandText, params DbParameter[] cmdParms)
        {
            DbDataReader reader2;
            OracleCommand cmd = new OracleCommand();
            if ((cmdParms != null) && (cmdParms.Length == 0))
            {
                cmdParms = null;
            }
            try
            {
                PrepareCommand((OracleConnection)connection, (OracleTransaction)trans, cmd, commandType, commandText, (OracleParameter[])cmdParms);
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                reader2 = reader;
            }
            catch (Exception exception)
            {
                connection.Dispose();
                throw exception;
            }
            finally
            {
                cmd.Dispose();
            }
            return reader2;
        }

View Code

 private static void PrepareCommand(OracleConnection connection, OracleTransaction trans, OracleCommand cmd, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
        {
            if (trans == null)
            {
                cmd.Connection = connection;
            }
            else
            {
                cmd.Connection = trans.Connection;
                cmd.Transaction = trans;
            }
            if (cmd.Connection.State != ConnectionState.Open)
            {
                cmd.Connection.Open();
            }
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;
            cmd.CommandTimeout = 0;
            if (cmdParms != null)
            {
                foreach (OracleParameter parameter in cmdParms)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
        }

View Code