Link data in custom SQL db with document library

Question

Environment: I have a windows network shared desktop application written in C# that leans against an MSSQL database. Windows sharepoint services 3.0 is installed (default installation, single processor, default sql express content database and so on) on the same Windows Server 2003 machine.

Scenario: The application generates MS Word documents during processing (creating work orders) that need to be saved on sharepoint, and the result of the process must be linked to the corresponding document. So, for each insert in dbo.WorkOrders (one work order), there is one MS Word document. I would need to save the document ID from the sharepoint library to my database so that later on, possible manual corrections can be made to the document related. When a work order is deleted, the sharepoint document would also have to be deleted. Also, there is a dbo.Jobs table which is parent to dbo.WorkOrders and can have several work orders.

I was thinking about making a custom list on sharepoint, that would have two ID fields - one is the documents ID and the other AutoID of the document. I don't think this would be a good way performance-wise and it requires too much upkeep, therefore it's more error prone.

Another path I was contemplating is metadata. I could have an Identity field in dbo.WorkOrders that would be unique and auto incremented, and I could save that value as a file name (1.docx, 2.docx 3.docx ... n.docx where n would be the value in dbo.WorkOrder's identity field). In the metadata field of the Word document, I could save the job ID from dbo.Jobs.

I could also just increment the identity field in the WorkOrder (it would be a bigint), but then the file names would get ugly and maybe I'd overflow the ID range (since there could be a lot of documents).

There are other options also that I have considered and dismissed, since none of them satisfied the requirements (linked data sources, subfolder structures etc.). I'm not sure how to proceed. I'm new to sharepoint and it's still a bit of a mystery to me, as I don't understand all the inner workings of the system.

What do you suggest?

Edit:

I think I'll be using guid as file names and save those guids in my database after sending documents to sharepoint. What do you think of that?

Answer 1

Hello again Lijenjin,

All the documents in SharePoint under the same Content Database (SQL Database) are stored in the same table, that said, you have an unique ID for files no matter where they are in the sharepoint structure.

When retrieving files by their UniqueID The API only gives you the option to get them if you also know their SPWeb, so you could easily store, for each record you have in your external database (or your custom list, the SPFile GUID and the SPWeb GUID) retrieving them with:

using(SPWeb subweb = (SPContext.Current.Site.OpenWeb(new Guid("{000...}")))
{
    SPFile file = subweb.GetFile(new Guid("{111...}"));
    // file logic
}

ps.: As Colin pointed out, url retrieval is possible but messy. I also changed the SPSite to the context since you are always under the same Site Collection in my example.

Answer 2

Like F.Aquino said, all items in sharepoint have a UniqueId field already (i.e. SPListItem.UniqueId and SPFile.UniqueId), which is a guid. Save that to your database, along with your web.'s guid. Then you can use the code provided by F.Aquino to get the file, or even the byte[] of the stream.

P.S. for F.Aquino, your code leaves the SPSite in memory, use this instead:

P.P.S this is just clarification, mark F.Aquino as the answer.

using(SPSite site = new SPSite("http://url"))
{
  using(SPWeb subweb = site.OpenWeb(new Guid("{000...}"))
  {
    SPFile file = subweb.GetFile(new Guid("{111...}"));
   // file logic
  }
}