CODEWITHSUNDEEP
c#sql-server-2008apihttp-postn-tier-architecture
TelJanini
TelJanini

Reputation: 835

Creating objects vs. SELECTing directly from database

I'm currently working on an integration project to connect two disparate systems.
My plan is to setup an HTTP-API to allow System A to issue commands through HTTP-POST to System B.
The commands will be used to issue CRUD instructions to a SQL server database to retrieve and update membership card data (e.g. 'create membership', 'update membership', etc.) and then return the data to System A as XML. I know n-tier design dictates that I should have a MembershipCard class with several properties: 

public class MembershipCard
{
    private string number;
    private decimal points;

    public string Number
    {
        get { return number; }
        set { number = value; }
    }

    public decimal Points
    {
        get { return points; }
        set { points = value; }
    }

Along with several methods that make calls to the DAL: 

    public string GetPoints(string cardnumber) 
    {
        return MembershipCardDB.BalanceRequest(cardnumber);
    }

However, I'm having some difficulty justifying this approach as the static DAL class, MembershipCardDB seems to perform all the work that I need (seen below): 

    public static string BalanceRequest(string cardNumber)
    {
        string response = string.Empty;
        string sqlselect = 
            "SELECT Balance                         " +
            "FROM tbl_MemberShipCard                " +
            "WHERE Card_No = @cardNumber                 " +
            "FOR XML PATH ('Card'), ROOT('CardBalance')";

        using (SqlConnection connect = new SqlConnection("Data Source=TEST\\TEST;Initial Catalog=TEST;User Id=sa;Password=TEST"))
        {
            connect.Open();
            using (SqlCommand command = new SqlCommand(sqlselect))
            {
                command.Parameters.AddWithValue("@cardNumber", cardNumber);
                response = (string)command.ExecuteScalar();
            }
        }
        return response;
    }

Is there anything that I'm overlooking by simply removing the MembershipCard class and just pulling the data from the database and formatting it as XML?

Upvotes: 3

Views: 233

Answers (4)

ChrisLively
ChrisLively

Reputation: 88074

Architecture is a funny thing. Before picking a paradigm it's always best to evaluate exactly what it is you are trying to accomplish.

Sometimes n-tier design can save you time later, however sometimes the amount of extra code you have to write today just isn't worth it. From what you are saying, it sounds like you've already gone into the latter camp.

So, getting back to my first statement you should ask yourself: Do you foresee the need to separate the data formatting (XML) from the retrieval (SQL Query)?

There are certainly cases where you might. For example, XML is very verbose and contains a lot of extra non-informative data that you might not want at a later time. By tying yourself to how SQL Server emits XML, you will be looking at a rewrite if you decide to go JSON.

Taking it a bit further, XML and even JSON are fine for limited amounts of data such as a few records at a time but if you start transferring hundreds or thousands at once then the amount of data could easily reach a point that you just want to send something akin to a CSV file across the wire. Again, you'd be facing a rewrite.

However, if you build it where you could just plug in a new provider to handle the various formatting requirements then you'd write new code versus replacing potentially large patches of old code... Also it would be fairly easy to support multiple formats simultaneously (XML, Json, CSV, Excel,...) if the formatting isn't tightly coupled to the retrieval.

Playing devils advocate, if we are talking about a small system it might not matter as the amount of time spent rewriting code due to a format change could be trivial, at which point I wouldn't spend the time building out an n-tier model.

Hope that helps, ultimately I don't think we can tell you which way to go.

Upvotes: 1

Ian Warburton
Ian Warburton

Reputation: 15676

n-tier design dictates that I should have a MembershipCard class with several properties

I'm not sure this is accurate. I think n-tier design dictates that each layer depends only on the layers below it. And each layer doesn't need to only depend on the layer directly below it - you can skip levels. So your web service interface can skip the domain layer and go directly to a repository. Although even then its best to add another layer in-between called, say, 'Application' which gives you space to add in extra non-data-access logic rather than have it leak into your UI/Web Service.

'Console.ReadLine' in your DB access class is mixing the UI in with a repository so that's obviously not good. Plus, using static methods makes it difficult to write unit tests so that's also not good.

Check out... Domain Driven Design by Eric Evans.

Upvotes: 0

Tim Copenhaver
Tim Copenhaver

Reputation: 3302

If you are specifically exposing CRUD operations, a REST interface may be the way to go (see Web API). With this route, I believe you would be justified in using your database classes directly. This isn't really a violation of the N-Tier approach - the web services actually serve as the interface to the data layer.

You can even take this one step further and expose delayed-execution querying over your service using the OData support in the current builds.

The primary issue is that Web API is still in beta and is changing regularly. OData support, for example, is barely there in the release preview. It's much more advanced in the nightly builds.

Upvotes: 1

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112402

The point is that you should be able to write your program logic independently from any database interface or xml format. Put all the database stuff into a separate class that loads and creates the objects. Do whatever you want to do with the objects. Finally store the objects back to the database or to xml. If your code is only about importing and exporting data, however, you can drop the extra classes.

By the way, you can simplify the MembershipCard class by using auto-implemented properties:

public class MembershipCard
{
    public string Number { get; set; }
    public decimal Points { get; set; }
}

I often have a static DB class

public static class DB
{
    public static string GetBalanceRequest(string cardNumber)
    {
        ...
    }

    public static MembershipCard LoadMembershipCard(string cardNumber)
    {
        ...
    }

    public static List<MembershipCard> LoadMembershipCards()
    {
        ...
    }

    public static void SaveMembershipCard(MembershipCard membershipCard)
    {
        ...
    }
}

Your MembershipCard class could have a method

    public string BalanceRequest()
    {
        return DB.GetBalanceRequest(this.Number);
    }

Like this you have a separation of database operations and other application logic.

Upvotes: 2

Related Questions