Wednesday, June 14, 2017

How to Convert Dataset or DataTable to List collection

Some time we need to convert Dataset or DataTable values to List items for easy filter or any search in the data. List collection have more feature compare then Dataset. Like we can select top 3 users or role is admin or etc. So we can write own LINQ query in the list and get relevant information without using any loop. This will help to improve you application performance as well.

The given below example I have one userdetails class have properties of user information and another one is data set to list conversion method. If you pass dataset to the function then the function will return collection of userdetails list.

Sample Code:

Convert DataSet to List Function:
        public IList ConvertToList(DataSet ds)
            List lst = new List();
                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    var myData = ds.Tables[0].AsEnumerable().Select(r => new UserDetails
                        UserID = r.Field("UserID"),
                        UserName = r.Field("Username"),
                        EmailID = r.Field("EmailID"),
                        Name = r.Field("DisplayName"),
                        RoleID = r.Field("RoleID").ToString(),
                        Sex = r.Field("Sex"),
                        DOB = r.Field("DOB"),
                        Mobile = r.Field("Mobile"),
                        IsActive = r.Field("IsActive")
                    lst = myData.ToList();
            catch (Exception ex)
            return lst;

//User detail Class information
        public class UserDetails
            public string UserID { get; set; }
            public string UserName { get; set; }
            public string EmailID { get; set; }
            public string Name { get; set; }
            public string RoleID { get; set; }
            public string Sex { get; set; }
            public DateTime? DOB { get; set; }
            public string Mobile { get; set; }
            public bool IsActive { get; set; }

Monday, June 12, 2017

How to Generate SQL Query in excel sheet two column

Today I have faced one problem in excel sheet. I have a situation need to update user email id details from excel to my client database. The users email list is more than 1000 users.

For this update to excel we can do line by line update to the database or import into database and write the join query for update. But the simple way is we can generate INSERT/UPDATE SQL query and execute in to the database. For my situation I have already user data in the database. So I want to update the emailID based on the employeeID contition.

For that the below Excel comment will help to generate the SQL UPDATE query with simple steps and I can update with in 5mintus.

=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")

Excel Sheet:

Emp ID Name EMail id Project
1110 Karthik Sprots Domain
1111 Kumar Sprots Domain
1112 Vijay Sprots Domain
1113 Yuvan Education Domain

Excel Sheet Query Generator:
=CONCATENATE("UPDATE tb_employee set EMPL_EMAILID= ('",C2,"') WHERE EMPL_ID= (",A2,");")

Output SQL Query :
SQL Query
UPDATE tb_employee set EMPL_EMAILID= ('') WHERE EMPL_ID= (1110);
UPDATE tb_employee set EMPL_EMAILID= ('') WHERE EMPL_ID= (1111);
UPDATE tb_employee set EMPL_EMAILID= ('') WHERE EMPL_ID= (1112);
UPDATE tb_employee set EMPL_EMAILID= ('') WHERE EMPL_ID= (1113);

Finlay copy all the sql query from excel sheet and execute into the database. Its take 5 mints to complete your task.

Try this and let me know your feedback.

Friday, May 19, 2017

Cannot Start Microsoft Office Outlook. Cannot open the Outlook window. Invalid XML, The View Cannot be Loaded.

Today when i switch on my computer and opened the outlook application. suddenly one pop up message displayed "Cannot Start Microsoft Office Outlook. Cannot open the Outlook window. Invalid XML, The View Cannot be Loaded." . I dont know what should i do next?

Then google helped me to get my problem resolved. Below is the way to get the problem get solved.

Start-> Run or press the Windows Logo + R on your keyboard.
outlook.exe /resetnavpane (note the SPACE)

Note: outlook.exe and need to give (SPACE)  then /resetnavpane and type enter. Then the outlook will start with out any issue.

Enjoy the day.

Thursday, December 15, 2016

Display number of days/weeks in the year using SQL Query

Each time if i develop any report i need to write SQL Query for list out number of days or week in the year. So here i am giving you the simplified way to get the number of days name,date and week number of each date. One of my requirement is to display each week the sales report for the product. So the below query will help you to list out number of weeks in the year with week name.

SQL Query:

@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
-- You can change @year to any year you desire
SELECT @year = 2016
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year - 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year - 1900 + 1, 0)

;WITH cte AS (
@FirstDateOfYear AS DateList ,
DATENAME(dw, @FirstDateOfYear) AS DayName


SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.DateList),
DATENAME(dw, DATEADD(d, 1 ,cte.DateList)) AS DayName
FROM cte
WHERE DATEADD(d,1,cte.DateList) < @LastDateOfYear

SELECT *  ,'Week '+Convert(nvarchar,DATEPART(wk,DateList)) as WeekName

OPTION (MaxRecursion 370)

The Result :

DayID DateList DayName WeekName
1 1-Jan-16 Friday Week 1
2 2-Jan-16 Saturday Week 1
3 3-Jan-16 Sunday Week 2
4 4-Jan-16 Monday Week 2
5 5-Jan-16 Tuesday Week 2
6 6-Jan-16 Wednesday Week 2
7 7-Jan-16 Thursday Week 2
"" "" "" ""
"" "" "" ""
366 31-Dec-16 Saturday Week 53

The above SQL query we will get the day count start from 1 to end with 365 or 366 and display the date and the Date name. Finlay added week number in the list so when you want only week of the day or number of weeks in the year you can use only the WeekName column.You can use DISTINCT option to display only week number on the display result. We can make this query to our convenience way to get proper result set.

Friday, May 6, 2016

Simple Example of Singleton Design Pattern in C# Example

Simple Example of Singleton Design Pattern in C# Example

UI Code:
 ApplicationState state = ApplicationState.GetState();
            state.RoleId= "Admin";
            ApplicationState state2 = ApplicationState.GetState();
            label3.Text = state2.LoginId;
            label5.Text = state2.RoleId;
            label6.Text = (state == state2).ToString();

Singleton Class Code:
 class ApplicationState
        private static ApplicationState instance=null;
        // State Information  
        public string LoginId
        { get; set;}
        public string RoleId
        { get; set; }
        private ApplicationState()
        //Lock Object
        private static object lockThis = new object();
        public static ApplicationState GetState()  
            lock (lockThis)      

                if (ApplicationState.instance == null)
                  instance = new ApplicationState();      
                return instance;            

Simple Example of Abstract Factory Design Pattern in C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Interface_Implement;

namespace AbstractFactoryPattern

    class Program

        static void Main(string[] args)
            //Client Code
            IMachineFactory factory = new HighBudgetMachine();// Or new LowBudgetMachine();
            ComputerShop shop = new ComputerShop(factory);

            factory = new LowBudgetMachine();// Or new HighBudgetMachine();
            shop = new ComputerShop(factory);

            factory = new AverageBudgetMachine();// Or new HighBudgetMachine();
            shop = new ComputerShop(factory);


    #region "Interface and implimantation"
    public interface IProcessor
        void PerformOperation();
    public interface IHardDisk { void StoreData();}
    public interface IMonitor { void DisplayPicture();}

    public class ExpensiveProcessor : IProcessor
        public void PerformOperation()
            Console.WriteLine("Operation will perform Quickly");
    public class CheapProcessor : IProcessor
        public void PerformOperation()
            Console.WriteLine("Operation will perform Slowly");
    public class ExpensiveHDD : IHardDisk
        public void StoreData()
            Console.WriteLine("Data Will take less time to store");
    public class CheapHDD : IHardDisk
        public void StoreData()
            Console.WriteLine("Data will take more time to store");
    public class HighResolutionMonitor : IMonitor
        public void DisplayPicture()
            Console.WriteLine("Picture quality is Best");
    public class LowResolutionMonitor : IMonitor
        public void DisplayPicture()
            Console.WriteLine("Picture quality is Average");

    #region "Factory Code will be as follows"
    public interface IMachineFactory
        IProcessor GetRam();
        IHardDisk GetHardDisk();
        IMonitor GetMonitor();
    public class HighBudgetMachine : IMachineFactory
        public IProcessor GetRam() { return new ExpensiveProcessor(); }

        public IHardDisk GetHardDisk() { return new ExpensiveHDD(); }

        public IMonitor GetMonitor() { return new HighResolutionMonitor(); }

    public class LowBudgetMachine : IMachineFactory
        public virtual IProcessor GetRam() { return new CheapProcessor(); }
        public IHardDisk GetHardDisk() { return new CheapHDD(); }
        public IMonitor GetMonitor() { return new LowResolutionMonitor(); }
    //Let's say in future...Ram in the LowBudgetMachine is decided to upgrade then
    //first make GetRam in LowBudgetMachine Virtual and create new class as follows

    public class AverageBudgetMachine : LowBudgetMachine
        public override IProcessor GetRam()
            return new ExpensiveProcessor();

    public class ComputerShop
        IMachineFactory category;
        public ComputerShop(IMachineFactory _category)
            category = _category;

        public void AssembleyMachine()
            IProcessor processor = category.GetRam();
            IHardDisk hdd = category.GetHardDisk();
            IMonitor monitor = category.GetMonitor();

            //use all three and create machine


8+ Interview question ASP.NET,C# with an answer

1.    Tell me about yourself.
2.    Tell me your strength and weakness in Technically
3.    Explain .Net Framework and tell me about CTS,CLS, JIS and MSIL and the flow
4.    Explain your project architecture and data flow.
5.    What is MangoDB and advantage of that. Can I store N number of records?
6.    What is SQLLite and who developed that or who released?
7.    What is the difference between SVN and Perforce source controls.
8.    What is FFMPEG and how fast its convert the video files.
9.    What is the new feature in .NetFramework 3.5 (VS2008)
10.  Have you done any code review?

1.    How to do performance tuning  in SQL
2.    Tell me Type of join and difference between INNER JOIN and LEFT OUTER JOIN
3.    What is SQL constrain and explain.
Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column, whereas table level constraints are applied to the whole table
In SQL, we have the following constraints:
·         NOT NULL - Indicates that a column cannot store NULL value
·         UNIQUE - Ensures that each row for a column must have a unique value
·         PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly
·         FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
·         CHECK - Ensures that the value in a column meets a specific condition
·         DEFAULT - Specifies a default value for a column

4.    What are the index are available in SQL and explain
Clustered Index:
Only 1 allowed per table physically rearranges the data in the table to confirm to the index constraints for use on columns that are frequently searched for ranges of data for use on columns with low selectivity.

Non-Clustered Index:
Up to 249 allowed per table creates a separate list of key values with pointers to the location of the data in the data pages For use on columns that are searched for single values For use on columns with high selectivity.

5.    Difference between Inner join and Union
Join allows you to relate similar data in different tables.
Union returns the results of two different queries as a single record-set.
Union eliminate duplicate values but if you need duplicate values as well then use Union all.

6.    Difference between Temp table and Global temp table and life time of temp table
Local Temporary Tables
Local temp tables are similar to Permanent Tables in SQL Server, it accepts the single hash value "#" as the prefix when created. Syntax: (CREATE TABLE #t). They are visible only to the connection that creates it, and are deleted when the connection is closed.
Global Temporary Tables
Global Temporary Tables are also similar to Local Temporary Tables in SQL Server, except two "##" values are used as the prefix at the time of their creation. Syntax: (CREATE TABLE ##tablename). They are visible to all connections of SQLServer, and only destroyed when the last connection referencing the table is closed (in which we have created the Global Temporary Table).

7.    I have two table in my View and DBA deleted one of the table column in the table, then what will happen while running select query and how you will prevent to delete column from in table?
8.    What is Profiler?
9.    Different type of index in SQL and explain.
Ref 4.
10.    Difference between temp table and act table. Which one is good?
11.    What is primary key and foreign key?
12.    Difference between stored procedure and function
13.    How to capture error in SQL and how to return error log ID

14.    What is DEAD lock and how to prevent that?

1.    What is oops and explain
2.    What is class?
3.    What is Polymorphism and explain the Run time and compile time polymorphism, where can we use this.
In Polymorphism we have 2 different types those are

        -   Compile Time Polymorphism (Called as Early Binding or Overloading or static binding)

        -   Run Time Polymorphism (Called as Late Binding or Overriding or dynamic binding)
Compile Time Polymorphism

Compile time polymorphism means we will declare methods with same name but different signatures because of this we will perform different tasks with same method name. This compile time polymorphism also called as early binding or method overloading.

Method Overloading or compile time polymorphism means same method names with different signatures (different parameters)


public class Class1
public void NumbersAdd(int a, int b)
Console.WriteLine(a + b);
public void NumbersAdd(int a, int b, int c)
Console.WriteLine(a + b + c);

Run Time Polymorphism

Run time polymorphism also called as late binding or method overriding or dynamic polymorphism. Run time polymorphism or method overriding means same method names with same signatures.

In this run time polymorphism or method overriding we can override a method in base class by creating similar function in derived class this can be achieved by using inheritance principle and using “virtual & override” keywords.
In base class if we declare methods with virtual keyword then only we can override those methods in derived class using override keyword.

//Base Class
public class Bclass
public virtual void Sample1()
Console.WriteLine("Base Class");
// Derived Class
public class DClass : Bclass
public override void Sample1()
Console.WriteLine("Derived Class");
// Using base and derived class
class Program
static void Main(string[] args)
// calling the overriden method
DClass objDc = new DClass();
// calling the base class method
Bclass objBc = new DClass();
If we run above code we will get output like as shown below


Derived Class
Derived Class

4.    What is encapsulation?
Encapsulation is way to hide data, properties and methods from outside the world or outside of the class scope and exposing only necessary thing. Encapsulation complements Abstraction. Abstraction display only important features of a class and Encapsulation hide’s unwanted data or private data from outside of a class. It hides the information within the object and prevents from accidental corruption.

public - The type or member can be accessed by any other code in the same assembly or another assembly that references it.
private - he type or member can only be accessed by code in the same class.
protected - he type or member can only be accessed by code in the same class or in a derived class.
internal - The type or member can be accessed by any code in the same assembly, but not from another assembly.
protected internal - The type or member can be accessed by any code in the same assembly, or by any derived class in another assembly.

5.    What is the default access modifier of class and what is the default access modifier of Interface.
The default access modifier of the class is Internal and default class member is Private. 
The Default access modifier of Interface is internal and interface members are public. 
The Internal access modifier can only access with in the namespace. Outside of the name space you need to access any class or interface then you need to use public as a default access modifier.
6.    Difference between Abstract and interface. Where can I use this both?
7.    What is sealed class?
8.    How to achieve multiple inheritance in C#.
9.    What are the design patterns are available. Explain singleton patterns, Iterate pattern (if you using foreach in your patters means you are using Iterate pattern)
10.     Explain SOLID principle
S – Single-responsiblity principle
                -A class should have one and only one reason to change, meaning that a class should have only one job
O – Open-closed principle
                -Objects or entities should be open for extension, but closed for modification
L – Liskov substitution principle
                -Let q(x) be a property provable about objects of x of type T. Then q(y) should be provable for objects y of type S where S is a subtype of T.
I – Interface segregation principle
                -A client should never be forced to implement an interface that it doesn’t use or clients shouldn’t be forced to depend on methods they do not use.
D – Dependency Inversion Principle
                -Entities must depend on abstractions not on concretions. It states that the high level module must not depend on the low level module, but they should depend on abstractions.

11.    What is Static class in C#?
In your design, if you feel that a class should have a set of methods which operate only on arguments that we pass without initiating object of that class, then we can make that class as static class (Example: System.Math). Merely call those instance methods by class name.

12.    What is struct class?
A struct is a value type , a class is a reference type. Value types hold their value in memory where they are declared, but reference types hold a reference to an object in memory. If you copy a struct, C# creates a new copy of the object and assigns the copy of the object to a separate struct instance. However, if you copy a class, C# creates a new copy of the reference to the object and assigns the copy of the reference to the separate class instance. Structs can't have destructors, but classes can have destructors. Another difference between a struct and class is that a struct can't have implementation inheritance, but a class can.
By default structures are sealed, that is the reason structures are not supporting inheritance.
Structures can be used for small data structures. If developer feels that data members of structure cannot to be modified after creating structure, then having structure will suit.

13.    What is Assembly and Type of Assembly? What is private and public assembly?
Assembly is a compiled output of program which are used for easy deployment of an application. They are executables in the form of exe or dll. It also is a collection of resources that were used while building the application and is responsible for all the logical functioning.
Private Assembly - Refers to the assembly that is used by a single application. Private assemblies are kept in a local folder in which the client application has been installed.
Public or Shared Assembly - Refers to the assembly that is allowed to be shared by multiple applications. A shared assembly must reside in Global Assembly Cache (GAC) with a strong name assigned to it.

14.    What is static assembly?
Satellite Assemblies - are the assemblies to provide the support for multiple languages based on different cultures. These are kept in different modules based on the different categories available.

15.    What is DLL and tell me the Difference between EXE and DLL?
16.    How to register the assembly IN GAC?   

1.    Tell me Page life cycle.
2.    When I give what will happen and explain the flow.
3.    Tell me Client side state management and server side state management.
4.    What is Application Object and what are the types are available. Where the application object available.
5.    Type of session management in Server.
6.    What is authentication and authorization, what are the authorizations are available and how to achieve on that.
7.     What is passport authorization and how to implement?
8.    What is Tracing and how to enable that?
9.    How to Host application in IIS.
10.    What is application pool, worker process?
11.    What is httpHandler and httpmodule.
12.    Explain the response and request in IIS and how IIS handling this.
13.    What is web service and how to use it?
14.    How to secure the webservice.
15.    What is WSDL?
16.    What is SOAP?
17.    What is Serialization and De-Serialization?
18.    What is JOSON Serialization?
19.    Have you worked any payment gate way, Have you used any Encryption method in your project.
20.    What is query string and how to secure that?
21.    How to get text box value in Javascript.
22.    What are the client side validations are available.
23.    What is JSON?
24.    What is latest IIS version?
25.    Have you worked any cloud server and hosted any website in cloud.
26.  What is CLS and CTS?
CTS- CTS ensure that data types defined in two different languages get compiled to a common data type. This is useful because there may be situations when we want code in one language to be called in other language. We can see practical demonstration of CTS by creating same application in C# and VB.Net and then compare the IL code of both application. Here the datatype of both IL code is same.
 CLS- CLS is a subset of CTS. CLS is a set of rules or guidelines. When any programming language adheres to these set of rules it can be consumed by any .Net language .CTS

1. Difference between Data Adapter and Data Reader.
2. Which one is connected architecture and disconnected architecture?
3. What is Dataset?
4. What is Entity?