CODEWITHSUNDEEP
linqdatatable
sarsnake
sarsnake

Reputation: 27733

Performing LINQ queries on a DataTable object

Say i have a DataTable object with data from the database, and I need to reorganize it. I think (according to my research) LINQ would be an elegant (but is it fast?) way to do so. The question is how.

The data comes from the database in the following format

ORDER  SAMPLE  METHOD    

1234   1      ASH-10        
1234   1      ASH-11   
1234   1      ASH-12   
1234   2      ASH-10    
1234   2      ASh-12
1234   5      ASH-10
1235   1      BSH-10
1235   1      BSH-11
1235   1      BSH-12

I need it to be reshuffled as

ORDER  SAMPLE ASH-10 ASH-11 ASH-12 BSH-10 BSH-11 BSH-12 
1234    1      Y       Y      Y     N        N    N
1234    2      Y       N      Y     N        N    N
1234    5      Y       N      N     N        N    N
1235    1      N       N      N     N        Y    Y

As you can see the method columns will be dynamic. I could, of course, loop through the data using the old fashioned loop, but it seems like LINQ would be a better and faster way to go about it. Correct me if I am wrong.

Upvotes: 0

Views: 293

Answers (3)

user372724
user372724

Reputation:

It is better to use Dynamic Pivoting in such cases(SQL SERVER 2005).

Oracle 11g supports Pivoting. For a lower version you can have a look at this or this or even a google search will produce a lot of example.

Upvotes: 0

sarsnake
sarsnake

Reputation: 27733

I ended up using a loop to iterate and sort the data. The approach of populating objects was a little bit redundant here - it is a web service, so all I needed was to sort the data and return it. So I kept it simple.

Upvotes: 1

Greg Sansom
Greg Sansom

Reputation: 20870

The DataTable is great for retrieving data from the database into your application, but once you have the data you are usually better off converting it into custom objects.

Your preliminary design might look like this:

 class AllOrders
 {
    public static List<Order> Orders = new List<Order>();
    public static List<Method> Methods = new List<Method>();

    public static void ProcessTable(DataTable dt)
    {
        foreach (DataRow dr in dt.Rows)
        {
            int sampleId = Convert.ToInt32(dr["SAMPLE"]);
            string methodName = Convert.ToString(dr["Method"]);
            AddOrder(sampleId, methodName);
        }
    }

    private static void AddOrder(int SampleId, string methodName)
    {
        Method m = Methods.FirstOrDefault(x => x.Name == methodName);
        if (m == null)
        {
            m = new Method(methodName);
            Methods.Add(m);
        }
        Order o = new Order(SampleId, m);
        Orders.Add(o);
    }
}

class Order
{
    public Order(int sampleId, Method m)
    {
        this.Method = m;
        this.SampleId = sampleId;
    }
    public int SampleId; //a more mature design might have a Sample class instead.
    public Method Method;

}

class Method
{
    public string Name;
    public Method(string name)
    {
        this.Name = name;
    }
}

Upvotes: 2

Related Questions