zaid-ajaj / dustytables Goto Github PK
View Code? Open in Web Editor NEWThin F# API for SqlClient for easy data access to ms sql server with functional seasoning on top
License: MIT License
Thin F# API for SqlClient for easy data access to ms sql server with functional seasoning on top
License: MIT License
Is your feature request related to a problem? Please describe.
I often need to filter a list of ids in my querys. I usally use the IN operator for tjat
Describe the solution you'd like
Hey Zaid,
I would like to add a list type for the SqlValue like this:
type SqlValue =
| TinyInt of uint8
| TinyIntList of seq<uint8>
and then add the list of parameters like this:
let addValues list =
list
|> Seq.map (fun x -> cmd.Parameters.AddWithValue(sprintf "%s%A" paramName x, x))
|> Seq.toArray
|> ignore
match snd param with
| SqlValue.IntList list -> list |> addValues
| SqlValue.TinyIntList list -> list |> addValues
You have to adjust the query string as well.
This could be done like this:
let createSkalarString list parameterName =
list |> Seq.map (fun x -> sprintf "@%s%A" parameterName x) |> String.concat ","
let skalarString = Sql.createSkalarString aggregationTypes "types"
This works is my test case and I have a PR ready just let me know if this solution would be ok for you.
In this code:
/// <summary>Executes the query asynchronously and returns the number of rows affected</summary>
let executeNonQueryAsync (props: SqlProps) = task {
let! token = Async.CancellationToken
use mergedTokenSource = CancellationTokenSource.CreateLinkedTokenSource(token, props.CancellationToken)
let mergedToken = mergedTokenSource.Token
if props.SqlQuery.IsNone then failwith "No query provided to execute. Please use Sql.query"
let connection = getConnection props
try
if not (connection.State.HasFlag ConnectionState.Open) then
do! connection.OpenAsync(mergedToken)
use command = new SqlCommand(props.SqlQuery.Value, connection)
populateCmd command props
if props.NeedPrepare then command.Prepare()
let! affectedRows = command.ExecuteNonQueryAsync(mergedToken)
return Ok affectedRows
finally
if props.ExistingConnection.IsNone then connection.Dispose()
}
Why wrap affectedRows
in Result.Ok
if potential exception is not being captured?
It should probably not wrap it at all and let the consumer handle it... no?
Hey Zaid,
I am using the library to add some test data to my database. Works nice so far, thanks!
Would it be possible that you could add the type Uniqueidentifier to the SqlValue DU. This would help alot.
Thanks
Describe the bug
I am getting a bit desperate and frustrated.
Completely out of the blue I am no longer able to use the SqlDataClient in a script file getting the below error. I had previously problems to get things up and running, but managed it with setting the reference to the runtimes/win SqlDataClient dll and
AppContext.SetSwitch("Switch.Microsoft.Data.SqlClient.UseManagedNetworkingOnWindows", true)
Then suddenly out of the blue I get the below error:
Exception message:
Stack trace:
System.MissingMethodException: Method not found: 'System.String System.String.TrimStart()'.
at Microsoft.Data.LocalDBAPI.GetLocalDbInstanceNameFromServerName(String serverName)
at Microsoft.Data.SqlClient.SqlConnectionString..ctor(String connectionString) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnectionString.cs:line 286
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnectionFactory.cs:line 140
at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) in /_/src/Microsoft.Data.SqlClient/netcore/src/Common/src/Microsoft/Data/ProviderBase/DbConnectionFactory.cs:line 230
at Microsoft.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnectionHelper.cs:line 71
at Microsoft.Data.SqlClient.SqlConnection.set_ConnectionString(String value) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnection.cs:line 517
at DustyTables.SqlModule.getConnection(SqlProps props)
at DustyTables.SqlModule.execute[t](FSharpFunc`2 read, SqlProps props)
{Data = dict [];
HResult = -2146233069;
HelpLink = null;
InnerException = null;
Message = "Method not found: 'System.String System.String.TrimStart()'.";
Source = "Microsoft.Data.SqlClient";
StackTrace = " at Microsoft.Data.LocalDBAPI.GetLocalDbInstanceNameFromServerName(String serverName)
at Microsoft.Data.SqlClient.SqlConnectionString..ctor(String connectionString) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnectionString.cs:line 286
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnectionFactory.cs:line 140
at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) in /_/src/Microsoft.Data.SqlClient/netcore/src/Common/src/Microsoft/Data/ProviderBase/DbConnectionFactory.cs:line 230
at Microsoft.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnectionHelper.cs:line 71
at Microsoft.Data.SqlClient.SqlConnection.set_ConnectionString(String value) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlConnection.cs:line 517
at DustyTables.SqlModule.getConnection(SqlProps props)
at DustyTables.SqlModule.execute[t](FSharpFunc`2 read, SqlProps props)";
TargetSite = System.String GetLocalDbInstanceNameFromServerName(System.String);}
To Reproduce
See my repository, I can also demonstrate this life to you @Zaid-Ajaj
Expected behavior
Is your feature request related to a problem? Please describe.
DustyTable does not generate Sql in statements, so we wrote a simple function that helps with that. The Problem is that fields on Sql.Props are private.
I'm not suggesting to add in statement generation because we aim for a good enough solution that works for our use cases and not a general solution. This issue is just about making some currently private things accessible so things like this are possible
Describe the solution you'd like
Make the fields public.
Describe alternatives you've considered
Currently using reflection for the proof of concept.
Additional context
(code of the POC)
[<RequireQualifiedAccess>]
module Sql =
open System.Text.RegularExpressions
open System.Reflection
open DustyTables
open System
open System.Collections
open System.Data
open Microsoft.Data.SqlClient
let list(items: 'a seq) : SqlParameter =
let parameter = SqlParameter()
parameter.Value <- items
parameter.DbType <- DbType.Object
parameter
let useInStatements (props: Sql.SqlProps): Sql.SqlProps =
let transform (query: string, param: string * SqlParameter) =
let parameterName, parameter = param
let regex = Regex(sprintf "(?<=\s)in\s*%s(?=$|\)|,|;|\s)" parameterName)
let buildStm (items: 'a seq, func: 'a -> SqlParameter) =
let buildName (index: int) = sprintf "%s_generated_%i" parameterName index
let parameters = Seq.mapi (fun i item -> buildName i, func item) items
let query : string =
match Seq.length parameters with
| 0 -> regex.Replace(query, "= null")
| 1 -> regex.Replace(query, "= " + (fst (Seq.head parameters)))
| n -> regex.Replace(query, "in (" + String.Join(", ", Seq.map fst parameters) + ")")
query, parameters
match parameter.Value with
| :? seq<int16> as items -> buildStm (items, Sql.int16)
| :? seq<int32> as items -> buildStm (items, Sql.int)
| :? seq<int64> as items -> buildStm (items, Sql.int64)
| :? seq<bool> as items -> buildStm (items, Sql.bool)
| :? seq<byte[]> as items -> buildStm (items, Sql.bytes)
| :? seq<decimal> as items -> buildStm (items, Sql.decimal)
| :? seq<double> as items -> buildStm (items, Sql.double)
| :? seq<string> as items -> buildStm (items, Sql.string)
| :? seq<Guid> as items -> buildStm (items, Sql.uniqueidentifier)
| :? seq<DateTime> as items -> buildStm (items, Sql.dateTime)
| :? seq<DateTimeOffset> as items -> buildStm (items, Sql.dateTimeOffset)
| :? IEnumerable as items -> failwithf "no transformation implemented for type of %s" (string items)
| _ -> query, Seq.singleton param
let folder (state: string * (string * SqlParameter) seq) (param: string * SqlParameter) : string * (string * SqlParameter) seq =
let query, parameters = state
let query', parameters' = transform (query, param)
(query', Seq.append parameters parameters')
let getQuery () : string option =
let value =
props
.GetType()
.GetProperty("SqlQuery", BindingFlags.NonPublic ||| BindingFlags.Instance)
.GetValue(props)
value :?> _
let getParameters () : (string * SqlParameter) list =
let value =
props
.GetType()
.GetProperty("Parameters", BindingFlags.NonPublic ||| BindingFlags.Instance)
.GetValue(props)
value :?> _
match getQuery () with
| Some query ->
let query, parameters = Seq.fold folder (query, Seq.empty) (getParameters())
props
|> Sql.query query
|> Sql.parameters (List.ofSeq parameters)
| None -> props
Is your feature request related to a problem? Please describe.
Hello! I'm looking for a way to use table-valued parameters with this package. TVPs let you use tables as parameters for stored procedures. For example, if I have a one-to-many relationship, I could use a TVP and a stored procedure to create the parent relationship and multiple children all in one function.
As it stands now, it looks like we're limited to only using the parameter types in the SqlValue
DU.
Describe the solution you'd like
I've played around with this package and have come up with a rough solution that works, although I don't think it's ideal yet for a couple reasons I'll get to below. Here's what my changes look like:
type SqlValue =
| TinyInt of uint8
| Smallint of int16
| Int of int
| Bigint of int64
| String of string
| DateTime of DateTime
| DateTimeOffset of DateTimeOffset
| Bool of bool
| Float of double
| Decimal of decimal
| Binary of byte[]
| UniqueIdentifier of Guid
| Null
| Table of TableValuedParameter
and TableValuedParameter = string * string list * list<list<SqlValue>>
For the TableValuedParameter
type, the first string is the SQL type name (e.g. "MyCustomTableType"). The second string list is the list of columns in the custom table.
I also added a convenience module for assembling a TableValuedParameter
:
module SqlTvp =
let typeName str : TableValuedParameter =
str, [], []
let columns cols ((typeName, _, rows) : TableValuedParameter) : TableValuedParameter =
typeName, cols, rows
let rows rows ((typeName, cols, _) : TableValuedParameter) : TableValuedParameter =
typeName, cols, rows
let addRow row ((typeName, cols, rows) : TableValuedParameter) : TableValuedParameter =
typeName, cols, rows @ [row]
And you can combine it all with a stored procedure like this:
let tvp =
SqlTvp.typeName "MyCustomTableType"
|> SqlTvp.columns ["FirstName"; "LastName"; "DOB"]
|> SqlTvp.addRow ["John"; "Doe"; someDate]
|> SqlTvp.addRow ["Jane"; "Doe"; someDate]
Sql.connect connStr
|> Sql.storedProcedure "sp_myStoredProc"
|> Sql.parameters
[ "@foo", SqlValue.Int 1
"@bar", SqlValue.Int 2
"@baz", SqlValue.Table tvp ]
Behind the scenes, the populate
function is changed to take the SqlValue.Table
case and turn it into a .NET DataTable:
//...
| SqlValue.Table (typeName, cols, rows) ->
let dataTable = new DataTable()
for col in cols do
dataTable.Columns.Add col |> ignore
for row in rows do
let rowValues = row |> List.map getParamValue |> Array.ofList
dataTable.Rows.Add rowValues |> ignore
let tableParameter = cmd.Parameters.AddWithValue(paramName, dataTable)
// TypeName must be set to the custom SQL tvp type
tableParameter.TypeName <- typeName
// SqlDbType must be set to Structured for a TVP parameter
tableParameter.SqlDbType <- SqlDbType.Structured
So this works pretty well, but I don't think it's 100% ideal at the moment. Chiefly that's because, as far as I understand, table-valued parameters only work with stored procedures; they can't be used in regular SQL queries/commands. That means the SqlValue
type suddenly has a case that only applies in one scenario and is completely invalid for others, which could lead to confusion and exceptions.
On top of that, you can't nest TVPs (as far as I know), so making the SqlValue
type "recursive" with the Table case can lead to more confusion.
Describe alternatives you've considered
I think an easier alternative is just adding a Custom
case to the SqlValue
DU, perhaps with a callback function that can configure the parameter (so we can set the param.TypeName
and param.SqlDbType
props).
type SqlValue =
| TinyInt of uint8
| Smallint of int16
| Int of int
| Bigint of int64
| String of string
| DateTime of DateTime
| DateTimeOffset of DateTimeOffset
| Bool of bool
| Float of double
| Decimal of decimal
| Binary of byte[]
| UniqueIdentifier of Guid
| Null
| Custom of obj * (SqlParameter -> unit)
And then in populateRow
you could do something like this:
// ...
| SqlValue.Custom (value, fn) ->
let sqlParam = cmd.Parameters.AddWithValue(paramName, value)
fn sqlParam
The drawback to this is that we lose the convenience of the SqlRow
and SqlValue
types, instead having to add each TVP row as regular .NET types.
I'd be happy to create a pull request for this feature, but I wanted to see if you had feedback and if it's something you're interested in adding to the package first.
Is your feature request related to a problem? Please describe.
I tried to read and Id and though the Id would be of the type tinyint. Somehow the query returned a empty array. Turned out the id as of the type int instead. I fiquered out it helps if you print out the unmatched case of the SqlValue.
Describe the solution you'd like
Before you just returned None as a result of the read value:
let readTinyInt name (row: SqlRow) =
row
|> List.tryFind (fun (colName, value) -> colName = name)
|> Option.map snd
|> function
| Some(SqlValue.TinyInt value) -> Some value
| _ -> None
I added a little helper to print out the unmatched case:
let returnInfo (x:SqlValue option) name =
printfn "got %A for name %s return None for now" x.Value name
None
let readTinyInt name (row: SqlRow) =
row
|> List.tryFind (fun (colName, value) -> colName = name)
|> Option.map snd
|> function
| Some(SqlValue.TinyInt value) -> Some value
| x -> returnInfo x name
This at least shows that there were some values and you just didn't picked the correct SqlValue type.
Hey! I've got a usecase where a stored procedure returns multiple result sets that I need to parse. To do this, I need access to the SqlDataReader so I can call reader.NextResult()
to move to the next result set. I see that the RowReader
type lets us access the reader, but given the Sql.execute
function reads the rows and advances the reader automatically, I'm not sure this will work the way I need it to. I'd need to somehow detect that the reader is on its last row and then advance it to the next result before the execute function's loop ends.
Would it be possible to add an executeReader
function? Something like this:
let executeReader (read: SqlDataReader -> 't) (props: SqlProps) : 't =
// ...
I'd be happy to create a pull request for it if you're interested in adding support for it.
For the three functions executeAsync
, iterAsync
, and executeRowAsync
, how about using the SqlDataReader.ReadAsync
method instead of SqlDataReader.Read
?
Is your feature request related to a problem? Please describe.
Microsoft.Data.SqlClient is the new .NET library for SQL Server. It includes new features and works better in Core in some scenarios. In my particular case Azure Functions v3. I believe it supports all the frameworks you target.
Describe the solution you'd like
This would be a breaking change, so I don't know if you would want to split libraries or just put a note on the readme. The change requires only changing your dep in Paket and changing one open statement.
Describe alternatives you've considered
As I said above, you could two create nuget packages if cutting it off feels too extreme.
Additional context
Here is a blog post from the initial announcement.
It's out of preview now.
Copy-paste whatever Npgsql.FSharp is doing and make it available from here to bring the same goodness for SqlClient
Do you think the executeTransaction
and executeTransactionAsync
methods should roll back the transaction when an error occurs? Unless the disposing of the Connection
does the trick?
Is your feature request related to a problem? Please describe.
I like your library and would like to run Transactions but since I'm already using TaskBuilder.fs I'd like to use Tasks directly and not wrap async.
Describe the solution you'd like
I would like two more functions added, executeTransactionTask
that throws and executeTransactionSafeTask
that returns Result<>
.
Describe alternatives you've considered
The alternative is to wrap executeTransactionAsync
with try/catch and Async.StartAsTask
Additional context
I am willing to submit a PR if you'd like.
Describe the bug
When trying to use the library I in thet FSI I get:
Binding session to 'C:\Users\cbollen.nuget\packages\microsoft.data.sqlclient\2.0.0\lib\netstandard2.0\Microsoft.Data.SqlClient.dll'...
Real: 00:00:00.083, CPU: 00:00:00.015, GC gen0: 0, gen1: 0, gen2: 0
[]
val it : Result<string list,exn> =
Error
System.PlatformNotSupportedException: Microsoft.Data.SqlClient is not supported on this platform.
at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString) in H:\tsaagent2_work\11\s\artifacts\Project\obj\Release.AnyCPU\Microsoft.Data.SqlClient\netcore\netstandard2.0\Microsoft.Data.SqlClient.notsupported.cs:line 318
at DustyTables.SqlModule.getConnection(SqlProps props)
at DustyTables.SqlModule.execute[t](FSharpFunc2 read, SqlProps props) {Data = dict []; HResult = -2146233031; HelpLink = null; InnerException = null; Message = "Microsoft.Data.SqlClient is not supported on this platform."; Source = "Microsoft.Data.SqlClient"; StackTrace = " at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString) in H:\tsaagent2\_work\11\s\artifacts\Project\obj\Release.AnyCPU\Microsoft.Data.SqlClient\netcore\netstandard2.0\Microsoft.Data.SqlClient.notsupported.cs:line 318 at DustyTables.SqlModule.getConnection(SqlProps props) at DustyTables.SqlModule.execute[t](FSharpFunc
2 read, SqlProps props)";
TargetSite = Void .ctor(System.String);}
When targetting netcoreapp3.1 I get:
Binding session to 'C:\Users\cbollen.nuget\packages\microsoft.data.sqlclient\2.0.0\lib\netcoreapp3.1\Microsoft.Data.SqlClient.dll'...
Binding session to 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.dll'...System.TypeLoadException: Could not load type 'System.ICloneable' from assembly 'System.Runtime, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
at DustyTables.SqlModule.connect(String constr)
at FSI_0003.executeGetLatest() in D:\Development\Informedica\apps\GenPed\src\Server\Scripts\Database.fsx:line 79
at <StartupCode$FSI_0004>.$FSI_0004.main@()
Stopped due to error
To Reproduce
Steps to reproduce the behavior:
Expected behavior
Should be able to run on dotnet in the FSI
Screenshots
If applicable, add screenshots to help explain your problem.
Desktop (please complete the following information):
NA
Smartphone (please complete the following information):
NA
Additional context
.NET Core SDK (reflecting any global.json):
Version: 3.1.301
Commit: 7feb845744
Runtime Environment:
OS Name: Windows
OS Version: 6.1.7601
OS Platform: Windows
RID: win7-x64
Base Path: C:\Program Files\dotnet\sdk\3.1.301\
Host (useful for support):
Version: 3.1.5
Commit: 65cd789777
.NET Core SDKs installed:
2.1.701 [C:\Program Files\dotnet\sdk]
2.1.801 [C:\Program Files\dotnet\sdk]
2.2.301 [C:\Program Files\dotnet\sdk]
2.2.401 [C:\Program Files\dotnet\sdk]
3.1.100 [C:\Program Files\dotnet\sdk]
3.1.101 [C:\Program Files\dotnet\sdk]
3.1.301 [C:\Program Files\dotnet\sdk]
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.