F# 初学者:从服务器检索数据数组

2022-01-05 00:00:00 database mysql f#

我正在尝试从 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)

其中 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 & 的经验，这 感觉 是错误的.口齿不清.必须有一种更......功能的方法来做到这一点.

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"

相关文章