Get Attachments from SharePoint SQL Server

11 Apr

I have been attempting to grab data from SharePoint to Geocode for a GIS Platform I was working on. I started by using Python to Parse the RSS of the List. Then I moved on to using the Web Service via SOAP. And lastly, I have switched to C# and am grabbing the SQL Server data for the SharePoint Backend. NOTE: If you can, run code on the server using SharePoint Service:

SPWeb web = new SPSite(“<Site URL>”).OpenWeb();
//Open List
SPList list = web.Lists[“<ListName>”];
//Get the item
SPListItem item = list.Items[1];
//Get the folder
SPFolder folder = web.Folders[“Lists”].SubFolders[strListName].SubFolders[“Attachments”].SubFolders[item.ID.ToString()];

foreach(SPFile file in folder.Files)
{
byte[] binFile = file.OpenBinary();
System.IO.FileStream fstream = System.IO.File.Create(“c:\\MyDownloadFolder\\” + file.Name);
fstream.Write(binFile, 0, binFile.Length);
}

I do not have access to the server, so I have had to go the difficult route. This method still requires a read only account on the database – which you are more likely to get than physical access to the server.

I have stated in previous posts how the SharePoint Database is structured. I usually stay in dbo.AllUserData. But to get the contents of an attachment, you need dbo.AllDocStreams. The Content field holds the data – in binary. This code will grab all the attachments and assign a filename. To do this right, you need to perform a few joins to figure out the real name. Pascal’s Blog may help you in this.  I got this far and moved on. All I really needed was the path to the file. I solved this by reading Pascal’s Blog and found that the attachments reside at:

 /MyRootWebsite/MyWebsite/Lists/MyList/Attachments/tp_ID/nvarchar14

This made my life easier. But still good to know how to grab the binary.  Here is the code to loop through the dbo.AllDocStreams and only grab the content. Then write it out as a JPG with an incremented filename.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Diagnostics;

namespace SharepointAttachmentCIT
{
    class Program
    {
        static void Main(string[] args)
        {
            StringBuilder myString = new StringBuilder();
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = “Data Source=ip.of.sharepoint.;” + “Initial Catalog=WSS_YourDB;” + “User id=Yourname;” + “Password=YourPasswd;”;

            try
            {
                myConnection.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            try
            {
                SqlDataReader myReader = null;
                SqlCommand myCommand = new SqlCommand(“select dbo.AllDocStreams.Content as ‘C’from dbo.AllDocStreams”,
                                                         myConnection);
               myReader = myCommand.ExecuteReader();

                int i = 0;
               while (myReader.Read())
               {
                   byte[] buffer = (byte[])myReader[“C”];
                   using (FileStream fs = new FileStream(“test”+i.ToString()+”.jpg”, FileMode.Create))
                      
                {
                    fs.Write(buffer, 0, buffer.Length);
                    i = i + 1;
                 
                }

                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

            try
            {
             
                myConnection.Close();
              
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

        }
    }
}

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: