How to get data from two SQL tables into .net

Previously, I asked a question about getting data from two tables where I take one row in a table and join it with several rows in another table. Here's the link to that discussion: SQL Select data from two tables (one row -> multiple rows)

Here is my SQL code:

SELECT 
    customer.fName, customer.lName, phone.phoneNumber 
FROM 
    Customers customer 
       开发者_开发百科 INNER JOIN phoneNumbers phone ON 
            customer.customerId = phone.customerId

What I would like to know now is: what is the best way to get this data organized in .net?

Let's suppose I have a C# class as following:

public class CustomerDetails
{
    int customerId;
    string fname;
    string lName;
    List<string> phoneNumbers;
}

For the sake of discussion, let's suppose that the above SQL query returns the following result:

fname, lname, phoneNumber

"John", "Smith", "111-111"

"Jane", "Doe", "222-1111"

"Jane", "Doe", "222-2222"

At a glance, I see that I have two customers; one has one phone number and the other has two phone numbers. How can I write code to efficiently parse this in C#?

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.