Writing Database Blobs to SharePoint Document Libaray

Question

I'm working on moving a large number of documents from an older document management system to SharePoint (MOSS 2007). The original system stored the documents as binary data in image fields in a SQL server database.

My thought is to programmatically create multiple document libraries to group these documents. I then plan to read the binary data out to a filestream and then use that stream to upload the file to SharePoint.

Has anyone done something similar? Is there a better approach than what I'm working on? Also, if anyone has any examples of reading from a binary database field and writing to a SharePoint document library, I would appreciate it. Thanks.

Magnus Johansson · Accepted Answer · 2009-04-22 19:39:35Z

4

You can easily create document programmatically from a byte array. Here's a bit of code snippet that you can build upon.

// If you have very large document, you might consider export the BLOBs to disk instead of in memory
string connstr = @”Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa”;

SqlConnection conn = new SqlConnection(connstr);
conn.Open();

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand(“SELECT * FROM [dbo].[ImageStore]“, conn);
DataSet ds= new DataSet();
da.Fill(ds);

byte[] blob = (byte[])ds.Tables[0].Rows[0]["imageContent"];


using (SPSite destinationSite = new SPSite("http://YourSite"))
{
  using (SPWeb = destinationSite.OpenWeb("http://YourSite"))
  {
    SPList destinationDocuments = destinationWeb.Lists["Documents"];
    string fileName = "mydoc.jpg";
    string relativeDestinationUrl = destinationDocuments.RootFolder.Url + "/";
    stringfileName += sourceItem.File.Name;
    relativeDestinationUrl += fileName;

    SPFile destinationFile = ((SPDocumentLibrary)destinationDocuments).RootFolder.Files.Add(relativeDestinationUrl, blob, true);
    SPListItem item = destinationFile.Item;
    item.UpdateOverwriteVersion();
  }
}

answered Apr 22, 2009 at 19:39

Magnus Johansson

28.4k19 gold badges110 silver badges170 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Price Over a year ago

Thanks for the reply. I will put it use.

Collectives™ on Stack Overflow

Writing Database Blobs to SharePoint Document Libaray

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related