More Information

Unlike JDBC, ADO.Net allows a user to use parameters in any SQL statement including ones that are not prepared. Parameters are very useful as they allow users to compose complicated SQL statements one time and generate many executions of that statement with different column values.

To include parameters in a SQL statement, the user has to prefix the parameter name with a character that allows the provider to “find” the parameter later and replace it with the parameter value. In early versions of the Connector/Net (back when it was called ByteFX), this marker was the ‘@’ character. Many people think that because the SQL Server provider uses ‘@’ it is a standard. It’s not and one of the reasons it is not is because this syntax would need to be different on different databases. Indeed, the ODBC and OleDB providers use ‘?’ for their parameter marker.

And that is exactly why we had to change our marker. MySQL uses ‘@’ to mark user variables. So the following code will create and use a MySQL user variable:

	SET @myvar = 5;
	SELECT @myvar

Here is the problem. If a user is trying to use parameters and fails to provide a parameter, the connector should throw an exception. Given the following SQL, this would be impossible without changing the parameter marker:

	SET @myvar = 5;
	SELECT @myvar, id FROM MyTable where id >= @maxId

Given this SQL, it is impossible for the connector to determine which are parameters and which are user variables. To fix this, we change to using ‘?’ for parameters so the above SQL would be rewritten like this:

	SET @myvar = 5;
	SELECT @myvar, id FROM MyTable WHERE id >= ?maxId