Using the data layer API to query your Kentico database

原帖地址:http://devnet.kentico.com/Knowledge-Base/API-and-Internals/Using-the-data-layer-API-to-query-your-Kentico-dat.aspx

This article describes how to make use of the Kentico API to perform complex queries or create generic code that queries both system and custom tables.

For system tables related to objects with info classes, you can access the data using providers, such as UserInfoProvider (for instance, to retrieve a UserInfo object to access data from cms_user table).

Database Table API documentation

You can also make use of the  data layer API . The CMS.DataEngine class can allow you more flexibility than using providers. You can perform more complex queries, generate queries within code, and query both system and custom tables.

Code examples for 5.5R2

Code examples for 6.0

Code example for 7.0

For more complex queries and queries against custom tables, you can use the ExecuteQuery method. See the last example in the above documentation. The query you call must exist in the CMS_Query table. By default any custom tables will have at least a selectall method.

Keep in mind that from 6.0 on, you need to use a QueryDataParameters object, but in 5.5R2 or earlier, you would use a generic object. In either case, you will want to establish a general connection:

GeneralConnection cn = ConnectionHelper.GetConnection();

Then execute the query using that connection:

cn.ExecuteQuery(…)