ThinkingCog

Everything is getting smart. Not everyone, but everything.

My lamp, dishwasher, washing machine, car and everything in between, now connects to the internet and sends notifications about important events, from completing their duties to depleting levels of various consumables required in their functioning.

With all these devices connecting to a distributed and redundant network like the internet, which further hosts distributed systems that spans heterogeneous hardware, multiple software stacks and geographically separated data centers, something, somewhere, is bound to fail, albeit for a fraction of a second, but it will fail. The device may lose its WIFI signal momentarily, the router may experience a hiccup due to a power brownout, the optic network cable may experience the wrath of an excavator bucket or the server may die in the line of duty before the redundant one kicks in. All these are very much possibilities and we experience them all the time.

Such issues that are ephemeral in nature are known as transient errors in the programming domain. They are there momentarily and then they are not, and because of their non-persistent nature, are not easy to debug, as re-producing them may not always be feasible.

One of the proven ways to increase dependability is to increase availability of the desired product or service. Web services and applications run on a 24-hour schedule on servers that continuously consume power. One way to increase their availability is to increase the redundancy i.e. have the same service or application be available on multiple servers. The servers may all be in the same data center or geographically spread. The geographical spread helps in averting a situation where a single data center may experience a power outage, network outage or a natural calamity and take down the entire service or application.

But increasing availability always comes associated with costs. Every server/virtual machine/container cost to run and the more of them exists, the more man hours go in their upkeep and maintenance.

In order to reduce costs, without introducing redundancy beyond warranted, we incorporate smarts into software, so that the application keeps running the desired actions without throwing error, creating an illusion of high availability. Such activity which can introduce resilience in an application, can be achieved with the help of programming frameworks. One, and at the moment only framework to exists for .Net developers happen to be Polly.

With Polly, we can easily incorporate resiliency patterns such as retry pattern, circuit breaker, timeout, bulkhead isolation etc. in our applications and services. Each pattern deserves a post unto itself and in future I will bring more information on the patterns along with code examples.

Till then, please enjoy a video in which Late Mr. Scott Allen discusses about building resilient applications in cloud and look for opportunities to apply a resilient framework like Polly.

Visit DALHelper.Codeplex.com for source code, ready to use assembly and related documentation.

Brief description

The DALHelper is a project that was born out of my needs to connect to SQL Server database for my projects. I generally work on projects and design them using domain model and to keep them flexible, understandable and fast, I prefer to connect to the database using ADO.NET. Writing ADO.NET commands to connect to database can become monotonous and repetitive. Using Microsoft's Enterprise Library's Data Access Application Block is one solution, but I generally find it requiring a bit more effort than should be invested. Of course it gives you the freedom to change your supporting back end database. Generally back end databases in applications do not change, especially in enterprise environments and small applications for which the express version of SQL Server can be sufficient, and if you work with such applications then, I believe DALHelper can make your life a bit easier.

NOTE: This library cannot be used with any other database other than SQL Server, as it is built upon SQL Server specific ADO.NET commands.

The library contains various helper methods that can simplify the task of doing CRUD (Create, Read, Update and Delete) on a SQL Server database in a .NET application. The library can be used with any .NET compliant language, is open sourced and will remain that way.

DALHelper touches upon the following technologies:
1. Visual C# 4.0
2. .NET Framework 4.0
3. ADO.NET technology, part of .NET framework, used to connect to databases.
4. Code Contracts, Microsoft’s implementation of Design by Contact paradigm.

The DALHelper library has been designed to be provided with the connection string to the database once via property injection and then perform various operations as desired by the user with the help of the methods provided in the library. Some of the best practices that have been followed while the methods were designed are the following:

1. The methods follow a pattern in their signature and are easy to use.

2. The methods provided in the library are overloaded, thus providing you just the signature that you need in order to accomplish what you desire, without cluttering up your code.

3. The names of the methods are very much self-explanatory in context what they service they are supposed to provide.

The variety of methods available should fit majority of the requirements found in any small to medium scale enterprise application. Every method available in the library allows you to execute a SQL query or a stored procedure.

Every method available in the library has two overloads:

1. Without parameters, and

2. With parameters

Every method available in the library has the signature of the format:

1. SQL text: Is the SQL query or stored procedure that needs to be executed.

2. SQLTextType: An enum which signifies whether the SQL text parameter is a SQL query or a stored procedure. By default, the choice is that of a stored procedure.

3. List<SqlParameter>: An optional generic list of SQL parameters type that might be required by the SQL query or stored procedure. The parameters can be of both input and output type.

Brief description of methods available in the library:

1. ExecSQL

The ExecSQL method can be used to execute a SQL statement or a stored procedure without returning any value. Available in two overloads:
public void ExecSQL(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
public void ExecSQL(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List<SqlParameter> parameterCollection = null)

2. GetRowsAffected

The GetRowsAffected method can be used to execute a SQL query or a stored procedure and return the total number of rows affected by execution. Available in two overloads:
public int GetRowsAffected(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
public int GetRowsAffected(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List<SqlParameter> parameterCollection = null)

3. GetDataTable

The GetDataTable method can used to execute a SQL query or a stored procedure and return the result set in a data table. Available in two overloads:
public DataTable GetDataTable(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
public DataTable GetDataTable(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List<SqlParameter> parameterCollection = null)

4. GetDataView

The GetDataView method can be used to execute a SQL query or a stored procedure and return the result set as a data view. Available in two overloads:
public DataView GetDataView(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
public DataView GetDataView(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List<SqlParameter> parameterCollection = null)

5. GetDataSet

The GetDataSet method can be used to execute a SQL query or a stored procedure and return the result set(s) in a dataset. Available in two overloads:
public DataSet GetDataSet(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc)
public DataSet GetDataSet(string sqlText, SQLTextType sqlTextType = SQLTextType.Stored_Proc, List<SqlParameter> parameterCollection = null)

DALHelper is an excellent way to implement repository architecture style and simple CRUD implementations.

Future Roadmap

In future I plan, to extend this library into a framework, supporting various data sources such as XML, Excel, CSV files etc.

Helpful Links

Some of the links that might be helpful in understanding the programming concepts and technologies that have gone in making of this library:
Visual C#:

1. http://msdn.microsoft.com/en-us/vstudio/hh341490.aspx

.NET Framework:

1. http://www.microsoft.com/net

2. http://msdn.microsoft.com/en-US/vstudio/aa496123

ADO.NET:

1. http://msdn.microsoft.com/en-us/library/e80y5yhx.aspx

Code Contracts:

1. http://research.microsoft.com/en-us/projects/contracts

2. http://msdn.microsoft.com/en-us/devlabs/dd491992.aspx

Dependency Injection:

1. http://en.wikipedia.org/wiki/Dependency_injection

2. http://msdn.microsoft.com/en-us/magazine/cc163739.aspx