Articles written by Parakh Singhal

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

Visit 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#:


.NET Framework:





Code Contracts:



Dependency Injection:



SignalR Sample Application 2 – Draggable Element

The last post showcased an elementary application, that was meant to prime up the reader on the basics of the SignalR library. This post builds upon the last post and makes a bit more involved web application,that uses jQuery UI library. The SignalR programming unto itself will remain simple. If you are new to SignalR, please first go through introduction to SignalR.


Make a draggable visual element which when dragged across in a client browser, should replicate motion in other client browser(s).

To do this, we will take help of jQuery UI library which builds upon jQuery library. As I have already covered the basic steps involved in making a basic application in my last post, I will keep the instructions concise.

1. Make an empty ASP.NET Web forms website.

2. Add a SignalR Hub class with the name “MoveShape” and declare a public method “Move” which accepts two integer parameters x and y, which we will use to pass the screen coordinates. Here is what the code should look like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.AspNet.SignalR.Hubs;
public class MoveShape : Hub
    public void Move(int x, int y)
        Clients.Others.shapeMoved(x, y);

Explanation of code:

The Move method will receive two integer values signifying the screen coordinates of the visual element on screen. The method will invoke method “shapeMoved” available on the client side and pass on the two coordinates, it received originally. These coordinates will then be used on the client side to position the visual element accordingly.

3. Add a Global.asax class and register hubs in the “Application_Start” method.

4. Add a css file with the following code. Note that the code is relatively simple and a lot of it is simply providing visual enhancement. It can be a lot simpler for this example.

    background: #261201;
    margin: 0;
    padding: 0;
    font-family: Sans-Serif;
    font-style: normal;
    font-size: 1em;
    font-weight: normal;
    font: Calibri, Verdana, Helvetica;
    color: #F2E7AE;
h1, h2, h3, h4, h5
    color: #A62103;
    margin: 0.25em;
    padding: 0.5em;
    margin-bottom: 0.25em;
    padding-left: 0.25em;
    border-bottom: 1px solid #EEE;
    color: #A62103;
    height: 50px;
    border: 3px solid #A62103;

Explanation of code:

The code having the id of draggable is the code that will make up the visual element in the form of a rectangle with a cream colored background.

5. Add an HTML file with the following code:

<!DOCTYPE html>
<html xmlns="">
<head runat="server">
    <title>Draggable Item</title>
    <link href="Style/Brown.css" rel="stylesheet" />
    <script src="Scripts/jquery-1.9.0.js"></script>
    <script src="Scripts/jquery.signalR-1.0.0-alpha1.min.js"></script>
    <script src="Scripts/jquery-ui-1.10.0.custom.min.js"></script>
    <script src="signalr/hubs"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var hub = $.connection.moveShape;
            var draggableShape = $('#draggable');
            hub.client.shapeMoved = function (x, y) {
                draggableShape.css({ left: x, top: y });
                draggableShape.html("Drag Me!!<br />x: " + x + "<br />y: " + y);
            $.connection.hub.start().done(function () {
                    drag: function () {
                        var position = draggableShape.offset();
                        $(this).html("Drag Me!!<br />x: " + position.left + "<br />y: " +;
    <div id="container">
        <div id="header">
            <h3>Making draggable item using jQuery.</h3>
            Drag the box around and SignalR synchronizes the motion in all open browser windows.
        <div id="draggable">Drag Me!!</div>

Explanation of code:

In the HTML code we have referenced all the required JavaScript files including the jQuery library, jQuery UI library and the SignalR JavaScript client. Notice that I am using the latest stable version of jQuery and jQuery UI library available at the time of writing this post. In the jQuery code, I am making the hub available at the client side, which will be used to program the methods that need to be executed at the client side. I am caching the visual element that will be made draggable, as reading the DOM repeatedly will lead to an efficient code. The method shapeMoved receives two integer parameters and uses those to change the css properties of x and y coordinates of the draggable elements in all the clients. Once the hub is instantiated, we make the div element bearing the id “draggable”, draggable with the help of the in-built function available in jQuery UI, and declare the drag property to record the x and y coordinates in the client view portal and execute the move function (which corresponds to the Move() method available at the server side code). The coordinates are also showed in the visual element just for referential purposes.

In the end the code hierarchy should look like in the following image:

code hierarchy 2













Now run the website in Debug mode and you will be able to observe a box which when moved in in client browser also moves in other client browser(s).

Browsers 2












This post was a bit heavy on the jQuery code, not so much on SignalR code, but shows the vast potential of the library. Anywhere, where you might require real time functionality between server and client(s), you can use SignalR.

Source code:

Draggable source code



Thanks for reading.

Relevant Links:

1. Building Real-time Web Apps with ASP.NET SignalR



SignalR Sample Application – Hello World

In my last post, I described the simplicity that SignalR brings to table when we want to include real time asynchronous communication between server and client(s) in our application. In this post I will make a simple application, the proverbial ‘Hello World’ kind of application to introduce the basic concepts of this new library.

The following are the pre-requisites for the application:

1. Visual Studio 2012

2. .NET Framework 4.5

3. ASP.NET and Web Tools 2012.2

The best thing about the web tools update 2012.2 is the included template that makes adding SignalR capabilities to your project amazingly easy. Our website will be based on ASP.NET Web forms and will use JavaScript client, which comes in the form of a jQuery plugin. So let’s start.

1. Fire up your Visual Studio and make a new empty ASP.NET Web forms web site.

2. Right click on the website and add a SignalR Hub class and provide “HelloWorld” as the name for the hub class. This will add the following items in the website:

a) App_Code folder – which will house our hub class and the App_Start folder which contains the RegisterHubs.cs file, housing the code that maps the hubs available at application startup.

b) Scripts folder which houses all the JavaScript files, including the bundled jQuery library and the JavaScript client based upon the jQuery library.

c) Bin folder housing all the necessary assemblies that co-ordinate things on the server side. This also contains the JSON serializer Json.NET.

d) packages.config which lists all the external dependencies.

Now we will have to change this setup a little bit in order to make it work in a web forms website. Remove the App_Start folder and the containing RegisterHubs.cs file from it. The App_Start folder is something that the MVC website can use to execute code at application startup. Web form website uses Global.asax to do just the same. I believe this a template error, and will be corrected out in future releases.

Delete App_Start folder and it's contents













3. Add a Global.asax file to the solution. The HTML portion of your Global.asax file should look like this:

<%@ Application Language="C#" Inherits="Global" CodeBehind="~/App_Code/Global.asax.cs"%>
<script runat="server">


and the code behind portion should look like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Routing;
using Microsoft.AspNet.SignalR;
/// <summary>
/// Summary description for Global
/// </summary>
public class Global:System.Web.HttpApplication
    void Application_Start(object sender, EventArgs e)
        // Code that runs on application startup

Explanation of code:

Note that we are required to include System.Web.Routing and Microsoft.Aspnet.SignalR namespaces and make the Global class inherit from System.Web.HttpApplication class.The purpose of having RouteTable.Routes.MapHubs is to map the hubs available to the client to a special default address: /signalr/hubs. If you want to configure the availability of hubs to a special address then you will have to provide custom address in the MapHubs as a string parameter. e.g. RouteTable.Routes.MapHubs(“~/signalr2”).

4. Now head over to the hub class and add the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Microsoft.AspNet.SignalR.Hubs;
public class HelloWorld : Hub
    public void Hello()


Explanation of code:

The main thing to notice here is the inclusion of the namespace Microsoft.Aspnet.SignalR.Hubs and derivation of the hub class HelloWorld from parent class Hub. From the perspective of the client and server model, this is the class that aptly serves as a hub and will receive all the communication from the client(s) and further the same to other client(s) or take some action on the server side. In code, we are declaring that there is a server side method called “Hello” which when executed will execute a method called “hello”, available to all the clients.

5. Now add an HTML page and add the following code in it.

<!DOCTYPE html>
<html xmlns="">
    <title>Hello World! with SignalR</title>
    <script src="Scripts/jquery-1.7.1.min.js"></script>
    <script src="Scripts/jquery.signalR-1.0.0-alpha1.min.js"></script>
    <script src="/signalr/hubs" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            //defining hub which will have client and server side methods defined.
            var hub = $.connection.helloWorld;
            //defining what the client side "hello" method should do when called.
            //by the server side code.
            hub.client.hello = function () {
                $('#AddText').append('Hello World!<br />');
            //starting the hub and specifying what should server side code should be
            //called when a certain client side event occur.
            $.connection.hub.start().done(function () {
                $('#SubmitButton').click(function () {
    <div id="AddText"></div>
    <input type="button" id="SubmitButton" value="Say Hello" />


Explanation of code:

Here we reference all the necessary JavaScript libraries among which are the jQuery library and SignalR JavaScript client based on jQuery. There is one special JavaScript reference “/signalr/hubs” which is the JavaScript that is dynamically generated at client side and referenced by DOM at runtime. The body of the HTML document consists of a div element bearing id “AddText” to which we will add text “Hello World!” each time the button “Say Hello” is pressed. The jQuery code for this example is very simple. In it we declare the name of the hub class. Note that we are using camel casing as per JavaScript coding conventions to refer to our server side hub class. Thus “helloWorld” at client side is “HelloWorld” at server side. We then define that the client side “hello” method which is actually adding the text “Hello World!” and a line break to the div element “AddText”. We then start the connection and when done, observes the click event of the “Say Hello” button. When the click event happens on the client side, we execute the server side method “Hello” of the hub class, which in turns executes the “hello” method available at all the clients. This adds text to the div element at all clients.

Speaking diagrammatically, this is what we are aiming to do:

Client-Server Model















In the end the code hierarchy should look like in the following image with all the code in the right place.

Code hierarchy
















6. As the last step start debug and run the website in IIS Express. Open the website in multiple different browsers and press the “Say Hello” button. It will add text “Hello World!” in all the browsers simultaneously in real time.

Multiple browsers












Website running in IE9 and Firefox, receiving instructions from server in real time.

The code example presented here has been deliberately kept simple to prime the reader of the basic concepts of this new library.

In a nut shell we achieved:

1. Real time communication between server and client(s).

2. Auto negotiation of protocol (WebSocket, forever frames, long polling etc.) between the client(s) and server.

3. Simple, unified programming interface built upon C# (server side) and jQuery (client side in ASP.NET).

Download code:

SignalR sample application download




In the next post I will demonstrate a more involved example, utilizing jQuery UI. Stay Tuned.

Introduction to SignalR

Key Takeaway:

SignalR is the newest member in the .NET ecosystem fully supported by Microsoft, offering an abstraction over the transport layer, that enables developers to make scalable applications that can have an real time asynchronous communication between server and client(s) and vice versa.

Read On

In the client server model, there are two modes of communication at play – push or pull. In pull paradigm, it is the client that pulls the information from the server. In push, it is the server that explicitly sends the information to the client. The web model is based on the pull technology, affectionately known as the request-response pattern. The client (browser) requests information from the server, and gets the response (web page, data etc.) Once this transaction is done the server merrily goes back into the state of amnesia. This pattern is the reason, why the web is stateless.

Whenever there is a requirement of data being explicitly pushed from the server, it poses a different set of problems than what the stateless nature of web is designed to solve. You then have to keep track of connections going to various clients all the while maintaining scalability and performance.

To achieve push from server this there are various kinds of hacks that are put in place, but all based on pull paradigm. The various sleights include opening a never ending connection to the server to polling the server again and again. There is even a standard term for such kinds of client-server interactions – COMET.

COMET operations fall under two kinds of categories –polling and streaming. Streaming is when the client opens up a connection to the server and keeps it open. Such kind of connection is called as a persistent connection and a characteristic of such a connection is the “Connection: keep-alive” command at the transport layer level. Persistent connection is predominantly used in web applications targeting real time data which needs to be server to a variety of versions of various browsers. One fine example is that of Google Finance. Notice that it uses persistent connection to bring in data continuously about the live condition of the financial market.

Persistent Connection 1

Google Finance uses persistent connection to update the webpage continuously.

Polling is the technique when the client keeps on sending requests to the server, demanding any new data that the server might have. A good example of polling is the Dropbox client application.

Polling 1











Dropbox client application in Windows 7 does polling.

Yet other trick involves using an iframe, which is fundamentally an HTML document embedded within a parent HTML document. This technique is primarily used to show ads dynamically within a web page and periodically pull a new one within the iframe.

iframe 1

Yahoo Mail’s new interface uses iframe to show advertisements.

The newest technology on the block is WebSocket protocol. WebSocket is part of HTML5 draft and is truly duplex and persistent form of connection to the server side processes. But sadly, not all the browsers and more importantly web servers support websocket protocol as of today. So if you are developing a public facing web site, you cannot truly rely on websocket yet.

So, all in all, right now we have a conundrum of technical tricks and an upcoming technology that is not uniformly supported by all client browsers and web servers, to solve the problem of serving real time data from server(s) to various client(s). Adding to the complexity is the fact that there are various kinds of clients available ranging from web browsers to sensors (that can use .NET Micro Framework) to native applications in iOS and Android ecosystems to desktop applications that require real time data pushed from a central server.













This is where SignalR comes into play and provides a unified approach to handle the requirement of asynchronous real time duplex communication between client(s) and server.

The way SignalR works is that it auto negotiates the protocol between the client and the server based on the capabilities of the pair. So if they support websocket, then that is used. If not websocket, then SignalR falls back to server sent events, if not that then forever frames and so on. So the developer is not required to worry about the protocol detection and usage and eventual graceful degradation. SignalR handles this automatically, all this while providing a uniform API to program against. SignalR is being actively developed by Damian Edwards’s team at Microsoft and they have already released the first version in the fall update (2012) of ASP.NET. SignalR scales well and already supports SQL Server, Service Bus and key-value system like Redis. Client side API is available for .NET (4.0 and 4.5), HTML in the form of a jQuery client, WPF, Silverlight 5 and Windows Phone 8.

In the next post I will make a simple application using SignalR. In the meantime please learn more about this awesome technology from the following videos:

1. Damian Edwards and David Fowler presenting basics of SignalR (Sorry video is too big to fit in here.)

2. Keeping It Realtime Conference - SignalR - Realtime on ASP.NET (Scott Hanselman & Paul Batum)



Please head out to the following links to get more information:



3. Source code of SignalR at GitHub