.NET Data Providers and parameter markers

ADO.NET does not specify a standard way to set parameter markers in a SQL statement. For example, if you use the SQL Server Data Provider, you have to write:

Select CustomerName from Customers Where CustomerId = @CustomerId

But in Microsoft's Oracle Data Provider, you write:

Select CustomerName from Customers Where CustomerId = :CustomerId

If you decide to pay the performance price and write ANSI SQL to be able to access both databases, you will have to abstract the way the parameters are used, and write something like:

statement = "Select CustomerName from Customers Where CustomerId = " + AddParameter("CustomerId");

Where AddParameter adds a ":' or a "@" to the parameter name.

If you are writing code for one database and you don't care about the other databases, you are affected by this issue anyway. If you want to use, for example, Data Direct's .NET Provider for Oracle, you have to write:

Select CustomerName from Customers Where CustomerId = ?

So, if you don't abstract the way to add parameter markers, you are really coding against a specific Data Provider...

Do I like this behavior? Of course!! DeKlarit generates the Data Access Layer, so you just select the Data Provider and we generate the right sentences. The more difficult the things are for the programmer, the best for us ;).