Databases
StackOverflow2013
Postsdbo
POHow to join DataTables?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to join DataTables?
    text
    copied!<p>I have two <code>DataTable</code>s. I have some data that was been retrieved from an XML file on the internet into a <code>DataTable</code> called <code>rates</code>. I also have a <code>DataTable</code> with a set of finanical trades that has been retrieved from a database called <code>openTrades</code> with code like this:</p> <pre><code>DataTable rates = DB.GetCurrentFxPrices("http://rates.fxcm.com/RatesXML"); DataTable openTrades = DB.GetOpenTrades(); </code></pre> <p>I want to add a column to <code>openTrades</code> called <code>rate</code> and put in there the current rate from the <code>rates</code> <code>DataTable</code> joining on a column called <code>symbol</code>.</p> <p>the rates <code>DataTable</code> has the following columns:</p> <p><code>Bid, Ask, High, Low, Direction, Last, Symbol</code></p> <p>The openTrades <code>DataTable</code> has the following relevant columns:</p> <p><code>tradeId, symbol</code> and the newly added <code>rate</code> column. I'm looking for the most efficient way to join this data together and have the results in the <code>openTrades DataTable</code> in the new <code>rate</code> column.</p> <p><strong>EDIT</strong></p> <p>I'm trying this code:</p> <pre><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&lt;string&gt;("symbol") equals rateRow.Field&lt;string&gt;("Symbol") select new { TradeRow = tradeRow, //Bid = rateRow.Field&lt;decimal&gt;("Bid"), //Ask = rateRow.Field&lt;decimal&gt;("Ask") Rate = (rateRow.Field&lt;decimal&gt;("Bid") + rateRow.Field&lt;decimal&gt;("Ask"))/2 }; foreach (var item in query) { //item.TradeRow["Bid"] = item.Bid; //item.TradeRow["Ask"] = item.Ask; item.TradeRow["lastPrice"] = item.Rate; } </code></pre> <p>But I get this error on the <code>select</code>:</p> <p><code>System.InvalidCastException: Specified cast is not valid.</code></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload