SharePoint, Document Library and SQL Server

Now, let’s spend a few minutes to see the application code:

The following files have been taken from the “Event Handler Toolkit”:

• BaseEventSink.cs
• LoggerEventSink.cs
• SPListEventArgs.cs
• LibraryEventSink.cs

We will not discuss these files. If you want more information about how to use the “Event Handler Toolkit”, read following detailed article on Microsoft’s site: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/html/tsptDocLibEvents_SV01034973.asp

We added our code in “LibraryEventSink” class. Let’s take a look!

private void OnInsert()
{
    try
    {

        spFile = EventWeb.GetFile(EventFileUrl);
        if (spFile.Exists)
        {
            spListItem = spFile.Item;

            //Retrieve Database string from config file
            string strDB;
            StreamReader sr = new StreamReader(@"C:\db.config");
            strDB = sr.ReadLine();
            sr.Close();

We write our code in “OnInsert()” because we want to capture the document information  at the time when it gets added to the library. If we wanted to capture the document when someone updated it, we would write our code in “OnUpdate()”. First of all, we get the URL of the file just added to the library.

spFile = EventWeb.GetFile( EventFileUrl ); 

spFile is an instance of class SPFile that represents a file in a SharePoint web site that is an item in a document library. Next, we open the “db.config” file that contains the connection string to the SQL Server. We read the connection string into the “strDB” variable.

// Create Instance of Connection and Command Object
SqlConnection myConnection = new SqlConnection(strDB);
SqlCommand myCommand = new SqlCommand("sp_AddDocInfo", myConnection);

// Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure; 

Instances of Connection and Command objects are created. Stored procedure expects parameters that will contain the values to be added in the database. In the following piece of code, we define the parameters and assign values to them. We capture following document details:

1. Document Name
2. Date of Modification
3. Approval Status
4. Approver Comments
5. Modified By

 // Add Parameters to SPROC
SqlParameter pName= new SqlParameter("@Name", SqlDbType.VarChar , 50);
pName.Value = spListItem["Name"];
myCommand.Parameters.Add(pName);

SqlParameter pModified= new SqlParameter("@Modified", SqlDbType.DateTime, 8);
pModified.Value = spListItem["Modified"];
myCommand.Parameters.Add(pModified);

SqlParameter pApprovalStatus= new SqlParameter("@ApprovalStatus", SqlDbType.Int , 4);
pApprovalStatus.Value = spListItem["Approval Status"];
myCommand.Parameters.Add(pApprovalStatus);

SqlParameter pModifiedBy = new SqlParameter("@ModifiedBy", SqlDbType.VarChar , 50);
pModifiedBy.Value = spListItem["Modified By"];
myCommand.Parameters.Add(pModifiedBy); 

In the following code, we use “if-else” to send proper value to the database to avoid error. If “Approver Comments” are empty, it will generate an error. It is important to tackle this situation. We check the property for the null value, in case it contains null, we add comments of our own otherwise we leave the original comments in tact. This property will always contain null because “Approver Comments” are always null when a new document is added to the library. Approver can add his/her comments only at the time of approval. I used this property thinking that I would demonstrate it’s use in the “update” event.

if (spListItem["Approver Comments"]!=System.DBNull.Value)
{
     SqlParameter pApproverComments = new SqlParameter("@ApproverComments", SqlDbType.VarChar , 255);
     pApproverComments.Value = "No Comments";
}
else
{
     SqlParameter pApproverComments = new SqlParameter("@ApproverComments", SqlDbType.VarChar , 255);
     pApproverComments.Value = spListItem["Approver Comments"];
     myCommand.Parameters.Add(pApproverComments);
} 

Finally, open the connection, execute the stored procedure and close the connection.

// Open the connection and execute the Command 
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close(); }}

You can catch exceptions using “try-catch”. Exceptions caught are logged in the Windows Event Log.

catch ( System.Security.SecurityException ex ) 
{ 
    message = "Error: " + ex.Message; 
    writeToEventLog(message + ":" + ex.ToString()); 
} 
catch ( Exception ex ) 
{ 
    Message = "Error: " + ex.Message; 
    writeToEventLog(message + ":" + ex.ToString());
} }