Help Needed with ORM and TreeView/RadGrid

[Sathish]

Hi,

I'm trying to display a list of categories in a treeview form inside a RadComboBox. I can do that either with TreeView or RadGrid. I want the ability to filter as well as we type in the RadComboBox.

I am using OpenAccess ORM with 3 Layers.

1. BL (Contains the objectscope provider)
2. BO (Persistent Classes)
3. UI (WEB)

I dont want to use the openaccessdatasource control. I want to populate the treeview from the IQueryResult (or BindingResult.. etc)

My table contains the following fields: 

Name,
CategoryID,
ParentCategoryID,
Description,
Notes

Since this table is Self referencing, I am not able to get the ParentCategoryID as a field, but instead as Category Object.
I am not able to bind the treeview as it requires a ParentCategoryID and I get the Category object instead of the field value.

Is it possible to execute a simple SQL query using ORM to return a datatable etc?

Also, How do I implement the search feature in treeview combobox or grid combobox.

[PetarP]

Hi Sathish,
you can achieve the desired effect by using this piece of code. You can bind the RadComboBox to a set of Order IDs using this:
PetarP

[Sathish]

Hi,

Could you please elaborate. Was there some code that you attached? I dont see any code.

 I have a couple of questions,


1. Is it possible to execute a simple SQL query using ORM to return a datatable etc?
For a table named: "Contacts" consisting, ID, Name, Description, Address, City, Country.
for ex. "SELECT ID, Name FROM Contacts".

2. How do I implement the search feature in treeview combobox or grid combobox. (as you type in radcombobox, the treeview should filter, but display the child nodes of everything thats in the result.

Also, if you can get a control that will have treeview/gridview in a combobox with search features, that would be great!!

[PetarP]

Hello Sathish,
Yes you can easily select several columns from a database using SQL query. Here is an example of selecting all Birth Dates from the Employee table from Northwind.

IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();
            string query = "Select x.BirthDate, x.EmployeeID from Employees as x where x.BirthDate > ?";
            var result = scope.GetSqlQuery(query, null,"timestamp myDate").Execute(DateTime.Parse("1/1/1960"));
            foreach (object[] obj in result)
            {
                Console.WriteLine(obj[0]); // Prints the Birth Date since it was selected first in the query
                Console.WriteLine(obj[1]); // Prints the Employee ID
            }

Note that this is parametrized query. Since we select only one field of the Employee table, the result cannot be mapped to employee because it does not contain all fields. If you want to use the results from the result set you will have to approach to them as objects. Each result row contains the selected information for each employee.

You can bind the combo box to the field that you are going to filter with ( for the example we have used the Order and order Details. We bind the combo box to Order ID and we are filtering the order details based on the order id selected from the combo box.)
You can bind the rad combo box to the Order ID using this code:

var result = from c in scope.Extent<OpenAccessData.Order>() orderby c.OrderID select c.OrderID;
                RadComboBox1.DataSource = result;
                RadComboBox1.DataBind();

In the SelectedIndexChanged even for the rad combo box paste the following code which will bind your grid to the filtered information.

int myValue = Int32.Parse(RadComboBox1.SelectedValue.ToString());
            var result = from c in scope.Extent<OpenAccessData.OrderDetail>() where c.OrderID == myValue select c;
            RadGrid1.DataSource = result;
            RadGrid1.DataBind();

You will need to set the auto post back property of the rad combo box to true.

All the best,
PetarP
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.