Reputation: 853
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.
Upvotes: 0
Views: 1603
Reputation: 312
Here's my take when I face similar case like this :
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.
Upvotes: 1