Allow passing unused parameters to parameterized queries

[roee88]

Description

Avoid failing when more-than-expected parameters are specified in a parameterized query. For example, if a user specifies 5 named parameters but the query only uses 3, don't fail because there are use cases where this is expected.

Example use cases that will be enabled by this change:

1. System provided parameters to ad-hoc user queries (which may or may not use all parameters)
2. Multi-statement commands with named (also positional?) parameters on different statements

   SELECT 1 WHERE $x = 1; SELECT 2 WHERE $y = 2;

Desired behavior

System.Data.SQLite behavior is as expected and accepts the additional y parameter

using var connection = new SQLiteConnection("Data Source=:memory:");
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = "SELECT 1 WHERE $x = 1;";
command.Parameters.Add(new SQLiteParameter("$x", DbType.Int32) { Value = 1 });
command.Parameters.Add(new SQLiteParameter("$y", DbType.Int32) { Value = 2 });
object result = command.ExecuteScalar();
Assert.AreEqual(1L, result);

Current behavior

using var connection = new DuckDBConnection("Data Source=:memory:");
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = "SELECT 1 WHERE $x = 1;";
command.Parameters.Add(new DuckDBParameter("x", 1));
command.Parameters.Add(new DuckDBParameter("y", 2));
command.ExecuteScalar();

A System.InvalidOperationException is thrown

System.InvalidOperationException: Invalid number of parameters. Expected 1, got 2

[Giorgi]

Does Microsoft.Data.Sqlite allow it?

[roee88]

Does Microsoft.Data.Sqlite allow it?

Yes

[Giorgi]

What about other database providers such as SqlClient and Npgsql?

[roee88]

I don't know, does it really matter? The main claim here isn't that others allow it so this library should too, but rather that there are valid use cases for it, as I mentioned.

Edit: Unreferenced_named_parameter_works unit test in Npgsql shows that it's allowed there too.

[Giorgi]

This is now live on NuGet