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

Comments