通过F＃和Npgsql在Postgresql中调用存储过程

问题描述

我正在尝试使用Npgsql类型提供程序从F＃调用postgresql中的存储过程。

I am trying to call a stored procedure in postgresql from F# using the Npgsql type provider.

当前，我已按以下方式连接到数据库：

Currently, I am connected to the database as follows:

open System
open System.Data
open System.Data.Entity
open System.Data.Linq
open Microsoft.FSharp.Data.TypeProviders
open Microsoft.FSharp.Linq
open Npgsql
open NpgsqlTypes

type internal dbSchema = SqlEntityConnection<ConnectionString="**my connection string**", Provider="Npgsql">

let internal db = dbSchema.GetDataContext()

但是，我只请参见 db 类型的表，而不是任何存储过程。是否可以通过类型提供程序以静态类型的方式使用存储过程，而不是仅调用原始查询字符串？

However, I only see the tables on the db type, not any of the stored procedures. Is there a way to use the stored procedures in a statically typed manner through the type provider, instead of just calling the raw query string?

推荐答案

我知道很久以前就问过这个问题，但是我想我会添加对 SqlProvider 。最近已添加了对PostgreSQL的支持，其中包括对SPROCS的支持。

I know this question was asked along time ago, but I thought I would add a reference to the SqlProvider. This has recently had support for PostgreSQL added to it and it includes support for SPROCS.

 [<Literal>]
 let connStr = "User ID=postgres;Password=password;Host=POSTGRESQL;Port=9090;Database=hr;"

 [<Literal>]
 let resolutionFolder = @"D:\Downloads\Npgsql-2.1.3-net40\"

 type HR = SqlDataProvider<ConnectionString=connStr,DatabaseVendor=Common.DatabaseProviderTypes.POSTGRESQL, ResolutionPath = resolutionFolder>
 let ctx = HR.GetDataContext()

 ctx.Procedures.ADD_JOB_HISTORY(100, DateTime(1993, 1, 13), DateTime(1998, 7, 24), "IT_PROG", 60)


 //Support for sprocs that return ref cursors
 let employees =
     [
       for e in ctx.Functions.GET_EMPLOYEES().ReturnValue do
           yield e
     ]

解析文件夹指向位置的位置NPGSQL .NET程序集。

Where the resolution folder points to the location of the NPGSQL .NET assemblies.

这篇关于通过F＃和Npgsql在Postgresql中调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！