SharePoint University

Hi Guys,
        Just wondering how you guys do this.
Lets say i have some lists etc on my WSS v3site.
But i want to use the data in that list together with some table somewhere
else in another database.
And i would need to have a report that needs the data from this 2
datasources.
Whats the best way to get  data out of the sharepoint site or db and make
use of it somewhere for reporting etc..
Any ideas or approaches would be appreciated.
Thanks in Advance

Just curious... why would you want to move it out of SharePoint for your reports. Why not pull the data into SharePoint through a data connection and create your reports from there?

Here's a link to a resource: Add a data connection to a SharePoint library or list.

 Mark

SharePoint has exposed two ways to get at the data in a structured format. One is through the SharePoint Object Model and the other is through web services. However, I agree with Matt -- you have a tremendous amount of reporting capabilities at your fingertips in SharePoint. I do know there are scenarios where the data needs to be presented outside of a SharePoint environment, but if there is any way possible to do so within SharePoint it would be much better.

The only data that we have exposed from SharePoint to an external entity is a Job Positions list from our HR department. It allows HR to keep a list of open positions updated and synchronized with our external visitors in real-time. This was done through web services.

Thanks guy for the replies
What i meant actually is to be able to use the data for example from a list and merge it into an existing table in a db.
Which i understand the only way i can do this is neither through the OM or through web services.

As anybody been in a scenario were you retrieve real time data from sharepoint via web services or OM and join it to an
existing table from another db? Or is it better getting the data from sharepoint and putting it in a new table.

Any ideas 

You can easily write a webpart or a app that uses the OM to return data from a list then do whatever you want with that data just as you would with any other data in .net. You can do just about anything you want with it. The issue you are going to run into is formatting the data in a way that the other database will accept. SharePoint uses it's own schema so you may need to do some formatting or conversion on the data before serializing it to the database. You will have to figure out how the formatting etc... needs to be done based on where you want to put the data.

 One thing to be very careful of however is never write code that directly talks to the SharePoint databases as Microsoft does not support direct manipulation of the SharePoint datbases. Just an FYI in case you need to open a support case.

naijacoder:

Which i understand the only way i can do this is neither through the OM or through web services.

Actually, the best way to do this is either through the OM or Web Services. Using web services you can return a string of XML data (I can't remember offhand if this is the case with the OM) and use that along with the dataset object to deserialize the data into a more native ADO.NET object. This will allow you to do anything you can do with a normal dataset. Remember, the dataset is abstracted away from the data source -- that's where adapters come in. So, in theory you can join data from multiple SQL databases, oracle databases or other OLE databases (and in our case, SharePoint lists) and treat all of this data as a convergant source of information. However, one of the things to keep in mind is that SharePoint allows for multi-value fields in some cases (for example, look-ups to other lists where multiple items can be selected as is the case with the default issues list). These will require some extra handling in the dataset, but it shouldn't be too difficult.

The other thing that you may want to take into consideration is how to handle really large lists of data. The web services allow you to configure the number of items from a list you want to return at a time. This will help in managing memory during the execution of your application, so in theory, you could real-time join the data in memory. However, if you are dealing with an exceptionally large list of data, it might be advisable to move the data into a new table prior to working with the data. This depends totally on your environment and the data you will be manipulating.

Hope this helps!

Westdccs and Bluephaze.. Good on you guys.
I would dig into that and share any encounter i come across.
Patrick