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.

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.

Tip of the day: Replace your switch case statement with generic Dictionary in c#

As i said in my previous post that i got the chance to work in one legacy application where i also optimized some of the code. Today’s post is inspired from one of the situation that i found in that application.

Now let me explain you the requirement. We have used “Dev Express” as reporting tool for our application and we have developed more than 20 reports. Each report binds to one view model / custom class i.e. you can say its a data source to that report. To fill our view model / custom class, we have used one third party dll which will return one class object but in their own format. So we developed individual method to bind our view model / custom class from their object. Before you think let me tell you one thing, We cannot develop common method here to bind model as each report contain different property and have their own logic. In this scenario any one can think of switch case or if else condition. So as expected i found that type of code in this application. Alright now no more detail and let’s jump into the situation and showing you some code.

First let’s create the required view model / custom class to bind report. Here assume that we wants to develop two reports, so we require two view model class as shown below.


class FranchiseeSalesEmployeeViewModel
{
	public int EmployeeID { get; set; }

	public string EmployeeName { get; set; }

	public int ExpectedTargetAmount { get; set; }

	public int AchievedAmount { get; set; }
}

class HREmployeeViewModel
{
	public int EmployeeID { get; set; }

	public string EmployeeName { get; set; }

	public int NoOfInterviweTaken { get; set; }

	public int NoOfRejectedCandidate { get; set; }
}

Now let’s create the source object which we’ll get from other third party dll.


class SourceThirdPartyObject
{
	public int EmployeeID { get; set; }

	public string EmployeeName { get; set; }

	public string DepartmentName { get; set; }

	// -- Other dependent sub class for HR activity

	// -- Other dependent sub class for Sales activity

	// -- and many more...
}

First let’s see the code in which we’ll use switch case statement.


static void Main(string[] args)
{
	// First bind the third party object
	// In real scenario we'll get the data from the third party dll
	SourceThirdPartyObject sourceObj = new SourceThirdPartyObject() {
		EmployeeID = 1,
		EmployeeName = "Bill Gates",
		DepartmentName = "Admin"
	};

	var modelObj = BindReportModel("Report_Name1", sourceObj);

	// same way for other report like this
	// var modelObj = BindReportModel("Report_Name2", sourceObj);
	// Now bind this model class to your report
}

private static object BindReportModel(string reportName, SourceThirdPartyObject sourceObj)
{
	object modelObj = null;

	switch (reportName)
	{
		case "Report_Name1":
			return BindModelForReportName1(sourceObj);

		case "Report_Name2":
			return BindModelForReportName2(sourceObj);

		// Same way for report3, report4 etc...

		//case "Report_Name3":
		//    return BindModelForReportName1(sourceObj);

		// Case so on...
	};

	return modelObj;
}

private static HREmployeeViewModel BindModelForReportName1(SourceThirdPartyObject sourceObj)
{
	// This method contain some complex logic to bind this model
	HREmployeeViewModel hrEmpModel = new HREmployeeViewModel()
	{
		EmployeeID = sourceObj.EmployeeID,
		EmployeeName = sourceObj.EmployeeName,
		NoOfInterviweTaken = 10,
		NoOfRejectedCandidate = 4
	};

	return hrEmpModel;
}

private static FranchiseeHREmployeeViewModel BindModelForReportName2(SourceThirdPartyObject sourceObj)
{
	FranchiseeHREmployeeViewModel franchiseeModel = new FranchiseeHREmployeeViewModel()
	{
		EmployeeID = sourceObj.EmployeeID,
		EmployeeName = sourceObj.EmployeeName,
		ExpectedTargetAmount = 50000,
		AchievedAmount = 45000
	};

	return franchiseeModel;
}

Now let’s see the same code, but this time we’ll replace the switch case statement with pre-initialize generic dictionary.


private static Dictionary<string, Func<SourceThirdPartyObject, object>> 
	methodDictionary = new Dictionary<string, Func<SourceThirdPartyObject, object>>
{
	{"Report_Name1", (sourceObj) => BindModelForReportName1(sourceObj) },
	{"Report_Name2", (sourceObj) => BindModelForReportName2(sourceObj) }
	// Same way for report3, report4 etc...
};

static void Main(string[] args)
{
	// First bind the third party object
	// In real scenario we'll get the data from the third party dll
	SourceThirdPartyObject sourceObj = new SourceThirdPartyObject() {
		EmployeeID = 1,
		EmployeeName = "Bill Gates",
		DepartmentName = "Admin"
	};

	var modelObj = methodDictionary["Report_Name1"](sourceObj);
    //var modelObj = BindReportModel("Report_Name1", sourceObj);
}

As you can see in the above code that we have completely replace our BindReportModel() method with Dictionary. So here we have already initialize our dictionary object with report name as key and its related bind method as value using Func delegate.

Reference

That’s it guys. Hope you enjoyed this post. Thanks for reading 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
 )
GO

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

Now let’s create the view.


CREATE VIEW view_Employee
AS
    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
	DROP COLUMN PANNo

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

ErrorInView

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.


EXEC SP_REFRESHVIEW view_Employee

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 –


DECLARE @query VARCHAR(MAX) = ''

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

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

PRINT(@query)
EXEC(@query)

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, 
			CAST((Select COUNT(*) FROM Sales WHERE SaleDate = CAST(GETDATE() AS DATE)) AS VARCHAR) AS Result 

UNION ALL 

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

UNION ALL 

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

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.

Implement multiple interfaces with same function name and parameters in a class

Few days ago, I got the consultancy work for one of my friend’s firm. In their project i have had faced one interesting issue where there were two interfaces which contain same function name with same parameters and i needed to implement it in one class.

Rather than go into much theory let’s create this situation and see how we can get rid of this situation.

But first we see that what happen if we implement those interfaces in one class. So let’s create the required interfaces and class. So here we have our first interface named – “IStudent

public interface IStudent
{
	///<summary>
	/// Get list of student who have birthday today
	/// </summary>
	/// <returns></returns>
	DataTable GetTodaysBirthdayList(string date = "");
}

And here we have second interface.

public interface IStaff
{
	///<summary>
	/// Get list of staff member who have birthday today
	/// </summary>
	/// <returns></returns>
	DataTable GetTodaysBirthdayList(string date = "");
}

Now let’s try to implement these two interfaces in class.

public class School : IStudent, IStaff
{
	#region IStudent Members

	public DataTable GetTodaysBirthdayList(string date = "")
	{
		var dtList = new DataTable();
		// Business logic to get list of student who have birthday today
		return dtList;
	}

	#endregion

	#region IStaff Members

	public DataTable GetTodaysBirthdayList(string date = "")
	{
		var dtList = new DataTable();
		// Business logic to get list of staff who have birthday today
		return dtList;
	}

	#endregion
}

Alright let’s rebuild the application and see what happen. Whoaa… we get the error!

InterfaceError

Error: Type “School” already defines a member “GetTodaysBirthdayList”

I think no need to explain the error message. Now let’s remove any of the method from the class and rebuild the application. Error is gone now, But the problem is how can you know that which interface method you wanna call!

So to overcome to this situation we need to explicitly implement those interfaces. How ? Let’s see this practically.

public class School : IStudent, IStaff
{

	#region IStudent Members

	DataTable IStudent.GetTodaysBirthdayList(string date)
	{
		var dtList = new DataTable();
		// Business logic to get list of studnet who have birthday today
		Console.WriteLine("=> From IStudent interface.");
		return dtList;
	}

	#endregion

	#region IStaff Members

	DataTable IStaff.GetTodaysBirthdayList(string date)
	{
		var dtList = new DataTable();
		// Business logic to get list of staff who have birthday today
		Console.WriteLine("=> From IStaff interface.");
		return dtList;
	}

	#endregion

	// You cannot directly access explicitly implemented interface method
	// So we need two public method to call above interface method

	public DataTable GetTodaysBirthdayListOfStudent(string date = "")
	{
		IStudent objStudent = (IStudent)this;
		return objStudent.GetTodaysBirthdayList();
	}

	public DataTable GetTodaysBirthdayListOfStaff(string date = "")
	{
		IStaff objStaff = (IStaff)this;
		return objStaff.GetTodaysBirthdayList();
	}
}

By creating an instance of the above class you cannot directly access explicitly implemented interface method. For that we need two public method to call implemented interface method as shown in above class.

And now its time to call the above class method, like this –

static void Main(string[] args)
{
    var school = new School();
            
    var staffList = school.GetTodaysBirthdayListOfStaff();
    var studnetList = school.GetTodaysBirthdayListOfStudent();
}

And we are done. Well after seeing above code you might think that instead of following the above stuff i can make one common interface with common method and inherit that interface in IStaff and IStudent interface. Well Ofcouse you can do that, But consider a situation where you are using any third party DLL or in your existing application you cannot alter or made any changes due to any architectural restriction.

Alright, Hope you enjoyed this post. Thanks for reading this post.

Resolve same namespace collision between two assembly

One of my friend worked in his freelancing project. In his project there were more than 4 class library projects used within his application. But by mistake he had created the same namespace with same class name within the two class library project. After adding the references of that class library in his main project, when he tried to create an instance of that class, he got the following error.


Error 1
The type ‘Lib1.PatientDataContext’ exists in both ‘d:\TFS\Lib1\bin\Debug\Lib1.dll’ and ‘d:\TFS\Lib2\bin\Debug\Lib2.dll’

It means the C# compiler gets confused that which assembly it has to use for creating an instance of the class. That’s why it throws an above error.

So he asked me to resolve his issue. well, i have never faced this type of error before. So i did some googling and came to the solution that by using extern alias provided in C#, above issue can be resolve.  How ? Let’s see this practically by creating that scenario.

In Visual studio, let’s create one console application and named it as – NameSpaceCollisionApp. Now within the same solution of the application add the two new class library project. In each class library assembly add one new class named – PatientDataContext.cs. But keep in mind that both of these class are within same namespace. The code of both the class looks like this –

ClassLibrary1 / PatientDataContext.cs

namespace DataServiceFactory.DataContext
{
    public class PatientDataContext
    {
        public PatientDataContext()
        {
        }

        public override string ToString()
        {
            return "From Class Library 1";
        }
    }
}

ClassLibrary2 / PatientDataContext.cs

namespace DataServiceFactory.DataContext
{
    public class PatientDataContext
    {
        public PatientDataContext()
        {
        }

        public override string ToString()
        {
            return "From Class Library 2";
        }
    }
}

When you build the application, you’ll get the error highlighted above. So let’s resolve it by following the below steps.

Step-1: In the solution explorer, expand the “References” folder and  select the required assembly name then right click on it and open its property window.

Service1Property

Step-2: In the property window, change the aliases name from “global” to “Service1 (Whatever you want)”.

ChangeAliase

Step-3: Same way follow the Step-2 for ClassLibrary2 reference also.

Step-4: Now to use it in your main application, you need to add – extern alias “alias name”; line of code to the top of your source file, something like this –

extern alias Service1;
extern alias Service2;

using System;

namespace NameSpaceCollision
{
    class Program
    {
        static void Main(string[] args)
        {
            var object1 = new Service1.DataServiceFactory.DataContext.PatientDataContext();

            var object2 = new Service2.DataServiceFactory.DataContext.PatientDataContext();

            Console.WriteLine(object1);
            Console.WriteLine(object2);
            Console.ReadLine();
        }
    }
}

And we are done. Hope you like this post and hope it may help to my future readers. You can find the demo application from here.

 

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)

SELECT @ColumnNames = ISNULL(@ColumnNames + ', ' + QUOTENAME(COLUMN_NAME), QUOTENAME(COLUMN_NAME)) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'PatientDetail'
	AND DATA_TYPE IN ('char', 'nvarchar', 'text', 'varchar')

--PRINT @ColumnNames

DECLARE @query VARCHAR(MAX)=
	'SELECT * FROM PatientDetail
	WHERE ''hello'' IN (' + @ColumnNames + ')'
	
EXEC SP_EXECUTESQL @query

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.