Mobile Log In
       Where ERP Meets the Web

Solving the Biggest Challenges Using OO .NET Transaction Construct & SQL
  • Mission Critical / Industrial Strength Power and Scalability
  • for those SQL update processes active for multiple users and requiring the highest level of accuracy
  • simplify concurrency considerations

  • Solve intermittent problems such as inconsistent run times in SQL processes
  • Could row locking decisions made behind the scenes by your database management software be the culprit?
  • proactively control which records are locked and which can be read by other update processes
  • Let us tell you about this technique to help you get into tune with these facets.
  • Basically, all data access, update and inquiry processes are encapsulated into 8 stored procedures per file.  This MSSQL set of of stored procedures is in use at EnvironmentMasterInquiry.aspx.

  • Environment Master file stored procedures list on MSSQL

  • Nail down row locking with our technique and gain these benefits
  • attempt lock for update for all files before initiating transaction reduces rollback situations (when updates to 2nd or 3rd file in line cannot be completed) (pessimistic row locking)
  • eliminate ability or need for other transactions to read uncommitted data or interim transaction results (dirty reads) and vice-versa

  • Technique does not Preclude Use of DB Constraints & Rules
  • but in some cases detecting lock conditions that would cause roll-back midstream
  • use when a two-phase or two-pass approach is needed
  • use in high volume situations where overlays are occurring
  • use for types of updates where frequent rollback is costly and/or inefficient
  • simplify concurrency considerations. This technique:
  • uses optimistic row locking to prevent overlays even at the column level (goes beyond techniques using the record level timestamp for example)
  • uses pessimstic row locking to explicitly lock rows (from other update processes) in multiple files between initial access and actual update
  • Boom!, you've got something super solid...

  • Access MS SQL Server or IBM Db2 for i or Other DBs* 
  • from c# or VB.NET or 3rd Party .NET Languages in Code-Behind
  • essentially a data access layer is organized into 8 stored procedures (per file)... Here is a list of SQL PL stored procedures on Db2 for i (in use at DigitalAssetDeliveryEventInquiry.aspx).

  • Digital Asset Delivery Event file stored 
                                procedures list on Db2 for i

  • Solve classic problems in a straight-forward manner
  • when inventory levels or detection of stock-out conditions need to be real time accurate
  • inventory quantity allocation at order entry time
  • pick/pack sheet print
  • when it is costly to POS sell more than you have
  • processes involving large numbers of order line updates and order header status changes
  • next unique invoice number assignment

  • Technique
  • Note: Use of this transaction technique from c#, VB.NET or Visual RPG for .NET allows commit and rollback boundaries to span across multiple stored procedure calls and SQL statements

  • Connect
  • Start Transaction
  • MS SQL Server Database Example:
  • using System.Data.SqlClient;

  • System.Data.SqlClient.SqlTransaction sqltxEnvironmentMasterRowUpdate = null;

  • sqltxEnvironmentMasterRowUpdate = cnnsvcMSSQL.BeginTransaction();

  • Db2 for i Database Example:
  • using IBM.Data.DB2.iSeries;

  • IBM.Data.DB2.iSeries.iDB2Transaction sqltxDigitalAssetDeliveryUIEventRowUpdate = null;

  • sqltxDigitalAssetDeliveryUIEventRowUpdate = cnnsvcDB2iSQL.BeginTransaction();

  • Prevent Interim Result Overlays - Access Data & Hold Pessimistic Row Lock(s)
  • Verify No Data Overlay Conditions (otherwise message user and rollback)
  • Start Update(s)
  • Commit if Successful, Otherwise Rollback
  • Dispose and Disconnect

Microsoft SQL Server picture (for illustrative purposes only)

DB2 for IBM i picture (for illustrative purposes only)

other databases illustration

database performance illustration via IBM Navigator for i

  • Let us help you write these type of transactions
  • we can work with you to formulate a version of this technique for you based on your in-house standards

        go to the home page Top of Application Software     go to next series page Next in Application Software     Site Map     Switch to
Mobile View

You are at the web site of Tegratecs Development Corp.  Click here to go to the home page of this site...
Integrity, Integration and ROI from Your Software
Our contact information:
Tegratecs Development Corp.
1320 Tower Road
Schaumburg, IL 60173
( please register or sign-in )
© 2018 Tegratecs Development Corp.