Analyze the data audit function of large. NET ERP System

Data audit, English expression is audit, is the process of tracking data changes, record the value before and after the data, for reference analysis. By setting up, ERP can track changes in all fields of a table, or only the value of a specified field can be recorded. European and American companies each year have an independent audit department, from the general manager to the lower section of the staff, a review of the past economic business of the book data and the actual consistency. The auditing function in ERP usually records the change of the value of the next table field. The ERP system makes the data access layer through the LLBL Gen Pro ORM Framework, first understanding the data audit capabilities provided by ORM.

Two important parts of the audit function: Record changes and the actions that lead to changes, persisting changing data.

The work that can be used for auditing:

1 set the property value. Consistent with the framework, changing the value of a property raises the OnChanged event, but it is not audited by setting the value of CurrentValue.

2 Remove the object reference. For example, the sales order instance no longer references the customer entity and removes the customer entity from the Customer collection (entitycollection).

3 Adding an object reference is in contrast to removing an object reference, which means that the property of the object is referenced to another object or to an object collection.

4 Save Entity Call Saveentity method.

5 Update Entity calls the Saveentity method.

6 Deleting an Entity call method deleteentity

7 Gets the property value gets the value of the property or calls the Getcurrentfieldvalue method, but does not audit by getting the value of CurrentValue.

8 Load Entity call method Fetchentity or Fetchentitycollectionnongeneric.

Define an enumeration of the eight scenarios mentioned above:

 Public enum audittype{        Deleteofentity=1,        directdeleteofentities,        directupdateofentities,        Dereferenceofrelatedentity,        referenceofrelatedentity,        entityfieldset,        insertofnewentity,        Updateofexistingentity}

The entity base class ENTITYBASE2 has been defined with the basic functions in eight events, listed below:

1 Onauditentityfieldset Setting property values

2 Onauditdereferenceofrelatedentity Removing object references

3 Onauditreferenceofrelatedentity Adding object references

4 Onauditinsertofnewentity Save entity

Onauditupdateofexistingentity Updating Entities

5 onauditdirectupdateofentities Update Entity

6 onauditdeleteofentity Deleting an entity

Onauditdirectdeleteofentities Deleting Entities

7 Onauditentityfieldget Get Property value

8 Onauditloadofentity Loading Entities

Create a database table auditsetting indicates whether the storage database table is auditing enabled (Audit).

CREATE TABLE  not NULL CONSTRAINT PRIMARY KEY CLUSTERED (    ASC) with (pad_index  off, Statistics_norecompute  off OFF, allow_row_locks on  , allow_page_locks on   [  PRIMARY on [PRIMARY]go

Design three database tables to store values before and after data for data auditing.

--audit record what features the user operates at each point in timeCREATE TABLE[dbo]. [Audit] ([Logno] [bigint] not NULL IDENTITY(1, 1), [Date] [datetime]NULL, [UserId] [nvarchar] (10)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [functioncode] [nvarchar] (8)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [Remarks] [nvarchar] (100)COLLATESQL_Latin1_General_CP1_CI_ASNULL) on[PRIMARY]GOALTER TABLE[dbo]. [Audit]ADD CONSTRAINT[Pk_audit]PRIMARY KEY CLUSTERED([Logno]) with(FILLFACTOR=70) on[PRIMARY]GO--auditTableTable that records the functionality of user actionsCREATE TABLE[dbo]. [Audittable] ([Logno] [bigint] not NULL, [Tableno] [int] not NULL,[Action] [int]NULL, [entityname] [nvarchar] (60)COLLATESQL_Latin1_General_CP1_CI_ASNULL, [KeyValue] [nvarchar] (200)COLLATESQL_Latin1_General_CP1_CI_ASNULL) on[PRIMARY]GOALTER TABLE[dbo]. [Audittable]ADD CONSTRAINT[Pk_audittable]PRIMARY KEY CLUSTERED([Logno], [Tableno]) with(FILLFACTOR=70) on[PRIMARY]GOALTER TABLE[dbo]. [Audittable] with NOCHECK ADD CONSTRAINT[Fk_audittrailtabledetail_audittrail]FOREIGN KEY([Logno])REFERENCES[dbo]. [Audit] ([Logno]) on DELETE CASCADE  on UPDATE CASCADEGO--auditTable columnDetail new and old values for record column valuesCREATE TABLE[dbo]. [Audittablecolumn] ([Logno] [bigint] not NULL, [Tableno] [int] not NULL, [ColumnNo] [int] not NULL, [ColumnName] [nvarchar] (100)COLLATESQL_Latin1_General_CP1_CI_AS not NULL, [OldValue] [ntext]COLLATESQL_Latin1_General_CP1_CI_ASNULL, [newvalue] [ntext]COLLATESQL_Latin1_General_CP1_CI_ASNULL) on[PRIMARY] textimage_on [PRIMARY]GOALTER TABLE[dbo]. [Audittablecolumn]ADD CONSTRAINT[Pk_audittrailcolumndetail]PRIMARY KEY CLUSTERED([Logno], [Tableno], [ColumnNo]) with(FILLFACTOR=70) on[PRIMARY]GOALTER TABLE[dbo]. [Audittablecolumn] with NOCHECK ADD CONSTRAINT[Fk_audittrailcolumndetail_audittrailtabledetail]FOREIGN KEY([Logno], [Tableno])REFERENCES[dbo]. [Audittable] ([Logno], [Tableno]) on DELETE CASCADE  on UPDATE CASCADEGO

Defines a data audit type (facade appearance mode) for data audit operations.

[dependencyinjectioninfo (typeof  (IEntity2),  "Auditortouse" )][serializable]public  sealed  class  databaseauditor:auditorbase, idisposable{ #region  class Me        Mber Declarations private  auditentity _audittrail; private  Audittablecollection _audittrailtabledetails; 

Because I'm using LLBL Gen Pro's adapter mode, we're adding auditing objects to the data access interface.

public  sealed       Class  Dataaccessadapter {private  databaseauditor _auditor; private  void           Initilaizeauditor (IEntity2 entity) {_auditor = new  databaseauditor (); _auditor.     Adapter = this ; } protected  override  void  Dispose ( Span class= "KWRD" >bool  isdisposing) {if  (_auditor! = n Ull ) {_auditor.                Dispose ();            _auditor = null ; }

     } public     overridevoid Commit ()     {           ifnull)           {                _ Auditor. Persistauditinfo ();                _auditor. Dispose ();                null;           }

     }}

Overrides the Commit method, which indicates that the method is called when the database-related operations transaction commits, and is used to hold audit information, which is the pre-and post-change data of the value.

When you save the field data, note that the old value of the table Audittablecolumn above oldvalue and the new Value NewValue field are string types, so you also need to write a method that converts the. NET data type to a value in string format.

How to get the old and new values of an entity property, note that the above code is intercepted with commit, holds the value data, and the value has not changed to the database when the database thing commits. This can be achieved by comparing the new value of the entity's property with the old value of the field in the database.

foreach  (IEntityField2 field in  entity ). Fields) {string  originalvalue = getfieldoriginalvalue (field, false); string  currentvalue = getfieldcurrentvalue (field, false );} 

The method of taking the old value, that is, the Dbvalue,llbl Gen Pro, which takes the ORM attribute field, is very useful in general functional design.

string fieldoldvalue=string. Empty; if null && field. Dbvalue! = DBNull.Value) {     fieldoldvalue= convertvaluetostring (field. Dbvalue, convertzerotoemptystring);}

The last one is the interface of the data audit query result, which helps to understand the design of audit function.

The data audit provides the enterprise's audit department with the convenience, also reduces the system performance, frequently records the field the old value and the new value, increases the transaction processing time.

For some unimportant business data, audit options should be turned off to provide system performance.

Analyze the data audit function of large. NET ERP System