I’ve recently started working with the OData format and specifically Microsoft’s implementation of OData which is WCF Data Services, formerly known as ADO.NET Data Services and formerly formerly known as Astoria. From a high level point of view, to return or expose data in OData format we need to create objects that implement the IQueryable interface. The idea is to have the WCF service expose these objects to the calling consumer.

For this example I’m using VS Web Developer Express 2010 where I have a very simple WCF Data Service hosted in a console app (thanks to http://www.bizcoder.com). It’s returning an IQuerable collection of a simple ‘Study’ class from a repository (located in a separated dll project), which will eventually retrieve ‘Study’ classes from a db project in a third dll – for this demo the repository is dummying up the data.

The Study class has some normal properties such as Id, Name, etc and also a child class called ‘Page’ implemented as a virtual IQueryable collection. To get the code below up and running

– create a standard dll project in Visual Studio called MyStudyService, and set the output type to Console app in the project properties.

– add a second project of type class library to the solution and call it MyStudyRepository

– add a third and final project of type class library to the solution and call it MyStudyDB

Set the console app containing the service project as the startup project. This will expose the ‘Study’ data and associated ‘Page’ items. To retrieve data from the service (or from any public OData service for that matter) we can use a great piece of software called LinqPad written by Joseph Albahari. Not only will this piece of kit query OData sources, it will also query standard databases and run free-form C# expressions and statements into the bargain..all for free. You can also query the data by typing the url specified in the service in a browser:

http://localhost:123/Studies

To get the first Study object in the collection:

http://localhost:998/Studies(1)

To get the Page object of the first Study object:

http://localhost:998/Studies(1)/Pages

So now for the code – here’s the simple service that’s hosted in a console app:


using MyStudyRepository;
using MyStudyDB;

namespace MyStudyService
{
    public class Program
    {
        public static void Main(string[] args)
        {
            string serviceAddress = "http://localhost:123";
            Uri[] uriArray = { new Uri(serviceAddress) };
            Type serviceType = typeof(StudyDataService);

            using (var host = new DataServiceHost(serviceType,uriArray))
            {
                host.Open();
                Console.WriteLine("Press any key to stop service");
                Console.ReadKey();
            }
        }
    }

    public class StudyDataService : DataService<StudyRepository>
    {
        public static void InitializeService(IDataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
        }
    }
}

Here’s the repository:


using MyStudyDB;
using MyStudyDB.Entities;

namespace MyStudyRepository
{
    public class StudyRepository : IRepository<Study>
    {
        List<Study> _listStudies = new List<Study>();
        List<Page> _listPages = new List<Page>();

        //Add data to populate myStudies list on creation of class
        public StudyRepository()
        {
            CreateStudies();
        }

        public IQueryable<Page> Pages
        {
            get
            {
                return _listPages.AsQueryable<Page>();
            }
        }

        public IQueryable<Study> Studies
        {
            get
            {
                return _listStudies.AsQueryable<Study>();
            }
        }

        public Study GetById(int itemId)
        {
            return _listStudies.SingleOrDefault(s => s.ID == itemId);
        }

        public Study GetByName(string itemName)
        {
            return _listStudies.FirstOrDefault(s => s.StudyName == itemName);
        }

        public void Add(Study item)
        {
            throw new NotImplementedException();
        }

        public void Update(Study item)
        {
            throw new NotImplementedException();
        }

        public void Delete(int itemId)
        {
            throw new NotImplementedException();
        }

        public IList<Study> List()
        {
            throw new NotImplementedException();
        }

        private void CreateStudies()
        {
            for (int i = 1; i < 5; i++)
            {
                Study myStudy = new Study()
                {
                    ID = i,
                    StudyOwnerId = i,
                    StudyName = "Study" + i.ToString(),
                    Pages = new List<Page>() {
                        new Page()
                        {
                            ID = i,
                            Name = "Page " + i.ToString(),
                            StudyId = i
                        }
                    }.AsQueryable()
                };

                myStudy.Pages.First().Study = myStudy;
                _listStudies.Add(myStudy);
            }
        }
    }
}

And finally here’s the model:

namespace MyStudyDB.Entities
{
    public class Study : IStudy
    {
        public int ID { get; set;}
        public int StudyOwnerId { get; set; }
        public string StudyName { get; set; }
        public virtual IQueryable<Page> Pages { get; set; }
    }

    public class Page : IPage
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int StudyId { get; set; }
        public virtual Study Study { get; set; }
    }
}
Advertisements

After uploading data from a spreadsheet into an Oracle table it turned out there were duplicate records in the data that I wasn’t expecting. It’s quite easy to select unique records using ‘distinct’, but it’s a bit trickier to select duplicates which requires the use of the ‘having’ clause.  The duplicate field was called sec_code so here’s the code I used:

select * from SECURITIES where SEC_CODE IN(

select SEC_CODE from SECURITIES
group by SEC_CODE
having ( COUNT(SEC_CODE) > 1 )
) order by sec_code;

I was returning some records in a web form from a database table through a WCF method. The table had 3 records and everything was working fine. I added a couple of hundred records to the table and went home for the evening, happy that I got the page working. However the next day when I tried to retrieve the records again I got an error:

The maximum message size quota for incoming messages (65536) has been exceeded. To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.

So after a bit of searching I found the answer. The default settings for max buffer size and max receive message size are 65536. To get over the error I needed to up these settings to a number larger than the size of the message being returned. In the client web.config file I changed the following values:

maxBufferSize=”65535″  and maxReceivedMessageSize=”65535″

to:

maxBufferSize=”5000000″  maxReceivedMessageSize=”5000000″

The reason this happend is that I am using paging on my other web forms and only returning 25 records per page, so the receive message size was ever that big. However the web form it happened on doesn’t have paging so when a couple of hundred records get inserted into the database the thing fell over. I had also read that these values need to be set in the service confif file also, but so far I haven’t done this – setting them just on the client worked.

Now and again we all come across situations where we need to save multiple records to a database, and the easy way is to set up a loop and insert the records one at a time. However there is a way to insert multiple records at once  using ODP.NET (also works for DDTek). It involves creating arrays for the parameters where you would normally have single values, and setting the ArrayBindCount of the command object to the number of elements in the array. Sample code:


public bool QueueEmail(EmailQueueItemCollection coll)
{
  bool retVal = false;
  OracleCommand cmd = new OracleCommand("CAEProc.CAE_FOF_EMAILQUEUE_PKG.QueueItem", conn);
  cmd.CommandType = CommandType.StoredProcedure;

  string[] emails = new string[coll.Count];
  string[] userids = new string[coll.Count];
  int[] emaildefs = new int[coll.Count];
  long[] secids = new long[coll.Count];

  for (int i = 0; i < coll.Count; i++ )
  {
    emails[i] = coll[i].EmailAddress;
    userids[i] = coll[i].UserId;
    emaildefs[i] = 1;
    secids[i] = coll[i].SecID;
  }

  //set the number of elements we're passing in
  cmd.ArrayBindCount = coll.Count;

  OracleParameter p_emailAddress = new OracleParameter("p_emailAddress_c", OracleDbType.VarChar);
  p_emailAddress.Direction = ParameterDirection.Input;
  p_emailAddress.Value = emails;
  cmd.Parameters.Add(p_emailAddress);

  OracleParameter p_userId = new OracleParameter("p_user_id_c", OracleDbType.VarChar);
  p_userId.Direction = ParameterDirection.Input;
  p_userId.Value = userids;
  cmd.Parameters.Add(p_userId);

  OracleParameter p_email_def_id = new OracleParameter("p_email_def_id", OracleDbType.Number);
  p_email_def_id.Direction = ParameterDirection.Input;
  p_email_def_id.Value = emaildefs;
  cmd.Parameters.Add(p_email_def_id);

  OracleParameter p_SECID = new OracleParameter("p_cae_sec_id", OracleDbType.Number);
  p_SECID.Direction = ParameterDirection.Input;
  p_SECID.Value = secids;
  cmd.Parameters.Add(p_SECID);

  OracleParameter p_result_n = new OracleParameter("p_result_n", OracleDbType.Int32);
  p_result_n.Direction = ParameterDirection.Output;
  cmd.Parameters.Add(p_result_n);

  conn.Open();

  // Make the Command object a SelectCommand for DataAdapter
  try
  {
    int recordsInserted = cmd.ExecuteNonQuery();
    int tmpInt = 0;
    retVal = (recordsInserted > 0);
  }
  catch (Exception ex)
  {
    LoggingHelper.Log(ex, "Exception when adding emails to queue");
  }
  finally
  {
    conn.Close();
  }

  return retVal;
}

I’ve been working on a multi tier WCF project for the past few months. One of the things we have to do as part of the saving and retrieving of data is translate a data contract to a business entity which can be used by the business tier and vice versa. To do this I created static translation classes for each entity to translate between the types, here’s a basic sample:


public static class SecurityTranslator
{
  public static Business.Security Translate(DataContract.Security from)
  {
    Business.Security to = new Business.Security();
    to.SecID = from.SecID;
    to.SecName = from.SecName;
    to.SecDesc = from.SecDesc;

    return to;
  }

  public static DataContract.Security Translate(Business.Security from)
  {
    DataContract.Security to = new DataContract.Security();
    to.SecID = from.SecID;
    to.SecName = from.SecName;
    to.SecDesc = from.SecDesc;

    return to;
  }
}

The Oracle tool of choice where I currently work is SQL Developer. While it’s good in a lot of ways it’s difficult to output the results of a proc that returns a sys_refcursor. I finally managed to do it after a couple of hours tweaking. It involves creating an output record structure that matches exactly the column structure of the records being returned. Here’s a cut down version of my proc:


open p_fof_sec_refcur for

  SELECT *
  FROM(
    SELECT securities.*, rownum rnum, v_total_count
    FROM
    (
      SELECT
      CFS.CAE_SEC_ID,
      CFS.FM_SEC_CODE,
      ...
      FROM
      CAEDBO.CAE_FOF_SECURITY CFS
      INNER JOIN caedbo.CAE_DATA_SET_ELEMENT CDSE_STAT
      ON (CDSE_STAT.DATA_SET_ELEMENT_ID = CFS.APPR_STATUS)
      ...
      WHERE APPR_STATUS = NVL(p_appr_status, APPR_STATUS)
      ...
    ) securities
  )
  WHERE rnum between v_pgStart and v_pgEnd;

In SQL Developer when viewing the proc, right click and choose ‘Run’ or select Ctrl+F11 to bring up the Run PL/SQL window. This creates a template with the input and output params which you need to modify. The proc returns a sys_refcursor and the tricky part for me was declaring a row type that is exactly equivalent to the select stmt / sys_refcursor being returned by the proc.


  DECLARE
  P_CAE_SEC_ID_N NUMBER;
  P_FM_SEC_CODE_C VARCHAR2(200);
  P_PAGE_INDEX NUMBER;
  P_PAGE_SIZE NUMBER;
  v_Return sys_refcursor;
  type t_row is record (CAE_SEC_ID NUMBER,FM_SEC_CODE VARCHAR2(7),
    rownum number, v_total_count number);
  v_rec t_row;

  BEGIN
  P_CAE_SEC_ID_N := NULL;
  P_FM_SEC_CODE_C := NULL;
  P_PAGE_INDEX := 0;
  P_PAGE_SIZE := 25;

  CAE_FOF_SECURITY_PKG.GET_LIST_FOF_SECURITY(
  P_CAE_SEC_ID_N => P_CAE_SEC_ID_N,
  P_FM_SEC_CODE_C => P_FM_SEC_CODE_C,
  P_PAGE_INDEX => P_PAGE_INDEX,
  P_PAGE_SIZE => P_PAGE_SIZE,
  P_FOF_SEC_REFCUR => v_Return
  );

  loop
    fetch v_Return into v_rec;
    exit when v_Return%notfound;
    DBMS_OUTPUT.PUT_LINE('sec_id = ' || v_rec.CAE_SEC_ID
      || 'sec code = ' ||v_rec.FM_SEC_CODE);
  end loop;

  END;

%d bloggers like this: