TreeView Hierarchy using Linq in c# (child have childrens means more than two levels)

public class Node {
            public int id { get; set; }
            public string name { get; set; }
            public int prntid { get; set; }
            public  List<Node> children { get; set; }
}


List<Node> parentsList = new List<Node>();
TestEntities db = new TestEntities();

 GetNodeTreeviewDetails();

 public List<Node> GetNodeTreeviewDetails()
        {
            try
            {
                parentsList = (from row in db.Tbl_Node.ToList()
                               where row.PARENT_ID == 0
                               select new Node
                               {
                                   id = row.ID,
                                   name = row.NODE_ID,
                                   prntid = row.PARENT_ID
                               }).ToList();
                GetChildNodeTreeviewDetails(parentsList);
                return parentsList;
            }
            catch (Exception ee)
            {
                throw ee;
            }
   }
        public void GetChildNodeTreeviewDetails(List<Node> nodesList)
        {
            foreach (Node childNode in nodesList)
            {
                List<Node> childNodesList = (from row in db.Tbl_Node.ToList()
                                             where row.PARENT_ID == childNode.id
                                             select new Node
                                             {
                                                 id = row.ID,
                                                 name = row.NODE_ID,
                                                 prntid = row.PARENT_ID
                                             }).ToList();
                childNode.children = childNodesList;
                GetChildNodeTreeviewDetails(childNodesList);
            }
        }

SET NOCOUNT ON in Sql server

Please refer this link:

http://dotnetprof.blogspot.in/2012/09/what-is-set-nocount-in-sql.html

difference between Top And Top with ties in Sql server

In this article, I will explain the use of the TOP WITH TIES Clause in SQL Server. In SQL Server, TOP WITH TIES,additional rows will be included if their values match, or tie, the values of the last row. So let's take a look at a practical example of how to use the TOP WITH TIES Clause in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

The WITH TIES can be used only with the following clause:

• Select Statemnet
• Order by clause is necessary for using this clause
• PERCENT clause

Creating a table in SQL Server

Now we create a table named employee using:

Create table Employee

(

EmpID int,

EmpName varchar(30),

EmpSalary int

)

The following is the sample data for the employee Table:

Top Clause

The SELECT TOP N query always returns exactly N records. The following example defines the TOP clause.

Example

SELECT [EmpID]

      ,[EmpName]

      ,[EmpSalary]

  FROM [master].[dbo].[Employee]

  Go

 Select   top(3)  [EmpName]  from [Employee]

order by [EmpName]

Output

Problem

In the above example, we can encounter the situation in which use of the top clause returns exactly N records and drops any record(s) arbitrarily that has the same value as the last record in the result set. Suppose the employee table has EmpName Rahul and the table contains one more EmpName with the same name, but it will not be in the result since they are ignored by the TOP clause.

Select Query Using TOP WITH TIES Clause

To see those recrods add the WITH TIES clause:

SELECT [EmpID]

      ,[EmpName]

      ,[EmpSalary]

  FROM [master].[dbo].[Employee]

  Go

 Select  top(3) [EmpName] from [Employee]

order by [EmpName]

 Go

 Select   top(3) with ties [EmpName]  from [Employee]

order by [EmpName]

Output

Recently, on this blog, I published an article on SQL SERVER – Interesting Observation – TOP 100 PERCENT and ORDER BY; this article was very well received because of the observation made in it. One of the comments suggested the workaround was to use clause WITH TIES along with TOP and ORDER BY. That is not the correct solution; however, but the same comment brings up the question regarding how WITH TIES clause actually works.

First of all, the clause WITH TIES can be used only with TOP and ORDER BY, both the clauses are required. Let us understand from one simple example how this clause actually works. Suppose we have 100 rows in the table and out of that 50 rows have same value in column which is used in ORDER BY; when you use TOP 10 rows, it will return you only 10 rows, but if you use TOP 10 WITH TIES, it will return you all the rows that have same value as that of the last record of top 10 — which means a total of 50 records.

refer below link for more understanding:-

http://blog.sqlauthority.com/2009/12/23/sql-server-order-by-clause-and-top-with-ties/

TOP WITH TIES in Sql server

The TOP clause is commonly used to get the top required rows from a result set. The beauty of this clause is that it can be used with the WITH TIES clause to retrieve all similar rows to a base result set.

According to BOL “WITH TIES specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified. “

For example, from the following simple table, I need to get records which have a minimum purchase date value. In the first method, we will use the common IN clause.

--Create temporary table
CREATE TABLE #MyTable (Purchase_Date DATETIME, Amount INT)
--Insert few rows to hold
INSERT INTO #MyTable
SELECT '11/11/2011', 100 UNION ALL
SELECT '11/12/2011', 110 UNION ALL
SELECT '11/13/2011', 120 UNION ALL
SELECT '11/14/2011', 130 UNION ALL
SELECT '11/11/2011', 150
--Get all records which has minimum purchase date (i.e. 11/11/2011)
SELECT * FROM #MyTable
WHERE Purchase_Date IN

       (SELECT MIN(Purchase_Date) FROM #MyTable)

We can also get our desired results by using TOP…WITH TIES.

SELECT TOP(1) WITH TIES * FROM #MyTable
ORDER BY Purchase_Date

Performance

By executing the above query, you can find TOP WITH TIES worked amazingly, but is this short code really smart code? Let’s compare their performances.



Though the TOP…WITH TIES clause really shortened our code, you can see that it performed poorly as compared to our traditional code. This happened just because of the ORDER BY clause. This poor performance can be controlled by placing a well defined index.

Getting values from table contains only one identity column

CREATE TABLE Test
(ID IDENTITY(1,1) INT NOT NULL)

INSERT INTO Test Default Values

Inserting default values in database while inserting record in Entity Framework

You can set "StoredGeneratedProperty" attribute of table column in the EDMX file to Computed to enable default value insertion in entity framework.

The Problem

While working with Entity Framework, you would have noticed that it does not insert default value into the database. You need to manually set values for those properties which have already a default value set in database table. This seems as an extra work to set values for default columns. Suppose, you are saving created date of a row in your table and for this you are using default constraint as today's days. Now when you insert a new record in database, it works absolutely fine but if you perform this operation using entity framework, it will not let you doing so. To insert default value, you need to manually assign a value to that column.

The Solution

If you are using code first approach, you can set default values using [DefaultValue()]  attribute or if you are using database first and using edmx file then you can set StoreGeneratedPattern = "Computed" of that particular column. Just follow the steps below.

Step 1: Open EDMX file and locate your table in that file.

Step 2: Select the property name. Right click and click on property.

Step 3: You will find a property named as StoreGeneratedPattern with value set as None.

Step 4: Set StoreGeneratedPattern = "Computed".

What should be the default behavior of LINQ-to-Entities while setting the default value of a field in SQL’s field designer ?

Sometimes it’s difficult to come up with a topic that we feel inspired to write about. I am writing about the problem I encountered while working with entity datamodel. After discussing with the team members I feel comfortable writing about it. I also feel good that this article would then help others who might have encountered the same problem.

Lets assume we create a table named TechZeus_Logging_Details having four fieldsPK_ID (auto incremented primary key) , LAST_NAME(varchar) , FIRST_NAME(varchar) and LOG_TIME(datetime)..
CREATE TABLE [dbo].[TECHZEUS_LOGGING_DETAILS] (

[PK_ID] [int] IDENTITY(1,1) NOT NULL,

[LAST_NAME] [varchar](50) NULL,

[FIRST_NAME] [varchar](50) NULL,

[LOG_TIME] [datetime] NULL )

Then we set the default value of a datetime field by getdate() bydefault to set the current time for newly inserted row

ALTER TABLE [dbo].[TECHZEUS_LOGGING_DETAILS] ADD CONSTRAINT [DF_TECHZEUS_LOGGING_DETAILS] DEFAULT (getdate()) FOR [LOG_TIME]

Now while inserting values into this table using direct SQL query , we can just omit the default field because that will be automatically taken care of as the default value is set in SQL server so that we dont need to set it explicitly.

INSERT INTO [dbo].[TECHZEUS_LOGGING_DETAILS](

LAST_NAME,FIRST_NAME)

VALUES(

‘ABC’, ‘XYZ’)

Here the matter of concern is that what we should do while using entity datamodel for such database operations?

The general misconception is to leave the default fields as such while saving changes using context object.

Something like this:

using (var contextObject = new DatabaseEntities())

{

  try {

 
         TECHZEUS_LOGGING_DETAILS objTZLD = new TECHZEUS_LOGGING_DETAILS(); //Create object of the table to insert data

         objTZLD.LAST_NAME = "XYZ"; // Assign value to LAST_FIELD field

         objTZLD.FIRST_NAME = "ABC"; // Assign value to FIRST_NAME field

 
         contextObject.AddToTECHZEUS_LOGGING_DETAILS(objTZLD); //Add object to insert data into table

         contextObject.SaveChanges(); //Commit inserted data or changed data.

       }

  catch(ex)

       {

         //Log exception into Exception Log table.

        }

}

This approach creates problem because Properties connected to [LOG_TIME] column which is set as default in SQL’s field designer will not auto-populate.after the SaveChanges action is performed on the Entity Context, rather NULL or default value(C# default value) will be written to the [LOG_TIME](datetime) field instead of setting the database level default value!!

So, to overcome this problem what should we do ? Well the answer is, we need to follow these 2 steps: Set the default value in SQL Server as usual as we do. After that Set ‘StoredGeneratedPattern‘ attribute to ‘Computed‘ against the default field in the .edmx file.We still need the default value in SQL Server though, the above setting will ensure Entity Framework’s expected behaviour. Now comes the question what is StoredGeneratedPattern in entitymodel? Basically it represents an enumeration that specifies three options indicating whether the column in the store schema definition language (SSDL) (.ssdl) needs a value during insert and update operations and it tells us what should be done if we insert or update rows. We can set it as None, Identity or Computed. i) None: No auto generated value is generated ii)Identity: A new value is generated on insert, but not changed on update iii)Computed: A new value is generated on insert and update. Note: There is always an another method to achieve the same behavior. Here also we have one more option i.e. we can manually set required value in the code itself and don’t rely on the SQL or Entity default settings. That will also ensure that we are not inserting nulls or any default values in tables but my point is why to avoid such provided properties instead of doing everything in our code. At the end of the day these are individual choices. So always take care of such small points which can lead to big issues later on.

The maximum recursion 100 has been exhausted before statement completion. in sqlserver

Error:-
WITH DateTable
AS
(
    SELECT CAST('2014-01-30 17:43:00.000' as Date) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE]) FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) <= cast('2016-05-10 05:47:03.000' as Date)
)
SELECT  dt.[DATE] FROM [DateTable] dt

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Solution:-
WITH DateTable
AS
(
    SELECT CAST('2014-01-30 17:43:00.000' as Date) AS [DATE]
    UNION ALL
    SELECT DATEADD(dd, 1, [DATE]) FROM DateTable
    WHERE DATEADD(dd, 1, [DATE]) <= cast('2016-05-10 05:47:03.000' as Date)
)
SELECT  dt.[DATE] FROM [DateTable] dt
option (maxrecursion 0);