SQL to SharePoint Migration tool
The most important part of the OfficeZealot site is really the content; therefore, one of the critical to quality elements is content migration.
To do that, and considering that there is plenty of content to migrate, we developed a simple Windows app that migrates all the data from our SQL Server database to SharePoint.
This was not as straightforward as expected. Why? Because most databases have relational data and SharePoint handles relational data in a completely different way. SharePoint is not a database.
SharePoint has the ability to insert data to the list with a form, so if you have some relational data, how is it supposed to work? One way would be to create another list to handle the relation, while that is a logical way to do it for people designing a relational database, when you are going to add some new data to the list you have to add a number (id) and insert another item to the other list creating the relationship. So instead of two list items related to each other, you have multiple lists and you now have to deal with separate ids… not ideal certainly.
There is a better way to do it with lookup fields. This field exists to do exactly what its name says, look up a value in another list and store a “pointer” to the related item.
What about the many-to-many relations? This field can store multiple values giving us the simplicity that our users need.
The application we built gets all the data from a selected table from the database and matches each column with the list fields we previously created.
Remember that we are not creating a tiered application and that this mini app is basically a utility app which will run only a couple of times during the project.
string myConnStr = ConfigurationManager.ConnectionStrings["SharepointMigration.ConectionString"].ConnectionString;
string qurey = "Select * from " + cmbTables.SelectedValue.ToString();
DataTable migratetable = new DataTable("migratetable");
using (SqlConnection connection = new SqlConnection(myConnStr))
{
using (SqlCommand command = new SqlCommand(qurey, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
migratetable.Load(reader);
connection.Close();
}
}
SPList list = web.Lists[cmbList.SelectedValue.ToString()];
foreach (DataRow dr in migratetable.Rows)
{
SPListItem listitem = list.Items.Add();
foreach (DataColumn dc in migratetable.Columns)
{
int numval = 0;
//Column that has the lookup property
if (name.Contains("Column"))
{
SPList template = web.Lists["list"];
foreach (SPListItem templateitem in template.Items)
{
if (templateitem["column"] != null)
{
if (templateitem["column"].ToString() == dr[dc.ToString()].ToString())
{
SPFieldLookupValue lookupvalue = new SPFieldLookupValue(templateitem.ID, templateitem["column"].ToString());
listitem[name] = lookupvalue;
}
}
}
}
else
{
if (int.TryParse(dr[dc.ToString()].ToString(), out numval))
{
listitem[name] = numval;
}
else
{
if (dr[dc.ToString()] != null)
{
if (!string.IsNullOrEmpty(dr[dc.ToString()].ToString()))
{
listitem[name] = dr[dc.ToString()].ToString();
}
}
}
}
}
listitem.Update();
}
We just get the list from sharepoint and the data from the database and start to insert the rows into the SharePoint lists.
This code works very smoothly for lists that do not have lookup fields. Lookup fields are a bit more complicated than this, but it is something that follows the same pattern.
By automating data migration tasks, we save a significant amount of time; and more importantly, we are able to deliver one of the most critical requirements for this project.
C-Ya
Adrian