I am running this query for every row in excel, but C# was not able to handle all rows(20K)
C# isn't your problem here. You are running the same query 20,000 times? That's 20,000 separate connections to your database, one at a time. You need to revisit what you are doing here. Maybe pull the entire table into a list in memory and use LINQ to query
that table.
Mark all posts that give the desired result the answer. If you only mark the last that gave you clarification because you misread an earlier post others will be confused. Some of us are here to help others and our point to post ratio matters.
You are still running the query 20,000 times. That's 20,000 separate hits to your database.
Mark all posts that give the desired result the answer. If you only mark the last that gave you clarification because you misread an earlier post others will be confused. Some of us are here to help others and our point to post ratio matters.
What if table contains 20lakh records, can i push these many records into a list?
Lists don't have a limit. And only pull the fields you need to help keep the memory footprint small.
Mark all posts that give the desired result the answer. If you only mark the last that gave you clarification because you misread an earlier post others will be confused. Some of us are here to help others and our point to post ratio matters.
Member
186 Points
357 Posts
Foreach loop limitation when validating rows from excel
Jun 27, 2017 12:51 PM|maheshvishnu|LINK
I have 20,000 rows in excel, i want to validate each row data against DB. My db query is
Select count(*) from Table where col1='rowCol1' and col2="rowCol2" and col3="rowcol3'
I am running this query for every row in excel, but C# was not able to handle all rows(20K)
What will be best way to do it? I know moving this check into DB will be a good idea, but was there any other option?
Contributor
7058 Points
2189 Posts
Re: Foreach loop limitation when validating rows from excel
Jun 27, 2017 01:00 PM|ryanbesko|LINK
Member
186 Points
357 Posts
Re: Foreach loop limitation when validating rows from excel
Jun 27, 2017 01:03 PM|maheshvishnu|LINK
I have placed foreach loop after opening connection to DB. With that i don't that there will be problems in connecting to DB, am i right here?
Contributor
7058 Points
2189 Posts
Re: Foreach loop limitation when validating rows from excel
Jun 27, 2017 01:08 PM|ryanbesko|LINK
You are still running the query 20,000 times. That's 20,000 separate hits to your database.
Member
186 Points
357 Posts
Re: Foreach loop limitation when validating rows from excel
Jun 27, 2017 01:15 PM|maheshvishnu|LINK
What if table contains 20lakh records, can i push these many records into a list?
Contributor
7058 Points
2189 Posts
Re: Foreach loop limitation when validating rows from excel
Jun 27, 2017 01:25 PM|ryanbesko|LINK