How to create queries independant of database

[Anwar]

Hello,

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

String autoSignQuery = "SELECT * FROM CREW_SCHED_FIELDS WHERE AUTO_SIGN_CHANGES = :AUTO_SIGN_CHANGES";

// If Firebird

String autoSignQuery = "SELECT * FROM CREW_SCHED_FIELDS WHERE AUTO_SIGN_CHANGES = @AUTO_SIGN_CHANGES";

command.CommandText = autoSignQuery;

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

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.

 

Thanks!

 

 

 

 

 

 

[Kaloyan Nikolov]

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
{
    get
    {
        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

Regards,
Kaloyan Nikolov
Telerik

 

 

[Anwar]

Hi Kaloyan,

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