A standard datareader to object sample web service

In one of my BlackBerry application, the requirements need to use MDS studio with background Oracle database, To push a table based query result present in blackberry smartphone screen.

Considering the MDS studio doen't support XML based dataset parsing and serializing. I have to think about other solution.

The MDS mainstream developing model is that invoke web service to generate a WSDL reference interface and then by MDS Data definitions and Message inbound/outbound mechanism to generate application.

Here is an example to get Oracle table to push up an object based data structure for MDS refer to. I feels like this is best solutino currently.

Originally, LINQ plus Generic solution is a good solution. But currently the Oracle still doesn't support LINQ without third part middleware supportings. Hope Microsoft can push out an solution with LINQ using on Oracle.

using System;using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.OracleClient;
using System.Collections.Generic;

namespace HourlySalesByObject
{
///

/// Summary description for GetHourlySalesByStoreObject
/// [WebService(Namespace = "http://services/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class
GetHourlySalesByStoreObject : System.Web.Services.WebService
{
[WebMethod] public List GetHourlySalesByStore_Sunday(int store_num)
{
List hourlySales = new List();
using (OracleConnection conn = new OracleConnection("Data Source=RTKTEST;Persist Security Info=True;User ID=korgiej;Password=korgiej;Unicode=True"))
{
OracleCommand cmd = conn.CreateCommand();
try
{
conn.Open();
cmd.CommandText =
"select * from (select STORE, EOW_DATE, HOUR, SUNDAY_TRANSACTIONS,
SUNDAY_AV_DOLLARS, SUNDAY_TOTAL_DOLLARS from PAM_HOURLY_SALES where STORE =" + store_num + " order by EOW_DATE desc) where rownum <= 50";

cmd.CommandType = CommandType.Text;

OracleDataAdapter da = new OracleDataAdapter(cmd);

//DataSet ds = new DataSet();
//ds.Clear();
//da.Fill(ds);
//conn.Close();

using (OracleDataReader odr = cmd.ExecuteReader())
{
while
(odr.Read())
{
HourlySale hourlySale = new HourlySale();
hourlySale.store = odr["STORE"].ToString();
hourlySale.eow_date = Convert.ToDateTime(odr["EOW_DATE"]);
hourlySale.hour = Convert.ToInt16(odr["HOUR"]);
hourlySale.transactions = Convert.ToDecimal(odr["SUNDAY_TRANSACTIONS"]);
hourlySale.av_dollars = Convert.ToDecimal(odr["SUNDAY_AV_DOLLARS"]);
hourlySale.total_dollars = Convert.ToDecimal(odr["SUNDAY_TOTAL_DOLLARS"]);
hourlySales.Add(hourlySale);
}
//List hourlySales = new List();
//foreach (DataRow row in ds.Tables[0].Rows)
//{
//hourlySale.store = row["STORE"].ToString();
//hourlySale.eow_date = Convert.ToDateTime(row["EOW_DATE"]);
//hourlySale.hour = Convert.ToInt16(row["HOUR"]);
//hourlySale.transactions = Convert.ToDecimal(row["SUNDAY_TRANSACTIONS"].ToString());
//hourlySale.av_dollars = Convert.ToDecimal(row["SUNDAY_AV_DOLLARS"].ToString());
//hourlySale.total_dollars = Convert.ToDecimal(row["SUNDAY_TOTAL_DOLLARS"].ToString());
// }
}
}
catch (Exception e)
{ hourlySales = null; }
}
return hourlySales;
}
}

public class HourlySale
{
public
HourlySale() {}
public string store { get; set; }
public DateTime eow_date { get; set; }
public Int16 hour { get; set; }
public Decimal transactions { get; set; }
public Decimal av_dollars { get; set; }
public Decimal total_dollars { get; set; }
}
}