zaid-ajaj / dustytables Goto Github PK

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 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.

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?

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

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.

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.
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.

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

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](FSharpFunc2 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:

1. Add the dependency
2. Target dotnet 2 or higher
3. Load the Dusty Table libraries in the FSI
4. Try to use them

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]

Copy-paste whatever Npgsql.FSharp is doing and make it available from here to bring the same goodness for SqlClient

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.

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?

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.

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