fsprojects / fsharp.data.sqlclient Goto Github PK

The TP has correctly determined that my nullable column should be Option,
but when reading a NULL value from the database at run-time, there is an error message saying Object of type 'System.DBNull' cannot be converted to type 'System.Int32'..

Same error for both Tuples and Record types.

When a SQL type parameter maps to a .NET reference type it is assumed by the type provider that the parameter cannot be null. However, there may be scenarios where a nullable integer or bit value may want to be passed into a SQL script. I can't currently see a way of achieving this.

It would be a neat feature if we could switch the type provider to assume that parameters are all nullable somehow.

As an example, one might want to give the option to filter a search by a bit column. In the SQL, we can write the query as

SELECT * FROM x WHERE @bitFilter IS NULL OR (BitCol = @bitFilter)

If the type of the BitCol is BIT, then the parameter would be of type bool, however we would want the parameter to be of type Option, or Nullable

The current workaround for this to to use a parameter of type string:

DECLARE @bitFilterStringCopy CHAR(1) = @bitFilterString;
DECLARE @bitFilter BIT;
IF @bitFilterStringCopy = 't' 
    SET @bitFilter = 1;
IF @bitFilterStringCopy = 'f'
    SET @bitFilter = 0;

This gets old pretty quickly when you don't care about Async:

  let cmd = Addresses(top = numberOfRows)
  cmd.Execute()
  |> Async.RunSynchronously 
  |> Array.ofSeq

I suggest following standard .Net practice, providing sync method without suffix, and async method with 'Async' suffix. This would mean Execute() and ExecuteAsync().
The implementation of Execute() could basically just do this.ExecuteAsync() |> Async.RunSynchronously. Possibly more familiar, and the code above gets slightly more simple. What do you think?

Might not be possible, but this would take advantage of the built-in serialization of MVC5 Web Api template apps to emit JSON in REST responses that has record value labels as well as the value. Currently the emitted JSON from a SqlCmd record type only has the value.

My current work-around involves creating a CLIMutable record type exactly like the type emitted by SqlCmd and copying over the values. Thoughts on a better workaround also appreciated.

The followting code consumes ~1.6GB RAM. A raw ADO.NET version runs in constant ~50MB RAM.

#r @"..\..\packages\SqlCommandTypeProvider.1.0.10\lib\net40\SqlCommandTypeProvider.dll"
#r @"..\..\packages\ExtCore.0.8.36\lib\net40\ExtCore.dll"

open System
open FSharp.Data.SqlClient
open System.Data
open ExtCore.Control

[<Literal>]
let connStr = @"Data Source=...;Initial Catalog=...;Integrated Security=True;"

type Full = SqlCommand<"dbo.Full", ConnectionString=connStr, CommandType=CommandType.StoredProcedure, ResultType=ResultType.Records>

let cmd = Full(ID_from=0L, ID_to=10000000L)
cmd.Execute()
|> Seq.choose (fun r -> 
    maybe {
        let! hash = r.hash
        let time = r.time
        return sprintf "%s\t%O" sha1 zone time })
|> fun xs -> 
    use en = xs.GetEnumerator()
    while en.MoveNext() do
        ()
    printfn "Done."

The explicit Enumerator is used (in real code) for chunking and it's not the cause of the issue - the ADO.NET version uses it in the same way with no problem.

Since SQL Server cannot infer result type for non-trivial SPs/queries, I suggest to add support for untyped IDataReader/IDataRecord, like https://github.com/mausch/FsSql does. The idea is to wrap IDataRecord into a new object (DictDataRecord in FsSql https://github.com/mausch/FsSql/blob/master/FsSql/DictDataRecord.fs#L15) that converts DBNulls to Options and allows to pull values of individual columns using the dynamic operator (?). All this would look like this:

type Cmd = 
    SqlCommand<"dbo.Sp", connString, CommandType = CommandType.StoredProcedure, ResultType = ResultType.DataReader>

type Result = { Col1: int; Col2: string option }

let cmd = new Cmd()
cmd.Execute() // returns seq<IDataRecord> where the actual type of the elements is DictDataRecord
|> Seq.map (fun r -> 
      { Col1 = (r?col1).Value // Dangerous! Runtime exception may occur if the column has None (NULL in the DB) value!
        Col2 = r?col2 })

This approach is certainly looses the result set type-safety, but all other good properties of the TP is retained.

The ability to have Sql live in external files has been added, which is great. Now change notification is needed. The scenario is that I'm editing my external sql file and save it. Immediately upon saving the file, the generated types should be updated, compile errors should pop up in code where Record field names have changed for instance.

The background compilation that happens when you edit the .fs file needs to be triggered when the external file is edited.

As it works now (in Xamarin Studio), everything works when I hit compile after editing, but I don't get any compile errors until then.

For inspiration, we can look at FSharp.Data, which implements this.
Not a high priority issue, but it is good to have it written down.

Right now QuotationsFactory.GetRows uses reflection to convert column value to instance of Option type.
...
if isNullableColumn.[i]
then
let t = typedefof<_ option>.MakeGenericType(Type.GetType columnTypes.[i])
row.[i] <- if reader.IsDBNull(i) || row.[i] = null
then t.GetProperty("None").GetValue(null, [||])
else t.GetMethod("Some").Invoke(null, [| row.[i] |])
...
This potentially hurts performance.
Generate quotation that does conversion and pass it into the GetRows method.

Currently SqlCommanProvider allows to configure connection string in similar way as built-in SqlDataConnection or SqlEntityConnection providers:

1. via ConnectionString parameter supplying connection string literal
2. via ConnectionStringName parameter providing name of connection string from config file.
   Initially I liked this design better that Entity Framework version where single parameter to DbContext class ctor plays dual role - it can be either connection string literal or name.
   http://msdn.microsoft.com/en-us/library/gg679467(v=vs.113).aspx
   http://msdn.microsoft.com/en-us/data/jj592674.aspx

Two distinct parameters design works fine for both SqlDataConnection or SqlEntityConnection providers because connection string information can be shared between root data context type and all internal types. This model breaks down for SqlCommandProvider because different type instances of SqlCommand<...> cannot share any information. Imagine 20-30 queries defined in an application all configured via config file.

type MyCommand1 = SqlCommand<...,ConnectionStringName = AdventureWorks">
type MyCommand2 = SqlCommand<...,ConnectionStringName = AdventureWorks">
...
type MyCommandN = SqlCommand<...,ConnectionStringName = AdventureWorks">

This is a lot of typing !!!
To make things worse ConnectionStringName has to be used as named parameter where ConnectionString can be passed by position

type MyCommand = SqlCommand<..., connStr>

It is also confusing that both ConnectionString and ConnectionStringName parameters are optional because there is no way to express that either one has to be supplied.

Considering all above I'm inclined to switch to EF-like design with one mandatory string parameter which can be either string literal or "name=VALUE" (value is name of connection string from config file). It will be possible to re-write the example above like following:

[]
let connectionString = "name=AdventureWorks"

type MyCommand1 = SqlCommand<...,connectionString >
type MyCommand2 = SqlCommand<...,connectionString >
...
type MyCommandN = SqlCommand<...,connectionString >

http://tomasp.net/blog/async-sequences.aspx/
or
http://github.com/Reactive-Extensions/Rx.NET/blob/master/Ix.NET/Source/System.Interactive.Async/IAsyncEnumerator.cs

create procedure [temp].[Test]
    @state bit = 1
as
begin
    set nocount on;
end

type DBType = SqlProgrammabilityProvider<ConnectionStrings.DB, ResultType=ResultType.Records>
let procs = DB("real conn string").``Stored Procedures``
procs.``temp.Test``.AsyncExecute(true)

The last line is not compiled:

The type provider 'FSharp.Data.SqlProgrammabilityProvider' reported an error: String was not recognized as a valid Boolean.

If I remove = 1, everything works OK.

In typical, most beneficial setting SqlCommandProvider coupled with intense T-SQL data access layer. It leads to numerous SqlCommand<...> declarations in application code base.

[<Literal>]
let connStr = @"Data Source=(LocalDb)\v11.0;Initial Catalog=AdventureWorks2012;Integrated Security=True"

type MyCommand1 = SqlCommand<"SELECT * FROM ...", connStr>
type MyCommand2 = SqlCommand<"SELECT * FROM ...", connStr>
...
type MyCommandN = SqlCommand<"SELECT * FROM ...", connStr>

Of course run-time database connection is different from design time. A one way to have different design-time/run-time connection string is to use config file see issue #39. At same time factory pattern has to be supported too because often configuration stored some else or developers prefer to configure objects in the code (for example using IOC container of choice). In current design SqlCommand... generated class accepts optional connection string override as constructor parameter. So the best that can be done in run-time is following :

module WebApi.DataAccess

open FSharp.Data.Experimental

[<Literal>]
let queryProductsSql = " 
SELECT TOP (@top) Name AS ProductName, SellStartDate
FROM Production.Product 
WHERE SellStartDate > @SellStartDate
"

type QueryProductsAsTuples = 
    SqlCommand<queryProductsSql, ConnectionString = @"Data Source=(LocalDb)\v11.0;Initial Catalog=AdventureWorks2012;Integrated Security=True">

module SqlCommand = 
    let inline create() : 'a = 
        let connStr = getConnectionString()
        (^a : (new : string -> ^a) connStr)  

let cmd : QueryProductsAsTuples = SqlCommand.create()

@vasily-kirichenko suggested to use static factory method instead of contructor:
#39 (comment)

I personally don't see particular advantage of static factory method over constructor but I'm open for discussion.

While all above is not very smooth it's still acceptable. But it becomes really ugly when somebody deals with multiple databases because the only information available to factory is ERASED type. How factory can figure out to which data source command belongs to?

I would like to point out couple important things for this discussion t

• currently there is no custom run-time type for SqlCommand.... It erased down to BCL System.Data.SqlClient.SqlCommand class. It means among other things that run-time info like reflection cannot be used.
• while many F# type providers use RootType.GetDataContext(connectionInfo) pattern http://stackoverflow.com/questions/13107676/f-type-provider-for-sql-in-a-class/13109084#13109084 it's inapplicable here because different instances of SqlCommandProvider are disconnected and share no info.
  Although I have some ideas how to address this issue I want to hear from community first.

Suggestions are very welcome.

We should add something similar to

https://github.com/fsprojects/SQLProvider/blob/master/src/SQLProvider/SqlDesignTime.fs#L298
or
https://github.com/fsprojects/FSharp.Management/blob/master/src/FSharp.Management.WMI.DesignTime/WmiProvider.DesignTime.fs#L441

to both SqlCommandProvider and SqlProgrammabilityProvider.

Example sql (schema is not important)

         select top (@top)
            a.Database_ID as Database_id,
            CONVERT(int, a.Address_Id) as Address_id, 
            isnull(a.Line2, '') as Street, 
            isnull(a.Town, '') as Town, 
            isnull(a.PostCode, '') as Postcode, 
            isnull(a.Country, '') as Country,
            v.Location_Id as LocationId
         from person p 
         inner join Address a  on a.Address_ID  = p.HomeAddress_ID and
                                  a.Database_ID = p.Database_ID
         inner join Patient pa on pa.Clinic_ID  = p.Clinic_ID and 
                                  p.Person_ID   = pa.Person_ID and 
                                  p.Area_ID     = pa.Area_ID and
                                  p.Database_ID = pa.Database_ID
         left outer join Location v on v.Address_ID  = a.Address_ID and
                                       v.Database_ID = a.Database_ID
         where v.Address_ID is null or DateDiff(d, v.Updated, GetDate() ) >= 365
         order by v.Updated, a.startdate desc

Location.Location_Id is an int, but since it is outer joined, it can be null. So I would expect the provided type to be int option, but it is int.

By the way, awesome work, keep it up!

There is an option for having connection strings in *.config, but what if I want to connect to ten different servers using the same TP? In some cases, connection strings can only be provided in run-time.

Even if they were in config only, I would not want to have ten different types for ten different databases and/or servers.

Suggestion is to have a property for it in the provided type's constructor, so that you can say let myQuery = myQueryType(conn = "..."), with fallback to type def, just like Sql TP works. Also, wsdl proxies work this way.

Creating a type for

SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table
ORDER BY ORDINAL_POSITION

and with ResultType.Records improperly makes IS_NULLABLE and DATA_TYPE Option and does not make MAXIMUM_LENGTH and NUMERIC_PRECISION Option when it should (the last 2 columns can return null).

let timeZone = TimeZoneInfo.FindSystemTimeZoneById("Pacific Standard Time")
let pacificTimeZoneData =
    data
    |> Array.map (fun x -> x.With(Date = TimeZoneInfo.ConvertTimeFromUtc(x.Date, timeZone)))
    |> Array.map (fun x -> x.Date.Date)

When I run this, the dates remain unchanged.

Using Web API app derived from Dan Mohl's F# MVC5 template.

When SqlCommand compiled in main application (using App.config for connection string for local execution), it works locally and it works on an Azure website.

When SqlCommand compiled in a library project in the Solution (using App.config in the library project for connection string for local execution), it works locally, but on Azure it throws the following exception:

An exception of type 'System.Exception' occurred in FSharp.Data.Experimental.SqlCommandProvider.dll but was not handled in user code

Additional information: Connection string [connection string name] is not found.

I have a stored procedure which uses temp tables when i try to execute that I get following error
"The type provider 'FSharp.Data.SqlCommandProvider' reported an error: The metadata could not be determined because statement ... uses a temp table."

Do I have to wrap every execution in Try/With?

Pretty low priority issue, but might want to be considered before the project graduates its experimental naming ...

Question

Can the implementation of the synchronous "Execute" method be implemented as a first-class citizen, rather than by calling Async.RunSynchronously of the "AsyncExecute" implementation?

Example Scenario

When working with an existing synchronous stack which uses TransactionScope, the transaction scope does not have an effect on the command executed via SqlCommand.Execute.

Workaround

To make TransactionScope work with Asynchronous SqlCommand execution, upgrade the project to use the .Net Framework 4.5.1 and instantiate the TransactionScope with constructor parameter "asyncFlowOption" set to TransactionScopeAsyncFlowOption.Enabled

Example

using (var scope = new TransactionScope(... ,
  TransactionScopeAsyncFlowOption.Enabled))
{
...
}

It appears you cannot use the same parameter more than once.

Comment out the second occurence:

--OR city LIKE @startsWith

and it builds.

Error 6 The type provider 'FSharp.Data.SqlClient.SqlCommandTypeProvider' reported an error: The undeclared parameter '@startsWith' is used more than once in the batch being analyzed.

[<Literal>]
let sqlCityCounty = @"
                SELECT DISTINCT
                    streetName = null, 
                    streetAddress = null, 
                    postalCode = null, 
                    listingID = null, 
                    mlsID = null,
                    classID = null,
                    neighborhood = null,
                    countryCode,
                    state,
                    county,
                    city
                FROM tbMLSQuickLocationListingCount WITH (NOLOCK)
                WHERE 
                    (
                        county LIKE @startsWith
                        OR city LIKE @startsWith                                           
                    ) 
                    AND locationType = 'City'
                    AND MLSID IN (select * from dbo.ConvertCommaSeparatedStringToTableOfInts(@mlsIDList))
                    AND CASE WHEN (MLSID = 10 AND companyID = @companyID) OR MLSID <> 10 THEN 1 ELSE 0 END = 1"

type QueryCityCounty = SqlCommand<sqlCityCounty, ConnectionStringName="staging", ResultType = ResultType.Records, ConfigFile="AutoComplete.config">
let cmd1 = QueryUserMLS(startsWith = "7L%", mlsIDList = "1,2,3", companyID = 1)

The connection string from design/build time is compiled into the TP, and it does not the recognize a runtime connection string. Proof is remove connectionStrings section from runtime config file and it still works (Windows console app).

Not high priority but would be fun

The .NET 4.5 specific version can leverage BCL built-it support for async ops (ExecuteNonQueryAsync, ExecuteReaderAsync) including not available on .NET 4 connection.OpenAsync(). Also sqlDataReader.ReadAsync() (issue #15)

Just to get a conversation started..

It would be nice if types could be generated instead of erased. This would enable use from C#. I have never made a generated type, so I have no idea how much work would be involved.

I suggest to replace Stored Procedures with simple Procedures.
Also, I think it'd be better if we separate schemas from SPs names. I mean, instead of:

myDb.``Stored Procedures``.``dbo.MyProc``.AsyncExecute()

we can write:

myDb.Procedures.dbo.MyProc.AsyncExecute()

As an extra benefit, it would give us better performance, because we could load schema names first, then only SPs names belonging to a particular schema.
As it is for now it takes significant time to load all the SPs from real DBs.

Would be nice to support a slightly older version of SQL. Not sure how easy it is to replicate things such as sp_describe_undeclared_parameters.

http://blogs.msdn.com/b/adonet/archive/2013/12/09/microsoft-sqlserver-types-nuget-package-spatial-on-azure.aspx

https://www.nuget.org/packages/Microsoft.SqlServer.Types/

Same issue as this: fsprojects/FSharpx.Extras#268

The TypeProvider should implement IDisposable.

New base time-type should cover following requirements:

1. Value equality
2. JSON.NET 2-way serialization
3. ToString same as F# records (special attention to option fields. Print 'None' not null).
4. "With" method to transform/"mutate" properties. Similar to built-in with syntax for F# records
5. dynamic lookup operator (read only)

My implementation uses SQL 2012 to build the types, and SQL 2008 R2 at runtime, so that may play a role in this issue.

If you have a column defined myBool (tinyint, null), selecting it in sql used to build a type appears to cause no problem at design time

[<Literal>]
let sql = @"SELECT myBool, ..."
type MyType = SqlCommand<sql, ...

but at run time it throws in a way that does not propagate the error back up to the application.

Because ctor is lazily generated in case of mistake in SQL script user sees "ctor for type ... is not defined" instead of meaningful error message from sys.sp_describe_undeclared_parameters.

The pop-up tooltip does not identify the namespace SqlClient types are defined in. Since "go to definition" on the type does not work in VS, the only way to get to the definition is a text search.

Error FS3033: The type provider 'FSharp.Data.SqlCommandProvider' reported an error: Could not load type 'Microsoft.SqlServer.Server.SqlDataRecord' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'. (FS3033)

I'm guessing mono doesn't implement Microsoft.SqlServer.Server.SqlDataRecord ?

Compiling with F# 3.0 works, but running the project I get

[A]Microsoft.FSharp.Control.FSharpAsync`1[System.Collections.Generic.IEnumerable`1[System.Object]] cannot be cast to [B]Microsoft.FSharp.Control.FSharpAsync`1[System.Collections.Generic.IEnumerable`1[System.Object]]. 

Type A originates from 'FSharp.Core, Version=4.3.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' in the context 'Default' at location 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\FSharp.Core\v4.0_4.3.1.0__b03f5f7f11d50a3a\FSharp.Core.dll'. 
Type B originates from 'FSharp.Core, Version=4.3.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' in the context 'Default' at location 'C:\Windows\Microsoft.Net\assembly\GAC_MSIL\FSharp.Core\v4.0_4.3.0.0__b03f5f7f11d50a3a\FSharp.Core.dll'

The following SP is not appear as property of generated type:

create procedure [dbo].[GetVersion]
    @version int output
as
begin
    set @version = 1
end

This is the showstopper for my team.

Sql < 2012 could be partially supported.
The format of the output can use set fmtonly on instead of sys.sp_describe_first_result_set. The tricky part is with replacing sys.sp_describe_undeclared_parameters

They could be read the same way as today, and if that fails, we could fall back to parsing them somehow. When parsing, we can not provide types for them, so in the fall-back case, they would be obj. Not ideal, but still useful.

This would also remove the limitation that temp tables can not be used.

What do you think? Any thoughts on how to best parse the parameters?

Same output format as standard F# records. Convenient for debugging/testing.

I think it is pretty bad that the compile-time connection string is hard-coded into the assembly. It is too easy to leak sensitive info this way.

My first though about this is to make connectionString/connectionName non-optional.

Add ToTraceString instance method that accepts same parameters as AsyncExecute/Execute to return the sql script to be executed against the database.

I think this provider now qualifies to have "Experimental" removed.

Having large chunks of Sql in code gets unwieldy, so it would be nice to be able to point to a file (root is project directory) instead.

Just trying to re-phrase #11 and flash out real-life use cases which would make it worth replacing provided types with generated. These use cases might include C# compatibility (or, rather more idiomatic support) and related scenarios, and so on.

So far, the only meaningful scenario I see is DAL layer written in F#.
This scenario would require:

• Output records to be C#-compatible:
  • simple nullable properties instead of options
  • mutable?
• Task<> instead of async as output option?
  • Accepting CancellationToken as an input parameter of async methods.

Any other scenarios/suggestions will be very welcome.

Currently for SqlCommandProvider when "AllParametersOptional = true" parameters of ref .NET types (like string) are not wrapped into the option type.