Cell level validation in Datatable/Dataset

[empyrean]

Hi.. i have imported a excel in to datatable which contains 5 columns and some thousand rows. I have to do two level validation.

First i have to check whether the column belongs to specific datatype.

If so, then i need to check each and every cell in that column for the constraints.

How can i achieve this using c#?

Example:

Name   No   Num   Value
apple    1      3       10.9
ruby     10    3        4.5
john      4    11       2.5

i have to find the datatype first and here the second colum contains No which is of datatype 'int' and the constraints are 0 - 9. As in second row it has value 10 in it, i want to find that value and replace it with null value. Any cell out of the constraint property should be replaced with null values

Please help me ...

[anoncodemonkey]

I'm assuming you're able to get the data into a DataTable. This should push you in the right direction.

DataTable dtExcelImport = MethodThatGetsThatExcelImportTable();

//If you use regex, declare it up here so it doesn't get created a bunch of times
string sDataType = string.Empty;

foreach (DataColumn dc in dtExcelImport.Columns)
{
	//Based on column name, give it a data type for validation
	switch (dc.ColumnName)
	{
		case "Name":
			sDataType = "string";
			break;

		case "No":
			sDataType = "Int1to9";
			break;

		case "Num":
			sDataType = "Int";
			break;

		case "Value":
			sDataType = "Money";
			break;

		//case "More!":
		//    sDataType = "More!";
		//    break;

		default:
			sDataType = "unknown";
			break;
	}
	//If the dc.ColumnName aren't set, then perhaps you could use a column index:
	//for (int i = 0; i < dtExcelImport.Columns.Count; i++)
	//{
	//    switch (i)
	//    {
	//        case 1:
	//            break;
	//        case 2:
	//            break;
	//    } 
	//}
	

	//Go though each row and use the validation type selected above on that row
	foreach (DataRow dr in dtExcelImport.Rows)
	{
		switch (sDataType)
		{
			case "string":
				if (dr[dc.ColumnName].ToString().Length > 50)
				{
					//String too long? >.>
					//Do some error stuff to it
				}

				//Perhaps you want to clean up the data.  This is a healthy spot to do it.
				//I don't think an Excel import can have a null in it, which if it did will error here.
				dr[dc.ColumnName] = dr[dc.ColumnName].ToString().ToLower();
				break;

			case "Int1to9":
				//Make sure each item is 1 to 9, or null it
				if(((Int32)dr[dc.ColumnName]) > 9 || ((Int32)dr[dc.ColumnName]) < 1)
				{
					dr[dc.ColumnName] = null;//or is this System.DBNull... I'm not sure >.>
				}

				//Add any other validation
				break;


			case "uniqueInt":
				//Making sure each value is unique, quite a task.
				DataRow[] dra = dtExcelImport.Select(dc.ColumnName + " = '" + dr[dc.ColumnName] + "'");
				if(dra.Length > 1)
				{
					//This value isn't unique >.>
					//Do some error stuff to it
				}

				//Add any other validation
				break;

			case "Int":
				//Use regex to see if this is an int.  The regex should be declared before the first foreach
				//to save time.
				//http://www.c-sharpcorner.com/uploadfile/prasad_1/regexppsd12062005021717am/regexppsd.aspx ^.^
				//If you have too much horsepower and want to be ineffecient, you could
				//try catch and Convert.ToInt32(dr[dc.ColumnName]), but thats ugly >.>
				break;

			case "Money":
				//Put another regex to use here looking for a double, float, or perhaps USD or however your data is formated.
				//Once you understand the basics of C# regex creation, there are libraries of Regex that can look for what
				//you need.
				//http://regexlib.com/
				break;
		}
	}
}