How to query a C# list against a database table

Question

My code isn't returning any rows from a test database table when I pass a string version of a list, but it does return rows if I pass the list members in directly.

When I use a message box to show the string joinedSerialsList, it appears to be formatted properly.

// Create comma delimited list of serials:
int currentSerial = beginning;
List<string> serialsList = new List<string>();

for (int i = 0; i < count; i++)
{
     serialsList.Add(currentSerial.ToString());
     currentSerial++;
}

string joinedSerialsList = string.Format("({0})", string.Join(", ", serialsList));

OleDbConnection connection = BadgeDatabaseDB.GetConnection();
string checkStatement
     = "SELECT SerialNumber, OrderNumber "
     + "FROM SerialNumbersMFG "
     + "WHERE SerialNumber IN (@List)";

OleDbCommand command = new OleDbCommand(checkStatement, connection);
command.Parameters.AddWithValue("@List", joinedSerialsList);

string duplicateSerials = "";

try
{
    connection.Open();
    OleDbDataReader dataReader = command.ExecuteReader();

    if (dataReader.Read())
    {
        duplicateSerials += dataReader["OrderNumber"].ToString() + "\n";
    }
}
catch (OleDbException ex)
    {
        throw ex;
    }
finally
    {
        connection.Close();
    }

return duplicateSerials;

Answer 1

I rewrited your sample, this work:

    private IEnumerable<string> getData()
    {
        // Create comma delimited list of serials:
        int currentSerial = 4452; // your constant
        var serialsList = new List<int>();
        var count = 100;

        for (int i = 0; i < count; i++)
            serialsList.Add(currentSerial++);

        var connString = getConnectionString();

        var results = new List<string>();
        string sqlSelect = $"SELECT SerialNumber, OrderNumber FROM SerialNumbersMFG WHERE SerialNumber IN ({string.Join(",", serialsList)})";

        using (var connection = new SqlConnection(connString)) // BadgeDatabaseDB.GetConnection();
        {
            using (var command = new SqlCommand(sqlSelect, connection))
            {
                connection.Open();
                var dataReader = command.ExecuteReader();

                while (dataReader.Read())
                    results.Add(dataReader["OrderNumber"].ToString());
            }
        }

        return results;
    }