CODEWITHSUNDEEP
c#asp.netperformancedatatable
AdvancingEnemy
AdvancingEnemy

Reputation: 422

how can get high performance when foreach DataRow in a DataTable in my code?

I get a datatable (ora_dt) from an Oracle DB, now i need to add an column(colu_sql) in ora_dt,however i have to get colu_sql's value from some Sqlserver DBs.

Here is my code:


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

For every DataRow, inorder to get row["Descpition"] value, i have to check its geo and des_id, and select them from another SqlserverDB.

If the row counts in DataTable is very large, then when i foreach the DataTable, i have to visit the sqlserver db many times, it makes performance bad,

Actually i cannot add new column description in Oracle.how can get high performance when foreach DataRow in a DataTable in my code?

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

Upvotes: 1

Views: 4805

Answers (1)

samar
samar

Reputation: 5201

My suggestion would be getting all the records for description and des_ID from geo_exemption in a single datatable at one go and then using LINQ to filter out the records based on des_ID. In this way you would need to hit the database only once. Rest all the operations will happen on asp.net side.

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

Hope this helps.

Upvotes: 1

Related Questions