Geek Answers Handbook

DataTable using LINQ

First, I apologize, if I do not explain it correctly, I was at this for hours, and now in the morning.

I tried so many methods, got so many errors that I can’t remember the original version, and I can’t solve the problem, here is my code, this is terrible, because I have to use the connection request that my SP is listening 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 to loop through a 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]

Any ideas? Completely dead brain, so it could be something simple.

thank

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 the implementation of the query template for the source type "System.Data.DataTable". "Where" not found.

, LINQ , , IEnumberable<T>.Where()? , , .

Edit2:

, ,

 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);
+5
2

dt.Rows.Add() DataRow, IEnumerable<DataRow> , DataTable, dt.NewRow() try, dt.ImportRow()

EDIT:

. , linq .

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;

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];
    }
}
+8

using System.Linq;

LINQ.

:

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

  • LINQ:

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

ADO.NET :

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

    // access ds;
}
0

More articles:


All Articles