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

本文关键字：DataRow 高性能 foreach 我的 代码 DataTable | 更新日期: 2023-09-27 17:50:10

我从Oracle数据库中获得了一个数据表(ora_dt(，现在我需要在ora_dt中添加一列(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，为了获得row["Descpiton"]值，我必须检查其geo和des_id，并从另一个SqlserverDB中选择它们。

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

实际上，我无法在Oracle中添加新的列描述。在我的代码中，当在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;
}

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

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

编辑：

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

希望这能有所帮助。

相关文章：