Extract Data from MOSS 2007 (Sharepoint)

The following classes shows how easy it can be to extract data from MOSS 2007 (Sharepoint) and fill the values into properties. The class also checks if there are attachments if so, then it will download attached files to a local path.

the sample below assumes that you have created a Web References to your sharepoint list web service: https://your.sharepoint.com/_vti_bin/lists.asmx

please notice that i have copy and pasted methods from various classes into one class without running it, so don't be surprised if you get any compilation error.

the main aim behind it is to extract data from a bug tracker list and import it over the tfs api 2010 into team foundation server.

public class Sharepoint2007Wrapper
  {
    public string Title { get; set; }
    public string Date { get; set; }
    public string CreatedBy { get; set; }

    [System.Diagnostics.DebuggerStepThrough]
    private ICredentials GetCredentials()
    {
      return new NetworkCredential("username", "password", "domain");
    }

    private DataRowCollection LoadSharepointData(string viewFields, string listName)
    {
      sharepoint.Lists sp = new MigrationToolExportImport.sharepoint.Lists();
      sp.Url = "http://url.ToYourSiteCollection.com/_vti_bin/Lists.asmx";
      sp.Credentials = this.GetCredentials();

      // this part is needed to receive the link to document attachments and all specified fields
      XmlDocument xmlDoc = new System.Xml.XmlDocument();
      XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
      XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");
      XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");
      
      // receive links to the attachments
      ndQueryOptions.InnerXml = "<IncludeAttachmentUrls>TRUE</IncludeAttachmentUrls>";
      ndViewFields.InnerXml = viewFields;
      ndQuery.InnerXml = ""; // <Query />

      XmlNode allLists = sp.GetListCollection();
      // ensure the number is big enough because without this value sharepoint returns configured amount which is 100 per view!
      // listsgetlistitems method lists only 100
      XmlNode lists = sp.GetListItems(listName, "", ndQuery, ndViewFields, "10000", ndQueryOptions, "");

      XmlNodeReader r = new XmlNodeReader(lists);
      DataSet ds = new DataSet();
      ds.ReadXml(r, XmlReadMode.Auto);

      return ds.Tables[1].Rows;
    }

    public void Extract(string listname)
    {
      if(string.IsNullOrEmpty(listname))
        listname = "Your Sharepoint 2007 List Name";
      // if nothing is provided then it returns all rows otherwise 
      // uese fieldRef tags to retrieve only requested columns
      // <FieldRef Name="LinkTitle" />  <FieldRef Name="Title" /> 
      string viewFields = "";
      DataRowCollection rows = LoadSharepointData(viewFields, listname);
      
      int i = 0;
      foreach (DataRow row in rows)
      {
        try
        {
          if ((++i % 10) == 0) 
            Console.Write('.');
          this.ParseAndExtractRow(row, GetCredentials());
        }
        catch (Exception ex)
        {
          Console.WriteLine(ex.Message);
        }
      }
    }

    private List<string> Attachments { get; set; }

    private void ParseAndExtractRow(System.Data.DataRow data, ICredentials credentials)
    {
      // this helps to identify which text we need to retrieve the column value
      this.PrintColNames(data);

      this.Title = this.GetRowValue(data, "ows_Title");
      this.Date  = this.GetRowValue(data, "ows_Date");
      this.CreatedBy = this.GetRowValue(data, "ows_CreatedBy");
    }

    private void DownloadAttachments(string urls, ICredentials credentials)
    {
      this.Attachments = new List<string>();
      string[] fileUrls = urls.Split('#');
      if (fileUrls != null && fileUrls.Length > 0)
      {
        foreach (string file in fileUrls)
        {
          string uri = file.Replace(";", "");
          if (!string.IsNullOrEmpty(uri) && uri.Length > 1)
            this.Attachments.Add(uri);
        }
      }

      List<string> filepaths = new List<string>();

      if (this.Attachments.Count > 0)
      {
        foreach (string url in this.Attachments)
        {
          try
          {
            string file = System.IO.Path.GetFileName(url);
            string filepath = new Attchments().DownloadSharepointAttachment(url, file, credentials);
            if (System.IO.File.Exists(filepath))
              filepaths.Add(filepath);
            else
              Console.WriteLine("File does not exists!!");
          }
          catch (Exception ex)
          {
            Console.WriteLine("Error upon file download: " + ex.Message);
          }
        }
        this.Attachments.Clear();
        this.Attachments.AddRange(filepaths);
      }
    }

    private void PrintColNames(System.Data.DataRow data)
    {
      for (int i = 0; i < data.Table.Columns.Count; ++i)
      {
        Console.WriteLine(data.Table.Columns[i].ToString());
      }
    }

    private string GetRowValue(System.Data.DataRow data, string columnName)
    {
      try
      {
        if (data[columnName] != null)
          return data[columnName].ToString();
        else
          return string.Empty;
      }
      catch (Exception ex)
      {
        return string.Empty;
      }
    }

    private class Attchments
    {
      private static CookieContainer _cookieContainer;
      public static CookieContainer CookieContainer
      {
        get
        {
          if (_cookieContainer == null)
          {
            _cookieContainer = new CookieContainer();
          }
          return _cookieContainer;
        }
      }

      public class CookieAwareWebClient : WebClient
      {
        protected override WebRequest GetWebRequest(Uri address)
        {
          WebRequest request = base.GetWebRequest(address);
          if (request is HttpWebRequest)
          {
            (request as HttpWebRequest).CookieContainer = AttchmentHandler.CookieContainer;
            (request as HttpWebRequest).KeepAlive = false;
          }
          return request;
        }
      }

      public string DownloadSharepointAttachment(string url, string filename, ICredentials credentials)
      {
        string localFolder = @"Z:\\YourRequestedLocationOnYourHardDrive\\";
        // ensure we do not overwrite files with the same name.
        string uniqueGuid = Guid.NewGuid().ToString();

        //now the code that will download the file 
        try
        {
          using (WebClient client = new CookieAwareWebClient())
          {
            client.Credentials = credentials;
            // client.DownloadFile("http://address.com/abc.pdf", @"c:\\temp\abc_local.pdf");
            client.DownloadFile(url, localFolder + uniqueGuid + "_-_" + filename);
            client.Dispose();
          }
          return localFolder + uniqueGuid + "_-_" + filename;
        }
        catch (Exception ex)
        {
          Console.WriteLine("Download Sharepoint Attachment Error: " + ex.Message);
          return string.Empty;
        }
      }
    }
  }