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

We have migrated one legacy application from old asp.net web-form to asp.net 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 –

TableData

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 –
ParsingErro

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()
{
	try
	{
		var connectionString = ConfigurationManager.ConnectionStrings["NorthwndConnectionString"].ConnectionString;

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

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

Result

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.

Advertisements

Tip of the day – How to create a class from json string using visual studio

In the last week, I had developed one web API method which return kinda complex json response. Then i gave that API to another team who gonna consumes it in their application. One of the team member of their team request me to provide class structure as per the api’s json response and that question inspired me to write this post.

Many times we waste our time to prepare class structure on our own from json string which sometimes leads us to some severe bugs. Well Microsoft already provide the solution to create concrete class from json string using visual studio. This feature is available only in VS 2015 and above. Let’s understand this feature by example.

Consider we have below json string –

{
	"APIResult": {
	"IsException": false,
	},
	"APIResponse": [
	  {
		"PlacedOrder": "91124",
		"ProductName": "Redmi 5A (Gold, 16 GB)",
		"ProductDetail": "Redmi 5A boasts of a beautiful fully-laminated 12.7 cm (5) HD display",
		"Price": "4,999",
		"RelatedProducts": [
			{
			"ProductID": "94666",
			"ProductName": "Nokia 6 (Matte Black, 64 GB)",
			"ProductDetail": "Get the Nokia 6 smartphone that exemplifies the ideal balance between performance and battery",
			"Price": "16,999"
			},
			{
			"ProductID": "59687",
			"ProductName": "HTC One E9 (White, 64 GB)",
			"ProductDetail": "No description available.",
			"Price": "21,500"
			}
		],
	}]
}

Now open one blank .cs file in visual studio and click on “Edit” menu and go to “Paste Special” which have 2 sub menus and then click on “Paste JSON As Classes”.
 

Paste_Special

Above process generates class structure as follows.

public class Rootobject
{
    public ApiResult APIResult { get; set; }
    public ApiResponse[] APIResponse { get; set; }
}

public class ApiResult
{
    public bool IsException { get; set; }
}

public class ApiResponse
{
    public string PlacedOrder { get; set; }
    public string ProductName { get; set; }
    public string ProductDetail { get; set; }
    public string Price { get; set; }
    public Relatedproduct[] RelatedProducts { get; set; }
}

public class Relatedproduct
{
    public string ProductID { get; set; }
    public string ProductName { get; set; }
    public string ProductDetail { get; set; }
    public string Price { get; set; }
}

That’s it. Hope you like this post.

Tip of the day: Different ways of creating empty List of Objects in c#

It has always been fun to play with C#. In this post we will see that how we can create a list of objects with different approach. Well, in this article’s title i have used the word “empty list of objects” which might be confusing for some developers. Actually, empty list mean – create an object but without assigning any value to the object’s property. So the scenario is – for one of my MVC application I need to bind the 5 empty rows to the kendo grid for bulk insert of the records. So whenever i open that page, kendo grid render 5 empty rows in editable mode.

In this post for better illustration, I have used the example of “Book” i.e. Let say I want to add multiple books for one library management software. So first let’s create one basic POCO class – Book with some properties, looks like following.

public class Book
{
	public string BookName { get; set; } = string.Empty;

	public string Author { get; set; } = string.Empty;

	public string ISBN { get; set; } = string.Empty;
}

So, Let’s begin our journey by exploring the syntax from very basic to some advance level. Before C# 3.0, we used to add objects and initialize collection something like this –

var bookList = new List<Book>();

// Intiazize the object and add it to the collection
var book1 = new Book();
bookList.Add(book1);

var book2 = new Book();
bookList.Add(book2);

and another way is using “for loop” like following –

var bookList = new List<Book>();

// Another one is using for loop
for(int i = 0; i < 2; i++)
{
       bookList.Add(new Book());
}

and another way is using “AddRange()” method – which add the objects to the specified collection.

var bookList = new List<Book>();

// Another one is using AddRange method.
bookList.AddRange(new[] {
					new Book(),
					new Book()
				});

and then c# 3.0 comes with a lot of enhancement and one of them is – Collection Initializers. It is a shortened syntax to create a collection.


// using collection initializer
var bookList = new List<Book>()
{
	new Book(),
	new Book(),
	new Book()
};

In the .Net framework there is one class – Enumerable which resides under “System.Linq” namespace. This class contain some static methods using which we can create the list of objects. So for example – using Enumerable.Repeat() method –


// using Enumerable.Repeat
var bookList = Enumerable.Repeat(new Book(), 2).ToList();

In the above method, the first argument is the object we want to create or repeat. Second argument is the number of times to repeat the object.

Another example is – using Enumerable.Range() method –


// and another one is Enumerable.Repeat
var bookList = Enumerable.Range(1, 2).Select(i => new Book()).ToList();

Well, Range() method generates a collection within a specified range. Kindly note there are so many use cases for this method.

All right, But i’m thinking of to use it in another application, so i decided to make an extension method and make that extension method generic. So here i have created two extension methods. First one will add the “N” number of objects to the list. Second one will return the collection of “N” number of objects.

public static class UtilityExt
{
	///<summary>
	/// Add "N" number of objects to the source list.
	/// </summary>
	/// <typeparam name="T"></typeparam>
	/// <param name="emptySource"></param>
	/// <param name="number">Number of elements to add</param>
	public static void AddNObjectsToCollection<T>(this List<T> emptySource, int number) where T : new()
	{
		emptySource.AddRange(Enumerable.Repeat(new T(), number));
	}

	///<summary>
	/// Returns the collection which contains "N" numbers of elements of type T
	/// </summary>

	/// <typeparam name="T"></typeparam>
	/// <param name="emptySource"></param>
	/// <param name="number">Number of elements to return</param>
	/// <returns></returns>
	public static IList<T> GenerateSpecificNumberOfCollection<T>(this IEnumerable<T> emptySource, int number) where T : new()
	{
		return Enumerable.Repeat(new T(), number).ToList();
	}
}

and you can call the above method like this –

// Calling first method - AddNObjectsToCollection
var bookList = new List<Book>();
bookList.AddNObjectsToCollection(2);

//  ==========  OR   ==========
// Calling second method - GenerateSpecificNumberOfCollection
var bookList = new List<Book>().GenerateSpecificNumberOfCollection(3);

Well, All the syntax mentioned above much affected the readability and repeatability of your code. Any ways, if you have any other way to achieve the same then feel free to add it in comment section and share with other. That’s it for now. Hope you like this post.

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.

Table_Result

Now my requirement is to produce the result like following –

Final_Result

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 –


DECLARE @DisplayCol NVARCHAR(MAX) = ''

SELECT
       @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
CROSS APPLY (
	VALUES
		('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.

Row_Constructor_Result

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) = ''

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

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

SELECT
	@ColumnToPivot += N', ' + QUOTENAME(BookId)
FROM dbo.Book WITH (NOLOCK)

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

SET @SqlQuery = N'
SELECT ColKey AS Attributes, ' + @DisplayCol + ' FROM
(
	SELECT BookId, tab.* FROM dbo.Book
	CROSS APPLY (
		VALUES
		(''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
EXEC SP_EXECUTESQL @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.

How to easily create self hosted signalr windows service using TopShelf framework

Recently i got the chance to work with windows service. As a web developer i have never created windows service in my carrier. Generally i used to refer MSDN and hence i was trying to understand the flow of windows service from MSDN. But for more complex example i did some googling in detail and found out “TopShelf” library.

Topshelf is an opensource windows service framework written in c#. Using TopShelf creating windows service is as easy as creating a console application. This framework will literally convert your console application into windows service. What!.. yes, you heard it correct. You can easily convert your typical console application into windows service and easily debug it and install it in “Service control manager” by using few lines of Topshelf’s configuration fluent API. It’s available on GitHub. Okay, in this blog post we’ll step-by-step create self hosted signalr server in windows service and consume it in another console application.

So before we go ahead, let’s understand what signalr is in brief. Well, SignalR is a library for a developer which allows real time bi-directional communication between server and client. Means using SignalR, server can broadcast its message instantly to the connected specific / all clients.

Now its time to do some code. First let’s create a blank solution project in visual studio. Now add one console application in that solution and name it – “SignalRServerUsingTopShelf“. Now add “TopShelf” dll from nuget as shown in below image.

NugetForTopShelf

Also Install Log4Net from nuget which is an open source library that allows application to log statements to a variety of targets

Note: Before you install TopShelf, your console application’s target .net framework must be 4.5.2 or greater.

Now let’s add “StartUp.cs” class to configure signalr middleware. This class contain “Configure()” method. In this method, we call “MapSignalR()” method which defines the root that client will use to connect our signalr Hub. To allow cross-domain request we set CORS middleware. But before that, you need to add the reference of Microsoft.AspNet.SignalR and Microsoft.Owin.SelfHost along with some other NuGet packages to our console application as shown in below image.

NuGetPackageList


public class StartUp
{
	public void Configuration(IAppBuilder app)
	{
		app.UseCors(CorsOptions.AllowAll);
		var hubConfiguration = new HubConfiguration
		{
			EnableDetailedErrors = true
		};

		app.MapSignalR(hubConfiguration);
	}
}

Let’s create signalr hub class. This class contains methods which allow real-time communication between server and client. You can think of this class as a mediator between server and client.


public class MyMessageHub : Hub
{
	//public static Dictionary<string, string> subscribedClients =
       //                 new Dictionary<string, string>();

	public void BroadcastMessage(string message)
	{
		Clients.All.BroadcastMessage(message);
	}
}

Now let’s add one new class named it – “SignalServer.cs” in which we write our core windows service related function and will later configure this class using TopShelf’s configurable API in program.cs file’s Main() method. Generally typical windows service class contains a method like OnStart, OnStop, OnPause etc. which determine what happens when the state of your service is changed. So in our case the “Start” method contains the defining and hosting of signalR server when my service will start and in “Stop” method i want to dispose the require resources which i have created on start method.


public class SignalRServer
{
	private ILog logger;

	IDisposable SignalR { get; set; }

	public SignalRServer(ILog logger)
	{
	     this.logger = logger;
	}

	public bool StartService()
	{
		logger.Info("Starting service...");
		var option = new StartOptions();
		//option.Urls.Add("http://localhost:18275");
		// You can either get dynamic ip OR set in app.config
		// But for demo purpose i have set it static
		option.Urls.Add("http://192.168.151.87:18275");
		SignalR = WebApp.Start(option);
		logger.Info("SignalR server started..");
		logger.Info("Service Started.");
		return true;
	}

	public bool StopService()
	{
		SignalR.Dispose();
		logger.Info("Service Stopped.");
		System.Threading.Thread.Sleep(1500);
		return true;
	}

	public bool PauseService()
	{
		logger.Info("I'm in Pause method");
		return true;
	}
}

Now its time to configure our “SignalServer.cs” class using TopShelf’s fluent API in program.cs.


class Program
{
	private static readonly ILog logger =
		LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

	static void Main(string[] args)
	{
		logger.Info("Programe launched");
		HostFactory.Run(config =>
		{
			config.Service(instance =>
			{
				instance.ConstructUsing(() =>
					new SignalRServer(logger));

				instance.WhenStarted(server => server.StartService());

				instance.WhenStopped(server => server.StopService());
			});

			config.SetServiceName("Signal_server");
			config.SetDisplayName("Signal server");
			config.SetDescription("Self hosted signal server using TopShelf");
			config.StartAutomatically();

			config.BeforeInstall(() =>
			{
				logger.Info("Service before install");
			});

			config.AfterInstall(() =>
			{
				logger.Info("Service after install");
			});

		});
		//Console.ReadLine();
	}
}

I guess no need to explain the above code as you can see in the above code, Just like normal windows service i have configured the start and stop method and set the service name etc. using TopShelf’s API. Now its time to run the code. Just press “F5” in vs and you can run it as normal console application. When you run the application, it’ll open this type of console window.
Output

Before we go ahead please take one note here – in SingalR when you specify ip address in url (refer SignalRServer class – StartService method) and while running the application sometimes it throws an exception of – “TargetInvocationException“. To get rid of that exception we need to register that url using “Netsh” command. Open command prompt in admin mode and type

— To Register url
netsh http add urlacl url=http://192.168.151.87:18275/ user=EveryOne

— To deregister url
netsh http delete urlacl url=http://192.168.151.87:18275/

netsh_addUrl

Now let’s install our service in “Service control manager” using topshelf command line utility. Go to bin/debug folder and copy the path. Now open command prompt in admin mode and paste the copied path and press enter. Then after enter below command –

“YourServiceName” install start

Topshelf will install the service and showing the following output which means your normal console application now works as a windows service.

Service_Install

You can verify it by opening service manager window.

HostedService

Uninstalling the service is also easy. Just type following command in command prompt.

“YourServiceName” uninstall

UnInstall_Service

This demo application source code is available for download on GitHub where I have added two more console app from which one works as message sender and another one works as message receiver. That’s it for now. Hope you like this post.

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.


DECLARE @Tab_ICDCodes TABLE (
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.


DECLARE @CodeId INT = 5

;WITH cteGetRootID
As
(

	SELECT 
		CodeID, CodeName, ParentID, 1 AS CodePosition
	FROM 
		@Tab_ICDCodes WHERE CodeID = @CodeId
	UNION All
	SELECT 
		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.

How to dynamically bind and configure column’s metadata in kendo grid for your MVC application

As a blogger you always strive for some interesting topic to write for your blog. Recently in one of our MVC application i got the chance to work with kendo grid control, but with some interesting requirement. Although i have already used kendo ui controls in other mvc application, but in this application i have had got very interesting requirement and somewhat challenging also.

Now let me explain you the requirement. Normally you will bind your kendo grid with some predefined columns. But in this case i need to bind the kendo grid where column type and number of columns to bind will decide at run-time. Let me add further one more point in this requirement – each column’s metadata will also decide at run-time. Well, column’s metadata mean column’s caption, datatype, width, format, header alignment and text alignment etc. will decide based on the type of column. Don’t worry, if you don’t get this point. Let’s see this practically.

For demo purpose, i have made two new classes. One class for actual data to bind in kendo grid. Name of that class is let say – RowValue. Another class is for column’s metadata. Name of that class is – ColumnMetaDataInfo
Here is the code of our both the classes.


public class RowValue
{
	public string PatientName { get; set; }

	public decimal OrderAmount { get; set; }

	public int PatientAge { get; set; }

	public DateTime OrderDate { get; set; }
}

public class ColumnMetaDataInfo
{
	public string FieldName { get; set; }

	public int Width { get; set; }

	public string Caption { get; set; }

	public string Format { get; set; }

	public string Align { get; set; }

	public bool Display { get; set; }

	public Type DataType { get; set; }
}

Well, for demo purpose i have defined the two classes, In real scenario i have two separate tables in the database – one for actual data and another for column metadata. Now let’s use both of these classes in Model which we’ll pass it in our index.cshtml View.


public class GridViewModel
{
       public List<ColumnMetaDataInfo> Columns { get; set; }

       public List<RowValue> RowValues { get; set; }
}

Now its time to initialize the above viewModel class and fill the defined property with required data in “Home” controller’s action method.


public class HomeController : Controller
{

	public ActionResult Index()
	{
		var gridViewModel = new GridViewModel()
		{
			Columns = new List<ColumnMetaDataInfo>()
			{
				new ColumnMetaDataInfo
					{
						Caption = "Patient Name",
						FieldName = nameof(ColumnInfo.PatientName),
						Width = 50,
						Display = true,
						Format = "",
						Align = "left",
						DataType = typeof(string)
					},
				new ColumnMetaDataInfo
					{
						Caption = "Order Amount",
						FieldName = nameof(ColumnInfo.OrderAmount),
						Width = 15,
						Display = true,
						Format = "{0:C2}",
						Align = "right",
						DataType = typeof(decimal)
					},
				new ColumnMetaDataInfo
					{
						Caption = "Patient Age",
						FieldName = nameof(ColumnInfo.PatientAge),
						Width = 15,
						Display = false,
						Format = "{0:N2}",
						Align = "right",
						DataType = typeof(int)
					},
				new ColumnMetaDataInfo
					{
						Caption = "Order Date",
						FieldName = nameof(ColumnInfo.OrderDate),
						Width = 20,
						Display = true,
						Format = "{0:MM/dd/yyyy}",
						Align = "center",
						DataType = Type.GetType("System.DateTime")
					}
			},
			// Actual value to bind for each row
			RowValues = new List<ColumnInfo>()
			{
				new ColumnInfo { PatientName = "Krishnraj Rana", OrderAmount = 100.1M, PatientAge = 25, OrderDate = new DateTime(2016, 11, 25) },
				new ColumnInfo { PatientName = "Vishal Vagadia", OrderAmount = 200.1M, PatientAge = 35, OrderDate = new DateTime(2016, 11, 29) },
				new ColumnInfo { PatientName = "Birju Patel", OrderAmount = 300, PatientAge = 45, OrderDate = new DateTime(2016, 11, 28) }
			}
		};

		return View(gridViewModel);
	}
}

Now its time to bind the model with kendo grid and here is the code of Index.cshtml view in which we pass the above ViewModel class.


@model KendoGridDynamicColumn.Models.GridViewModel

@{
    ViewBag.Title = "Home Page";
}
	<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.common.min.css" />
	<link rel="stylesheet" href="http://cdn.kendostatic.com/2014.3.1316/styles/kendo.default.min.css" />
<script src="~/Scripts/jquery-1.12.3.min.js"></script>
<script src="~/Scripts/kendo.all.min.js"></script>
<script src="~/Scripts/kendo.aspnetmvc.min.js"></script>


<div class="jumbotron">

<h2>Dynamic Kendo Grid</h2>


<h4>POC - V.1.0.0</h4>

</div>



<div class="row">

<div class="kendolist">
        @(Html.Kendo().Grid(Model.RowValues)
            .Name("grdDynamicCol")
            .Columns(columns =>
            {
                foreach (var column in Model.Columns)
                {
                    columns.Bound(column.FieldName).Title(column.Caption)
                        //.Visible(column.Display)
                        .Format(column.Format)
                        .Width($"{column.Width}%")
                        .HtmlAttributes(new { @style = $"text-align:{column.Align.ToLower()}" })
                        .HeaderHtmlAttributes(new { @style = $"text-align:{column.Align.ToLower()}" });
                }
            })
            .Pageable()
            .Sortable()
            .Resizable(resize => resize.Columns(true))
            .DataSource(dataSource => dataSource
                .Ajax()
                .Model(model =>
                {
                    foreach (var column in Model.Columns)
                    {
                        model.Field(column.FieldName, column.DataType);
                    }
                })
            )
        //.ColumnMenu()
        //.HtmlAttributes(new { @class = "gridview" })
        )
</div>

</div>

<script>
    $(document).ready(function () {

        HideGridColumn();
    });

    function HideGridColumn() {
        var grid = $("#grdDynamicCol").data("kendoGrid");
        @foreach (var column in Model.Columns.Where(col => col.Display == false))
        {
            <text>grid.hideColumn('@column.FieldName');</text>
        }
    }

    function gvchange(e) {
        var grid = $("#grdDynamicCol").data("kendoGrid");
        if (e.checked) {
            grid.showColumn(e.id);
        } else {
            grid.hideColumn(e.id);
        }
    }
</script>

and when grid binds, this is how it look in the browser.

result

That’s it. For any query or doubt, write in the comment section. Hope you enjoyed 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
,@FrnID INT = NULL
,@AccountNickName VARCHAR(50)
,@SystemName VARCHAR(30) = NULL

AS
BEGIN

SET NOCOUNT ON;

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

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

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

	 END

	 -- Check if @FrnID is not null
	 IF @FrnID IS NOT NULL
	 BEGIN

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

	 END

END

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
,@FrnID INT = NULL
,@AccountNickName VARCHAR(50)
,@SystemName VARCHAR(30) = NULL

AS
BEGIN

	SET NOCOUNT ON;

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

END

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]
(
	@RoleID TINYINT,
	@MenuID INT
)
RETURNS BIT
WITH SCHEMABINDING
AS
BEGIN
	
	DECLARE @IsAuthorised BIT = 0

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

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.

Tip of the day: Get the first element from the xml using LINQ to XML

Today, one of my colleague in my office asked me a question that how to get the first element from the XML string. In my previous organization, i had worked with XML but using old methodology. So i thought this is good opportunity to learn and explore LINQ to XML. After some googling i told him that using XElement class of LINQ to XML you can full fill your requirement.

So what exactly the requirement is? Well, In one of my application module, we have save some configuration information in XML format in the database. We need to display the first element of the XML in one label in another web page. So let’s see the XElement class of LINQ to XML in action.

What is XElement?

XElement is one of the fundamental class of LINQ to XML. You can use this class to parse the XML string, create the new element, change the content of the element etc. In this post we’ll use this class to parse the XML string. Well, by not going into much detail let’s see this practically.

Consider i have XML in below format.


<ApplicationName>
  <ModuleConfig AccountNo="Test123" Type="M56X35" Source="XML" PNKNumber="12345" Final="False" />
  <LibraryList>
    <Library1 FolderName="Vendor" />
    <Library12 FolderName="Dealer" />
    <Library123 FolderName="Dealer" />
  </LibraryList>
</ApplicationName>

Now his requirement is –
1). Read the first element from the “LibraryList” tag which is “Library1”.
2). Read the value of attribute “PNKNumber” from “ModuleConfig” tag.

Let’s create the new console application in visual studio.

Here is the solution for the first requirement.


using System.Linq;
using System.Xml.Linq;
using static System.Console;

class Program
{
	static void Main(string[] args)
	{
		string xml = @"<ApplicationName>
						  <ModuleConfig AccountNo=""Test123"" Type=""M56X35"" Source=""XML"" PNKNumber=""12345"" Final=""False"" />
						  <LibraryList>
							<Library1 FolderName=""Vendor"" />
							<Library12 FolderName=""Dealer"" />
						  </LibraryList>
					  </ApplicationName>";

		// First let us parse the xml string using XElement
		var parsedXML = XElement.Parse(xml);

		// Now get the first element from the <LibraryList>  tag.
		string firstElement = parsedXML.Element("LibraryList").Descendants()
                                     .FirstOrDefault().Name.LocalName;
		WriteLine("First Element is - {0}", firstElement);
		ReadLine();
	}
}

Output

First Element is – Library1

Same way to get the 2nd or upto nth tag, you can use the ElementAtOrDefault() method by passing tag index like this –


string elementAt = parsedXML.Element("LibraryList").Descendants()
                  .ElementAtOrDefault(1).Name.LocalName;
WriteLine("Element at Index 1 - {0}", elementAt);
// Output 
// Element at Index 1 - Library12
		
elementAt = parsedXML.Element("LibraryList").Descendants()
            .ElementAtOrDefault(2).Name.LocalName;
WriteLine("Element at Index 2 - {0}", elementAt);
// Output 
// Element at Index 1 - Library123

Here is the solution for the second requirement – Read the value of attribut “PNKNumber” from “ModuleConfig” tag.


using System.Linq;
using System.Xml.Linq;
using static System.Console;

class Program
{
	static void Main(string[] args)
	{
		string xml = @"<ApplicationName>
						  <ModuleConfig AccountNo=""Test123"" Type=""M56X35"" Source=""XML"" PNKNumber=""12345"" Final=""False"" />
						  <LibraryList>
							<Library1 FolderName=""Vendor"" />
							<Library12 FolderName=""Dealer"" />
                            <Library123 FolderName=""Vendor"" />
						  </LibraryList>
					  </ApplicationName>";

		// First let us parse the xml string using XElement
		var parsedXML = XElement.Parse(xml);

		// Read the value of attribute "LNKNumber" from "ModuleConfig" tag.
		// First we check whether "LNKNumber" attribute exists within this "ModuleConfig" tag or not.
		if (parsedXML.Element("ModuleConfig").Attributes().Any(x => x.Name == "PNKNumber"))
		{
			// Now we read the value of attribute "LNKNumber"
			var attrValue = parsedXML.Element("ModuleConfig").Attribute("PNKNumber").Value;
			WriteLine("Attribute value is - {0}", attrValue);
		}
		else
			WriteLine("Attribute PNKNumber does not exists in the xml.");
		
		ReadLine();
	}
}

Output

Attribute value is – 12345

Same way you can check for other attribute also. LINQ to XML really reduce the number of lines of code in your application for complex requirement. My above example is simple, so you can not find some major difference. Any ways that’s it for now. Hope you like this post.