SQL Gist

gistfile1.txt

public int GetDescendantsCount(Item item)
        {
            using (SqlConnection conn = new SqlConnection(Sitecore.Configuration.Settings.GetConnectionString(Sitecore.Context.Database.ConnectionStringName))) 
            {     
                try    
                {
                    conn.Open();
                    Assert.IsNotNull(item, "A valid Sitecore item must be provided to this method");

                    string query = @"with cteChildren as
                                        (select ID
                                            from [dbo].[Items]
                                                where ID = CAST(@ItemId as UNIQUEIDENTIFIER)
                                                union all
                                                    select e.ID
                                                        from cteChildren cte
                                                            inner join [dbo].[Items] e
                                                                on cte.ID = e.ParentID)
                                        select count(ID) - 1 from cteChildren";
                                        
                    using(SqlCommand cmd = new SqlCommand(query, conn)) 
                    {                    
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        { 
                            while (dr.Read()) 
                            { 
                                // do your thing
                            } 
                        } 
                    }
                } 
                catch (Exception e) 
                { }
            }
        }