Search code examples
c#sql-serverado.netparametersoledb

C# - SQL Server - ? parameters and Oledb

I am writing a small framework in C# for importing data to SQL Server.

I have noticed that if you try to use the ? placeholder for parameters, they do not work if you use the System.Data.SqlClient namespace.

However, if you use the System.Data.OleDb namespace for working with the database, they work just fine. (you just have to add Provider=SQLOLEDB in your connect string)

So I have a few questions, because the ? syntax is required by my solution:

  1. Is there an alternative to using named parameters with System.Data.SqlClient? I want to use ordered parameters.
  2. Is there any performance impact to using one namespace (ado provider) over the other?
  3. Is there any other reason I should prefer one namespace over the other?

To elaborate on what I'm trying to do, my framework is going to take a class decorated with some custom attributes and generate SQL like so:

INSERT INTO myTable (col1, col2, ...) VALUES (?, ?, ...)

This string will be created with one IDbCommand and several IDataParameters and for each ExecuteNonQuery it will merely set the values on the IDataParameters.

Any thoughts?

Edit: I tried using named parameters as p0, p1, etc. as an answerer mentioned and it is working pretty well on SqlClient. However if I use Oledb, it errors saying:

"Must declare the scalar variable @p0."

My parameter is created like so:

new OleDbParameter("p" + index, GetType(attribute)); 
//GetType does work to get the correct SqlDbType

What is wrong? I also tried adding and removing the @ symbol in the parameter name to no avail.

FINAL EDIT: I just made Oledb use ? and SqlClient use named parameters. I can change back and forth for new providers.

Solution

  • Thats the behaviour of those classes. OleDb uses ?, SqlClient named parameter.

    Perfomance: I have no benchmarks, but I think the SqlClient should perform better with an Sql Server than OleDB. SqlClient is explicit for Sql Server, OleDB for any kind of Datasource (even Excel). But pls. try it out.

    With SqlClient you can also have "ordered parameter". Just call you parameter @Param1, @Param2, @Param3....