how to fetch a record if a table contains 1000's of records

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi guys,

I have asp.net website with C#, sql server 2008.

In one of sql table, the table contains 1000's of records.

I have to fetch a record based on its RecordID.

so, i have stored table in session while loading the page.

using C# code, i'm selecting the record.

DataTable dt = new DataTable();
dt = (DataTable)Session["dt"];
string ddl_val = ddl.selectedValue.toString();

foreach (DataRow dr in dt.Rows)
                {
                    if (dr["RecID"].ToString() == ddl_val)
                    {
                        txt.Text = dr["OldRef"].ToString();
                        txtd.Text = dr["Description"].ToString();
                        txtu.Text = dr["UnitMeasurement"].ToString();
                        txtup.Text = dr["UnitPrice"].ToString();
                    }
                }

its a dropdown list from where i'm getting value to fetch a record.
n
its in a repeater.

so, its taking some where around 1min to fill all the textboxes, on every selection.

Can anyone please, help me, how can i sort out this.

Please,

Thanks

Posted 15-Oct-14 21:46pm

abdul subhan mohammed

Updated 15-Oct-14 22:19pm

Add a Solution

Comments

Mehdi Gholam 16-Oct-14 3:51am

Obviously you need to rethink how you are doing things.

george4986 16-Oct-14 4:11am

pass the ddl_val to backend and fetch only matched record instead of loading all records in table

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-10-15T22:07:00

Solution 1

Don't retrieve all records: use an SQL WHERE clause to restrict the return to just the values you are interested in:

SQL

SELECT OldRef, Description, UnitMeasurement, UnitPrice FROM MyTable WHERE RexId=@ddl_val

And provide the @ddl_val parameter via a parameterised query.

Posted 15-Oct-14 22:07pm

OriginalGriff

Comments

Mehdi Gholam 16-Oct-14 4:15am

I believe the problem is not the query but how he is displaying the data (in text boxes in a repeater)

abdul subhan mohammed 16-Oct-14 4:16am

Dude, its a dropdownlist n its located in a repeater, so, if i call it from d server, then on each n every dropdownlist selection it will hit the server again n again, bcoz of this i have stored this table in the session n taking from session.
Secondly, this contains 1000's of record on which its taking time to fetch the record.
Is there any other sol'n. Please.

NaibedyaKar · Answer 2 · 2014-10-15T22:39:00

I think you can do this by fetching the specific record from the database as you have it with you from the session. Check the below code,

C#

var recordData = dt.Select("RecID = " + ddl_val);

if (recordData.Count() > 0)
{
     txt.Text = recordData[0]["OldRef"].ToString();
     txtd.Text = recordData[0]["Description"].ToString();
     txtu.Text = recordData[0]["UnitMeasurement"].ToString();
     txtup.Text = recordData[0]["UnitPrice"].ToString();
}

Laurence1234 · Answer 3 · 2014-10-15T23:14:00

In this situation I'd try to filter the data as close to the source as possible. SQL is optimised so that it's more performant returning queries than manipulating large amounts of data in memory using C#.

If this isn't possible and manipulating this in the code-behind is a must, wouldn't LINQ be more appropriate rather then iterating through the whole collection?

Thanks

how to fetch a record if a table contains 1000's of records

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0