C# SqlCommand and non named parameters [duplicate]

Question

I have a huge program which has already tons of queries with parameters.

In the whole project I accessed to the database only through IDbConnection, IDbCommand, etc interfaces instead of using directly OleDbConnection, OleDbCommand, etc.

This allowed me to use different database engines and connection types with my application. Until now, for example, I used OleDbConnection to connect to MS SQL Server databases, OdbcConnection for MySql and SQLiteConnection from an external library for, obviously, SQLite.

Now I would like to try replacing the OleDbConnection with SqlConnection to connect to SQL Server but I found some problems with the parametrized queries.

I was used to do something like that with parametrized queries:

IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = "UPDATE foo SET field1=? WHERE ID=?";
DbUtils.AddParameter(cmd, DbType.StringFixedLength, "hello");
DbUtils.AddParameter(cmd, DbType.Int32, 1);
cmd.ExecuteNonQuery();

The AddParameter static method checks the type of the IDbCommand and adds the parameter value to it.

This works with the three connection types I specified above, but doesn't work with SqlConnection, it throws the error: Incorrect syntax near '?'.

SqlCommand seems to require named parameters to work:

IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = "UPDATE foo SET field1=@pfield1 WHERE ID=@pID";
DbUtils.AddParameter(cmd, DbType.StringFixedLength, "@pfield1", "hello");
DbUtils.AddParameter(cmd, DbType.Int32, "@pID", 1);
cmd.ExecuteNonQuery();

Do you know if it is possibile to avoid using named parameters to maintain compatibility with the other connection types?

Thanks!

Francesco

@krillgar: I just tried, but it gives me the error: Must declare the scalable variable '@' — Formentz
– Formentz, Commented Apr 17, 2015 at 13:52
@Formentz If you use it only as @ in your command , it is too normal to say that error because it is not a named parameter with just itself. — Soner Gönül
– Soner Gönül, Commented Apr 17, 2015 at 13:54
@xanatos sorry, you're right. The post you linked is also an answer for my question. Should I remove my question? — Formentz
– Formentz, Commented Apr 17, 2015 at 13:59

Soner Gönül · Accepted Answer · 2015-04-17 13:47:38Z

3

I don't think so.

From SqlCommand.Parameters property

The Microsoft .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called by a command of CommandType.Text. In this case, named parameters must be used.

answered Apr 17, 2015 at 13:47

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Formentz Over a year ago

thank you, I didn't find that comment in the docs... Seems I have to keep using OleDbProvider

Soner Gönül Over a year ago

@Formentz Glad to help. You should always use the right provider that you needs of course ;)

Collectives™ on Stack Overflow

C# SqlCommand and non named parameters [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related