System.Data.OracleClient – Oracle Stored Procedure + Parameter + Cursor 方法

System.Data.OracleClient – Oracle Stored Procedure + Parameter + Cursor 方法

 
   using System;
   using System.Collections.Generic;
   using System.ServiceModel;
   using System.ServiceModel.Web;
   using Oracle.DataAccess;
   using Oracle.DataAccess.Client;
   using System.Data;
 
   [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Xml, UriTemplate = "player/team/{id}")]
   public List<PLAYER_VIEW> GetAllPlayerByTeam(string id)
   {
      try
      {
 
         List<PLAYER_VIEW> list = new List<PLAYER_VIEW>();
         PLAYER_VIEW player;
 
         String connectionString = "Data Source=(DESCRIPTION="
                                              + "(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.76)(PORT=1521))"
                                              + "(CONNECT_DATA=(SERVICE_NAME=orcl)));"
                                              + "User Id=APPUSER1;Password=Passw0rd;";
 
         String queryString = "PLAYER_TEAM_CURSOR";
 
         OracleConnection OrclConnection = new OracleConnection();
 
         OrclConnection.ConnectionString = connectionString;
 
         OracleCommand OrclORACCommand = OrclConnection.CreateCommand();
 
         OrclORACCommand.CommandText = queryString;
 
         OrclORACCommand.CommandType = System.Data.CommandType.StoredProcedure;
 
         OrclORACCommand.Parameters.Add("TEAMID", OracleDbType.Int32).Value = Int32.Parse(id);
         OrclORACCommand.Parameters.Add("PLAYERREC", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
 
         OrclConnection.Open();
 
         OracleDataReader OrclDataReader = OrclORACCommand.ExecuteReader();
 
         System.Data.DataTable dt = new System.Data.DataTable();
         dt.Load(OrclDataReader);
 
         foreach (System.Data.DataRow dr in dt.Rows)
         {
            player = new PLAYER_VIEW();
 
            player.PlayerID = dr[0].ToString();
            player.Player = dr[1].ToString();
            player.Position = dr[2].ToString();
            player.Nation = dr[4].ToString();
            player.Team = dr[3].ToString();
            player.Age = dr[5].ToString();
 
            list.Add(player);
         }
 
         OrclDataReader.Close();
 
         OrclConnection.Close();
 
         return list;
 
      }
      catch (Exception ex)
      {
         return new List<PLAYER_VIEW>();
      }
 
   }