ThinkingCog

Articles written by Parakh Singhal

DALHelper – A convenient way to connect to SQL Server in .net applications.

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