使用F＃创建insert语句 [英] Creating insert statements with F#

问题描述

我每隔几个月就使用F＃，在它之间似乎我忘记了一切，所以我希望你能原谅我的无知。我的代码下面是从雅虎的数据。这是一个很好的例子，代表我需要做的。返回的第一行具有列标题。我需要获取数据（列表的尾部），并将其插入数据库。基于返回的列标题（列标题与数据库列名称匹配）生成insert语句的最佳方法是什么？

I use F# once every few months or so and in between it seems I forget everything, so I hope you'll excuse my ignorance. My code below is pulling data from Yahoo. It's a good example that represents what I need to do. The first row returned has the column headings. I need to take the data (the tail of the list) and insert it into the database. What's the best way to generate an insert statement based on the column headings returned (the column headings match the database column names)?

在下面的示例中dataWithHeaders。[0]将包含日期，打开，高，低，关闭，音量，Adj关闭。我应该只是拿这个字符串，并在标题周围添加括号来创建插入？然后在insertData中将该值作为参数添加？是否有更优雅的解决方案？

In the example below dataWithHeaders.[0] will contain "Date,Open,High,Low,Close,Volume,Adj Close." Should I just take that string and put brackets around the headings to create the insert? Then in insertData add the value as a parameter? Is there a more elegant solution?

let url = System.String.Format("http://ichart.finance.yahoo.com/table.csv?s={0}&g=d&ignore=.csv", "FB")

let splitLineIntoArray (line : string) = 
    line.Split(",".ToCharArray())

let insertData (data : string[]) =
    // insert data
    ()

let client = new WebClient()
let dataWithHeaders = 
    client.DownloadString(url).Split(Environment.NewLine.ToCharArray())

let data =
    dataWithHeaders
    |> Array.toList
    |> List.tail
    |> List.map(splitLineIntoArray)
    |> List.iter insertData

推荐答案

到SQL Server中，您可以使用这个出色的CSV阅读器（免费）和 SqlBulkCopy 类。

If you're loading the data into SQL Server you can use this excellent CSV reader (free) and the SqlBulkCopy class. It's simple and efficient.

let loadStockPrices ticker =
  use client = new WebClient()
  let url = sprintf "http://ichart.finance.yahoo.com/table.csv?s=%s&g=d&ignore=.csv" ticker
  use stringReader = new StringReader(client.DownloadString(url))
  use csvReader = new CsvReader(stringReader, hasHeaders=true)
  use con = new SqlConnection("<connection_string>")
  con.Open()
  use bulkCopy = new SqlBulkCopy(con, DestinationTableName="<destination_table>")
  bulkCopy.WriteToServer(csvReader)

目标表应该具有与传入数据（OHLC等）相同的列。

The destination table should have the same columns as the incoming data (OHLC, etc).

这篇关于使用F＃创建insert语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！