如何根据数据库表查询 C# 列表

Question

当我传递列表的字符串版本时，我的代码不会从测试数据库表返回任何行，但如果我直接传递列表成员，它会返回行。当我使用消息框显示字符串 joinedSerialsList 时，它的格式似乎正确。// 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

我重写了你的样本，这项工作：

    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;

    }