3

I am querying the mySQL database and storing the response in datatable. I need to check for the specific column to verify if its blank or null.Below is what I am doing

DataTable dataTab = new DataTable("ServiceOrderProtocol");

string strQuery = "select OrderID, ServiceOrder, ExternalID, Project_key, ProjectType from ServiceOrder;";

using (MySqlCommand cmd = new MySqlCommand(strQuery, conn))
{
using (MySqlDataAdapter sqlDa = new MySqlDataAdapter(cmd))
 {
 sqlDa.Fill(dataTab);
 return dataTab;
 }}

Now I am not sure how to check for the field ExternalID returned from the query stored in dataTab is null or empty.

xyz
  • 531
  • 1
  • 10
  • 31
  • A table normally stores multiple rows, do you want to know if any of them has a field `ExternalID` that is null or that all are null? – Tim Schmelter May 03 '17 at 13:10
  • Possible duplicate of [How to retrieve column values separately through sql data adapter class?](http://stackoverflow.com/questions/10805578/how-to-retrieve-column-values-separately-through-sql-data-adapter-class) – Mark C. May 03 '17 at 13:10
  • @TimSchmelter yes all the rows with ExternalId is null or blank – xyz May 03 '17 at 13:11
  • @MarkC. it is about the Dataset is that that the same way we deal with the DatatTable as well – xyz May 03 '17 at 13:12

1 Answers1

1

If you want to know if any of the rows in the table contains a null value in the field ExternalID:

bool anyNullExternalID = dataTab.AsEnumerable().Any(r => r.IsNull("ExternalID"));

If you want to know if all are null:

bool allNullExternalID = dataTab.AsEnumerable().All(r => r.IsNull("ExternalID"));

If you want all rows where it's null:

IEnumerable<DataRow> allNullExternalIDRows = dataTab.AsEnumerable()
    .Where(r => r.IsNull("ExternalID"));

If it's not an int(like the name suggests) but a string and you want to include null and an empty string, append || string.IsNullOrEmpty(r.Field<string>("ExternalID"))

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939