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.

This seems a good task for [Table Valued Parameters](https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code) and Stored Procedure — Steve, Feb 19 '18 at 21:44
You probably want to use a table valued parameter for the input. There are a number of posts that explain how to do so. This one is old but still relevant: https://stackoverflow.com/questions/1525126/sql-in-clause-in-stored-procedurehow-to-pass-values — Jacob H, Feb 19 '18 at 21:45
Thanks you two, but I only find examples for adding data tables, not for selecting tables. As you can see I'm pretty new to SQL, could you may give me a hint? Tia — Max R., Feb 19 '18 at 22:29

Erlangga Hasto Handoko · Accepted Answer · 2018-02-20T04:44:12.057

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.

Find matching contacts from contact list in an SQL table

1 Answers1