Querying a DataTable using LINQ

First apologies if I don't explain this properly, I've been at this for hours, and it's now morning.

I've tried so many methods, got so many errors that I can't remember the original version, nor can I solve the problem, here's my code, it's terrible as I should be using a join query my SP's are bugged on this server.

SqlConnection conn = new SqlConnection(connstring);
DataSet ds = new DataSet();
SqlDataAdapter ad;
SqlCommand cmd = new SqlCommand();
ad = new SqlDataAdapter("SELECT * FROM booking WHERE bookstartdate BETWEEN '" + ReturnDbDate(datefrom).ToString() + "' AND '" + ReturnDbDate(dateto).ToString() + "'", conn);
ad.Fill(ds, "CustomerIds");

ad = new SqlDataAdapter("SELECT customerid, firstname, lastname, telephone, email FROM customer", conn);
ad.Fill(ds, "Customers");

DataTable dt = new DataTable();
dt.Columns.Add("Customerid", typeof(String));
dt.Columns.Add("Firstname", typeof(String));
dt.Columns.Add("Lastname", typeof(String));
dt.Columns.Add("Telephone", typeof(String));
dt.Columns.Add("Email", typeof(String));

int lol = ds.Tables["CustomerIds"].Rows.Count;

foreach (DataRow row in ds.Tables["CustomerIds"].Rows)
{
    IEnumerable<DataRow> r = from dr in ds.Tables["Customers"].AsEnumerable()
                             where dr.Field<Guid>("customerid").ToString() == row[2].ToString()
                             select dr;
    dt.Rows.Add(r);
}

return dt;

When I try loop through the dataset using the following:

foreach (DataRow rows in dt.Rows)
{
    sb.Append("<tr><td>" + rows["Customerid"].ToString() + "</td><td>" + rows[1] + "</td><td>" + rows[2] +"</td><td>" + rows[3] + "</td></tr>");
}

I get:

System.Data.EnumerableRowCollection`1[System.Data.DataRow]

Anyone any ideas? Completely brain dead atm so it might be something simple.

Thanks

Edit:

DataRow r = from dr in ds.Tables["Customers"]
            where dr.Field<Guid>("customerid").ToString() == row[2].ToString()
            select dr;    
dt.ImportRow(r);

Error: Could not find an implementation of the query pattern for source type 'System.Data.DataTable'. 'Where' not found.

I'm assuming my LINQ syntax is not incorrect, although I think there's a IEnumberable<T>.Where() method? I 开发者_如何学运维remember it, just can't remember how to access it.

Edit2:

I fail, managed to re-create the problem again, sigh

 IEnumerable<DataRow> r = from dr in ds.Tables["Customers"].Select().Where(x => x.Field<Guid>("customerid").ToString() == row[2].ToString())
                            select dr;



                dt.ImportRow(r);

dt.Rows.Add() takes in a DataRow but your providing IEnumerable<DataRow> Also note that rows can only be added to a DataTable that has been created with dt.NewRow() try using dt.ImportRow() instead

EDIT:

Skip the temp DataTable and join the two datatables in the dataset instead. Or even better, skip using linq and join the tables in the database query instead.

return 
  from dr in ds.Tables["Customers"].AsEnumerable()
  join dr2 in ds.Tables["CustomerIds"].AsEnumerable()
    on dr.Field<Guid>("customerid") equals dr2.Field<Guid>(2)
  select dr;

Plain SQL

public DataTable GetCustomers(DataTime datefrom, DataTime dateto)
{
    var sql = @"
        SELECT customer.customerid, firstname, lastname, telephone, email
        FROM customer
        JOIN booking
            ON customer.customerid = booking.customerid
        WHERE bookstartdate BETWEEN '" + ReturnDbDate(datefrom).ToString() + "' AND '" + ReturnDbDate(dateto).ToString() + "'";

    using (SqlConnection conn = new SqlConnection(connstring))
    using (SqlDataAdapter ad = new SqlDataAdapter(sql, conn))
    {
            DataSet ds = new DataSet();
            ad.Fill(ds);
            return ds.tables[0];
    }
}

Don't forget

using System.Linq;

otherwise you can't use neither LINQ extension method.

Try out this:

• Add reference to System.Data.DataSetExtensions.dll
• IEnumerable<DataRow> r = ds.Tables["Customers"].AsEnumerable();

• Use any LINQ extension method against it:

  from r in ds.Tables["Customers"].AsEnumerable()
  where r.Field<Guid>("customerid") == row[2]
  select r;
  

Your ADO.NET code rather better could look like this:

using (DataSet ds = new DataSet())
{
    using (SqlConnection conn = new SqlConnection(connstring))
    using (SqlDataAdapter ad = new SqlDataAdapter("", conn))
    {
        ad.Fill(ds);
    }

    // access ds;
}