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