首页 文章

如何在我的代码中使用DataTable中的foreach DataRow获得高性能？

提问于
浏览 次
1

我从Oracle DB获得了一个数据表（ ora_dt ），现在我需要在 ora_dt 中添加一个列（ colu_sql ），但是我必须从某些Sqlserver数据库中获取 colu_sql 的值 .

这是我的代码：

public void ProcessDataTable(DataSet _ds)
{
    _ds.Tables[0].Columns.Add(new DataColumn("Descpition", typeof(string)));

    int countryId = -1;
    string des_ID = string.Empty;
    string geo = string.Empty;

    foreach (DataRow row in _ds.Tables[0].Rows)
    {
        if (row["des_ID"] != DBNull.Value)
            des_ID = row["des_ID"].ToString();

        if (!string.IsNullOrEmpty(des_ID))
        {
            if (countryId == 12 || countryId == 13)
                geo = "NA";
            else if ((countryId == 10 || countryId == 11))
                geo = "LA";
            else
                geo = "EMEA";
            row["Descpition"] = GetDes(geo, des_ID);
        }
        else { row["ExemptionDes"] = string.Empty; }
    }
}

对于每个DataRow，为了获得行["Descpition"]值，我必须检查它的 geo 和 des_id ，并从另一个SqlserverDB中选择它们 .

如果DataTable中的行计数非常大，那么当我使用DataTable时，我必须多次访问sqlserver db，这会使性能变差，

实际上我无法在Oracle.how中添加新的列描述在我的代码中使用DataTable中的foreach DataRow时可以获得高性能吗？

``

private string GetDes(string geo, string des_ID)
{
    string description = string.Empty;
    string query = "select description from geo_exemption  where des_ID= " + des_ID;
    Database DbSQL = DbSQLFactory.CreateDatabase(geo);
    using (DataReader dr = DbSQL.ExecuteReader(sqlCmd))
    {
        while (dr.Read())
        {
            if (dr["description"] != null)
                description = dr["description"].ToString();
        }
        dr.Close();
    }
    return description;
}
c# asp.net performance datatable

1 回答

  • 1

    我的建议是一次性在一个数据表中从 geo_exemption 获取 description 和 des_ID 的所有记录，然后使用LINQ根据des_ID过滤掉记录 . 这样，您只需要访问一次数据库 . 休息所有操作都将在asp.net端进行 .

    EDIT:

    public void ProcessDataTable(DataSet _ds)
{
    if (Session["GeoExpAllRec"] == null)
    {
        //Fetch all records here and add it to a datatable i.e. "select des_ID, description from geo_exemption"
        //Then Add the datatable to the session variable Session["GeoExpAllRec"]
    }

    _ds.Tables[0].Columns.Add(new DataColumn("Descpition", typeof(string)));

    int countryId = -1;
    string des_ID = string.Empty;
    string geo = string.Empty;

    foreach (DataRow row in _ds.Tables[0].Rows)
    {
        if (row["des_ID"] != DBNull.Value)
            des_ID = row["des_ID"].ToString();

        if (!string.IsNullOrEmpty(des_ID))
        {
            if (countryId == 12 || countryId == 13)
                geo = "NA";
            else if ((countryId == 10 || countryId == 11))
                geo = "LA";
            else
                geo = "EMEA";

            //Instead of calling "GetDes" function which will hit the database
            //Type-cast the session variable Session["GeoExpAllRec"] to datatable i.e. (Session["GeoExpAllRec"] as DataTable)
            //Fire a LINQ query on the datatable to get the desired Description like below

            //row["Descpition"] = GetDes(geo, des_ID);

            DataTable dt = (Session["GeoExpAllRec"] as DataTable);
            row["Descpition"] = dt.AsEnumerable().Where(r => r.Field<string>("des_ID") == des_ID).First()["description"];

        }
        else { row["ExemptionDes"] = string.Empty; }
    }
}

    希望这可以帮助 .

    回复于

相关问题