Accessing SharePoint data directly into the SharePoint database is not difficult. How...?
In general, we access the data through the SharePoint Web Service or the object model provided by SharePoint. At the time this article, the author invites the reader how to access the data directly into the SharePoint database through query
Starting in the Database Query SharePoint Site
Firstly, set the SharePoint Site that we want to do the testing. For this purpose, we will use the data that made the CMS consists of a lot of SharePoint content such as document, list, or other. You can use your own data.
Obtaining Data Web Site in SharePoint
We can obtain a list on a web site on SharePoint Webs table. By using the query as follows.
select id, title, description from webs
We will get some of the web site in SharePoint, and we also see the value "CMS" in the title. This is an application that we will focus on this article
Retrieving Data Content
Each SharePoint site will have the data content of various types of data and queries, we also obtained data using the query as follows:
tp_title select from alllists
Retrieving Data Format Content
Once we see the list of data content in a SharePoint site, then we can also see the data format of the data content is, eg we want to see the data format Product_Price. Then we can also see the data format via a query. The format of data content is listed on the label AllList on the column tp_Fields and tp_ContentTypes. Tp_Fields column provides information on the field what they have on the data content. Meanwhile, the columns contain information tp_ContentTypes type daytanya. Mendaatkan following query to format the data content Product_Price:
select tp_fields, tp_contenttypes from alllists where tp_title = 'Product_Price'
If we fill in the fields open tp_Fields, then we will get the data where we see the field name and format. As an example for the field Price:
From the data above can be known that the field type is Number Price with 2 digits behind the decimal comma. Here we also know that the Price field is "Required" means the data must be filled.
Retrieving Data Content Content
We see the available data on Product_Price and we can also display data in a way to make a query in the database. SharePoint stores all data, both types of documents and to list in one table, the AllUserData. Steps to retrieve data through a database query as follows:
1. Define the data content is sought, for example Product_Price, and so Product_Gen
2. Make a query to the table AllLists to get the information field which is used in the tp_Fields
3. Make a query to the table allUserData in accordance with the available field
To facilitate the illustration using the data content Product Price, the first step we perform a query to obtain information columns used to how to save in the database table AllUserData. The results of this query column name (field) Price and value ColName here is "float1". This means the data field stored in the Price field float1 on the table AllUserData. In the same way we can, such as the table below
TABEL 1.
FIELD NAME FIELD NAME SAVE IN ALLUSERDATA TABEL
Price float1
Product_Barcode nvarchar3
Retail_Article_ID nvarchar4
After getting information about the field of Product_Price. So we will be able to make direct queries to the table AllUserData as follows:
select tp_id, tp_dirname, nvarchar3, nvarchar4, float1 from alluserdata
where tp_dirname like 'cmdb / lists / product_price%' and tp_iscurrent = 1 and tp_deletetransactionid = 0
Let's surgical purposes on the query. Value "tp_id, tp_dirname, nvarchar3, nvarchar4, float1" shows that we will do a query and get the data value field that indicates the appropriate column in table 1. Tp_id Field ID of the data shows Product_Price. Meanwhile, the value "tp_dirname like 'cmdb / lists / product_price%'" shows the filtering that we will check the data on the Product_Price. To value "tp_iscurrent = 1 and tp_deletetransactionid = 0" indicates that the data is active and the data are not erased.
Viewing Data Deleted
Any data or documents from the list will be stored by SharePoint to AllUserData in the table. If you want to get the data that are currently active, then we must add a filter, namely:
tp_iscurrent = 1 and tp_deletetransactionid = 0
When we want to get the data off, but the status is deleted, then we add a filtering as follows:
tp_iscurrent = 1 and tp_deletetransactionid <> 0
Each data item is removed from the document list or the value of the transaction will be recorded by SharePoint. For example, we have the data that are currently active and the status of the erased portal CMS Product_Price as follows:
select tp_id, tp_dirname, nvarchar3, nvarchar4, float1, tp_deletetransactionid from alluserdata
where tp_dirname like 'cmdb / lists / product_price%' and tp_iscurrent = 1 and tp_deletetransactionid <> 0
Deleting Data Through Queries
We can remove data items on the list through a document or query. If we perform the deletion of data through SharePoint portal, SharePoint provides the data in the field tp_DeleteTransactionId value 0x means that the status data is not deleted, but if a particular value, the data are erased.
For example, we want to delete the data on the Product_Price with ID = 2, product _barcode 8852410888516 and retail_article_id 12346. As we know before, product_barcode refer to the field nvarchar3 and retail_article_id nvarchar4 so that its query as follows:
update alluserdata set tp_deletetransactionid = 0x00000010 where tp_dirname like 'cmdb / lists / product_price%' and tp_id = 2 and tp_iscurrent = 1
In general, we access the data through the SharePoint Web Service or the object model provided by SharePoint. At the time this article, the author invites the reader how to access the data directly into the SharePoint database through query
Starting in the Database Query SharePoint Site
Firstly, set the SharePoint Site that we want to do the testing. For this purpose, we will use the data that made the CMS consists of a lot of SharePoint content such as document, list, or other. You can use your own data.
Obtaining Data Web Site in SharePoint
We can obtain a list on a web site on SharePoint Webs table. By using the query as follows.
select id, title, description from webs
We will get some of the web site in SharePoint, and we also see the value "CMS" in the title. This is an application that we will focus on this article
Retrieving Data Content
Each SharePoint site will have the data content of various types of data and queries, we also obtained data using the query as follows:
tp_title select from alllists
Retrieving Data Format Content
Once we see the list of data content in a SharePoint site, then we can also see the data format of the data content is, eg we want to see the data format Product_Price. Then we can also see the data format via a query. The format of data content is listed on the label AllList on the column tp_Fields and tp_ContentTypes. Tp_Fields column provides information on the field what they have on the data content. Meanwhile, the columns contain information tp_ContentTypes type daytanya. Mendaatkan following query to format the data content Product_Price:
select tp_fields, tp_contenttypes from alllists where tp_title = 'Product_Price'
If we fill in the fields open tp_Fields, then we will get the data where we see the field name and format. As an example for the field Price:
From the data above can be known that the field type is Number Price with 2 digits behind the decimal comma. Here we also know that the Price field is "Required" means the data must be filled.
Retrieving Data Content Content
We see the available data on Product_Price and we can also display data in a way to make a query in the database. SharePoint stores all data, both types of documents and to list in one table, the AllUserData. Steps to retrieve data through a database query as follows:
1. Define the data content is sought, for example Product_Price, and so Product_Gen
2. Make a query to the table AllLists to get the information field which is used in the tp_Fields
3. Make a query to the table allUserData in accordance with the available field
To facilitate the illustration using the data content Product Price, the first step we perform a query to obtain information columns used to how to save in the database table AllUserData. The results of this query column name (field) Price and value ColName here is "float1". This means the data field stored in the Price field float1 on the table AllUserData. In the same way we can, such as the table below
TABEL 1.
FIELD NAME FIELD NAME SAVE IN ALLUSERDATA TABEL
Price float1
Product_Barcode nvarchar3
Retail_Article_ID nvarchar4
After getting information about the field of Product_Price. So we will be able to make direct queries to the table AllUserData as follows:
select tp_id, tp_dirname, nvarchar3, nvarchar4, float1 from alluserdata
where tp_dirname like 'cmdb / lists / product_price%' and tp_iscurrent = 1 and tp_deletetransactionid = 0
Let's surgical purposes on the query. Value "tp_id, tp_dirname, nvarchar3, nvarchar4, float1" shows that we will do a query and get the data value field that indicates the appropriate column in table 1. Tp_id Field ID of the data shows Product_Price. Meanwhile, the value "tp_dirname like 'cmdb / lists / product_price%'" shows the filtering that we will check the data on the Product_Price. To value "tp_iscurrent = 1 and tp_deletetransactionid = 0" indicates that the data is active and the data are not erased.
Viewing Data Deleted
Any data or documents from the list will be stored by SharePoint to AllUserData in the table. If you want to get the data that are currently active, then we must add a filter, namely:
tp_iscurrent = 1 and tp_deletetransactionid = 0
When we want to get the data off, but the status is deleted, then we add a filtering as follows:
tp_iscurrent = 1 and tp_deletetransactionid <> 0
Each data item is removed from the document list or the value of the transaction will be recorded by SharePoint. For example, we have the data that are currently active and the status of the erased portal CMS Product_Price as follows:
select tp_id, tp_dirname, nvarchar3, nvarchar4, float1, tp_deletetransactionid from alluserdata
where tp_dirname like 'cmdb / lists / product_price%' and tp_iscurrent = 1 and tp_deletetransactionid <> 0
Deleting Data Through Queries
We can remove data items on the list through a document or query. If we perform the deletion of data through SharePoint portal, SharePoint provides the data in the field tp_DeleteTransactionId value 0x means that the status data is not deleted, but if a particular value, the data are erased.
For example, we want to delete the data on the Product_Price with ID = 2, product _barcode 8852410888516 and retail_article_id 12346. As we know before, product_barcode refer to the field nvarchar3 and retail_article_id nvarchar4 so that its query as follows:
update alluserdata set tp_deletetransactionid = 0x00000010 where tp_dirname like 'cmdb / lists / product_price%' and tp_id = 2 and tp_iscurrent = 1
Comments
Post a Comment