F＃初学者：从服务器检索数据数组 [英] F# Beginner: retrieving an array of data from a server

问题描述

我尝试从MySQL数据库获取数据。

I'm trying to grab data from a MySQL database.

方法2 - 应用/地图样式

Approach 2 - apply/map style

我正在使用 MySQL ADO参考尝试构建此系统。特别是，示例位于21.2.3.1.7。

I'm using the MySQL ADO Reference to try to build this system. In particular, the example found at 21.2.3.1.7.

（使用伪代码）

let table = build_sequence(query.read)

返回表中的一行（或者更确切地说，是表中的一行元素的列表）。和表变量是一个列表的列表，它将表示从查询返回的表。

Where query.read returns a row in the table(Or rather, a list of elements that happen to be a row in the table). And the table variable is a list of lists that will represent a table returned from the query.

我看过下面给出的代码，它的语法是我的头，恐怕。

I've stared at the code given below, and it's syntax is over my head, I'm afraid.

方法1 - 循环。

问题1：它不合适，需要可变。

Problem 1: It's inelegant, requiring a mutable.

问题2：这只是感觉错误，根据我以前的经验与Prolog& Lisp。有更多... 功能的方式来做这个。

Problem 2: This just feels wrong, based on my prior experience with Prolog & Lisp. There's gotta be a more...functional way to do this.

我不知道从哪里开始。评论&

I'm not sure where to begin though. Comments & thoughts?

let reader : MySql.Data.MySqlClient.MySqlDataReader = command.ExecuteReader()

let arr = []

let mutable rowIter = 0
let readingLoop() =
    while(reader.Read()) do
        rowIter = rowIter + 1
        for i = 0 to reader.FieldCount do

            //set arr[someiterator, i] = reader.GetValue[i].ToString())

推荐答案

Seq类型有一个整齐的函数用于处理称为generate_using的数据库游标（参见 F＃手册和 F＃基础）。这是一个高阶函数，它使用一个函数打开游标，另一个函数（重复调用）从游标处理记录。下面是一些使用generate_using执行sql查询的代码：

The Seq type has a neat function for handling database cursors called generate_using (see F# Manual and the Data Access chapter in Foundations of F#). This is a higher order function that takes one function to open the cursor and another (called repeatedly) to process records from the cursor. Here is some code that uses generate_using to execute a sql query:

let openConnection (connectionName : string) =
    let connectionSetting = ConfigurationManager.ConnectionStrings.Item(connectionName)
    let connectionString = connectionSetting.ConnectionString
    let connection = new OracleConnection(connectionString)
    connection.Open()
    connection

let generator<'a> (reader : IDataReader) =
    if reader.Read() then
        let t = typeof<'a>
        let props = t.GetProperties()
        let types = props
                    |> Seq.map (fun x -> x.PropertyType)
                    |> Seq.to_array
        let cstr = t.GetConstructor(types)
        let values = Array.create reader.FieldCount (new obj())
        reader.GetValues(values) |> ignore
        let values = values
                     |> Array.map (fun x -> match x with | :? DBNull -> null | _ -> x)
        Some (cstr.Invoke(values) :?> 'a)
    else
        None

let executeSqlReader<'a> (connectionName : string) (sql : string) : 'a list =        
    let connection = openConnection connectionName

    let opener() = 
        let command = connection.CreateCommand(CommandText = sql, CommandType = CommandType.Text)
        command.ExecuteReader()

    let result = Seq.to_list(Seq.generate_using opener generator)        

    connection.Close()
    connection.Dispose()
    result

在Oracle数据库中我们需要定义一个列定义类型并调用executeSqlReader，如下所示：

For example to list all the tables in an Oracle database we need to define a column definition type and invoke executeSqlReader as follows:

type ColumnDefinition = {
    TableName : string;
    ColumnName : string;
    DataType : string;
    DataLength : decimal;                
}

let tableList = executeSqlReader<ColumnDefinition>
    "MyDatabase"
    "SELECT t.table_name, column_name, data_type, data_length FROM USER_TABLES t, USER_TAB_COLUMNS c where t.TABLE_NAME = c.table_name order by t.table_name, c.COLUMN_NAME"

这篇关于F＃初学者：从服务器检索数据数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！