comparing sharepoint list and sqlserver table

Question

I have a list in sharepoint which maintains particular month OnCall list,and we are maintaining employee directory in sql server. My requirement is to get complete data from sql server and show it in sharepoint and compare with sharepoint list and show small icon for the employees who are On Call for that particular Month. Can anyone please suggest me the waus of implementing this.

Thanks in advance.

Update: I have finished the part where I have to connect to the sqlserver database and get the employees information. For this we are using 3rd party web part to connect to the sql server and pull the data from the table. Now I have to show some kind of image on the employee name to show that he is on-call for that week. We are going to cretae custom list for maintaing the list of people who are on-Call. Can anyone please advise me on how to accomplish this.

Answer 1

Write a custom webpart which will pull the data from the list using sharepoint object model and SQL server using ADO.NET and do the said comparison.

If you were looking for out of the box, I am afraid there i too little information given here to analyze if its feasible out of the box or not.

Answer 2

If you have the SharePoint Enterprise version, you can look at using the Business Data Catalog. This will let you bind columns to external data sources. This might provide you with the functionality you're looking for.

If you do not have the Enterprise features, do you have access to deploy WSP packages and custom code?

You will have to write your own data access to your external data source. Your options would be to have a job that pulls data from the external data source and populates SharePoint list(s) or create a custom view that pulls the external data on-demand.

You'll have to come up with synchronization strategies. Meaning, is the data in the external SQL data source static, reference information that does not need to be updated depending on what a user does in SharePoint? This seems to be the case based on your question. If you do need to update the external data source, you'll have to hook into the on save event (so probably a custom event handler that listens for ItemAdding) to update the data, validate, and optionally cancel the operation with an error message.

If you can't deploy WSP packages / DLLs, you could take a look at the jQuery SharePoint library. This will let you interact with lists using jQuery. If you also write a WCF or Web Service wrapper around the data you need access to from your external data source that is accessible from the SharePoint environment, you can use hack together a solution.

To accomplish this you'd need to place a Content Editor Web Part on the page you need custom data access. In there you will write the code to reference the jQuery javascript library and jQuery SharePoint library. The code will have to make the calls to your external data service and make any updates you need.

This is the least reliable method to accomplish what you want since it's entirely page-based and can be broken by simply disabling script or someone editing the CEWP or removing it altogether.

If you don't have access to place a CEWP or any of the other solutions, then you have no options at all.

Answer 3

it relatively easy now to pull all the data using the third party webpart and saving it into a custom list. I would recommend you not only creating custom list but also creating the content types for this list. take a look at SharPoint MVP's post about creating a Custom List with Content Types