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.
maheshvishnu
Member
175 Points
341 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?
ryanbesko
Contributor
6167 Points
1807 Posts
Re: Foreach loop limitation when validating rows from excel
Jun 27, 2017 01:00 PM|ryanbesko|LINK
maheshvishnu
Member
175 Points
341 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?
ryanbesko
Contributor
6167 Points
1807 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.
maheshvishnu
Member
175 Points
341 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?
ryanbesko
Contributor
6167 Points
1807 Posts
Re: Foreach loop limitation when validating rows from excel
Jun 27, 2017 01:25 PM|ryanbesko|LINK