On Wed, 11 Jan 2006 01:08:28 +0100, "Massimo" <bar***@mclink.it>
wrote:

>I'm trying to write some DB-independent code using ADO.NET 2.0 and the
>System.Data.Common.DbX classes, as suggested before; but I'm encountering
>another weird problem.
>
>Here's some code (let's assume the provider name and the connection string
>are correct):
>
>
>public void TestQuery(string providername, string connectionstring)
>{
>
>    // Factory
>
>    DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
>
>
>    // Connection
>
>    DbConnection dbconn = factory.CreateConnection();
>
>    dbconn.ConnectionString = connectionstring;
>
>
>    // Command Preparation
>
>    DbCommand command = dbconn.CreateCommand();
>
>    command.CommandText = "SELECT * FROM Table WHERE Column = @Val";
>
>    DbParameter param = factory.CreateParameter();
>
>    param.DbType = DbType.Byte;
>    param.PArameterName = "Val";
>
>    command.Parameters.Add(param);
>
>
>    // Execution
>
>    dbconn.Open();
>
>    command.Parameters["Val"].Value = 42;
>
>    command.ExecuteReader();
>
>    return;
>}
>
>
>As you can see, I'm trying to keep the code as provider-indipendent as
>possibile; this is much semplified code, in the real code the provider
>selection happens at program startup and is handled by some classes, the
>comand preparation happens somewhat later and is handled by totally
>different parts of the application, and the command execution happens at
>runtime, almost everywhere; I want the last two phases to not know anything
>about the specific provider used.
>
>The problem: this code works only with SQL Server, which uses "@Val" as a
>parameter placeholder. Oracle uses ":Val", and I'm told other providers use
>even different characters ("?").
>
>
>How can this be solved without cluttering the command building with
>provider-based IFs and CASEs?
>
>
>Thanks
>
>
>Massimo
>
>P.S.
>I know I could write some code like
>
>public string GeneratePlaceHolder(string paramname);
>
>and use it when building commands, but I'd like something simpler (and
>hopefully less ugly) to use.

On Thu, 12 Jan 2006 03:21:18 +0100, "Massimo" <bar***@mclink.it>
wrote:

>"Mary Chipman [MSFT]" <mc***@online.microsoft.com> ha scritto nel messaggio
>news:h18bs19n05aht56ppom6oc1m83cm8revme@4ax.com...
>
>> Unfortunately, the factory model in 2.0 does not do anything for you
>> in parameterized query scenarios when you don't know what provider
>> you're using ;-(
>>
>> You're going to have to branch in your code or provide your own
>> framework (which is probably more work than you want to do).
>
>Actually, I quickly wrote some methods to handle this issue, and they're
>(more or less) working; this code accepts queries in SQL Server format
>(using '@Name' for parameters) and rewrites them in order to work wih
>different providers; it builds DbCommands on the fly with any number of
>parameters.
>It uses some members from the class it belongs to, but it should be quite
>clear:
>
>----------
>protected DbCommand CreateCommand(string query,params DbType[] parameters)
>{
>    DbCommand command = dbconn.CreateCommand();
>
>    string[] sections = query.Split('@');
>
>    string[] parameternames = new string[sections.Length - 1];
>
>    for(int p = 0;p < parameternames.Length;p++)
>    {
>        int i;
>
>        for(i = 0;i < sections[p + 1].Length;i++)
>            if(!char.IsLetterOrDigit(sections[p + 1][i]))
>                break;
>
>        parameternames[p] = sections[p + 1].Substring(0,i);
>    }
>
>    switch(dbtype)
>    {
>        case DBTypes.SQL:
>            command.CommandText = query;
>            break;
>        case DBTypes.Oracle:
>            command.CommandText = query.Replace('@',':');
>            break;
>        case DBTypes.MySQL:
>            // Not implemented yet
>            break;
>    }
>
>    for(int i = 0;i < parameternames.Length;i++)
>    {
>        DbParameter parameter = command.CreateParameter();
>
>        parameter.ParameterName = parameternames[i];
>        parameter.DbType = parameters[i];
>
>        command.Parameters.Add(parameter);
>    }
>
>    return(command);
>}
>----------
>
>It can obviously be improved, but it's ok for my needs.
>
>Howewer, it would nice if the framework handled this issue; it should be its
>job, since ADO.NET 2.0 tries to provide an abstract data access layer.
>
>
>Massimo