Web Service with Oracle Database

Rajesh Moorthy

10y935

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

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajesh Moorthy10y ago
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 Moorthy10y ago
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 Srivastava10y ago
hi,
i understand your problem ,but according your code your doing correct.
have you check debug value on each step.
Rajesh Moorthy10y ago
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
Bikesh Srivastava10y ago
Hi Rajesh ,
Acutally now i am working on webapi with oracle ,
what is your problem please explain.