Convert pipe separated string to c# dictionary object using dapper’s custom handler

We have migrated one legacy application from old web-form to mvc. But we cannot change the database structure as other applications also depends on this database. To communicate with the database we have used dapper in our mvc application. Dapper is an open source micro orm developed by some awesome people at stackoverflowDapper has emerged as one of the most powerful micro-ORM. After getting the data from the database, dapper will automatically map or deserialize the data into our custom object. But sometimes we are in a situation where we want the complete control on how database data is mapped to our custom complex object. This where a custom object type handler comes into picture. Dapper have this one of the powerful feature which you can achieve using SqlMapper.TypeHandler.

So i have a situation where my legacy application saves pipe separated data into table which looks something like this –


and i want to map this pipe separated data into C# Dictionary object. So to handle this situation, dapper provides a custom type handler. In today’s article we will learn about how to use it.

So let’s get started. To bind the data from the database into my model object. Here is the structure.

public class SiteMasterModel
	public int SiteId { get; set; }

	public string SIteName { get; set; }

	public int ClientId { get; set; }

	public SiteConfigurationDictionary SiteConfiguration { get; set; }

public class SiteConfigurationDictionary : Dictionary <string, string>
	// code goes here..

So now if i try to bind my SiteConfiguration column data (shown in above screenshot) which is pipe separated string to SiteConfigurationDictionary object, then we will get the following exception –

Dapper here clearly tells us that it has no idea of how to deal with your complex SiteConfigurationDictionary object. So this is where the SqlMapper.TypeHandler comes to rescue us. So what you need to do is crate a separate class, let say called it – SiteConfigurationHandler class which is inherited by SqlMapper.TypeHandler base class. Then in that class you need to override two important following method –

1). Parse() :- This method is called when we select the data from the database and deserialize into our complex object.

2). SetValue() :- This method is called when we try to save our modified data back to the database.

public class SiteConfigurationHandler : SqlMapper.TypeHandler<SiteConfigurationDictionary>
	public override void SetValue(IDbDataParameter parameter, SiteConfigurationDictionary value)
		parameter.Value = value.ToString();

	public override SiteConfigurationDictionary Parse(object value)
		return SiteConfigurationDictionary.FromConfigurationString(value.ToString());

So as you see in the above code that Parse() method called FromConfigurationString() of SiteConfigurationDictionary class. Well, this is a static method where we actually implemented our custom mapping logic to deal with our complex object. So now our SiteConfigurationDictionary class looks like this –

public class SiteConfigurationDictionary : Dictionary<string, string>
	public override string ToString()
		return Join("|", this.Select(item => $"{item.Key}={item.Value}"));

	public static SiteConfigurationDictionary FromConfigurationString(string value)
		//IDictionary<string, string> configDict = new Dictionary<string, string>();
		SiteConfigurationDictionary configDict = new SiteConfigurationDictionary();

		if (string.IsNullOrEmpty(value))
			return configDict;

		var configArray = value.Split('|');

		foreach (var configInfo in configArray)
			var splitedData = configInfo.Split('=');
			if (!configDict.ContainsKey(splitedData[0]))
				configDict.Add(splitedData[0], splitedData[1]);

		return configDict;

Now our handler is ready to use. We just need to register it with Dapper. If you are developing web application then you can register it in Application_Start event. In my case this is just a simple demo console application. To register handler following is the code –

// First we need to register our custom type handler
SqlMapper.AddTypeHandler(new SiteConfigurationHandler());

Now when we fetch the data from the database using following code.

static void Main(string[] args)
	// First we need to register our custom type handler
	SqlMapper.AddTypeHandler(new SiteConfigurationHandler());

	List<SiteMasterModel> siteList = GetSiteMasterList();

private static List<SiteMasterModel> GetSiteMasterList()
		var connectionString = ConfigurationManager.ConnectionStrings["NorthwndConnectionString"].ConnectionString;

		using (IDbConnection conn = new SqlConnection(connectionString))
			return conn.Query
					"SELECT SiteId, SIteName, ClientId, SiteConfiguration FROM dbo.WebSiteMaster"
	catch (Exception ex)
		throw ex;

Now when you run the application, this is how dapper mapped the data when you debug the code –


Well, you can download the full source code from my Github repo to play with this feature. Have you ever used this feature of dapper, you can share us your scenario in comment section. Hope you like this post. Thanks for reading.


SQL Server-How to get column name along with its value as row and display custom column header dynamically using pivot.

It has been a couple of month since i write any article on this blog. Well, Today I have come up with the very interesting requirement and will see that how we can achieve the result in sql server. By-The-Way this article’s title might be looking confusing for some readers, so i would recommend them to read the whole article.

Let’s move ahead. For one of my project, I have been given a requirement where my client wanted to display column name along with its value as row and generate custom column header to display for each entity.  Hmm still confusing!! Don’t worry. Consider I have one table let say – Book which have different columns and contains data as shown in following image.


Now my requirement is to produce the result like following –


Cool. Hmm I can smell of pivoting of sql server. Well, if you think this then you are in the correct direction. But that’s not enough to achieve the result. Let’s see this practically by slicing out the result and see it step-by-step.

First i want to generate custom header like – “Book 1”, “Book 2” and so on as shown above. We can achieve it by using sql server’s ranking function – ROW_NUMBER. See this sql script –


       @DisplayCol += N', [Book- ' + CONVERT(NVARCHAR(500), ROW_NUMBER() OVER(ORDER BY BookId)) + ']'
FROM dbo.Book

SET @DisplayCol = STUFF(@DisplayCol, 1, 2, '')

PRINT @DisplayCol

which produces the following output.

[Book- 1], [Book- 2], [Book- 3], [Book- 4]

Well, There might be other better way to get this type of result. Now next step is to display column name along with its value as row. To achieve this we’ll use “Table Value Constructor”  t-sql syntax. Well, “Table Value Constructor” is also known as “Row Constructor”, a new feature introduced in sql server 2008 which allows multiple rows of data to be specified in a single statement using “VALUES” clause.

SELECT BookId, tab.* FROM dbo.Book
		('BookTitle', BookTitle),
		('Publisher', Publisher),
		('Author', Author),
		('Number Of Pages', CONCAT(NoOfPages, ' Page(s)')),
		('Language', Language),
		('Publication Date',  CONVERT(VARCHAR(10), PublicationDate,101))
	) tab(ColKey, ColValue)

which produces the following output.


Now it’s time to produce the final output and for that we need to use a dynamic pivot query (as there can be “n” number of rows in the table) which will allow us to build the columns based on the type values. Now if we combine everything mentioned above then final query will looks like this –

DECLARE @SqlQuery NVARCHAR(MAX) = '',  @ColumnToPivot NVARCHAR(MAX) = '', @DisplayCol NVARCHAR(MAX) = ''

	@DisplayCol += N', ' + QUOTENAME(BookId) + ' AS [Book-' + CONVERT(NVARCHAR(500), ROW_NUMBER() OVER(ORDER BY BookId)) + ']'

SET @DisplayCol = STUFF(@DisplayCol, 1, 2, '')

	@ColumnToPivot += N', ' + QUOTENAME(BookId)

SET @ColumnToPivot = STUFF(@ColumnToPivot, 1, 2, '')

SET @SqlQuery = N'
SELECT ColKey AS Attributes, ' + @DisplayCol + ' FROM
	SELECT BookId, tab.* FROM dbo.Book
		(''BookTitle'', BookTitle, 1),
		(''Publisher'', Publisher, 2),
		(''Author'', Author, 3),
		(''Price'', CONVERT(VARCHAR(10), Price), 4),
		(''Number Of Pages'', CONCAT(NoOfPages, '' Page(s)''), 5),
		(''Language'', Language, 6),
		(''Publication Date'',  CONVERT(VARCHAR(10), PublicationDate,101), 7)
	) tab(ColKey, ColValue, OrderVal)
) res
PIVOT (MAX(ColValue)
	FOR BookId IN (' + @ColumnToPivot + ')
) pv'

--PRINT @SqlQuery

Here i have created Sql Fiddle to play with this query. Enjoy.

That’s it. Hope you like this blog post. Feel free to give your feedback or query in comment section. Thank you for reading.

SQL Server: How to get top most parent id from the nth level in parent-child hierarchy

In the last week one of my team member who worked on ICD10 project asked me one question where he wanted to find the top most icd-code id from any child level. So in today’s post we’ll learn about how to get the top most parent id or let say root id from the leaf level or from the nth level in parent-child hierarchy. Hmm sounds interesting.

SQL Server 2005/2008 comes with a major tsql enhancement which really ease the life of developer. CTE(Common Table Expression) is one of them. CTE is a temporary result set which you can reference it in SELECT, UPDATE, INSERT or DELETE statement. There are two types of CTEs in SQL Server – Recursive and non-recursive. To achieve our expected result we need to use here recursive CTE. By not going into much detail, let me give you the short description about it. A recursive CTE is one that reference itself or call itself within that CTE and continues to execute until the query return the whole result. Let’s dive into it and see it in action.

For demo purpose, i have defined the table variable here with the required data in it.

CodeID [int] NOT NULL,
CodeName [varchar](250) NULL,
Code [varchar](250) NULL,
ParentID [int] NULL

INSERT INTO @Tab_ICDCodes(CodeID, CodeName, Code, ParentID)
VALUES  (1, 'Abdominal Pain', 'AP', 0),
	(2, 'Epigastric', 'EP', 1),
	(3, 'Ruptured', '', 2),
	(4, 'Acute abdomen', '', 2),
	(5, 'Aortic aneurysm', '', 3),
	(6, 'Nail', 'N', 0),
	(7, 'Nail Pain', 'N', 6)

Now to answer the original question here is the query which return top most parent / root – code id and code name. In below query we’ll pass child id as variable and query will return its top most parentid.


;WITH cteGetRootID

		CodeID, CodeName, ParentID, 1 AS CodePosition
		@Tab_ICDCodes WHERE CodeID = @CodeId
		ic.CodeID, ic.CodeName, ic.ParentID, CodePosition + 1
	FROM @Tab_ICDCodes ic
	INNER JOIN cteGetRootID cte ON ic.CodeID = cte.ParentID
SELECT TOP 1 CodeID, CodeName FROM cteGetRootID
ORDER BY CodePosition DESC

So, that’s it guys. Hope you like this post.

Tip of the day: SQL Server – In Stored Procedure if passing parameter is not null then update column value else keep old column value in Update query.

As a developer, we always try to write better code and try to reduce the possible bugs in the application. But at the same time its also important that how you write the code, how much readable it is. Well, we all are human beings and its almost impossible to write bug free code irrespective of your work experience. That’s where the process of code review comes into the picture. Importance of code review is not limited to code refactoring or bug fixing,  at-least for me it is also a solid mechanism of knowledge sharing / gathering.

So as a part of it, I was reviewing the code of stored procedure and found some unwanted if conditions. In that SP there were 3 optional parameters and those if conditions was something like this – check if parameter passed is not null then use it in update statement. And same condition for other two columns. Here is the sample code of that stored procedure.

CREATE PROCEDURE [dbo].[App_UpdateAccountInfo]
 @AcntId INT
,@CompanyID INT
,@AccountNickName VARCHAR(50)
,@SystemName VARCHAR(30) = NULL



	UPDATE [dbo].[tblAccount]
		CompanyID = @CompanyID
		,AccountNickName = @AccountNickName
		,LastUpdateOn = GETDATE()
	 WHERE AccontID = @AcntId

	 -- Check if @SystemName is not null
	 IF @SystemName IS NOT NULL

		UPDATE [dbo].[tblAccount]
			SystemName = @SystemName
		 WHERE AccontID = @AcntId


	 -- Check if @FrnID is not null

		UPDATE [dbo].[tblAccount]
			FrnID = @FrnID
		 WHERE AccontID = @AcntId



Now what if there were more than 5 optional parameters in above SP! As a developer do you think to write 5 or more if conditions and make SP more verbose? So what should be the proper way?

Well, Let me give you one hint. What if you update column with itself in update statement. Hmm no need to think much, here is the modified code of above SP.

CREATE PROCEDURE [dbo].[App_UpdateAccountInfo]
 @AcntId INT
,@CompanyID INT
,@AccountNickName VARCHAR(50)
,@SystemName VARCHAR(30) = NULL



	UPDATE [dbo].[tblAccount]
		CompanyID = @CompanyID
		,AccountNickName = @AccountNickName
		,SystemName = ISNULL(@SystemName, SystemName)
		,FrnID = ISNULL(@FrnID, FrnID)
		,LastUpdateOn = GETDATE()
	 WHERE AccontID = @AcntId


Well, SQL Server is smart enough to not write the same value on the disk which results in minimal log activity. However this is not true in some cases. There are several factors which is nicely described in Paul White’s article and this stackexchange link.

That’s it. Have you ever faced this type of scenario? write it down in the comment section. Hope you like this post.

SQL Server Error: Cannot schema bind function because table name is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Today for one of my application, i was trying to create a schema bound function for performance optimization. Before we go into the detail you should check this link to know that how the query optimizer can use it to make better performance decisions. Alright, now the function i wanted to create is used to check that particular role id have access to menu link or not. Simple right! Here is the code of my function.

CREATE FUNCTION [dbo].[udf_IsAuthorisedForMenuLink]
	@MenuID INT
	DECLARE @IsAuthorised BIT = 0

	IF EXISTS(SELECT 1 FROM tblRoleMenu WHERE RoleId = @RoleID AND MenuID = @MenuID)
		SET @IsAuthorised = 1
	RETURN @IsAuthorised

But when i try to create this function, i get the following error.

Msg 4512, Level 16, State 3, Procedure udf_IsAuthorisedForMenuLink, Line 23 
Cannot schema bind function ‘dbo.udf_IsAuthorisedForMenuLink’ because name ‘tblRoleMenu’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

So why i got this error? What is the resolution of this error? Well, the resolution itself is in the error message. As it said – “Names must be in two-part format” i.e. [Schema].[ObjectName] – in our case its dbo.tblRoleMenu. Actually i forgot to add schema in the select statement in my above function code. Once i add it and press “F5” and boom, function created successfully without any error.

Did you guys ever face this type of issue in your application? Write in the comment. Hope you like this post.

Alter table definition not reflected in SQL Server View

Few days ago, i got the chance to work with one of our legacy application. There was some changes in business requirement and i need to drop few columns from the table and so on and that table was used in one sql server view. After done my changes in table when i test my view, surprisingly it gives me an error! whooa!

Msg 4502, Level 16, State 1, Line 7
View or function ‘view_name’ has more column names specified than columns defined.

How is it possible as i have used “Select *” in my view. It should automatically reflect the changes in my view. Well, SQL server will not work in that way. After referring MSDN, i knew that metadata for my view is not automatically updated when i alter any dependent tables. So in today’s post we’ll see how we can resolve this type of error in SQL Server. Alright, let’s create this scenario.

CREATE TABLE [dbo].[EmployeeMaster](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](40) NOT NULL,
	[LastName] [varchar](40) NOT NULL,
	[JoiningDate] [date] NULL,
	[BloodGroup] [varchar](5) NULL,
	[PANNo] [varchar](20) NULL

INSERT INTO EmployeeMaster(FirstName,LastName,JoiningDate,BloodGroup)
	VALUES ('Azim', 'Premji', '2015-04-27', 'B+'),
			('Narayana', 'Moorthy', '2015-04-27', 'A+')

Now let’s create the view.

CREATE VIEW view_Employee
    SELECT * FROM  dbo.EmployeeMaster 

If you querying the view, everything works fine as expected. Now let us drop the “PANNo” column from the EmployeeMaster table.

ALTER TABLE EmployeeMaster

And let’s querying our view and see what happen.


We get the error! Now to fix this error one of the way is alter our view or use the sp_refreshview stored procedure. So let’s see how we can fix the error using sp_refreshview SP.


Now again querying our view and as expected the error is gone now. Same way let say if you have added 2 new columns in your table then this time you won’t get any error when you querying your view but those 2 new columns not reflected in your view. So again to fix this issue you need to use “sp_refreshview” as shown above.

That’s it guys. Hope you like and enjoyed this post.

Execute a predefined tSql query stored in a column of table

Today’s post is inspired from the question asked here in the stackoverflow site. So the question is how to execute a predefined tsql query which is stored in a column of table and then display the result in one report. You can find the answer here i gave him. You might find it yourself in this type of situation in your application where you need to display the consolidated report over your other report in which you need to display the name of the report and its associated count or some other data.

Allright, let’s create this situation. For explanation i copied the data from that question. so here is the required table with some required records in it.

CREATE TABLE #tmpConsReport
	ReportName [varchar](100), 
	ReportQuery [varchar](2000)

INSERT INTO #tmpConsReport (ReportName, ReportQuery) 
	VALUES ('Sales Daily Report Count', 'Select COUNT(*) FROM Sales WHERE SaleDate = CAST(GETDATE() AS DATE)'),
			    ('Sales Weekly Report Count', 'Select COUNT(*) FROM Sales WHERE SaleDate >= CAST(GETDATE() -6 AS DATE) '),
				('HR Dept. Count', 'Select COUNT(*) FROM Users DepartmentID = 1'),
				('Todays New Joiny', 'Select TOP 1 firstname FROM Users ORDER BY EmpID DESC');

Now to execute the query which is stored in column of table we need to build dynamic query which looks something like this –


SELECT @query += 'SELECT ' + QUOTENAME(ReportName, '''') 
							+ ' AS ReportName, CAST((' + ReportQuery + ') AS VARCHAR) AS Result UNION ALL ' 
From #tmpConsReport

SET @query = LEFT(@query, LEN(@query) - 10)


If you don’t get any idea after seeing this query, no problem. Let’s print that dynamic query and it looks like this –

SELECT 'Sales Daily Report Count' AS ReportName, 


SELECT 'Sales Weekly Report Count' AS ReportName, 
			CAST((Select COUNT(*) FROM Sales WHERE SaleDate >= CAST(GETDATE() -6 AS DATE) ) AS VARCHAR) AS Result 


SELECT 'HR Dept. Count' AS ReportName, 
			CAST((Select COUNT(*) FROM Users DepartmentID = 1) AS VARCHAR) AS Result 

SELECT 'Todays New Joiny' AS ReportName, 
         CAST((Select TOP 1 firstname FROM Users ORDER BY EmpID DESC) AS VARCHAR) AS Result

Hmmm Now looks better. But its easy right!!! Yes, using UNION ALL i have built the dynamic query and then execute it.

That’s it guys. Hope you like this post and learned something new today. Have a nice day.

NOWAIT hint in SQL Server

Today, I was reading about Table Hint in MSDN for other reason and came across this “NOWAIT” hint. In today’s post we will learn about NOWAIT hint. Well, SQL server uses locks to ensure that the data in your table can be accessed, without risk of corruption or dirty reads. Let say when you are inserting or updating records within transaction then your table are locked and other transaction have to wait to read or change the records.

Sometime we are in situation that when our application request for data and if our request is locked by SQL server then rather than wait for some interval, its better to move out. So in that type of situations “NOWAIT” hint comes into the picture. It means when you apply this hint and requesting for select records from the table, the command fails immediately if current request is blocked and then reporting an error.

Let’s see this practically –

Open two query window in your SQL Server Management Studio. Assume that you have a table named Products and you tried to insert some records within one transaction as shown below in query window 1.


     VALUES (1, 'iPhone 5s', 55200)


To create the blocking situation, i have not committed transaction in above query


Now in second query window if you fire select statement command without any hint then you have to wait for infinite interval of time as our above transaction is not completed. But what happen if you fire select command with “NOWAIT” hint like below –


Above query results in an error as shown below.


And you have to now manage the exception in your application and modify the code accordingly. Hope this post may help you in future.

That’s it for now folks. Hope you like this post.

Tip: Finding a value in any of the columns in a SQL Server table

Few days ago one of my colleague working in another team in my office asked me one question that he have had one table named “PatientDetail” with 58 columns and he wanted to find value from all varchar columns. As a developer the first solution came in my mind was – check value in each column like this FirstName = ‘hello’ OR LastName = ‘hello’ OR…so on. One thing to note here is there are more than 50 varchar columns out of 58 columns. So your query looks something like this –

SELECT * FROM PatientDetail
    WHERE FirstName = 'hello'
	  OR LastName = 'hello'
	  OR Column3 = 'hello'
	  OR Column4 = 'hello'
	  Or Column5 = 'hello'
	  OR ... Column52 = 'hello'

Don’t you think that above query becomes too much lengthy if i write each column name here. So What is the preferred solution ?

Hmm Do you know that you can use column name in “IN Clause” in sql server!! Really ??

Yes, the above query you can rewrite like this –

SELECT * FROM PatientDetail
     WHERE 'hello' IN (FirstName, LastName, Column3, Column4, Column5,...)

Well, we just reverse the IN Clause! and this is perfectly valid statement for sql server. Try it yourself. Now the only problem here is we still have to write more than 50 columns in “IN” clause. So to overcome this problem, let’s convert the above query into dynamic sql.

-- *** Prepare comma seperated column list ***
DECLARE @ColumnNames VARCHAR(3000)

WHERE TABLE_NAME = 'PatientDetail'
	AND DATA_TYPE IN ('char', 'nvarchar', 'text', 'varchar')

--PRINT @ColumnNames

	'SELECT * FROM PatientDetail
	WHERE ''hello'' IN (' + @ColumnNames + ')'

That’s it. Hope you like this tip. If you know any other solution then post it in the comments. Thank you for reading this post.

The Role of data type precedence for COALESCE and ISNULL Function in SQL Server

Few days ago, I had a discussion regarding key difference between COALESCE and ISNULL function with one of my colleague in my office. Though he knows some of the differences but he doesn’t have any idea about data type precedence in both of these functions. How the data type of the result is determine here. So i thought to write the post regarding that.

In COALESCE data type of the result determines based on the data type precedence. As per the MSDN

Returns the data type of expression with the highest data type precedence.

 Let’s see it practically.


As this function evaluates the arguments in order and returns the first non-nullable value. In the above query first argument is null, so it evaluates second argument which is “1” and is of “Int” data type. But the third argument is “GetDate()” which is of “Datetime” data type and DATETIME data type has a higher precedence than INT. So the output of the above query is in DATETIME i.e. When we execute that query SQL Server will convert “1” into DATETIME.

1900-01-02 00:00:00.000


Let’s see the another query.

SELECT COALESCE (NULL, 'Hello World', 1);


As you can see in above query that SQL Server will try to convert “Hello World” which is of VARCHAR data type into INT and results in error. Why ? because “INT” has highest data type precedence than “VARCHAR”. You can refer the Data Type Precedence chart from MSDN.

In ISNULL data type of the result determines based on the “first argument”, i.e data type precedence will not influenced the result. As per the MSDN

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value.

 NOTE: Here check_expression is the first argument and replacement_value is the second argument passed in the ISNULL  function.

Now Let’s see it practically.

DECLARE @var_datetime DATETIME

SELECT ISNULL(@var_datetime, 1)


Here variable “@var_datetime” is of type datetime and by default it’s value is NULL. So as the first argument is of type DATETIME which means result type is of datetime. So when you execute query, sql server will convert “1” into datetime and return the result.

Let’s see the another query.

DECLARE @var_int INT

SELECT ISNULL(@var_int, 'Hello World')


As you can see that first argument is of type INT. So SQL Server will try to convert “Hello World” which is of VARCHAR into INT and results in error.

That’s it for now. Hope you like this post.