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); } } … … } } |