LINQ,OQL and Native SQL

[Joebet]

Good day to all. Im a little confuse about the three methods on using SQL with Open access. May I know whats the difference of the three? And when should i be using it? and whats the best to use in an application?

tnx

[Joebet]

anyone pls?

[Petko_I]

Hello Joebet,

There are pros and cons to using the three methods and here I will make a brief overview of them.

1)    1)    Native SQL is a powerful tool if you want to push the chosen database to the limit. This means you can fully utilize the specific features of the database vendor. This approach is a nice one to use when there are things you cannot accomplish easily otherwise. For example, with native Transact-SQL (the MS SQL Server standard) you can have the TOP PERCENT clause in your SELECT statement which helps you retrieve a percentage of the records in a table. It is usually recommended that the other two approaches are used because should you decide to change the database vendor, some of the SQL queries may not work properly. Another disadvantage is that you cannot check the validity of your queries during compile time.

2)    2)    LINQ is currently considered the most powerful tool to manage result sets. Firstly, LINQ is database-agnostic (like OQL) which means that irrespective of the database vendor you choose you can have the same retrieval logic in your applications. Secondly, LINQ provides you with the ability to use lambda expressions and anonymous types which can help you easily accomplish very complex scenarios using features of the programming language. LINQ also provides you type safety and compile-time checking of the types of the objects you retrieve. Last but not least, Telerik OpenAccess provides the ability to specify FetchPlans which, with the combination of LINQ, can heavily improve performance. This is achieved through the filtered selection of only the attributes you need. Here are some useful links regarding this great feature of Telerik OpenAccess:   

http://blogs.telerik.com/openaccessteam/posts/09-12-02/linq_tip_of_the_week_linq_and_fetch_plans.aspx

http://blogs.telerik.com/openaccessteam/posts/09-08-13/linq_tip_of_the_week_linq_and_anonymous_types.aspx

        http://blogs.telerik.com/openaccessteam/posts/09-07-30/linq_tip_of_the_week_a_quick_peek_behind_the_curtains.aspx

3)    OQL stands for Object Query Language. It was introduced to make the retrieval of objects non- database-vendor-specific. It resembles SQL as it uses strings to build your queries and besides makes full use of the object-oriented programming paradigm. It is worth noting that with OQL and native SQL you can build queries dynamically based on user input. However, you should be cautious when using this approach, especially when using native SQL, as you can end up being the victim of an SQL injection. Here is a blog post which covers some topics on OQL and provides links to other resources which can further introduce you to this query language.
http://blogs.telerik.com/openaccessteam/posts/09-09-24/altering_oql_queries_to_generate_different_join_clauses_in_sql.aspx

If you have any further questions, do not hesitate to contact us.

All the best,
Petko_I
the Telerik team