Importing documents from SQL Server to Document Library

Recently I had come across a requirement where we had to migrate a existing SQL server tables data to a document library. The SQL table was containing documents (blob field and its meta data) i wanted to share with you all the small script we had used for the same

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();  

}

}