Comments (7)
Well, I was thinking on a condition that selects which transaction scope to use based on the selected SQLite library. There are already similar match expressions in the SQLite provider implementation (see example below). Using something similar it should be possible to support Microsoft.Data.Sqlite transactions without breaking System.Data.SQLite.
let getSchema name conn =
match sqliteLibrary with
| SQLiteLibrary.MicrosoftDataSqlite -> customGetSchema name conn
| SQLiteLibrary.AutoSelect ->
#if NETSTANDARD
customGetSchema name conn
#else
getSchemaMethod.Value.Invoke(conn,[|name|]) :?> DataTable
#endif
| _ ->
getSchemaMethod.Value.Invoke(conn,[|name|]) :?> DataTable
from sqlprovider.
That is weird. What if you do new transaction and wrap the datacontext inside it, something like this:
open System.Transactions
[<EntryPoint>]
let main _ =
let scope = new Transactions.TransactionScope(Transactions.TransactionScopeAsyncFlowOption.Enabled)
let ctx = sqLite.GetDataContext()
let parent = ctx.Main.Parent.Create()
parent.Name <- Some "Hello"
ctx.Main.Child.``Create(ParentId)``(100) |> ignore
ctx.SubmitUpdates()
scope.Complete()
0
from sqlprovider.
I tried that but the parent row still gets committed to the database.
from sqlprovider.
I think it might be a problem with Microsoft.Data.Sqlite
itself. Somehow it does not seem to adhere to the System.Transaction scope. I get the same behavior in the example below: when using the System.Data.SQLite
it works as expected, when using Microsoft.Data.Sqlite
it creates the Parent row. Should this be a bug on Microsoft.Data.SQLite
instead?
namespace TransTest
open Microsoft.Data.Sqlite
open System.Data.SQLite
open System.Transactions
module Program =
let [<Literal>] ConnectionString = "Data Source=" + __SOURCE_DIRECTORY__ + "/BugRepro.db;foreign keys=true"
[<EntryPoint>]
let main _ =
use trans = new TransactionScope()
// Using Microsoft.Data.Sqlite
use conn = new SqliteConnection(ConnectionString)
use cmd1 = new SqliteCommand("INSERT INTO Parent(Name) VALUES ('MyName')",conn)
use cmd2 = new SqliteCommand("INSERT INTO Child(ParentId) VALUES (1000)",conn)
// Using System.Data.Sqlite
// use conn = new SQLiteConnection(ConnectionString)
// use cmd1 = new SQLiteCommand("INSERT INTO Parent(Name) VALUES ('MyName')",conn)
// use cmd2 = new SQLiteCommand("INSERT INTO Child(ParentId) VALUES (1000)",conn)
conn.Open()
try
cmd1.ExecuteNonQuery() |> ignore
cmd2.ExecuteNonQuery() |> ignore
trans.Complete()
with
| ex ->
printfn "%s" ex.Message
conn.Close()
0
from sqlprovider.
Definitely it's issue in Microsoft.Data.Sql, but I'm not sure if they fix it or just close it "as designed":
I'd expect the problem is that they don't support System.Transactions as every other .NET database driver, but they have some custom way of doing their transactions.
But you never know, it could maybe easy for them to add a wrapper to their library to implement proper transactions with their current implementation.
The benefits of using System.Transactions is clear: what if you want a transaction over multiple data sources, without them you'd have to write lot of non-happy-path code that is hard to test.
from sqlprovider.
@Thorium based on what you say, do you think it would be a lot of work to adapt SQLProvider to use Microsof.Data.Sql BeginTransaction and EndTransaction methods? From a quick look at the source, it seems relatively simple to add a condition for the transaction scope in the ProcessUpdatesAsync and ProcessUpdates methods of the SQLiteProvider class. I can also do it and send a pull request. What do you think?
from sqlprovider.
Sure, the main concern is will that break System.Data.SQLite.
from sqlprovider.
Related Issues (20)
- [Microsoft,System].Data.SqlClient is not supported on this platform HOT 17
- Issue with connecting to PostgreSQL database HOT 1
- MSSQL stored procedures does not correctly works with schemas
- SQLProvider + PostgreSql works with Rider and VSCode but not visual studio 2022
- Incorrect FROM statement is produced when a table name contains periods HOT 2
- [Question] How to use Pragma with SQLITE? HOT 6
- Simple group by query with postgresql causes doubling up of double quotes when using double quoted declared fields HOT 3
- OnConflict implementation for SQLite provider uses wrong feature of SQLite HOT 3
- `SqlRuntime.DataContext.addCache` timeout on first query when application starts HOT 8
- Auto-generate types by `dotnet publish` time HOT 3
- dotnet 8.0.100-preview.6.23330.14 error. HOT 12
- Performance in SQL Server when comparing `varchar` column against `string` parameter HOT 23
- Is it possible to pull down a partial row and then update it? HOT 1
- Migration guide to 1.3.x HOT 3
- Typeprovider not working when compiled with dotnet build HOT 11
- Could not load file or assembly 'Microsoft.Extensions.Logging.Abstractions, Version=8.0.0.0 HOT 24
- OnConflict.Update ignores None/ValueNone values when using DatabaseProviderTypes.POSTGRESQL
- SQLite provider ResolutionPath fail HOT 8
- Support for DuckDB HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sqlprovider.