It is well known that unfortunately SharePoint 2007 does not support transactional access. This is very unfortunate since in many cases having transactional access is very important.
For example, It is common to have data stored in SQL Server referring to some documemnts stored in SharePoint. In such cases, inserting or updating records in both systems needs to be an atomic operation.
While there are a few suggestions online that talk about creating true trasaction in a Sharepoint (see http://www.lcbridge.nl/vision/2008/transactions.htm and http://prjsoft.ru/blog/?p=9) they are at best cumbersome to write and maintain and at worst could cause unexpected behaviour or not work alltogether (this is beased on some feedback I saw online).
For situations like the one I described above (SQL Server referencing SharePoint data), there is a very simple solution that, while it does not implemet full blown transactions, provides the behavier you need (albeit, it is only applicable in certain situations).
What I fond working well is using ADO.NET transactions for SQL operations and then attepting to execute SharePoint operation after all SQL operation completed but before the ADO.NET transaction commits. If SharePoint operation succeds, we commit, otherwise we rollback.
This appoach, obviously, has serious limitations: one still cannot put two or more SharePoint operations in the same transaction, but for many situations this is not neccesary and often all that is needed is to make a SQL and SharePoint Update or Insert atomic. For that such approach works well.
Here is an example code. In this code we save an entry in SQL and then save a document linked to that entry in a SharePoint document library. Enjoy:
public void Insert(SPWeb web, string filename, byte[] file)
{
using (SqlConnection cn = new SqlConnection(conStr))
{
SqlCommand cm = new SqlCommand();
cm.Connection = cn;
cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "dbo.YourStoredProcedure";
//Here set the parameters for stored procedure
cn.Open();
//Set up ADO.NET transaction
SqlTransaction ts = cn.BeginTransaction();
try
{
cm.Transaction = ts;
//perform SQL operation
cm.ExecuteNonQuery();
//perform SharePoint Operation
web.AllowUnsafeUpdates = true;
SPFolder docLib = web.GetFolder("YourDocFilder");
if (docLib.Exists != true) throw new Exception("Unable to find ‘YourDocFolder’ document library in the current SPWeb");
if (file == null) throw new Exception("Unable to insert an empty file");
SPFile spFile = docLib.Files.Add(filename, file);
ts.Commit();
}
catch (Exception)
{
ts.Rollback();
throw;
}
cn.Close();
}
}
As always, if you see any mistakes or inaccuracies in this blog, please let me know.
