Forums (Obsolete)

Member

positivepurchasing - 7/19/2012 4:32:30 AM

Retrieving data from the database

Hi

I've looked at the documentation and I can't anything about getting data from the database. There must be information about it however I just can't find it.

What is the simplest way to get content from the db?

I've managed to do the following.

TreeProvider treeProvider = new TreeProvider();

DataSet dataSet = treeProvider.SelectNodes(
CMSContext.CurrentSiteName,
ContentPath,
null,
true,
"custom.Test");

foreach (DataRow dataRow in dataSet.Tables["custom.Test"].Rows)
{
// do stuff
}

Which is ab bit messy. Is there a nicer way to do this?

Basically all I'm asking is how do I retrieve data from the database?

Please Help!

Kentico Support

kentico_radekm2 - 7/19/2012 5:55:33 AM

RE:Retrieving data from the database

Hello.

What data in particular you want to retrieve?

For object data, like users or documents in your example, our API is the easiest way.
What exactly is messy about a code sample you provided?

To get a data, you need to call just a single line of code:
TreeProvider.SelectNodes() method.

The foreach cycle you wrote is for additional operation if I am not wrong.

Do you want even simpler way than calling one method?

Beside our API, you can use standard ADO.NET API and execute any query over your DB, as our DB is standard MS SQL DB.

So, for our objects you can use our API, for general operation standard ADO.NET API.

Best Regards,
Radek Macalik


Member	positivepurchasing - 7/19/2012 6:19:43 AM RE:Retrieving data from the database Hi OK, if that is the way to do it then that is fine. For example. Once in the foreach loop to get to the data values I've used this 'dataRow[148].ToString();' is this how I should retrieve the actual data? If you can point to some documentation about retrieving content from the db that would be great. Cheers

Member

positivepurchasing - 7/19/2012 10:23:32 AM

RE:Retrieving data from the database

Hi

Now I realise you can use the column names as well as cloumn index, as normal, which is good. I see there is a type called 'CMS.TreeEngine.TreeNode' is this more useful when retrieving data?

Learning Kentico by trial and error is very difficult and time consuming Please direct me to some documentation about the basics of retrieving data from the database. I can't find any!

Cheers

Certified Developer 8

Jiveabillion - 7/20/2012 1:13:51 PM

RE:Retrieving data from the database

I know it can be difficult to learn to query the tree using the API, I have had a lot of frustrating moments trying to get the data I need.

If you want to access the results of tree.SelectNodes() as TreeNode objects you can just do this:


var nodeDataSet = tree.SelectNodes([your select parameters]);
if(nodeDataSet !=null) // this is important to check
{
  var treeNodes = nodeDataSet.Items; // the Items property is a TreeNodeCollection of the dataset.
foreach(TreeNode node in treeNodes)
{
   string aValue = node["ColumnName"].ToString();

//it's actually a better idea to use the validation helper to access the properties of a node so you don't have to check if they are null before converting the values to the type you need
string bValue = ValidationHelper.GetString(node["ColumnName"],String.Empty);

//You can also directly access the default document and node properties

string aliasPath = node.NodeAliasPath;
}
}

Certified Developer 8

Jiveabillion - 7/20/2012 1:19:58 PM

RE:Retrieving data from the database

I have also created a nice little class to make it easier to query the tree by using a pre-configured NodeSelectionParameters object. In the constructor, I pre-configure some of the properties to the settings that I use most often. here it is:


public class NSP : NodeSelectionParameters
{
    public NSP():base()
    {
        CultureCode = TreeProvider.ALL_CULTURES;
        this.MaxRelativeLevel = -1;
        this.CombineWithDefaultCulture = true;
        this.SiteName = CMSContext.CurrentSiteName;
        this.SelectOnlyPublished = true;
       
    }
    public NSP(NSP config):this((NodeSelectionParameters)config)
    {
       
    }
    public NSP(NodeSelectionParameters config)
    {
        this.OrderBy = config.OrderBy;
        this.SelectAllData = config.SelectAllData;
        this.SelectOnlyPublished = config.SelectOnlyPublished;
        this.SelectSingleNode = config.SelectSingleNode;
        this.SiteName = config.SiteName;
        this.TopN = config.TopN;
        this.Where = config.Where;
        this.AliasPath = config.AliasPath;
        this.ClassNames = config.ClassNames;
        this.Columns = config.Columns;
        this.CombineWithDefaultCulture = config.CombineWithDefaultCulture;
        this.CultureCode = config.CultureCode;
        this.MaxRelativeLevel = config.MaxRelativeLevel;
    }
    
}

The way you can use this class is like so:


var tree = new TreeProvider();
var blogPosts = tree.SelectNodes(new NSP(){ ClassNames="cms.blogpost",OrderBy="BlogPostDate DESC", 
AliasPath="/MyBlog/%" });
//See how you only need to set the properties that are different than what you use by default? This has saved me a bunch of time when writing a lot of tree selection queries.


Member	scott_hancock-urmc.rochester - 12/24/2012 9:36:01 AM RE:Retrieving data from the database Hi, What does this function do? I'm confused by the syntax. public NSP(NSP config):this((NodeSelectionParameters)config) Thanks, Scott


Member	kentico_alleng - 7/20/2012 5:45:40 PM RE:Retrieving data from the database You may find the information in this Knowledgebase article helpful: Using the data layer API Regards, Allen


Member	positivepurchasing - 7/23/2012 5:02:15 AM RE:Retrieving data from the database Hi, Thank you both for your replies, very helpful. Cheers

Certified Developer 8

Jiveabillion - 7/23/2012 9:43:34 AM

RE:Retrieving data from the database

It's important to remember that if you query using TreeProvider.SelectNodes() that, unless you specify the classname(s), it will only return the default node data that is common for all document types that can be found in the view View_CMS_Tree_Joined.
The same thing goes for when you use any Web Parts that query documents. Make sure you select the document types you want to retrieve.

If you find yourself wondering why there seems to be no data in one of your document's custom fields when you are using a transformation or something and you know that there is data in it, chances are that you forgot to set the ClassNames in your settings.

The other thing to remember is that each TreeNode has a Children property. This Children property isn't populated until it's enumerated or accessed in such a way that causes enumeration. It also does not automatically return nodes with all of their columns filled. You can make it load them with all their columns by accessing node.Children.WithAllData or by setting the node.Children.ClassNames property.

Setting the node.Children.ClassNames property also filters the children and only returns the documents of the type(s) you specify.

The node.Children property has most of the properties of the NodeSelectionParameters type, but it has some preset properties that you need to be aware of. If you wish to set the Where clause of the node.Children property, keep in mind that it is already set to be "NodeParentID = 123(the id of the node you are working with)". So if you want to add conditions to the where clause you should use
node.Children.Where = SqlHelperClass.AddWhereCondition(node.Children.Where,"Your Where Clause")

Also remember that if you are querying for multiple classnames, your Where clauses and OrderBy clauses must only use column names that exist in all of the classnames you specify, otherwise you will get a SQL error. Also keep in mind that the OrderBy clause only orders each individual classname's table by that clause. So if you are querying 3 classnames with OrderBy = "NodeOrder", and you enumerate through them all, then you will get 3 groups of nodes ordered by the order you entered the classnames and then by NodeOrder. You would have to sort them again after they are returned.

That's all the tips for now. I hope you enjoy using Kentico as much as I do!


Certified Developer 8	Jiveabillion - 7/28/2012 4:53:15 PM RE:Retrieving data from the database I almost totally forgot about the TreeHelper class. You can use the TreeHelper.SelectNodes and TreeHelper.SelectSingleNode methods for some shorthand node selection options.


Kentico Support	kentico_radekm2 - 7/30/2012 1:09:35 AM RE:Retrieving data from the database Hello. Thank you for participating this forum and sharing your solution. Best Regards, Radek Macalik