Populate data using query

[Amrutha]

Hi,

I am new to the Open Access ORM. I have created a website in which i have added an already existing database(NorthWind).Now i have a 'EntitiesModel.rlinq' in my application. I created an aspx page where i want to populate a RadGrid with the data in 'Orders' table on a button click in server side using sql query. How should i write the query? How can i accomplish this?

Thanks,
Amrutha.

[Doroteya]

Hi Amrutha,

In OpenAccess ORM queries are composed using Language-Integrated Query (LINQ). All you need to do are the following steps:
    - In Solution Explorer find your .aspx file.
    - Right-click on it and select View Designer.
    - Double-click on your button to add a method that caches the OnClick event of the button.
    - Inside that method create a new instance of the OpenAccess context (within a using statement).
    - Compose a LINQ statement and save its result to an IQueariable<T> variable.
    - Attach the result as a data source of the RadGrid.

The next lines of code show an example on how that could be done:

protected void btn_PopulateGrid_Click(object sender, EventArgs e)
    {
        //Create an instance of the context
        using (EntitiesModel dbContext = new EntitiesModel())
        {
            //Select rows from the database
            IQueryable<Order> ordersByCustomer = dbContext.Orders.Where(
                             order => order.CustomerID == "ALFKI");

            //Attach the result as a data source of the RadGrid
            OrderGrid.AutoGenerateColumns = true;
            OrderGrid.DataSource = ordersByCustomer;
            OrderGrid.DataBind();
        }
    }

For further information about the CRUD operation you can check the CRUD Operations section in the Developer’s Guide of our documentation.


Kind regards,
Doroteya
the Telerik team

[Amrutha]

Hi Doroteya,

Thanks for the reply. Can i use sql query? I tried and am getting an error.. Invalid object name 'LoginDetail' ..  is this because of sql query.. Please reply..

C#:

using (EntitiesModel dbContext = new EntitiesModel())
        {
            string query="select * from LoginDeatil";
            IEnumerable<LoginDetail> categories = dbContext.ExecuteQuery<LoginDetail>(query);
        }

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'LoginDetail'.

Please do reply soon... its urgent.

Thanks,
Amrutha

[Doroteya]

Hi Amrutha,

There is no problem to execute SQL statements with OpenAccess. In fact, there is a detailed article that covers the matter here.

About the error you are getting, my best guess would be that the table name in the current SQL query probably doesn't match with the name defined in the database. Could you verify that?

In case the names are matching, I would ask you to send us a copy of the .rlinq file and the schema of your database for further investigation.


Kind regards,
Doroteya
the Telerik team

[Amrutha]

Hi Doroteya,

Thank you very much for the reply. I could solve the issue. As you said, the error was related with the name I used in the query.

Thanks again for the support.
Amrutha.