So far, you might have used various means of keeping your test data or have retrieved it from various sources such as flat files, MS Excel, MS Access, SQL etc. Have you ever come across a scenario where you have to pull data from SharePoint? you don’t! No worries, it’s always a good thing to learn something new. Maybe this will give you some idea to automate couple more things 🙂 .
This a three part series on using sharepoint as data source
Part 1 – Sharepoint and usage Basics
Part 2 – Retrieve data from Sharepoint
Part 3 – Post data to Sharepoint
In this part we will discuss the basic understanding of SharePoint, so that it will be easy to proceed further.
In this Post
Why would I need to connect to SharePoint?
You must be wondering, why I need to connect SharePoint when I already have various other ways. Here are some of the scenarios –
- Share-point is being used for storing all the artifacts at common place. Your automation require to upload some kind of documentation to the share-point for everyone to refer or its required to fetch a document for your automation purpose.
- Data for application is being maintained at share-point – It might be possible that the input data or any other reference data has been maintained at share-point list which you need to retrieve or update data.
- Request for automation – There could be a scenario where you want to automate in such a way that your automation will trigger based on the request and data entered at a form at share-point. And your script would act based on the data provided.
- Use share-point as reference data to trigger Test Execution – You want to have a common location to select test cases to trigger execution, and have the status updated at the same location instead of maintaining separate excel file.
There could be many more scenarios like this. If we just know the basic approach to pull and push data to SharePoint, This would solve the other different scenarios.
What is SharePoint?
This is for those who are not aware of SharePoint, although I feel everyone does know by now but just in case not!
Microsoft SharePoint is a browser-based collaboration and document management platform. This is widely being used as intranet sites for everyone in an organization for news, announcement, tasks, data, documentation and access to variety of information. There are various features which can be utilized to make an easy and effective collaboration.
Although, it has some 8-10 components for different purposes. here are the three important things which everyone must know about SharePoint
-
Sites: A share-point site is a Web site that share-point manages in ways that are compatible with its content-management features. share-point sites can contain many user facing share-point features such as Document Libraries, Lists, Wikis, Blogs, and Discussion Boards.
-
Document Libraries: A Document Library provides a common location or repository for the team to collaborate and keep artifacts, which includes document check-in and check-out, versioning, security, and workflow.
-
Lists: Lists is like MS Excel or MS Access, mostly Access, that stores data in columns and rows. Almost all of the user facing share-point features stem from Lists.
Connecting SharePoint
Data from SharePoint can be retrieved by any of the following ways
- Using Sharepoint Object model – this can be used only on the machine (server) where SharePoint is installed.
- Using Sharepoint Lists Service – Connecting SharePoint and retrieving data from SharePoint list web service.
- Using OLE DB Connection – connect the way, you use to connect databases.
I will not cover the Sharepoint Object Model as part of this post, as its out of topic for this post. also this is not something you would normally wont need to cover in your day to day work. In order to the object model to work, it is required to have the SharePoint server installed on the same machine where you are working. It is mostly being used by the SharePoint developers to create Web Parts etc.
Using Sharepoint Lists Service
SharePoint provides various methods to work with SharePoint lists via its lists web service. This service can be accessed for your site by setting the web reference as below
http://yoursharepointsite/_vti_bin/Lists.asmx
To retrieve the data, we need to post request to service and the data will be contained in the response. This needs to be parsed for further use.
Few things you should know before you proceed.
CAML query – to retrieve the data based on some condition, we need to write request xml accordingly. This would be possible easily Collaborative Application Markup Language (CAML). CAML Query is XML based query language and it has predefined tags. For example root element for the query will be defined by the tag – Query.
Within the Query element you have two elements possible:
1. Where – to filter the data
2. OrderBy – to categorize the data
A simple structure of the CAML query is as follows:
<Query> <Where> <Eq> <FieldRef Name=”FieldName” /> <Value Type=”DataType”>Value</Value> </Eq> </Where> <OrderBy> <FieldRef Name=”FieldName” /> <FieldRef Name=”FieldName” /> </OrderBy> </Query>
SharePoint field Naming Convention in Response – Next important thing you should know is, the way Sharepoint names the fields or columns internally. Sharepoint manage the field names differently. Just like you see in most of the web URL, %20 for the spaces, SharePoint uses x0020 for the spaces. For example, for field name “First Name” the internal name would be ”Firstx0020Name”. you should use this name for the query.
In the response you will find the same, represented as with a suffix “ows”, so for First Name field, this would be “ows_First_x0020_Name”. In case there are no spaces, the name would be “ows_FirstName”.
These are all in terms of a brief basics on Sharepoint, I hope yo enjoyed learning this. In the next post we will go into details of connecting to a Sharepoint using the list services method.
Thanks for the article Saket. This indeed is very useful and informative. Looking forward for next chapters in the series.
Thanks Ajay.
[…] the last post [ Using MS SharePoint as data source : Part 1 ], we went through the understanding of SharePoint in brief. Now that you have all the […]
[…] last part of the series: Using MS SharePoint as Data Source. In the earlier posts we learned the basics of SharePoint and the easy way to retrieve data to SharePoint list. In this post we are going to discuss the […]