How to create queries independant of database

3 posts, 1 answers
  1. Anwar
    Anwar avatar
    14 posts
    Member since:
    Oct 2012

    Posted 26 May 2015 Link to this post


    I need to support multiple databases using Telerik data access. I am having issues in the specific case where I need to perform certain queries "by hand" by using the IdbCommand interface because specifying the query parameters  is different on the target database (:param with Oracle and @param with Firebird):

     // If Oracle


    // If Firebird


    command.CommandText = autoSignQuery;

    var autoSign = command.CreateParameter();
    autoSign.ParameterName = "@AUTO_SIGN_CHANGES";           // for Oracle :AUTO_SIGN_CHANGES
    autoSign.Value = 0;

    var result = command.ExecuteReader();


    Is there a simple way of building these queries in a generic way? Note that in my specific case I can't use link.









  2. Answer
    Kaloyan Nikolov
    Kaloyan Nikolov avatar
    118 posts

    Posted 29 May 2015 Link to this post

    Hello Anwar,

    The parameter prefix you can resolve from the context based on the current targeted backend. 

    To achieve that you can add the following property in a partial class file of your context:
    private BackendConfiguration.BackendInformation backendInfo;
    public BackendConfiguration.BackendInformation BackendInfo
            if (backendInfo == null)
                backendInfo = this.GetScope().Database.BackendConfiguration.BackendInfo;
            return backendInfo;

    Once you have exposed this information you will be able to use a backend independent symbol to indicate the parameter prefix and replace it with the actual one before executing the query.

    Here is an example code:

    using (var context = new EntityContext())
        var sql = "Select name from sys.tables where schema_name(schema_Id) = ##schema";
        sql = sql.Replace("##", context.BackendInfo.ParameterPrefix);
        var schemaParam = new Telerik.OpenAccess.Data.Common.OAParameter("schema", "dbo");
        schemaParam.DbType = System.Data.DbType.AnsiString;
        var tables = context.ExecuteQuery<string>(sql, schemaParam);

    I am using ## to indicate the parameter prefix to ensure that of for some reason it is not replaced will not be executed. In my sample I use a system table for simplicity and it is not backend independent query anyway, but I wanted to state with this that the sql queries might depend to the backend.

    Unfortunately there is no way how to avoid any other SQL specifics imposed by the current backend.

    I hope this helps

    Kaloyan Nikolov
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  3. DevCraft banner
  4. Anwar
    Anwar avatar
    14 posts
    Member since:
    Oct 2012

    Posted 08 Jun 2015 in reply to Kaloyan Nikolov Link to this post

    Hi Kaloyan,

     Thanks a lot for your response. This is exactly what I was looking for!

Back to Top