Find matching contacts from contact list in an SQL table

Question

I'm currently writing a server application for a chat app which receives the phone contact list from an users adressbook as a List<string> where the string is a standardized phone number. I now want to compare it to my SQL Database where all registered users are saved.

Right now I iterate trough every item on the list and query SELECT * FROM Users WHERE Number = @0 and open on every query a new connection with SqlConnection(connectionString). I do so because I know that .NET will handle/pool the open connections.

var phoneNumbers = new List<string> { "0049123456789", "001123456789" };
var registeredUsers = new List<string>();
foreach (var phoneNumber in phoneNumbers)
{
    try
    {
        var userName = GetFrom(phoneNumber);
        registeredUsers.Add(userName);
    }catch{}
}

//...

public static string GetFrom(string telephoneNumber)
{
    string retVal = null;
    using (var connection = new SqlConnection(connectionString))
    {
        using (var cmd = SetSQLCommand(connection, "SELECT * FROM Users WHERE Number = @0", telephoneNumber))
        {
            connection.Open();
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())//Get last
                    {
                        retVal = (string)reader["Name"];
                    }
                }
                else
                {
                    throw (new KeyNotFoundException());
                }
            }
        }
    }
    return retVal;
}

This method feels quite stupid as an user contact list could contain 1000 or more contacts.

Is there a more elegant way to find all matching telephone numbers from a List of telephone numbers I have as an C# object in an SQL table?

As performance is quite important to me I use the .NET 4.5 implementation of SQL System.Data.SqlClient, the Entity Framework is too slow for my purposes.

Answer 1

Here's my take when I face similar case like this :

• I will concatenate all the phone numbers into one single formatted string
• I will pass that string in a dynamic SQL query by utilizing the "IN" T-SQL

It could be as simple as this :

    public List<string> ReturnContactNames(string telephoneNumbers)
    {
        var retVal = new List<string>();
        using (var connection = new SqlConnection(connectionString))
        {
            using (var cmd = new SqlCommand("SELECT * FROM Customers WHERE Phone IN (" + telephoneNumbers + ")", connection))
            {
                connection.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        retVal.Add((string)reader["ContactName"]);
                    }
                }
            }
        }
        return retVal;
    }

And then this is how you may use this method :

        var phoneNumbers = "'030-0074321','0621-08460'";
        var list = ReturnContactNames(phoneNumbers);

Warning : use parameterized query whenever possible. This is just a quick sample only to give you an idea.

Hope it helps.