How to get data from two SQL tables into .net

One option is to use LINQ to create a instance of the CustomerDetails class.

Let me know if you would like an example.

Example 1:

List<CustomerDetails> customers = db.Customers.Select(c => new CustomerDetails(){
                                                                  customerId = c.customerID,
                                                                  fname = c.fName,
                                                                  lName = c.lName,
                                                                  phoneNumbers = (from p in db.PhoneNumbers where p.customerID == c.customerID select p.phoneNumber1).ToList<String>()});

Example 2:

List<CustomerDetails> custs = (from c in db.Customers
                         select new CustomerDetails()
                         {
                             customerId = c.customerID,
                             fname = c.fName,
                             lName = c.lName,
                             phoneNumbers = (from p in db.PhoneNumbers where p.customerID == c.customerID select p.phoneNumber1).ToList<String>()
                         }).ToList<CustomerDetails>();

I understand that you are looking for ORM object-relational mapping. In .NET I can recommend IBatis.net or LINQ.

Solution 1:
I assume that the records are sorted by name in the SQL query's results and that you also select the customerID. Appending "ORDER BY customer.fName, customer.lName" to your original query will do the trick.

I'll assume that you get your result in a DataReader, so you can do the following:

// Lets start by declaring a collection for our records
List<CustomerDetails> myRecords = new List<CustomerDetails>();

// Iterate all records from the results and fill our collection
while (yourReader.Read())
{
    int customerID = int.Parse(yourReader["customerID"]);
    int nrRecords = myRecords.Count;

    if (nrRecords > 0 && myRecords[nrRecords - 1].customerId == customerID)
    {
        myRecords[nrRecords - 1].phoneNumbers.Add(yourReader["phoneNumber"]);
    }
    else
    {
        CustomerDetails newCustomerDetails = new CustomerDetails();
        newCustomerDetails.customerId = customerID;
        newCustomerDetails.fName = yourReader["fName"];
        newCustomerDetails.lName = yourReader["lName"];

        List<string> phoneNumberList = new List<string>();
        phoneNumberList.Add(yourReader["phoneNumber"]);
        newCustomerDetails.phoneNumbers = phoneNumberList;

        myRecords.Add(newCustomerDetails);
    }
}

P.S. If ordering the list is not an option, then you can't just check the latest added records customerid - instead you'll need to iterate through the myRecords list and search for the existance of it. This can be done in many ways including with myRecords.Contains() or with a foreach.

Solution 2:
Do the telephone number grouping directly from SQL. Create a function for selecting a comma separated string with all the telephone numbers of a particular customer:

CREATE FUNCTION [dbo].[GetCommaSeparatedPhoneNumbers]
(
    @customerID int
)
RETURNS varchar(max)
AS
BEGIN
    declare @output varchar(max)
    select @output = COALESCE(@output + ', ', '') + phoneNumbers
    from phoneNumbers 
    where customerId = @customerID

    return @output
END
GO

Then you can nicely select the list of all customer you want:

SELECT customerId, dbo.GetCommaSeparatedPhoneNumbers(customerId)
FROM Customers 
GROUP BY customerId

This will return:

"John", "Smith", "111-111"
"Jane", "Doe", "222-1111,222-2222"

Now it's all a question of parsing the results with a foreach or while loop, but no checking for existance is needed. Just split the string at ',' and insert the values into the List. If there is a chance, that there will be no phone numbers for some customers, then you can filter that field for null.

PS. Will not work if there is a comma as pointed out by BQ in his comment.

Iterate over the result, for each row check if it is in your list of costumers, if not, add a new one, if yes, add the phone number to the existing one.

Of course, you shouldn't parse the string use appropiate classes to access the database, like ado.net.