Custom Provider or DataSet From Custom View

IT Dept asked on September 9, 2015 11:48

I have added a new custom view in Site Manager -> Development -> System Tables -> Views

I want to access the data from this view in my code. I am running an older version though (7.0.103) and so I don't have access to the DataQuery API. Ideally I would like to be able to make a custom InfoProvider but I am happy just to store the returned data in an object I have created.

What is the best way to go about this?

kentico 7 view

Recent Answers

Dawid Jachnik answered on September 9, 2015 12:55

Hello Chris,

The view that you created is just the same view as you created in the database. So you need to have a qustom query that retrive the data by:

CMS.DataEngine.GeneralConnection gc = CMS.DataEngine.ConnectionHelper.GetConnection();
System.Data.DataSet ds = gc.ExecuteQuery("SELECT * FROM YOUR_VIEW", null, QueryTypeEnum.SQLQuery, false);

2 votesVote for this answer Mark as a Correct answer

IT Dept answered on September 9, 2015 14:23 (last edited on September 9, 2015 14:31)

Thanks. I'm using Kentico 7 which doesn't have the QueryTypeEnum but this looks ok:

CMS.DataEngine.GeneralConnection gc = CMS.DataEngine.ConnectionHelper.GetConnection();
System.Data.DataSet ds = gc.ExecuteQuery("SELECT * FROM View_Custom_ProductData", null);

It seems to be working but I am having trouble with the view name. In the cms desk it shows as:

schema: dbo name: View_Custom_ProductData

I have tried every option I can think of but always get a not found exception

[GeneralConnection.ExecuteQuery]: Query 'SELECT * FROM ProductData' not found.

[GeneralConnection.ExecuteQuery]: Query 'SELECT * FROM View_Custom_ProductData' not found.

[GeneralConnection.ExecuteQuery]: Query 'SELECT * FROM dbo.View_Custom_ProductData' not found.

0 votesVote for this answer Mark as a Correct answer

IT Dept answered on September 9, 2015 15:10

Figured it out. This works:

var test = SqlHelperClass.ExecuteQuery("SELECT * FROM View_Custom_ProductData", null, QueryTypeEnum.SQLQuery);

0 votesVote for this answer Mark as a Correct answer

Dawid Jachnik answered on September 9, 2015 16:04

My example coming out from Kentico 7 :) so it should work too :)

2 votesVote for this answer Mark as a Correct answer