Handling Sandwich Code in C#

What is “Sandwich Code”? I found it in Ruby Koans.

Often, you have to do something at the beginning and at the end, even though your main lock is in the middle. Let’s say, you want to retrieve data from database. You have to create a connection, open it, use it to load data, and close it. You are interested in loading data, but you have to do some chores like opening a connection and closing it.

_connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Db"].ConnectionString);
_connection.open()var sql =
    @"SELECT     DATEPART(YEAR, PDate) AS [Year],
                 DATEPART(MONTH, PDate) AS [Month],
                 DATEPART(DAY, PDate) AS [Day],
                 COUNT(*) AS [Count]
        FROM     JobPostingPeriod
       WHERE     PublishToDate >= GETDATE() AND PDate <= GETDATE()
       GROUP BY  DATEPART(YEAR, PDate), DATEPART(MONTH, PDate), DATEPART(DAY, PDate)
       ORDER BY  [Year], [Month], [Day]";

var result = _connection.Query(sql));return result;_connection.close()



Those “bread-in-the-sandwich” code is repeated whenever you write any code that load data, and it can be quite annoying as you go on. In ruby, you can write a method that handles the opening and closing with block. In C#, you can use lamda delegate and Func.

I wrote a simple method, “ManageConnection”, with the help of Dan, my Totaljobs colleague, as he happened to be with me.
It accepts an anonymous method that has SqlConnection as input, and return T.
And now I can re-write the data loading method like this.
private T ManageConnection(Func func)
{
    _connection.Open();
    var result = func(_connection);
    _connection.Close();
    return result;
}
The excution flow is
1. GetJobCountByDate is called
2. It calls ManageConnection and pass conn.Query(sql) as anonymous method
3. In ManageConnection, _connection is opened.
4. In ManageConnection, func(_connection) is called. func is conn.Querysql at this point.
5. ManageConnection returns the result.
6. GetJobCountByDate receives the result from ManageConnection, and now returns the result.
I think this is nice and clean, once you understand how it works.
There are loads of sandwich code. If you write file, access database, write to http stream, …

	ection.open()
var sql =
    @”SELECT    DATEPART(YEAR, PostedDate) AS [Year],
                DATEPART(MONTH, PostedDate) AS [Month],
                DATEPART(DAY, PostedDate) AS [Day],
                COUNT(*) AS [Count]
       FROM     JobPostingPeriod
      WHERE     PublishToDate >= GETDATE() AND PostedDate <= GETDATE()
      GROUP BY  DATEPART(YEAR, PostedDate), DATEPART(MONTH, PostedDate), DATEPART(DAY, PostedDate)
      ORDER BY  [Year], [Month], [Day]”;
var result = _connection.Query(sql));
return result;
_connection.close()_connection = new SqlConnection(ConfigurationManager.ConnectionStrings[“JobSeekerReadOnly”].ConnectionString);
_connection.open()
var sql =
    @”SELECT    DATEPART(YEAR, PostedDate) AS [Year],
                DATEPART(MONTH, PostedDate) AS [Month],
                DATEPART(DAY, PostedDate) AS [Day],
                COUNT(*) AS [Count]
       FROM     JobPostingPeriod
      WHERE     PublishToDate >= GETDATE() AND PostedDate <= GETDATE()
      GROUP BY  DATEPART(YEAR, PostedDate), DATEPART(MONTH, PostedDate), DATEPART(DAY, PostedDate)
      ORDER BY  [Year], [Month], [Day]”;
var result = _connection.Query(sql));
return result;
_connection.close()
_connection = new SqlConnection(ConfigurationManager.ConnectionStrings[“JobSeekerReadOnly”].ConnectionString);
_connection.open()
var sql =
    @”SELECT    DATEPART(YEAR, PostedDate) AS [Year],
                DATEPART(MONTH, PostedDate) AS [Month],
                DATEPART(DAY, PostedDate) AS [Day],
                COUNT(*) AS [Count]
       FROM     JobPostingPeriod
      WHERE     PublishToDate >= GETDATE() AND PostedDate <= GETDATE()
      GROUP BY  DATEPART(YEAR, PostedDate), DATEPART(MONTH, PostedDate), DATEPART(DAY, PostedDate)
      ORDER BY  [Year], [Month], [Day]”;
var result = _connection.Query(sql));
return result;
_connection.close()
Handling Sandwich Code in C#

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s