Access SharePoint Data from the Database

When you are developing web parts or other kind of solutions for SharePoint it is possible you work yourself in a situation where you use the SharePoint API for your solutions but you do not want to use it because it is insufficient.

When you are in a situation like this you can choose to connect to the content database of your site collection. When you choose to connect to the database you have to remember you should not alter the database directly but use the API for changing properties.

A few days ago I was in a situation like this. One of my colleges asked me if there was a web part in SharePoint were in he could see all the alerts you are subscripted to. The answer was no so I started looking if I could develop a web part.

I started with retrieving the alerts for the current user like this:

SPAlertCollection alertCollection = SPContext.Current.Web.CurrentUser.Alerts; 

I thought I would get all off the alerts off the current user for the entire farm but that wasn’t it. The CurrentUser.Alerts only retrieves the alerts for the user within the context you are in, so I received all off the user alerts that were made in that particular SPWeb.

So I thought lets iterate trough all the site collections and then retrieve the alert like this:

 List<SPAlert> Alerts = new List<SPAlert>();

 SPWebApplication webApp = SPContext.Current.Site.WebApplication;

 foreach (SPSite site in webApp.Sites)
 {
    using (site)
    {
       foreach (SPWeb web in site.AllWebs)
       {
          using (web)
          {
             SPAlertCollection alerts = web.CurrentUser.Alerts;

             foreach (SPAlert alert in alerts)
             {
                Alerts.Add(alert);
             }
          }
       }
    }
 }

The solution works but the problem with this solution is when you get to a site you don’t have access to you will get a access denied and besides that if you have a large collection of SPWebs or SPSites it will take forever to perform this piece of code.

So my next solution was to retrieve the alerts from the database. You can do this by retrieving the content databases from your web application and connect to them. You can use a query like below to retrieve all the alerts for the user you want.

SELECT SchedSubscriptions.Id, SchedSubscriptions.NotifyFreq, SchedSubscriptions.WebId, SchedSubscriptions.SiteId, (SchedSubscriptions.SiteUrl+SchedSubscriptions.WebUrl) AS URL, SchedSubscriptions.ListTitle, SchedSubscriptions.AlertTitle, SchedSubscriptions.AlertType, SchedSubscriptions.Status
 FROM  SchedSubscriptions
 WITH  (NOLOCK)
 Where  SchedSubscriptions.UserId = 1 AND  SchedSubscriptions.Deleted = 0
 UNION 
 SELECT  ImmedSubscriptions.Id, NotifyFreq = 0, ImmedSubscriptions.WebId, ImmedSubscriptions.SiteId, (ImmedSubscriptions.SiteUrl+ImmedSubscriptions.WebUrl) AS  URL, ImmedSubscriptions.ListTitle, ImmedSubscriptions.AlertTitle, ImmedSubscriptions.AlertType, ImmedSubscriptions.Status
 FROM  ImmedSubscriptions
 WITH  (NOLOCK)
 Where  ImmedSubscriptions.UserId = 1 AND  ImmedSubscriptions.Deleted = 0

This is a simple query that queries the content database for all the alerts. The alerts are saved in two tables:

• ImmedSubscriptions
• SchedSubscriptions

The WITH (NOLOCK) we use in the query is for making sure the tables will not get locked will retrieving the values.

My code for retrieving all off the alerts for a user within a web application looked something like this:

 private const string getAlertCommand = "SELECT SchedSubscriptions.Id, SchedSubscriptions.NotifyFreq, SchedSubscriptions.WebId, SchedSubscriptions.SiteId, (SchedSubscriptions.SiteUrl+SchedSubscriptions.WebUrl) AS URL, SchedSubscriptions.ListTitle, SchedSubscriptions.AlertTitle, SchedSubscriptions.AlertType, SchedSubscriptions.Status FROM SchedSubscriptions WITH (NOLOCK) Where SchedSubscriptions.UserId = {0} AND SchedSubscriptions.Deleted = 0 UNION SELECT ImmedSubscriptions.Id, NotifyFreq = 0, ImmedSubscriptions.WebId, ImmedSubscriptions.SiteId, (ImmedSubscriptions.SiteUrl+ImmedSubscriptions.WebUrl) AS URL, ImmedSubscriptions.ListTitle, ImmedSubscriptions.AlertTitle, ImmedSubscriptions.AlertType, ImmedSubscriptions.Status FROM ImmedSubscriptions WITH (NOLOCK) Where ImmedSubscriptions.UserId = {0} AND ImmedSubscriptions.Deleted = 0";

 protected override void CreateChildControls()
 {
    base.CreateChildControls();

    List<SPContentDatabase> contentdatabases = new List<SPContentDatabase>();

    Guid siteID = SPContext.Current.Site.ID;
    int UserID = SPContext.Current.Web.CurrentUser.ID;

    SPSecurity.RunWithElevatedPrivileges(delegate()
    {
       SPWebApplication webAPP = null;

       using (SPSite site = new SPSite(siteID))
       {
          webAPP = site.WebApplication;

          if (webAPP != null)
          {
             contentdatabases = RetrieveContentDatabases(webAPP);

             foreach (SPContentDatabase database in contentdatabases)
             {

                //use the sql connection so that it will be disposed when we are done
                using (SqlConnection con = new SqlConnection(database.DatabaseConnectionString))
                {
                   //open the database connection
                   con.Open();

                   //Use the command. It will be disposed when we are done using it
                   using (SqlCommand com = con.CreateCommand())
                   {
                      com.CommandText = string.Format(getAlertCommand, UserID.ToString());

                      //read the informtion from the from the database                
                      using (SqlDataReader reader = com.ExecuteReader())
                      {
                          while (reader.Read())
                          {
                             //do what you like to do with the information you retrieved
                          }
                       }
                    }
                 }
              }
           }
        }
     });
 }