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;

You could use dapper if you really want this behavior: https://dapper-tutorial.net/knowledge-base/31997381/dapper--in--clause-not-working-with-multiple-values — Ramūnas, Apr 09 '19 at 19:19
You'll end up with a quoted list where SerialNumber IN ('123, abc, foo'). There are a couple work-arounds. See https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand or https://stackoverflow.com/questions/83471/string-list-in-sqlcommand-through-parameters-in-c-sharp — Peter, Apr 09 '19 at 19:19

score 0 · Accepted Answer · answered Apr 09 '19 at 20:37

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;
    }

Wow, works! I kept everything the same as I originally had it, just changed the statement. A question, if you don't mind: What does the "$" character do before the Select string statement? — T. Hill, Apr 10 '19 at 18:58
$"" is new handy formatting. Allow use {} in string. For example `var str = $"Result is: {result}";` — bmi, Apr 16 '19 at 20:12

How to query a C# list against a database table

1 Answers1