v:* { behavior: url(#default#VML) }
o:* { behavior: url(#default#VML) }
.shape { behavior: url(#default#VML) }
SharePoint, Document Library and SQL Server
It's been years now, SharePoint 2007 is about to appear in the market, SharePoint 2003 seems to be something that is history now (Exaggerating .). Many companies have adopted SharePoint during the last one year. Good thing is that I have seen lots of small businesses acquiring SharePoint during the last one year. SharePoint's popularity is growing day by day and it is considered to be a top level portal product.
Document Libraries are collections of files that you can share with team members on a web based on Windows SharePoint services. Want to know more about Document Libraries?? Read more about Document Libraries on this site:
This link will show you how to create and use a document library. You can extend the functionality of these document libraries by using the "Event Handler Toolkit" that can be downloaded from the Microsoft site:
Suppose, whenever a document is added in the document library, you want to store the name of the document and the time it was added, in the database. How can you do that? Why would you do that? You can capture events fired in the document library. When ever a document is added, updated, deleted, etc, you can capture the event and add your own business logic or simply capture document information and store it in the database. Why would you store document information in the database? Well, there can be many reasons. You may want to create reports for the senior management who may want to look at the documents added to the library from time to time. You may want to track the document history or you may want to add a workflow to your document library. All this can be done by using the "Event Handler Toolkit".
Before, we proceed, let me tell you the purpose of writing this. There are many excellent articles on the internet that show you how to capture document library events and do processing but still many people come to me and ask me the same question again and again and the question is related to the capturing of document information and storing it in SQL Server. Seeing the demand, I decided to write a small article that will show you how you can capture the document information in the library and store it in SQL Server. You can do so many things, for example:
- When a document is added, you can copy or move it to another document library.
- You can delete a document.
- You can capture document information and store it in SQL Server.
- You can update document information by updating the properties associated with the document.
- You can add workflow mechanism to your document library. For example, when a document is added to the document library, you send an email to the approver and ask him to approve the document. Document, after getting approval from approver 1, goes to approver 2 and so on.
These are only some of the important things that you can do with Document Library EventHandler. I wish I could show you all of the above in my article but unfortunately that is not possible due to shortage of time but I promise I will try my best to update this tutorial from time to time and add more content to it. In this article, we will see how you can capture document information when a document is inserted in the library and store this information in DB.
- Unzip the downloaded file to the root of your system drive. Zip file contains the complete source code and the installation scripts.
- You must create a database before you start working with the code. Open your SQL Server and create a blank database and name it as "SPSDocLibHandler".
- Add a table to this database. Table name is "DocInfo". Create following fields in the table:
* Please note that "id" is an identity column.
Here is the SQL script to create this table:
CREATE TABLE [dbo].[DocInfo] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Modified] [datetime] NULL ,
[ModifiedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApproverComments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApprovalStatus] [int] NULL
) ON [PRIMARY]
You will find this script in "db.sql" file that is located in the "DB" folder inside the application folder. "db.sql" file also contains the script to create a stored procedure that will be used in our code. I would suggest that you just create the database and then run the "db.sql" file to create the required table and stored procedure but that's up to you. You can follow the steps listed here to do it manually.
- Create a stored procedure called as "sp_AddDocInfo". Here is the SQL script:
CREATE PROCEDURE dbo.sp_AddDocInfo
(
@Name varchar(50),
@Modified datetime,
@ApprovalStatus int,
@ModifiedBy varchar(50),
@ApproverComments varchar(255)
)
as
insert into DocInfo values (@Name,@Modified,@ModifiedBy,@ApproverComments,@ApprovalStatus)
- There is a file called as "db.config" in the root of the application folder. This one line configuration file contains connection string to your SQL Server. Edit this file in your notepad and add your SQL Server connection details.
Data Source = YourSQLServerName (Replace this with your SQL Server's name)
Initial Catalog = SPSDocLibHandler (This is the database name)
User Id = sa (Replace this with your own user id)
Password = (Add password here, if any)
- Copy this file "db.config" to the root of your system drive. For example, if your system drive is "C:", copy the file to the root of "C:" drive. If your system drive is "D:" then copy the file to the root of "D:" drive.
- There is a file named as "Install SPSDocLibHandler.bat" in your application folder. Open this file in notepad and see if you need to make any changes. This file installs your DLL into GAC, especially check the path where gacutil.exe is located on your computer. Run this file to install DLL into GAC. You can install the assembly into GAC manually as well. "SharePoint.DocLib.Handler.dll" can be found in the "bindebug" folder inside your application folder.
- After installing the DLL into GAC, you need to bind your assembly to the document library where this assembly will be used. Before doing that, you also need to enable "Event Handlers" on the virtual server where your SharePoint resides. If you don't know how to do this then read this article on Microsoft's site:
For your convenience, I am listing the steps required to enable the "Event Handlers" on your virtual server.
a) Go to SharePoint Central Administration page.
b) Click "Configure virtual server settings from the virtual server list page" under "Portal Site and Virtual Server Configuration". This will open the "Virtual Server List" page:
c) Click on your virtual server from the list shown on this page. Clicking on your virtual server will open the following page:
d) Click on "Virtual server general settings" link on this page. You will see the following page:
On the bottom of the page, you will find "Event Handlers" section. Select "On" in the Event Handlers section to enable use of event handlers on the virtual server.
Now that you have enabled "Event Handlers" on your virtual server, your next step is to bind Your assembly to the document library. Following are the steps required to accomplish this:
a) From a view of the document library to which you want to attach the event handler assembly, click Modify settings and columns.
b) Click "Change advanced settings" on the page that opens for customizing the document library.
c) On the Document Library Advanced Settings page, type the strong name for the assembly in the Assembly Name box. In our case assembly name box would contain:
SharePoint.DocLib.Handler, Version=1.0.0.0, Culture=neutral, PublicKeyToken=32bb15c33a2f8eca.
You can get the values used in the strong name by right-clicking the DLL of your assembly in the Local_Drive:WINDOWSassembly directory and then clicking Properties on the shortcut menu.
d) Type the fully qualified, case-sensitive name of the class in the Class Name box, which in this case is SharePoint.DocLib.Handler.LibraryEventSink
e) Reset IIS by typing iisreset at a command prompt.
- You are now ready to test the application. Insert a document in your library and then go to your SQL Server, open the table and you will see the details of the newly added document. You can create reports based on the data in this table for the senior management. This is just one example of what you can do with SharePoint Document Libraries and the "Event Handler Toolkit".
Code Discussion
Now, let's spend a few minutes to see the application code:
Following files have been taken from the "Event Handler Toolkit":
a) BaseEventSink.cs
b) LoggerEventSink.cs
c) SPListEventArgs.cs
d) 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:
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";
myCommand.Parameters.Add(pApproverComments);
}
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());
}
}
Conclusion
Some people will find this tutorial to be very simple while others may find it very complicated. To be honest, it will be complicated for the beginners but those who have lot of experience in SharePoint will find it to be simple. Well, my objective was to explain the process to the people who form the "beginner" class. You might find some errors including typos in the tutorial because it was written hastily. If you get stuck some where and need my assistance then write to me at share.point@yahoo.com or ss_ahmed1@hotmail.com. Although it is not possible to reply to each and every email but I will try my best to answer as many queries as possible. Even though I have stopped authoring tutorials for the last 2-3 years, still I get 30-40 emails daily from people who read and appreciate my tutorials, who read and criticize my work and from people who ask me to help them on their personal projects.
Please stay connected, the next tutorial in this series will be more advanced and will tell you more about the advantages of capturing documents in SharePoint Document Libraries offer.
Good Luck.
S.S. Ahmed
