如果表包含1000个记录，如何获取记录 [英] how to fetch a record if a table contains 1000's of records

问题描述

大家好，



我有一个带有C＃，sql server 2008的asp.net网站。



在一个sql表中，该表包含1000个记录。



我必须根据其RecordID获取记录。



所以，我在加载页面的过程中存储了表格。



使用C＃代码，我正在选择记录。

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();
                    }
                }

这是一个下拉列表，我从中获取了获取记录的价值。

n

它在一个中继器中。



所以，它花费大约1分钟来填充所有文本框，每个选择。



任何人都可以，请帮助我，我该如何解决这个问题。



请，





谢谢

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

推荐答案

不要检索所有记录：使用SQL WHERE子句将返回限制为您感兴趣的值：

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

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

And通过参数化查询提供@ddl_val参数。

And provide the @ddl_val parameter via a parameterised query.

我认为你可以通过从中获取特定记录来做到这一点。数据库就像你在会话中一样。检查以下代码，

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,

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();
}

在这种情况下，我会尝试尽可能靠近源过滤数据。 SQL经过优化，因此返回查询比使用C＃在内存中处理大量数据更有效。



如果这不可行并在代码中操纵它 - 背后是必须的，LINQ是不是更合适而不是遍历整个系列？



谢谢

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

这篇关于如果表包含1000个记录，如何获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！