ASP.NET MVC 4 Web API + Oracle.DataAccess.Client ( Data Access Object – Cursor Procedure ) 方法

ASP.NET MVC 4 Web API + Oracle.DataAccess.Client ( Data Access Object – Cursor Procedure ) 方法

   web.conf ( Project : SoccerWebAPIODAC )
 
   <?xml version="1.0" encoding="utf-8"?>
 
   <configuration>
 
      <appSettings>
 
         … …
 
         <add key="OracleHost" value="192.168.1.76" />
         <add key="OraclePort" value="1521" />
         <add key="OracleServiceName" value="orcl" />
         <add key="OracleSchemaName" value="Soccer" />
         <add key="OracleSchemaPasswd" value="xxxxxx" />
 
      </appSettings>
 
      … …
 
   </configuration>
 
   \Entity\Player.cs ( Project : SoccerDAO )
 
   using System;
   using Newtonsoft.Json.Linq;
 
   namespace SoccerDAO.Entity
   {
      public class Player
      {
         public int PlayerID { get; set; }
         public string PlayerName { get; set; }
         public string PlayerPosition { get; set; }
         public string PlayerNation { get; set; }
         public string PlayerTeam { get; set; }
         public int PlayerAge { get; set; }
 
         public Player(int PlayerID, string PlayerName, string PlayerPosition, string PlayerNation, string PlayerTeam, int PlayerAge)
         {
            this.PlayerID = PlayerID;
            this.PlayerName = PlayerName;
            this.PlayerPosition = PlayerPosition;
            this.PlayerNation = PlayerNation;
            this.PlayerTeam = PlayerTeam;
            this.PlayerAge = PlayerAge;
         }
 
         public Player()
         {
         }
 
         public JObject getJSONObject()
         {
            JObject obj = new JObject();
 
            obj.Add("PlayerID", this.PlayerID);
            obj.Add("PlayerName", this.PlayerName);
            obj.Add("PlayerPosition", this.PlayerPosition);
            obj.Add("PlayerNation", this.PlayerNation);
            obj.Add("PlayerTeam", this.PlayerTeam);
            obj.Add("PlayerAge", this.PlayerAge);
 
            return obj;
         }
      }
   }
 
   \DAO\GenericDAO.cs ( Project : SoccerDAO )
 
   using System;
   using System.Collections.Generic;
   using System.Configuration;
   using System.Text;
   using Oracle.DataAccess.Client;
   using Newtonsoft.Json.Linq;
   using SoccerDAO.Entity;
 
   namespace SoccerDAO.DAO
   {
      public class GenericDAO
      {
         private string OracleHost = ConfigurationManager.AppSettings["OracleHost"].ToString();
         private string OraclePort = ConfigurationManager.AppSettings["OraclePort"].ToString();
         private string OracleServiceName = ConfigurationManager.AppSettings["OracleServiceName"].ToString();
         private string OracleSchemaName = ConfigurationManager.AppSettings["OracleSchemaName"].ToString();
         private string OracleSchemaPasswd = ConfigurationManager.AppSettings["OracleSchemaPasswd"].ToString();
 
         internal OracleConnection OrclConnection;
         internal OracleCommand OrclORACCommand;
         internal OracleDataReader dr;
 
         internal void InitializateConnection(string QueryStatement)
         {
            this.OrclConnection = this.getOracleConnection();
            this.OrclORACCommand = this.OrclConnection.CreateCommand();
            this.OrclORACCommand.CommandText = QueryStatement;
            this.OrclConnection.Open();
            this.dr = OrclORACCommand.ExecuteReader();
         }
 
         internal void InitializateConnection(string QueryStatement, List<Parameter> parameterlist)
         {
            this.OrclConnection = this.getOracleConnection();
            this.OrclORACCommand = this.OrclConnection.CreateCommand();
            this.OrclORACCommand.CommandText = QueryStatement;
            this.OrclORACCommand.CommandType = System.Data.CommandType.StoredProcedure;
 
            foreach (Parameter item in parameterlist)
            {
               if (item.Type.Equals(OracleDbType.RefCursor)) {
                  this.OrclORACCommand.Parameters.Add(item.Name, item.Type).Direction = (System.Data.ParameterDirection)item.Value;
               } else {
                  this.OrclORACCommand.Parameters.Add(item.Name, item.Type).Value = item.Value;
               }
            }
 
            this.OrclConnection.Open();
            this.dr = OrclORACCommand.ExecuteReader();
         }
 
         internal void CloseConnection()
         {
            this.dr.Close();
            this.OrclORACCommand.Dispose();
            this.OrclConnection.Close();
         }
 
         internal OracleConnection getOracleConnection()
         {
            StringBuilder connectionString = new StringBuilder();
 
            connectionString.Append("Data Source=(DESCRIPTION=");
            connectionString.Append("(ADDRESS=(PROTOCOL=tcp)(HOST=" + OracleHost + ")(PORT=" + OraclePort + "))");
            connectionString.Append("(CONNECT_DATA=(SERVICE_NAME=" + OracleServiceName + ")));");
            connectionString.Append("User Id=" + OracleSchemaName + ";Password=" + OracleSchemaPasswd + ";");
 
            return new OracleConnection(connectionString.ToString());
         }
 
         public class Parameter
         {
            public Parameter(string Name, OracleDbType Type, Object Value)
            {
               this.Name = Name;
               this.Type = Type;
               this.Value = Value;
            }
 
            public string Name { get; set; }
            public OracleDbType Type { get; set; }
            public Object Value { get; set; }
         }
      }
   }
 
   \DAO\PlayerDAO.cs ( Project : SoccerDAO )
 
   using System;
   using System.Collections.Generic;
   using SoccerDAO.Entity;
   using Newtonsoft.Json.Linq;
   using Oracle.DataAccess.Client;
   using System.Data;
 
   namespace SoccerDAO.DAO
   {
      public class PlayerDAO : GenericDAO
      {
         public JObject getPlayerByID(int PlayerID)
         {
            try
            {
               Player player = new Player();
 
               String QueryStatement = "SELECT * FROM PLAYER_VIEW WHERE \"PlayerID\" = " + PlayerID.ToString();
 
               this.InitializateConnection(QueryStatement);
 
               while (this.dr.Read())
               {
                  player.PlayerID = Int32.Parse(dr["PlayerID"].ToString());
                  player.PlayerName = dr["Player"].ToString();
                  player.PlayerPosition = dr["Position"].ToString();
                  player.PlayerNation = dr["Nation"].ToString();
                  player.PlayerTeam = dr["Team"].ToString();
                  player.PlayerAge = Int32.Parse(dr["Age"].ToString());
               }
 
               this.CloseConnection();
 
               return player.getJSONObject();
            }
            catch (Exception ex)
            {
               Console.WriteLine(ex.Message);
               return null;
            }
         }
 
         public JArray getPlayerByTeam(int TeamID)
         {
            try
            {
               Player player = new Player();
 
               JArray playerary = new JArray();
 
               String QueryStatement = "PLAYER_TEAM_CURSOR";
 
               List<Parameter> list = new List<Parameter>();
               list.Add(new Parameter("TEAMID", OracleDbType.Int32, TeamID));
               list.Add(new Parameter("PLAYERREC", OracleDbType.RefCursor, ParameterDirection.Output));
 
               this.InitializateConnection(QueryStatement, list);
 
               while (this.dr.Read())
               {
                  player.PlayerID = Int32.Parse(dr[0].ToString());
                  player.PlayerName = dr[1].ToString();
                  player.PlayerPosition = dr[2].ToString();
                  player.PlayerNation = dr[5].ToString();
                  player.PlayerTeam = dr[3].ToString();
                  player.PlayerAge = Int32.Parse(dr[7].ToString());
 
                  playerary.Add(player.getJSONObject());
               }
 
               this.CloseConnection();
 
               return playerary;
            }
            catch (Exception ex)
            {
               Console.WriteLine(ex.Message);
               return null;
            }
         }
      }
   }
 
   \Library\SoccerWebAPIHelper.cs ( Project : SoccerWebAPIODAC )
 
   using System;
   using System.Web.Http;
   using SoccerDAO.DAO;
 
   namespace SoccerWebAPIODAC.Library
   {
      public class SoccerWebAPIHelper : ApiController
      {
         internal PlayerDAO playerDAO;
         public SoccerWebAPIHelper()
         {
            this.playerDAO = new PlayerDAO();
         }
      }
   }
 
   \Controllers\PlayerController.cs ( Project : SoccerWebAPIODAC )
 
   using System;
   using System.Web.Http;
   using SoccerWebAPIODAC.Library;
   using SoccerDAO.DAO;
   using Newtonsoft.Json.Linq;
 
   namespace SoccerWebAPIODAC.Controllers
   {
      public class PlayerController : SoccerWebAPIHelper
      {
         [Route("rest/player/nation/{id}")]
         public JArray GetNation(int id)
         {
            try
            {
               return this.playerDAO.getPlayerByNation(id);
            }
            catch(Exception ex)
            {
               return JException.JArrayException(1, ex.Message);
            }
         }
 
         [Route("rest/player/team/{id}")]
         public JArray GetTeam(int id)
         {
            try
            {
               return this.playerDAO.getPlayerByTeam(id);
            }
            catch(Exception ex)
            {
               return JException.JArrayException(1, ex.Message);
            }
         }
 
         … …
      }
   }