Web Service with Oracle Database

Question

Hi We are developing an android app to display some data from an Oracle database. The data is required to be exposed as a web service . I have chosen WEB API for the same. The data from the oracle database is taken as an inner Join from 12 tables. Each table having approximately 100 to 120 fields In order to get the data to my web service should I create a model for all the fields in the oracle table. This is my first web service using WEB API. Can somebody help? regards Raj

Rajesh Moorthy · Answer

Thanks to all for the timely help I had made a mistake of copying the code as it is from the web. I found the issue to be that I was declaring the list outside the finction and the list was maintaining the values each time it was called.. I removed the word private and moved the line highlighted inside the iEnumerable classs and DONE!!! Lesson learnt understand the code before implementing!! :) public class ConsumerList { private List consumer = new List (); string text = string.Empty; public IEnumerable GetConsumerBillDetails(string ConsumerNo) { DataSet dataSet = new DataSet(); using (OracleConnection oCon = new OracleConnection(ConfigurationManager.AppSettings[ConnString].ToString())) { OracleCommand cmd = new OracleCommand(WSS_GETBILL_DETAILS); public class ConsumerList { string text = string.Empty; public IEnumerable GetConsumerBillDetails(string ConsumerNo) { List consumer = new List (); DataSet dataSet = new DataSet(); using (OracleConnection oCon = new OracleConnection(ConfigurationManager.AppSettings[ConnString].ToString())) { OracleCommand cmd = new OracleCommand(WSS_GETBILL_DETAILS);

Rajesh Moorthy · Answer

Thanks Bikesh I wanted to write a web service with oracle as a back end. I have been able to do that. I only have one small problem now. Every time I run this web service the number f records keeps multiplying. In other words if no of records returned is 1 in the first run. It becomes 2 in the second 3 in the third so on. My code In the Model public class Consumer { public string ConsumerNumber { get; set; } public string MobileNumber { get; set; } } namespace mWssServiceRest.Models { public class ConsumerList { private List consumer = new List (); string text = string.Empty; public IEnumerable GetConsumerBillDetails(string ConsumerNo) { DataSet dataSet = new DataSet(); using (OracleConnection oCon = new OracleConnection(ConfigurationManager.AppSettings[ConnString].ToString())) { OracleCommand cmd = new OracleCommand(WSS_GETBILL_DETAILS); cmd.Connection = oCon; try { oCon.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(P_SERVICE_NO OracleDbType.Varchar2).Value = ConsumerNo; cmd.Parameters.Add(V_SREFCUR OracleDbType.RefCursor).Direction = ParameterDirection.Output; OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(cmd); oracleDataAdapter.Fill(dataSet); foreach (DataRow dr in dataSet.Tables[0].Rows) { consumer.Add(new Consumer() { ConsumerNumber = dr[0].ToString() MobileNumber = dr[1].ToString() }); } } catch (Exception ex) { Write2Log(ex.ToString()); return null; } finally { oCon.Close(); } } return consumer; } Controller public class ConsumerController : ApiController { static readonly ConsumerList repository = new ConsumerList(); //[HttpGet] //[Route(api/test/{_serviceNo})] public IEnumerable GetServiceDetails(string _serviceNo) { return repository.GetConsumerBillDetails(_serviceNo); } } What am I doing wrong. the output xmlns:i=http://www.w3.org/2001/XMLSchema-instance xmlns=http://schemas.datacontract.org/2004/07/mWssServiceRest.Models > ); background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;> 006014227402 00601422740211032016150038 ); background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;> 006014227402 00601422740211032016150038 Any Help is appreciated regards Rajesh

Rajesh Moorthy · Answer

My c# code returns 1 row. However on each refresh of the browser I get one record added. I am not sure how to proceed. This will work with one. Need to check the impact if multiple users access the same webservice. Thanks in advance

Bikesh Srivastava · Answer

hi i understand your problem but according your code your doing correct. have you check debug value on each step.

Bikesh Srivastava · Answer

Hi Rajesh  Acutally now i am working on webapi with oracle  what is your problem please explain.