Export Blogroll from OPML File to Community Server Blog Links

God breathing through me

Export Blogroll from OPML File to Community Server Blog Links

Published on January 30, 2006

[Update: I replaced this Smart Client with an Add-on for Community Server 2.0 but its files are attached to this post.]

Yesterday Scott posted his blogroll that was created automatically from his OPML. I don’t want to do the same with my blogroll and add it to my blog but think that it can be useful for other Community Server users to have a tool to automatically do this for them (especially because CS 2.0 comes with new Links page and probably some users want to remove their blogrolls from home page and have them in this new page). Today I wrote a simple application which gets database connection string, an OPML file and SectionID (The ID of a blog in Community Server) and adds all links from OPML file to database. To find your blog SectionID in Community Server 2 just click on Change Blog button and in next page click on the blog you want to export your OPML to it. Now you are redirected to new page. Look at query string in address bar and find your SectionID number after SectionID parameter.

It’s available with source code here and works with Community Server 2.0 Beta 3 (and maybe older versions but I didn’t test it). If I get enough feedbacks about it, will try to play around this application and add more features to it.

At present it has some limitations:

User must enter SectionID manually.
It doesn’t check that you have a valid OPML file so I recommend that you export OPML files just with folders. If you have subfolders in your folders or don’t have any folder, maybe it will cause unexpected exception.
It’s obvious that you can’t have two or more folders with same name. (See my description about the process of design to find the reason) It’s not usual to have two categories of blogrolls with same name in blog. Note that if you have currently some categories with the same name of your folders in OPML file, the same problem will arise. To solve all of these problems just try to check and rename your folders when export them from your RSS reader. Most of RSS readers produce some tools for customizing exported OPML file.
You can’t customize links before updating the database in application itself but can do it in OPML file and your RSS reader.

I highly recommend that you test it first on localhost, if you want to use it on your server.

Here I shortly describe the process of design. As source is available you can edit it for yourself. If you think that some new features are needed just let me know. It was better to add this ability directly to application itself but the source code of CS 2.0 is not available yet.

But the process of design has simple steps:

Load OPML file and transform it to new XML file. One of these XML files contains all categories and other contains all links.
Load these new XML files to Dataset object and send them to a class.
Add categories and links from Datasets to database.

This is a sample OPML file. I got it from FeedDemon and removed some parts to have shorter code. It had an outline element as parent of other outline elements and I removed it to work with my XSLT files in application. If you have same problem modify XSLT files or remove parent outline element manually.

		<?xml version="1.0" encoding="utf-8"?>

		<opml version="1.0">

		  <head />

		  <body>

		    <outline title="Individuals">

		      <outline title="Tech Guru" xmlUrl="http://aliparvaresh.com/rss.aspx" htmlUrl="http://aliparvaresh.com/" description="Ali's Blog" />

		      <outline title="ScottGu's Blog" xmlUrl="http://weblogs.asp.net/scottgu/rss.aspx" htmlUrl="http://weblogs.asp.net/scottgu/" description="" />

		    </outline>

		    <outline title="UnCategorized">

		      <outline title="Keyvan Nayyeri" xmlUrl="http://nayyeri.net/rss.aspx" htmlUrl="http://nayyeri.net/default.aspx" description="High Tech reflection in my life" />

		    </outline>

		  </body>

		</opml>

This is the code for adding categories from Categories Dataset to database. It uses cs_LinkCategory_CreateUpdateDelete stored procedure to add categories:

		''' <summary>

		''' Adds categories to database

		''' </summary>

		''' <param name="Categories">Dataset of categories</param>

		''' <returns>A boolean value indicates that if the process was successful</returns>

		''' <remarks></remarks>

		Public Function AddCategories(ByVal Categories As DataSet) As Boolean

		    Try

		        Using Connection As New SqlConnection

		            Connection.ConnectionString = ConnectionString

		            Connection.Open()

		            For Each Row As Data.DataRow In Categories.Tables(0).Rows

		                Dim cInsert As New SqlCommand

		                cInsert.CommandType = CommandType.StoredProcedure

		                cInsert.Connection = Connection

		                cInsert.CommandText = "dbo.cs_LinkCategory_CreateUpdateDelete"

		                cInsert.Parameters.AddWithValue("SectionID", Section)

		                cInsert.Parameters.AddWithValue("SettingsID", 1000)

		                cInsert.Parameters.AddWithValue("Name", Row.Item("Name"))

		                cInsert.ExecuteNonQuery()

		            Next

		        End Using

		        Return True

		    Catch ex As Exception

		        MsgBox(ex.Message)

		        Return False

		    End Try

		End Function

This is the code for adding links from Sites Dataset to database. I used a Dictionary collection to save current categories with their ID to retrieve them later. This is the reason that you can’t have same category names:

		''' <summary>

		''' Adds sites to database

		''' </summary>

		''' <param name="Sites">Dataset of sites</param>

		''' <returns>A boolean value indicates that if the process was successful</returns>

		''' <remarks></remarks>

		Public Function AddSites(ByVal Sites As DataSet) As Boolean

		    Try

		        Dim Dictionary As New Dictionary(Of String, Integer)

		        Dim DataReader As SqlDataReader

		        Dim cSelectCategories As New SqlCommand

		        Using Connection As New SqlConnection

		            Connection.ConnectionString = ConnectionString

		            With cSelectCategories

		                .Connection = Connection

		                .CommandText = "dbo.cs_LinkCategories_Get"

		                .CommandType = CommandType.StoredProcedure

		                .Parameters.AddWithValue("SectionID", Section)

		                .Parameters.AddWithValue("SettingsID", 1000)

		                .Parameters.AddWithValue("PreLoadLinks", False)

		            End With

		            Connection.Open()

		            DataReader = cSelectCategories.ExecuteReader(CommandBehavior.CloseConnection)

		            While DataReader.Read

		                Dictionary.Add(DataReader.Item("Name"), DataReader.Item("LinkCategoryID"))

		            End While

		        End Using

		        Using Connection As New SqlConnection

		            Connection.ConnectionString = ConnectionString

		            Connection.Open()

		            For Each Row As DataRow In Sites.Tables(0).Rows

		                Dim cInsertLinks As New SqlCommand

		                With cInsertLinks

		                    .Connection = Connection

		                    .CommandText = "dbo.cs_Link_CreateUpdateDelete"

		                    .CommandType = CommandType.StoredProcedure

		                    .Parameters.AddWithValue("LinkCategoryID", Dictionary.Item(Row.Item("Category")))

		                    .Parameters.AddWithValue("Title", Row.Item("Name"))

		                    .Parameters.AddWithValue("Url", Row.Item("URL"))

		                    .Parameters.AddWithValue("Description", Row.Item("Description"))

		                    .Parameters.AddWithValue("SettingsID", 1000)

		                End With

		                cInsertLinks.ExecuteNonQuery()

		            Next

		            Connection.Close()

		            End Using

		        Return True

		    Catch ex As Exception

		        MsgBox(ex.Message)

		        Return False

		    End Try

		End Function

1 Comments

Keyvan Nayyeri
Apr 05, 2006 11:24 AM
#

When I was off, Dave Burk did a good job and sent his comments about my OPML to Blogroll converter...

Keyvan Nayyeri

Recent Posts

Archive

Export Blogroll from OPML File to Community Server Blog Links

Leave a Comment