Efficient way to Find and Replace values in DataColumn

Question

I have a Dataset containing one DataTable and that table got 3 columns (Column1,Data,Column2) & multiple data-rows.

I also have a dictionary which contains list of values that I need to search within DataColumn and the values with which I should replace the original values.

I want to search with-in the column whose name is 'Data'.

Following image contains the original data table and the resultant DataTable along with dictionary which contains keys and values which needs to be searched and replaced.

DataTable

What is the most efficient way to perform this operation?

I am executing an MDX query and getting the data back. So I have to modify the data using C#. I know I check if column name is "Data" then I can loop through the rows and then find and replace the value. I dont want to go that wat — SharpCoder, Jun 27 '13 at 08:49

Steve · Answer 1 · 2013-06-27T08:39:23.480

I am sure that there is best way to do this using LinQ, but this will do

Dictionary<string,string> dic = new Dictionary<string,string>();
dic.Add("AAA", "A++");
dic.Add("BBB", "B++");
foreach(KeyValuePair<string, string> kvp in dic)
{
    DataRow[] sl = dt.Select("DATA='" + kvp.Key + "'");
    foreach(DataRow r in sl)
        r["DATA"] = kvp.Value;
}

The inner foreach loop could be replaced by the expression

 sl.ToList().ForEach(x => x.SetField<string>("DATA", kvp.Value));

but I haven't tested for performance

Probably is not very efficient for large sets of data, but a thing that should be considered is to loop on dictionary Keys and not on the single rows of the table.

score 0 · Answer 2 · answered Jun 27 '13 at 08:19

Go for simplicity

foreach (DataRow row in dataSet.Tables[0].AsEnumerable())
{
  string data = row.Field<string>("Data");

  string newData = null;
  if (dict.TryGetValue(data, out newData))
  {
    row.SetField("Data", newData);
  }
}

(you'll need to add System.Data.DataSetExtensions to your project references)

score 0 · Answer 3 · answered Jun 27 '13 at 08:21

I would handle it like this:

DataSet ds = new DataSet();
string strNewXmls = ds.GetXml().Replace("AAA", "A++");
System.IO.StringReader srXmlStringReader = new System.IO.StringReader(strNewXmls);
ds.ReadXml(srXmlStringReader, XmlReadMode.IgnoreSchema);

This is a quick and simple solution. You can do this with every kind of stuff in your whole DataSet.

Hope it helps,

Taragneti

Efficient way to Find and Replace values in DataColumn

3 Answers3