Hi SharePointers,
For those who are new to SharePoint development, I found that very useful article about using Dispose() for disposing SharePoint objects.
http://www.elumenotion.com/Blog/Lists/Posts/Post.aspx?ID=67

public static string GetSSPConnectionString(SPWebApplication webApp)
{
    try
    {
        ServerContext serverContext = ServerContext.GetContext(webApp);

        Type serverContextType = serverContext.GetType();
        // Get the value of the field m_ServerFarm;                
        SPPersistedObject serverFarm = (SPPersistedObject)serverContextType.
        GetField("m_ServerFarm", BindingFlags.Instance | BindingFlags.NonPublic).
        GetValue(serverContext);
        // Now we need to retrieve a collection of all the Shared Services Providers
        // public SharedResourceProviderCollection SharedResourceProviders { get; }
        PropertyInfo sharedSerivesProvidersProperty = serverFarm.GetType().
        GetProperty("SharedResourceProviders");
        IEnumerable sharedServicesProviders = (IEnumerable)
        sharedSerivesProvidersProperty.GetValue(serverFarm, null);

        Type sharedServiceProviderType = Assembly.GetAssembly(serverContextType).
        GetType("Microsoft.Office.Server.Administration.SharedResourceProvider");
        PropertyInfo webApplicationsProperty = sharedServiceProviderType.
        GetProperty("WebApplications");

        object serviceDB=null;
        foreach (SPPersistedObject sharedSericeProvider in sharedServicesProviders)
        {
            IEnumerable webApplications = (IEnumerable)webApplicationsProperty.
            GetValue(sharedSericeProvider, null);
            // Iterate through all the web applications associated with the current SSP.
            foreach (SPWebApplication webAppAssociatedWithSSP in webApplications)
            {
                if (webApp.Id == webAppAssociatedWithSSP.Id)
                {
                    serviceDB = sharedSericeProvider.GetType().GetProperty
                    ("ServiceDatabase").GetValue(sharedSericeProvider, null);
                    return ((Microsoft.SharePoint.Administration.SPDatabas(serviceDB)).
                    DatabaseConnectionString);                
                }
            }                    
        }
        return String.Empty;                  
    }
    catch (Exception ex)
    {
        ReportsLogger.GetInstance().LogException(ex);
        return String.Empty;
    }
    finally
    {
        ReportsLogger.GetInstance().Debug("Exiting method");
    }

}

You can also download the method above from here.

For those of you that are not familiar with the Property Bag concept, it is a place to store metadata or properties for your SharePoint objects. However, when exploring the SharePoint object model, I have noticed that SPWeb provides a Property bag to store metadata info related to a web site, where SPSite doesn’t. So the only way to store metadata info related to a site collection (SPSite) is to use the property bag of the root web.

SPSite  Represents a collection of sites on a virtual server, including a top-level site and all its subsites.
SPWeb Represents a Windows SharePoint Services Web site.

To store a property in the bag :

SPPropertyBag Bag =  site.RootWeb.Properties;
if (!Bag.ContainsKey("PropertyName"))
{            
      Bag.Add("PropertyName", "PropertyValue");
      Bag.Update();
}

And to remove a property, use the following snippet :

SPPropertyBag Bag =  site.RootWeb.Properties;
if (Bag.ContainsKey("PropertyName"))
{
    Bag["PropertyName"]=null;                            
    Bag.Update();
}

For more SharePoint code snippets, I would recommend SPCodeSnippets available at codeplex.

ListViewWebPart cannot be exported by design, that’s true! ListViewWebPart elements are tied to the underlying list and views by GUID, and the GUID is unique for each view and each list in each site.

But what if you need to export it to add it to an AllUsersWebPart element in an onet.xml file ?

The following code snippet will export any web part (even if it is a ListViewWebPart ) from the page specified.

, System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared);

foreach(WebPart webpart in mgr.WebParts )
{
          string Filename = @"c:\webparts\"+ webpart.ID + ".dwp";

          webpart.ExportMode = System.Web.UI.WebControls.
                                    WebParts.WebPartExportMode.All;
          mgr.ExportWebPart(webpart, writer);
          writer.Close();
}

Using Features makes it easy to add actions to menus of the user interface in SharePoint. To define a custom action for a particular menu, you must identify the menu by setting the location to the appropriate SharePoint namespace, and by using the ID that SharePoint uses to identify the specific location. The following example shows how to add a link to to the a view selector through a Feature.

<CustomAction
    Id="custom.ActionMenu"
    Location="Microsoft.SharePoint.StandardMenu"
    GroupId="ViewSelectorMenu"
    Title="XSLT View"
    RegistrationType="List"
    RegistrationId="101"

  >
<UrlAction Url="xsltdataviewurl.aspx"/>
</CustomAction>

Earlier versions of SharePoint did make some use of the .NET Framework, but SharePoint 2007 has been rebuilt from ground up on top of it, meaning SharePoint applications are in fact tremendously powerful ASP.NET applications.

What are Application Pools?
A classic example that I always utilize to discuss application pools is a web hosting company that hosts many websites on a common server, and therefore all the websites hosted on this server share the system resources. If one of the hosted websites had a memory leak, this potentially takes memory away from the other hosted sites, causing errors and inconveniences.
Prior to IIS 6.0, at least, that was the case. Too many server resources were shared between websites, and it was too easy for an error to creep in and damage the entire server process.

Fortunately, IIS 6.0 introduced a new concept called application pools. Application pools form boundaries around applications, separating them from each other even though they are being hosted on the same server.

Each application pool is given its own set of server resources (including memory). That way, the hosting company can now host each website in its own application pool to accomplish security, reliability, and availability and to prevent the leaky or misbehaving site from affecting other sites.

Application Pools in SharePoint Object Model

The SharePoint OM comprises types whose instances correspond to IIS entities such as application pools, web sites,…. These types permit you to program against these entities within your code

SharePoint represents each IIS application pool with an instance of a type named SPApplicationPool, which allows you to program against IIS application pools from your managed code. Here are some of the public methods of the SPApplicationPool class that you can use in your managed code:

• Provision. This method creates the application pool.
• Unprovision. This method removes the application pool.
• Update. This method updates and commits the changes made to the application pool.
• UpdateCredentials. This method updates and commits the credentials under which the
  application pool is running.

   1:  SPWebServiceCollection wsc = new SPWebServiceCollection(SPFarm.Local);
   2:  foreach (SPWebService ws in wsc)
   3:  {
   4:       SPApplicationPoolCollection appPoolCollection= ws.ApplicationPools;
   5:       foreach (SPApplicationPool appPool in appPoolCollection)
   6:       {
   7:           // Your code
   8:       }
   9:  }

I am writing this blog post because I have recently come across numerous blog posts and articles discussing how you can directly query SharePoint databases. I have also stumbled upon a web part available for free that shows the most popular content among the SharePoint farm, this web part directly queries the SharedServiceProvider database to retrieve the information needed by the web part. Moreover, I have recently downloaded a tool that grabs deleted documents from the content database and saves them back to your hard drive.

Before diving into the pros and cons of querying SharePoint databases, let’s explore them and see how we can write direct T-SQL queries against them.

Below is a very high-level database diagram. I would like to extend a special Thank you to Ethan for coming up with this useful diagram.

For instance, dbo.AllUserData is a table that holds information about all the list items for each list. Here is a sample query to retrieve the contributions of each user in a certain site collection:

SELECT      tp_author, count(*) as 'Number Of Documents & Items'
FROM        dbo.AllUserData
Where       tp_SiteId='GUID'
Group by    tp_author

And here is another query to the SharedservicesProvider database that retrieves the most popular documents:

SELECT ANLResource.DocName, COUNT_BIG(*) AS HitCount 
FROM ANLResourceHits 
INNER JOIN 
ANLWeb 
ON ANLResourceHits.WebGuid = ANLWeb.WebGuid 
INNER JOIN 
ANLResource
ON ANLResourceHits.ResourceId = ANLResource.ResourceId 
WHERE (CHARINDEX(’.aspx’, ANLResource.DocName) = 0) 
AND (CHARINDEX(’.swf’, ANLResource.DocName) = 0) 
GROUP BY ANLResource.DocName 
ORDER BY HitCount DESC

Well, I know it’s incredibly easy, you can extract as much data as you want and consume this Data in your custom solutions (Reports, Web Parts,…) and the sky is the limit.

BUT

1. This is completely unsupported by the EULA you agreed to when you installed SharePoint.
2. Your queries are not guaranteed to work after applying any patches or service packs to SharePoint since Microsoft could change the database schema anytime.
3. Directly querying the database can place extra load on a server and hence performance issues.
4. Direct SELECT statements against the database take shared read locks at the default transaction level so your custom queries might cause deadlocks and hence stability issues.
5. Your custom queries might lead to incorrect data being retrieved.

A Debate about the stability issues

I have recently took part in a debate on one of the forums with a guy who claims that there are no stability issues when you directly query the database since you can use the With (NoLock) clause in your TSQL Queries as follows :

Select * From dbo.AllDocs With (NoLock)

This may seems better, you can avoid the deadlocks by using (NoLock) clauses BUT you will run into another problem which is retrieving incorrect data because you will be observing the database in an intermediate state (Data are not committed yet).

Insertions and modifications of the SharePoint databases

It is clearly unsupported to update, delete, or insert records. The risks are surly far more obvious.
Also be aware that any database changes would definitely break the supportability as stated by Microsoft. Examples of such database changes include, but are not limited to, the following:

• Adding new indexes or changing existing indexes within tables .
• Adding database triggers.
• Adding, changing, or deleting any primary or foreign key relationships.
• Changing or deleting existing stored procedures.
• Adding new stored procedures.
• Adding, changing, or deleting any data in any table of any of the databases.
• Adding, changing, or deleting any columns in any table of any of the databases.
• Making any modification to the database schema .
• Adding tables to any of the databases.
• Changing the database collation

The proper and supported ways for data access in SharePoint

The process of data-retrieval in SharePoint should be executed via the SharePoint object model or the built-in SharePoint web services and this is attributed to the following:

1. Microsoft has gone through a lot of work to ensure that using the object model or web services will lead to stable and efficient database interactions.
2. The likelihood of Microsoft breaking their own object model is far less that the database schema changes.
3. You are not going to lose Microsoft supportability.

Summary

Direct Queries to SharePoint databases is a considered a no-no, you should use SharePoint object model or web services instead.Do not worry, I will show you in later posts how to do the data access through the object model, stay tuned!

I know the title seems weird, but that was exactly the subject of an e-mail message that I have received today from a SharePoint developer asking about the way he can execute some code when a document is published. The guy needs to send out the URL of a document as soon as it is published to a SharePoint group members for taking some actions.

Actually there is no such thing as “ItemPublished” event, the subject of the e-mail was misleading, the guy was searching for such an event but unfortunately SharePoint doesn’t provide it in its object model.

Workaround :

When a document in a library is published, “ItemUpdated” event fires and the only way to detect that an end user has chosen to publish a document is  by checking ModerationInformation in ItemUpdatedevent handler to see if the list item is in a published version or not. Approved is published.

properties.ListItem.ModerationInformation==SPModerationStatusType.Approved

Hope this helps..