How to join DataTables?

I have two DataTables. I have some data that was been retrieved from an XML file on the internet into a DataTable called rates. I also have a DataTable with a set of finanical trades that has been retrieved from a database called openTrades with code like this:

DataTable rates = DB.GetCurrentFxPrices("http://rates.fxcm.com/RatesXML");
DataTable openTrades = DB.GetOpenTrades();

I want to add a column to openTrades called rate and put in there the current rate from the rates DataTable joining on a column called symbol.

the rates DataTable has the following columns:

Bid, Ask, High, Low, Direction, Last, Symbol

The openTrades DataTable has the following relevant columns:

tradeId, symbol and the newly added rate column. I'm looking for the most efficient way to join this data together and have the results in the openTrades DataTable in the new rate column.

EDIT

I'm trying this code:

DBUtil DB = new DBUtil();
DataTable rates = DB.GetCurrentFxPrices("http://rates.fxcm.com/RatesXML");
DataTable openTrades = DB.GetOpenTrades();

openTrades.Columns.Add("Bid", typeof(decimal));
openTrades.Columns.Add("Ask", typeof(decimal));

var query = from DataRow tradeRow in openTrades.Rows
            join DataRow rateRow in rates.Rows
            on tradeRow.Field<string>("symbol") equals rateRow.Field<string>("Symbol")
            select new
            {
                TradeRow = tradeRow,
                //Bid = rateRow.Field<decimal>("Bid"),
                //Ask = rateRow.Field<decimal>("Ask")
                Rate = (rateRow.Field<decimal>("Bid") + rateRow.Field<decimal>("Ask"))/2
            };

foreach (var item i开发者_StackOverflow社区n query)
{
    //item.TradeRow["Bid"] = item.Bid;
    //item.TradeRow["Ask"] = item.Ask;
    item.TradeRow["lastPrice"] = item.Rate;
}

But I get this error on the select:

System.InvalidCastException: Specified cast is not valid.

---

You can join your existing tables with the following query, and then iterate over the resulting sequence to update your Rate values in the trade table.

var query = from DataRow tradeRow in openTrades.Rows 
            join DataRow rateRow in rates.Rows 
            on tradeRow.Field<string>("Symbol") equals rateRow.Field<string>("Symbol")
            select new 
            {
                 TradeRow = tradeRow,
                 Rate = rateRow.Field<decimal>("Rate") // use actual type
            };

foreach (var item in query)
{
     item.TradeRow["Rate"] = item.Rate;
}

Your openTrades table should reflect the changes, and you can continue to do whatever work you need.

---

Have you looked into using Linq to do this?

Something similar to the following should do what you need. The resulting anonymous type could be loaded into a DataTable if needed or just bound directly with a grid control.

using (var wc = new System.Net.WebClient()) {
var openTrades = new [] {
    new {tradeId="000", symbol="EURUSD"},
    new {tradeId="001", symbol="USDJPY"}
};

var resultData = XElement.Parse(wc.DownloadString("http://rates.fxcm.com/RatesXML"))
    .Elements("Rate")
    .Select(x=>new {
        Symbol=x.Attribute("Symbol").Value, 
        Bid=x.Element("Bid").Value,
        Ask=x.Element("Ask").Value,
        High=x.Element("High").Value,
        Low=x.Element("Low").Value,
        Direction=x.Element("Direction").Value,
        Last=x.Element("Last").Value
    })
    .ToList()
    .Join(openTrades, x=>x.Symbol, x=>x.symbol, (rate,trades)=> new {trades.tradeId, rate.Symbol, rate.Ask, rate.Bid, rate.High, rate.Low, rate.Direction, rate.Last})
    .ToList();          

}

To make this work, you will need the following namespaces referenced: System.Linq System.Xml.Linq

Good luck!