MOSS 2007 - Migrating content from 3rd party content management solution to MOSS Publishing/Collaboration Portal

Requirement: I have an existing content management solution where I store the content of the user generated pages in database (in my case SQL Server). Here is a snapshot of the table where the contents of those pages are stored:

I have created a new content type in my collaboration portal with the name "mycontent". I have taken the parent group as Article Page and below is the snapshot of the content type. Notice I have marked the content type id in the URL. You need to copy it somewhere, we will use it in our code.

Now using SharePoint Designer I have created a Page Layout name "mylayout.aspx" using the "mycontent" content type. We have default content fields like "Article Date", "Byline" and "Page Content". I have also used "Title" Page field to show the Title of the page.

My target is to fill up all these content fields from ContentTable from my database. Remember this will actually migrate the content from my old solution's DB to SharePoint's backend DB.

Here is a wonderful article by Andrew Connell regarding creating publishing page dynamically:

https://www.andrewconnell.com/blog/archive/2006/11/15/5168.aspx

At last, here is my code. This is a web part where we have only a button. Once you click the button, it selects contents from database table and creates publishing pages dynamically. You can use the same code from any other type of application for example: a console application . I have used web part as demonstration purpose. You need to add reference of "Microsoft.SharePoint.Publishing.dll". You can find it here: "C:\Program Files\Microsoft Office Servers\12.0\Bin".

using System;

using System.Runtime.InteropServices;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Serialization;

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

using Microsoft.SharePoint.Publishing;

using System.Data;

using System.Data.SqlClient;

namespace WPContentCreator

{

[Guid("b771c700-889b-4cc1-bd36-fe4abac02183")]

public class WPContentCreator : System.Web.UI.WebControls.WebParts.WebPart

{

Button myBtn;

string SqlQuery = "Select contentid,title,contentbody,contentdate,author from dbo.ContentTable";

public WPContentCreator()

{

}

protected override void CreateChildControls()

{

myBtn = new Button();

myBtn.Text = "Submit";

myBtn.Click += new EventHandler(myBtn_Click);

Controls.Add(myBtn);

}

protected override void Render(HtmlTextWriter writer)

{

myBtn.RenderControl(writer);

}

void myBtn_Click(object sender, EventArgs e)

{

SPSite site = SPContext.Current.Site;

SPWeb web = site.OpenWeb();

PublishingSite publishingSite = new PublishingSite(site);

PublishingWeb publishingWeb = PublishingWeb.GetPublishingWeb(web);

SPContentTypeId articleContentTypeID = new SPContentTypeId("0x010100C568DB52D9D0A14D9B2FDCC96666E9F2007948130EC3DB064584E219954237AF3900242457EFB8B24247815D688C526CD44D0091053586ECA36C43BD6FD6B1B28A2071");

PageLayout[] layouts = publishingWeb.GetAvailablePageLayouts(articleContentTypeID);

PageLayout articlePageLayout = layouts[0];

SqlConnection newSqlConnection = new SqlConnection();

SqlCommand newSqlCommand = new SqlCommand(this.SqlQuery, newSqlConnection);

newSqlCommand.CommandType = System.Data.CommandType.Text;

newSqlConnection.ConnectionString = "Integrated Security=True;Initial Catalog=test;Data Source=pranab-sec";

newSqlConnection.Open();

IDataReader rdr = newSqlCommand.ExecuteReader();

bool more;

int results = 0;

do

{

results++;

while(rdr.Read())

{

string pageName = rdr[0].ToString();

PublishingPage newPage = publishingWeb.GetPublishingPages().Add(string.Format("{0}_article_{1}.aspx", pageName, results), articlePageLayout);

newPage.Title = rdr[1].ToString();

newPage.ListItem["Page Content"] = rdr[2].ToString();

newPage.ListItem["Article Date"] = DateTime.Parse(rdr[3].ToString());

newPage.ListItem["Byline"] = rdr[4].ToString();

newPage.Update();

newPage.CheckIn("");

}

more = rdr.NextResult();

} while(more);

newSqlConnection.Close();

}

}

}