21 Apr 2016 05:03

Joined:	06/06/2011
Level:	Gold
Points:	1173

Points:	2

Forloop not helping for Validating 10000 rows from excel

Im doing some validation for excel rows... Incase of 10000 rows in excel, this validation inside a for loop or even foreach loop giving out timeout error. ( Code in C#)

Any other best method to come across this timeout issue?

#765609

21 Apr 2016 21:54

Joined:	29/05/2009
Level:	Gold
Points:	10354

Points:	1

Hi

Check your database Transaction Time out part.

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

#765615

21 Apr 2016 23:32

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

loop on each row in excel file will leads to timeout exception as there are lot of rows in a excel sheet
what you can do is read excel file using OLEDB and then loop on each row using datareader, instead of for. it will help you more
Here are some good links
https://social.msdn.microsoft.com/Forums/vstudio/en-US/09654227-99a8-47d0-9d6c-c2ab0d293aba/validate-excel-sheet-data-before-importing-to-database-in-c-windows-application?forum=csharpgeneral
http://www.codeproject.com/Tips/705470/Read-and-Write-Excel-Documents-Using-OLEDB

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#765621

22 Apr 2016 01:09

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	2

Hi lilly,

As suggested by prasad, Get your data into dataset and loop the records using dataset, this will give the best result.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#765622

22 Apr 2016 02:14

Joined:	06/06/2011
Level:	Gold
Points:	1173

Points:	1

HI Prasad, Thanks for the reply

I came across one good code like this which will set all 10000 value as expected in 1 sec..

Table.AsEnumerable().ToList().ForEach(p => p.SetField<string>("PERSON"," "));

But I need to include a small if condition since I dont want all row value to change.. only certain..

Expecting like Table.AsEnumerable().ToList().ForEach(p => if(p.PERSON ==null or 0) p.SetField<string>("PERSON"," "));

Any idea??

#765623

22 Apr 2016 03:21

Joined:	29/05/2009
Level:	Gold
Points:	10354

Points:	2

Hi

Without see your code we canot give proper solution . Can you share your code with excel sheet.

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

Forloop not helping for Validating 10000 rows from excel

Email subscription

About dotnetspider.com

Quick Links

General

Services