SQL server transaction manager alternatives
We currently have a vb.net application that is connected to a SQL server database through LINQ, table adapters and stored procedures.
For a while now we have been getting a lot of problems with things like double adjustments sale not processing etc - (When a sale is processed for example the stored procedure for the sale are run twice, other times is does not run at all and most of the time the sale is processed correctly, we think this could be caused by deadlocks but we are unsure. When a sale is processed, we sometimes get that the stock has been adjusted incorrectly – double adjustments. These problems are currently occurring at our bigger clients).
Someone has mentioned making use of a transaction manger. The only one that I could find that is compactable with SQL server was Java database Connectivity (JDBC). Are there other transaction manager options we can use?
Or must we fix all the SQL in our application using SQL transactions?
For a while now we have been getting a lot of problems with things like double adjustments sale not processing etc - (When a sale is processed for example the stored procedure for the sale are run twice, other times is does not run at all and most of the time the sale is processed correctly, we think this could be caused by deadlocks but we are unsure. When a sale is processed, we sometimes get that the stock has been adjusted incorrectly – double adjustments. These problems are currently occurring at our bigger clients).
Someone has mentioned making use of a transaction manger. The only one that I could find that is compactable with SQL server was Java database Connectivity (JDBC). Are there other transaction manager options we can use?
Or must we fix all the SQL in our application using SQL transactions?
lcohan
Why/what would you prevent to use rather somewhat "native" ADO.NET to connect to Microsoft SQL Server instead of JDBC or other drivers? Please see details here https://support.microsoft.com/en-us/help/308656/how-to-open-a-sql-server-database-by-using-the-sql-server-net-data-pro about how to open a SQL Server Database connection by using the SQL Server .NET Data Provider with Visual Basic .NET
ASKER
We do not have a problem with connecting to the database. We can connect and execute queries on the database.
Our main problem is that we need something to manage all the queries to the database for example if we do a sale we have a sale table that the sale needs to be entered into, then we have a sale detail that with all the line items of the sale and we need to adjust the stock according to the sale.
So we sometimes find that the sale goes through but some of the sale details was lost or in other cases we have the sale and sale detail but so inventory adjustment was done.
In other words we need a way of either the whole sale goes through or the whole sale fails.
Can we solve this problem using transactions in our .net application or is there other solutions?
Our main problem is that we need something to manage all the queries to the database for example if we do a sale we have a sale table that the sale needs to be entered into, then we have a sale detail that with all the line items of the sale and we need to adjust the stock according to the sale.
So we sometimes find that the sale goes through but some of the sale details was lost or in other cases we have the sale and sale detail but so inventory adjustment was done.
In other words we need a way of either the whole sale goes through or the whole sale fails.
Can we solve this problem using transactions in our .net application or is there other solutions?
Ahh....so there must be some coding issues/untrapped errors most likely that are causing the failures you mentioned above plus on the database side maybe some missing relationships/constraints that allows this to hapen. Regardless of the exact cause I believe you're looking for is ORM (Object Relational Mapping) and Entity Framework is an ORM framework. I would also add that LINQ is a standalone framework that can be used to query data data, is easy to use and will fit into most projects rather easily however on the downside, it can be misused more easily than EF. Aside all that if I'm not mistaken the LINQ to SQL was declared as and end of the road technology prior to release of EF1.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.