title: Examples of C# OO design for data access
body: <p>I am using the MS Enterprise Library 5.0 (data access blocks) for the main data layer of my application.</p>
  
  <p>Although I understand the basics of OO (yes, I need to keep learning - contantly!), I am trying to work through understanding where/why/how to make use of good design, i.e. not repeating code un-necessarily etc, and also still trying to keep the code simple to read and of course debug.</p>
  
  <p>So first of the bat, I have the following class and default/example method:
  ( p.s: the return value from the db/proc is XML )</p>
  
  <pre><code>public class ajaxget
  {
      public enum outputType : int { JSON = 0, XML = 1 }
  
      public static string getMemberContacts(string sStartsWith, string sEndswith, outputType eOT)
      {
          //  Get the associated members based upon the criteria
          Database db = DatabaseFactory.CreateDatabase("MyDatabase");
          DbCommand cmd = db.GetStoredProcCommand("get_memberContactsXML");
          db.AddInParameter(cmd, "@memberID", DbType.Int64, Convert.ToInt64(sID));
          db.AddInParameter(cmd, "@startsWith1", DbType.String, sStartsWith);
          db.AddInParameter(cmd, "@startsWith2", DbType.String, sEndswith);
          IDataReader dr = db.ExecuteReader(cmd);
          StringBuilder sb = new StringBuilder();
          while (dr.Read())
          {
              sb.Append(dr.GetValue(0));
          }
  
          //  Clean up
          dr.Close();
          dr.Dispose();
          cmd.Dispose();
  
          //  What format to return?
          if (eOT == outputType.XML)
          {
              return sb.ToString();
          }
          if (eOT == outputType.JSON)
          {
              XmlDocument xdoc = new XmlDocument();
              xdoc.LoadXml(sb.ToString());
              return JsonConvert.SerializeXmlNode(xdoc);
          }
      }
  }
  </code></pre>
  
  <p>So.. pretty basic so far.  I am just trying to establish a template here of how I should proceed with say more "getMemberContacts" methods.. i.e. should I make a general "get" method which is a virtual method, and override the params?  I know this sounds very remedial and it is!  part of learning I guess.</p>
  
  <p>So in summary, I am trying to of course re-use a "get" method, where the params/proc name is obviously different, however the actual return value (XML in this case, hence the while/append part that concatenates the returning XML blocks) needs to be the same, i.e. where the return can be left as XML or if supplied, JSON can be returned.</p>
  
  <p>I realise this is very basic stuff/concept, but any pointers/ideas would be gratefully received!</p>
  
  <p>David. </p>
  
  <p><strong>--- EDIT ---</strong></p>
  
  <p>Just to show the SQL 2008 XML part as I am curious about the comments about not returning XML straight from SQL - again I understand generally that getting the raw data in order to use it in different ways is a good thing, but in my case all of my front-end framework uses either XML or JSON (JS framework by the way is www.dhtmlx.com which has been awesome).</p>
  
  <p>So the proc from SQL 2008 is:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[get_messagesForMemberXMLByID]
      @memberID as bigint=null,
      @days as int=-7
  AS
  BEGIN
      SET NOCOUNT ON;
  
      /* Setup the starting point (in the past) from when we wish to select the messages */
      Declare @startDate datetime
      set @startDate = DateAdd(d,@days, getdate())
  
      SELECT inboxID as "@id", convert(varchar(12),messageCreated,13) as messageCreated, convert(varchar(8), messageCreated,108) as messageCreatedTime, subject,message, messageOpened, messageFrom, messageFromID
      FROM bizzbox
      WHERE memberID = @memberID
      AND convert(char(8), messageCreated, 112) BETWEEN convert(char(8), @startDate,112) AND convert(char(8), getdate(), 112)
      ORDER BY messageCreated desc
      FOR XML PATH('row'), ROOT('rows')
  END
  </code></pre>
  
  <p>Which the output pulls back as:</p>
  
  <pre><code>&lt;rows&gt;
    &lt;row id="1"&gt;
      &lt;messageCreated&gt;31 Oct 2010 &lt;/messageCreated&gt;
      &lt;messageCreatedTime&gt;21:27:32&lt;/messageCreatedTime&gt;
      &lt;subject&gt;Testing 123&lt;/subject&gt;
      &lt;message&gt;Wibble Bibble!&lt;/message&gt;
      &lt;messageFrom&gt;David&lt;/messageFrom&gt;
      &lt;messageFromID&gt;7&lt;/messageFromID&gt;
    &lt;/row&gt;
  &lt;/rows&gt;
  </code></pre>
  
  <p>.. which is exactly how I need the data formatted for my front-end.</p>
  
  <p>I can see the reasons why not to use this method - i.e. what if I use another framework or need the data in a straight datatable for instance.. I guess I can have a param variable to output as XML or straight tabular form.. or dare I say even two procs.. but again, interested to hear the comments either way...</p>
  
  <p>Thanks again for all the input - it really is very appreciated.</p>