Introduction
As we all know, Microsoft SharePoint Portal Server is used to develop portals smart enough to seamlessly connect users, teams, and knowledge, enabling us to take advantage of relevant information across business processes.
In that situation, it becomes imperative to be able to cross-utilize the data being stored by SharePoint and any other application where it's intended to use the same data, which is being entered through SharePoint GUIs. The real cost, time and productivity advantage of SharePoint can be availed by enabling ourselves to use the portals and data entered through these for integration with other applications smoothly.
As such, we don't have any control on how the data is being stored inside a SharePoint portal but that should not be a constraint in designing your user interfaces with SharePoint. SharePoint provides enough methodologies to play with all the data that one would want for integrating with their other applications, external to SharePoint.
Principally, SharePoint Portal Server and Windows SharePoint Services use a SQL Server database to store all of its documents, web pages, and metadata. Another database is used to store the configuration detail. In fact, almost everything pertaining to SharePoint is stored in a SQL Server 2000 or MSDE database. By its design itself, database is the heart of SharePoint, and developers have to be familiar with its format to be able to make use of it.
During the installation, it can be defined whether the database for SharePoint is going to be MSDE or SQL Server 2000. SharePoint data is stored in a relational database either using MSDE or SQL Server 2000 depending on how it was installed. Entire SharePoint data can be seen in the Enterprise Manager on the relevant SQL Server.
The database name differs based on how the portals have been named there. A few crucial databases and their SharePoint nomenclature are like this:
- Database ending with _Config_db: it stores the entire configuration database for the SharePoint server/farm. Anything pertaining to global configuration and which is set through the SharePoint central administration is stored in this database.
- Database ending with _SITE: it stores all the content of a specific portal. It contains the files, web pages, webs, sites, and all inter-related SharePoint infrastructure.
- Database ending with _PROF: it stores entire information about User Profiles; it also stores details of what data to gather about various User Profiles.
- Database ending with _SERV: It stores data regarding search, notification, and indexing. It stores the gatherer log information as well as the text indexes for the content that is crawled. In fact, it can be easily checked up by right-clicking on any of the database and choosing Properties. In fact, that could be a way to know how much space each function/activity of your portal is taking up.
From a developer's point of view, the _SERV and _SITE databases are quite important. The _SERV database has several very important tables - srch_GathererLog_* (this could be many tables), srch_docsspecialprops, and sub_PropVersions. Each of these tables contain data about searching. The srch_GathererLog_* tables contain all the log information from gatherer logs. SharePoint does not allow clearing the gatherer log from UI and this can be done only through the Enterprise Manager.
The srch_docsspecialprops and sub_PropVersions contain data about files that have been indexed. The srch_docsspecialprops table includes information such as title, author, description, etc. for all files crawled as an external file source.
The tables in _SITE database are really important tables. There are tables like Webs, Docs, DocVersions, and Lists. These tables have content defined by their names. The Webs table contains information about the webs on the portal and the Docs table contains the path, names and actual content of all the documents in your portal infrastructure.
One can look into the Docs and DocVersions tables to find out the number of documents and document versions in your portal quickly. In fact, the number of rows in the Docs table tell the number of documents in that particular table. The same is true for the DocVersions table. The number of files shown by the SharePoint object model will be different because it also counts all the aspx files and webparts (.dwp) files to tell you the number, and not just the user stored files.
Connecting to the SharePoint database is as simple as any regular database connection. As with regular databases, first you need to create a connection to a database. In case you want to write code manipulating you SharePoint database, you will need to create an instance of a SqlConnection object in order to connect, after including a reference to the System.Data and System.Data.SqlClient to get this to work in C# code.
SqlConnection connect = new _
SqlConnection ("Server=�Viveks-server�; " +
"Database=�ViveksTest1_SITE�; Integrated Security=true ;");
Or else, if you are connecting through VS.NET SQL connection object in VB.NET, your resulting connection string would look like this:
Me.OleDbConnection1.ConnectionString = _
"Integrated Security=SSPI;Packet Size=4096;" & _
"Data Source=""MSBU-CMS1"";Tag with column" & _
" collation when possible=False;Initial " & _
"Catalog=SPS01_Config_db;Use Procedure for" & _
" Prepare=1;Auto Translate=True;Persist Security " & _
"Info=False;Provider=""SQLOLEDB.1""" & _
";Workstation ID=""MSBU-CMS1"";Use Encryption for Data=False"
To create the query in C#, we can use the following code:
SqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "Select Distinct Sites.PortalName From Docs Cross Join Sites";
This particular query gets the listing of portals from the database.
Once you have the query defined, you can execute it the normal way as in ADO.NET. So, going by our normal ADO.NET experience, the following code executes the query and creates the data reader object.
connect.Open();
SqlDataReader reader = cmd.ExecuteReader();
It's really simple as this, the same way you can insert or update into your SharePoint data directly. Though, you must be careful while deleting data from it as some of the databases are critical for running your SharePoint site. The rationale here is to illustrate a way in which one can develop one's own applications that get data from the SharePoint database directly. It also emphasizes that the SQL Server system is the heart of SharePoint, and if something happens to SQL Server, your SharePoint systems will likely go down. So, it should be adequately clear that with the functionalities of ADO.NET and SQL Server 2000, one can definitely play around with the SharePoint database to their advantage when they have to integrate it with their own applications.
