ADO.NET: Database-independent way to generate parameter placeholders

[roji]

In theory, parameter placeholders are an entirely database-specific details - there is no way to write a single SQL containing parameters, and then execute it on different databases.

In practice, it seems that most ADO.NET have aligned on the @name convention. This is quite a problem, since for databases which don't support it natively, the ADO.NET provider must parse and replace the @name placeholder with the one accepted by the database. For example, PostgreSQL uses positional placeholders ($1, $2...), which by the way are also specific to individual statements in a batch (parameters are per-statement, not per-batch).

We could design an API where the provider would generate the placeholder. This could be as simple as adding a Placeholder property on DbParameter, which each provider would implement as necessary. The user would then interpolate the placeholder into the CommandText. For positional placeholders, the logic would typically calculate the placeholder as the DbParameter is inserted into a DbParameterCollection, whereas for named placeholders the ParameterName would be returned.

PS Parameter placeholder translation is one of two things forcing Npgsql to parse the CommandText - the other is the lack of a proper batching API (#3688), which requires to parse and split on semicolons.

[roji]

/cc @divega @ajcvickers @saurabh500

[divega]

System.Data Triage: this seems to be a good idea. Once we prototype the API we can try using it in EF Core and other components that generate SQL.

Moving to future.

[roji]

Note: this should take into account that some providers don't use placeholders, but rather render values literally into the command text (e.g. MySqlConnector in text mode, @bgrainger).

[roji]

Continuing the above, there are three kinds of placeholders (not sure the naming is good):

• Named placeholders: the user provides an arbitrary string name in the placeholder, and the same name is matched with DbParameter.ParameterName in the command's parameter list. Example: @city in SQL Server.
• Explicit positional placeholders: the user provides a placeholder which includes the parameter's position in the parameter list. Since the placeholder contains an explicit position, it can be used more than once in the SQL, referring to the same parameter. Example: $2 in PostgreSQL.
• Implicit positional placeholders: the user provides a placeholder which doesn't include either a name or a position - the placeholder's position in the SQL implicitly indicates which parameter it corresponds to in the list. Referencing the same parameter twice from the SQL is impossible, and the parameter must be duplicated. Example: ? in ODBC.

Our API would need to somehow let consumers know whether placeholders are implicit or not, otherwise they can't know whether they can reuse the same placeholder or not.

Note the DataSourceInformation schema collection which has ParameterMarkerFormat, which provides some of the information here (i.e. we can know whether the placeholders are explicit or implicit). This could be useful for a backwards-compatibility shim (assuming it is well-implemented).

Finally, even if placeholder generation is taken over by the provider, the user should still have the option of providing an arbitrary string name. If the database supports named placeholders, that name could be used (better for logging etc). If it doesn't, the name would be ignored.

/cc @divega @ajcvickers @bricelam

[ghost]

Due to lack of recent activity, this issue has been marked as a candidate for backlog cleanup. It will be closed if no further activity occurs within 14 more days. Any new comment (by anyone, not necessarily the author) will undo this process.

This process is part of our issue cleanup automation.

[roji]

Keep this open for possible future work.