Fetching Value from a Datatable into Datatable With Where Clause

Fetching value from a datatable into datatable with where clause

var res = db.dtTable.Where(x=> x.Value > 0).ToList();

OP: No its not Database Table its DataTable.

DataTable filteredTable = dtTable.AsEnumerable().Where(row => row.Field<int>("Value") > 0).CopyToDataTable();

OP: sorry the datatype is int64, its giving the following error 'Cannot cast DBNull.Value to type 'System.Int64'. Please use a nullable type.'

DataTable filteredTable = dtTable.AsEnumerable().Where(row => row.Field<Int64?>("Value") > 0).CopyToDataTable();

Get specific value from datatable with where-clause

query will be an IQueryable<DataRow>, so st will be a DataRow. Try this:

foreach (var st in query)
{
MessageBox.Show(st.Field<int>("id").ToString());
}

Or if you know there will only item with that item_name, here's an alternative version which does essentially the same thing, but is probably a bit easier to understand:

var st = producten.Rows.Cast<DataRow>().FirstOrDefault(x => x.Field<string>("item_name") == test);
if(item != null)
{
MessageBox.Show(st.Field<int>("id").ToString());
}

Select from datatable with where clause

You could use,

dt.where(e => {check something}).Select({select code here})

Do this on both the places. Hope this helps.

Get a cell value from datatable with two condition in where clause with LINQ

You are querying the wrong column. The code should be:

var query = from r in dt.AsEnumerable()
where r.Field<string>("Key") == "Trainee"
where r.Field<string>("Sub_Key_Name") == "BM"
select r;

If you only need the Sub_Key_Value you should try:

var query = from r in dt.AsEnumerable()
where r.Field<string>("Key") == "Trainee"
where r.Field<string>("Sub_Key_Name") == "BM"
select r.Field<string>("Sub_Key_Value");

Select statement on DataTable without where clause using Linq

fullTable
.AsEnumerable()
.Select(x => new
{
PartnerID = x.Field<int>("PartnerID"),
PartnerName = x.Field<string>("Partner Name")
})
.Distinct();

This will create an anonymous type with the two properties you want. You than apply a Distinct to remove the duplicates. Anonymous types handles GetHashCode and Equals for you which Distinct uses to identify duplicates.

Filtering a datatable row using a where clause

The Select method of a DataTable returns an array of DataRow even if your query selects only one row

DataRow[] dr = dtTable.Select("VendorID = " + Session["VendorId"].ToString());

Then, if you really expects just one row, you could easily grab the expected row checking for the length of the array. In this case it is my opinion that no fancy Enumerable extension methods are really needed

if(dr.Length > 0)
{
string avalue = dr[0]["AColumnName"].ToString();
...
}

union three select queries with where clause into one datatable

i believe i found a solution not sure if its the easiest to implement in vb.net code, ill have to call the temp table and usually has a long winded name auto generated. #TEMP_____________231651651 for example is one i got back for another application. but anywho this is what we came up with

Go
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP Table #Data
SELECT A.ItemId ,A.RefId ,B.EquipmentName ,C.PointId ,C.PointDescription,
D.InspectionTimeStamp ,D.AlarmCode,D.AlarmDescription ,A.RouteId ,A.Type ,A.SequenceNo
,A.Skip ,A.Item INTO #Data FROM [Trident].[Maintenance].
[BasicCareEquipmentPointDetails] A INNER JOIN [Trident].[Maintenance].[Equipment] B ON
A.ItemId = B.EquipmentId INNER JOIN [Trident].[Maintenance].[BasicCarePointDetails] C
ON A.RouteId = C.RouteId LEFT OUTER JOIN [Trident].[Maintenance].
[BasicCareInspectionHistory] D ON C.PointId = D.PointId AND D.InspectionTimeStamp
BETWEEN '03/06/2020' AND DATEADD(DAY, 1, '03/06/2020') WHERE A.RouteId In
('RG00000792', 'RG00000800', 'RG00000801') AND A.Skip = 0 AND A.ItemId =
C.TemplateId ORDER BY A.RefId, A.SequenceNo Desc

SELECT *
FROM (SELECT ItemId
,RefId
,EquipmentName
,PointId
,PointDescription
,InspectionTimeStamp
,AlarmCode
,AlarmDescription
,RouteId
,type
,SequenceNo
,Skip
,Item FROM #Data) AS s
PIVOT (
MAX(AlarmCode)
FOR RouteId IN ([RG00000792], [RG00000800], [RG00000801])
) p
ORDER BY RefId, SequenceNo DESC

Using the pivot feature. not sure if aggregating the AlarmCode column is the cleanest though, even though its not doing a max on a string value



Related Topics



Leave a reply



Submit