Delete Orphaned Event Handler Using Code

During preupgradecheck, I found many orphaned event handlers listed out. This need to be deleted from almost every site. I googled a lot but not able to find any solution to delete this using code. So I decided to create my own solution.


My Solution is Loop over all web applications, then loop over all lists inside all webs. Look for event receivers attached with list. And then try to look for the assembly associated with event receiver is valid or not. If assembly is invalid delete the event receiver.


Download working project EventReceiverHelper. 


Check my code below:


//get local farm
            SPFarm localFarm = SPFarm.Local;


            //loop Services
            foreach (SPService spService in localFarm.Services)
            {
                //if service is WebService
                if (spService is SPWebService)
                {
                    SPWebService webService = (SPWebService)spService;


                    //loop over web applications
                    foreach (SPWebApplication webApp in webService.WebApplications)
                    {
                        //Loop Site Collections
                        foreach (SPSite site in webApp.Sites)
                        {
                            //Loop Sites
                            foreach (SPWeb web in site.AllWebs)
                            {
                                try
                                {
                                    //Loop Lists
                                    foreach (SPList list in web.Lists)
                                    {
                                        SPEventReceiverDefinitionCollection spEDC = list.EventReceivers;


                                        if (spEDC.Count > 0)
                                        {
                                            List<Guid> orphandIDs = new List<Guid>();
                                            for (int i = 0; i < spEDC.Count; i++)
                                            {
                                                SPEventReceiverDefinition spED = spEDC[i];
                                                string assembly = spED.Assembly;
                                                string className = spED.Class;


                                                System.Reflection.Assembly eventReceiverAssembly = System.Reflection.Assembly.LoadWithPartialName(assembly);


                                                if (eventReceiverAssembly == null)
                                                    orphandIDs.Add(spEDC[i].Id);
                                            }


                                            for (int i = orphandIDs.Count - 1; i >= 0; i--)
                                            {
                                                list.EventReceivers[i].Delete();
                                            }
                                        }
                                    }
                                }
                                catch (Exception ex)
                                {
                                    
                                }
                                web.Dispose();
                            }
                            site.Dispose();
                        }
                    }


                }
            }
        }

SharePoint Directly Query Database

Query Documents:

dbo.AllDocs is a table that holds information about all the documents in all document libraries for all sites and sub sites. Here is simple query to retrieve documents from "Shared Documents" library from all sites. 



SELECT *
FROM AllDocs 
JOIN AllLists 
ON AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON Webs.Id=AllLists.tp_WebId
 where 
 AllLists.tp_Title = 'Shared Documents'
 ORDER BY webs.title


The above query will return all documents including System Forms like "EditForm.aspx". In order to exclude these documents executue query below:



SELECT *
FROM AllDocs 
JOIN AllLists 
ON AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON Webs.Id=AllLists.tp_WebId
 where 
 AllLists.tp_Title = 'Shared Documents' and
 AllDOcs.Dirname not like '%/Forms%' and
 AllDocs.LeafName !='Forms'
 ORDER BY webs.title


To execute query on particular web add another condition in where clause "webs.Title='title of web'" as below:

SELECT *
FROM AllDocs 
JOIN AllLists 
ON AllLists.tp_Id=AllDocs.ListId
JOIN Webs
ON Webs.Id=AllLists.tp_WebId
 where 
 AllLists.tp_Title = 'Shared Documents' and
 AllDOcs.Dirname not like '%/Forms%' and
 AllDocs.LeafName !='Forms' and
 webs.title = 'Development'


To get detail about user contribution we can use query:




SELECT  count(*) as 'Number Of Documents & Items', UserInfo.tp_Title
FROM AllDocs, AllLists, Webs,UserInfo
where AllLists.tp_Id=AllDocs.ListId and
Webs.Id=AllLists.tp_WebId and
UserInfo.tp_SiteID = Webs.SiteId and
UserInfo.tp_ID = tp_Author and
webs.title = 'Development'
Group by UserInfo.tp_Title






Note:

1. All these queries are completed unsupported.
2. Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and stability issues. 

To overcome stability issue

To overcome stability issue when we directly query database we can use With (NoLock).  

Select * From dbo.AllDocs With (NoLock)