Wednesday, July 27, 2011

Configure NH in code instead of hibernate.cfg.xml

In one of my ASP.Net MVC projects xml file was used for NH configuration (hibernate.cfg.xml) because of historical reasons. It was annoying because settings were stored in several files: web.config and hibernate.cfg.xml. It was double annoying because Fluen NHibernate contains API for configuration (in my project Fluent NH was used as well). But as it often happens I had no time to fix it. Configuration code looked like this:

   1: public class ConfigurationFactory
   2: {
   3:     public Configuration Build(string configurationFile)
   4:     {
   5:         var configuration = new Configuration();
   6:  
   7:         if (string.IsNullOrEmpty(configurationFile))
   8:             configuration.Configure();
   9:         else
  10:             configuration.Configure(configurationFile);
  11:  
  12:         return Fluently.Configure(configuration)
  13:             .Mappings(cfg =>
  14:             {
  15:                 cfg.FluentMappings.AddFromAssemblyOf<UserMap>()
  16:                     .Conventions.Setup(mappings =>
  17:                         {
  18:                             mappings.AddAssembly(typeof(UserMap).Assembly);
  19:                             mappings.Add(ForeignKey.EndsWith("Id"));
  20:                         });
  21:             }).BuildConfiguration();
  22:     }
  23: }

And configuration file is the following:

   1: <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
   2:   <session-factory>
   3:     <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
   4:     <property name="connection.connection_string">...</property>
   5:     <property name="show_sql">false</property>
   6:     <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
   7:     <property name="cache.provider_class">NHibernate.Caches.SysCache.SysCacheProvider,NHibernate.Caches.SysCache</property>
   8:     <property name="cache.use_query_cache">true</property>
   9:     <property name="adonet.batch_size">100</property>
  10:      <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
  11:   </session-factory>
  12: </hibernate-configuration>

Recently I had time to fix it and publish it here for quick reference:

   1: public class ConfigurationFactory
   2: {
   3:     public Configuration Build(string connectionString)
   4:     {
   5:         if (string.IsNullOrEmpty(connectionString))
   6:         {
   7:             connectionString =
   8:                 ConfigurationManager.ConnectionStrings["connectionStringName"].ConnectionString;
   9:         }
  10:  
  11:         return Fluently.Configure()
  12:             .Database(
  13:                 MsSqlConfiguration.MsSql2005
  14:                     .Cache(c => c
  15:                         .UseQueryCache()
  16:                         .ProviderClass<SysCacheProvider>())
  17:                     .ConnectionString(connectionString)
  18:                     .AdoNetBatchSize(100)
  19:                     .DoNot.ShowSql()
  20:                     .ProxyFactoryFactory(typeof(ProxyFactoryFactory))
  21:             )
  22:             .Mappings(cfg =>
  23:             {
  24:                 cfg.FluentMappings.AddFromAssemblyOf<UserMap>()
  25:                     .Conventions.Setup(mappings =>
  26:                         {
  27:                             mappings.AddAssembly(typeof(UserMap).Assembly);
  28:                             mappings.Add(ForeignKey.EndsWith("Id"));
  29:                         });
  30:             }).BuildConfiguration();
  31:     }
  32: }

Here we read connection string from web.config and do the rest actions via Fluent configuration API. After that hibernate.cfg.xml can be finally removed from the project.

Saturday, July 23, 2011

Change page URL for social comments without SP1 in Sharepoint

Social comments are part of Sharepoint 2010 social computing features. They allow users to comment content on the site built on Sharepoint. There is one problem with using social comments – internally they are bind to the page using absolute URL of this page. It means that if you e.g. move page from one site to another or change URL of the whole site – comments will be lost.

About a month ago Microsoft released Service pack 1 for Sharepoint 2010. It contains new method MergeSocialNotes() in SocialDataManager class. There is also new PowerShell cmdlet Move-SPSocialComments – wrapper for mentioned class. The following post contains other improvements in social features: Changes to Social Computing features in SharePoint Server 2010 Service Pack 1. This is useful enhancement, but what if you don’t have SP1 installed yet, but still need to change URL for social comments? E.g. if customers didn’t approve SP1 installation yet. This is possible and in this post I will show how to do it.

First of all you need to know that social comments are stored in social database of User profile service application – in SocialComments table:

image

As you can see it has UrlID – foreign key to another table Urls which contains absolute URLs:

image

Now let’s see how SocialDataManager.MergeSocialNotes() method is implemented in SP1:

   1: internal static bool MergeSocialNotes(UserProfileApplication userProfileApplication,
   2: Guid partitionID, string oldUrl, string newUrl)
   3: {
   4:     if (null == userProfileApplication)
   5:     {
   6:         throw new ArgumentNullException("userProfileApplication");
   7:     }
   8:     if (string.IsNullOrEmpty(oldUrl))
   9:     {
  10:         throw new ArgumentNullException("oldUrl");
  11:     }
  12:     if (string.IsNullOrEmpty(newUrl))
  13:     {
  14:         throw new ArgumentNullException("newUrl");
  15:     }
  16:     if (!userProfileApplication.CheckAdministrationAccess(
  17: UserProfileApplicationAdminRights.ManageSocialData))
  18:     {
  19:         throw new UnauthorizedAccessException();
  20:     }
  21:     using (SqlCommand command = new SqlCommand("dbo.proc_SocialData_MergeSocialNotes"))
  22:     {
  23:         command.CommandType = CommandType.StoredProcedure;
  24:         command.Parameters.Add("@partitionID", SqlDbType.UniqueIdentifier).Value = partitionID;
  25:         command.Parameters.Add("@correlationId", SqlDbType.UniqueIdentifier).Value = ULS.CorrelationGet();
  26:         command.Parameters.Add("@oldUrl", SqlDbType.VarChar).Value = oldUrl;
  27:         command.Parameters.Add("@newUrl", SqlDbType.VarChar).Value = newUrl;
  28:         SqlParameter parameter = new SqlParameter("@success", SqlDbType.Bit);
  29:         parameter.Direction = ParameterDirection.Output;
  30:         command.Parameters.Add(parameter);
  31:         userProfileApplication.SocialDatabase.SqlSession.ExecuteNonQuery(command);
  32:         if (!((bool) command.Parameters["@success"].Value))
  33:         {
  34:             return false;
  35:         }
  36:     }
  37:     return true;
  38: }

Implementation is quite simple – it just calls stored procedure dbo.proc_SocialData_MergeSocialNotes with specified parameters. This procedure is defined in SQL scripts socialsrp.sql and socialup.sql which are located in 14\Template\SQL\SPS folder (scripts are updated with SP1, previous version didn’t have this stored procedure). Both files contain the same code for dbo.proc_SocialData_MergeSocialNotes (I’m not sure why it is duplicated in 2 files):

   1: IF  EXISTS (SELECT * FROM dbo.sysobjects
   2: WHERE id = OBJECT_ID(N'[dbo].[proc_SocialData_MergeSocialNotes]')
   3: AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
   4: DROP PROCEDURE [dbo].[proc_SocialData_MergeSocialNotes] 
   5: GO
   6:  
   7: CREATE PROCEDURE [dbo].[proc_SocialData_MergeSocialNotes] 
   8:     @partitionID uniqueidentifier,
   9:     @oldUrl varchar(2048),
  10:     @newUrl varchar(2048),
  11:     @success bit = null output,
  12:     @correlationId uniqueidentifier = null
  13: AS
  14: BEGIN
  15:     SET NOCOUNT ON;
  16:  
  17:     DECLARE @oldUrlID bigint
  18:     DECLARE @newUrlID bigint
  19:  
  20:     exec proc_Social_GetUrlID @partitionID, @oldUrl, @oldUrlID output, @correlationId
  21:     exec proc_Social_GetUrlID @partitionID, @newUrl, @newUrlID output, @correlationId
  22:  
  23:     IF (@oldUrlID is null)
  24:     BEGIN
  25:         -- nothing to merge
  26:         set @success = 1
  27:         return
  28:     END
  29:  
  30:     IF (@newUrlID is null)
  31:     BEGIN
  32:         EXEC proc_Social_EnsureUrlID @partitionID = @partitionID, @url = @newUrl,
  33: @urlID = @newUrlID output, @correlationId = @correlationId;
  34:     END
  35:  
  36:     IF (@newUrlID is null)
  37:     BEGIN
  38:         -- not able to create new Url - error
  39:         set @success = 0
  40:         return
  41:     END
  42:  
  43:     UPDATE SocialComments
  44:     SET UrlID = @newUrlID
  45:     WHERE UrlID = @oldUrlID
  46:  
  47:     -- we succeed, even if there is no data to migrate
  48:     set @success = 1
  49: END
  50: GO

It uses 2 other stored procedures: proc_Social_GetUrlID and proc_Social_EnsureUrlID which existed before SP1. The code of the procedure is quite simple: it ensures that newUrl exists in Urls table and after updates foreign keys UrlID in SocialComments table to relate comments with new URL. In order to use it in environment without SP1 first of all you need to create this stored procedure in the social database of User profile service application (just execute above script). After that the only thing which should be done – is to write SQL script which will update URLs based on requirements.

E.g. imaging that we need to move news from http://example.com/Departments/IT to the parent site http://example.com/Departments. During content migration we need to preserve social comments. It can be done with the following SQL script:

   1: declare @comment nvarchar(4000), @url nvarchar(2048), @newUrl nvarchar(2048)
   2: declare @partitionId uniqueidentifier
   3: declare @b bit
   4:  
   5: set @partitionId = CAST('...' as uniqueidentifier)
   6:  
   7: declare CommentsCursor cursor for
   8: select sc.Comment, u.Url from dbo.SocialComments sc
   9:     inner join dbo.Urls u on sc.UrlID = u.UrlID
  10: where Url like '%/Departmens/IT/%'
  11:  
  12: open CommentsCursor
  13:  
  14: FETCH NEXT FROM CommentsCursor 
  15: INTO @comment, @url
  16:  
  17: while @@FETCH_STATUS = 0
  18: begin
  19:     print @url + '        ' + @comment
  20:     
  21:     set @newUrl = replace(@url, '/Departmens/IT/', '/Departmens/')
  22:     if @newUrl <> @url
  23:     begin
  24:         print 'Change url ' + @url + ' -> ' + @newUrl
  25:         exec proc_SocialData_MergeSocialNotes @partitionId, @url, @newUrl, @b output
  26:         print 'Result: ' + str(@b)
  27:     end
  28:     
  29:     FETCH NEXT FROM CommentsCursor 
  30:     INTO @comment, @url
  31: end
  32:  
  33: close CommentsCursor
  34: deallocate CommentsCursor

(This particular example can be implemented using simple Update query, but I decided to use cursor because in real life requirements may be more complicated and Update’s possibilities won’t be enough).

It gets all comments with their URLs, filter them by particular condition and updates one by one. You may also add transaction here. The last question is where to get @partitionId. The simplest way is just perform the following query on your social database:

   1: select sc.Comment, sc.PartitionID, u.Url, u.PartitionID from dbo.SocialComments sc
   2:     inner join dbo.Urls u on sc.UrlID = u.UrlID

and see what partition will be returned for the comments in your web application.

When you will execute this script – social comments will be merged to the news migrated to parent site.

Of course this half-hack: it manipulates social database directly without object model. However as we saw – object model is just a thin wrapper over used stored procedure. Also this approach doesn’t require SP1 installed on production – which can be important for real environments. Anyway I hope that this information will be useful in your work.