Creating common module/feature for multiple projects

[aashish_9601]

Hi
I wish to Add the feature of "User Audit" (User Logs-For Login,LogOut, Add/Edit/Delete Record) in few of my projects , kindly suggest correct and easiest way to implement the same.

Reagrds
TIA

[Zvoni]

Database. But it depends which DBMS you are using.

User Login/Logout --> separate table?
Add/Edit/Delete Records --> via Triggers in Database itself. No need to do it from the Frontend

[sapator]

No Triggers if you are new to Databases.
Personally I wouldn't use them but I can't confront Zvoni in this.

Some kind of encryption in the user if they will be loggining in.

If it's for a few projects I would suggest to create a new control so you can import it and not having to re do it again and again.

[aashish_9601]

The "Users" which i am referring to are not the same user that logs in to the db (Sql Sever), Actually I use the same "Sql Login" for all the systems.

I have a table in the db that stores the User Info(user id/pwd). The users in this table logs in/out of the software (frontend)..This interaction i need to store(log), along with add/edit/delete record, view report,open forms,etc...

[Zvoni]

The "Users" which i am referring to are not the same user that logs in to the db (Sql Sever), Actually I use the same "Sql Login" for all the systems.

Bad idea in this case.
You are using a so called "Application-user", which probably even has dba-rights.
Use "real" Database-Users, so each SQL-Statement is executed in the context of the User.
In case of auditing you get the "Invoker" of the Statement for free, which with an Application-User it's the same for all Statements.

If you want to stay with your setup, you would have to pass the User with each Statement

I have a table in the db that stores the User Info(user id/pwd). The users in this table logs in/out of the software (frontend)..This interaction i need to store(log), along with add/edit/delete record, view report,open forms,etc...

As i said: Best practice is to let it do the DBMS itself via Triggers, which will be "problematic" with an Application-User

[dday9]

No Triggers if you are new to Databases.
Personally I wouldn't use them but I can't confront Zvoni in this.

Triggers are the devil reincarnate.

Hi
I wish to Add the feature of "User Audit" (User Logs-For Login,LogOut, Add/Edit/Delete Record) in few of my projects , kindly suggest correct and easiest way to implement the same.

Reagrds
TIA

Based on your signature, I assume you know how to interact with the database, so I won't give you direction on how to do the database operations.

Also, just to be clear, logging in and logging out is state management and I would not recommend storing state in a single location. You can and should have the business logic to validate the login in a single location but storing the state related to the logged in user should not.

What I would recommend doing is creating a class library project. Create a new class where the constructor accepts your database connection information. Have the class have a method called ExecuteRawSql where the signature is a string and a paramarray of object. E.g.

Public Class Database
    Private ReadOnly _connectionString As String

    Public Sub New(connectionString As String)
        _connectionString = connectionString
    End Sub

    Public Function ExecuteRawSql(command As String, ParamArray parameters() As Object) As Object
        ' connect
        ' execute the raw sql command with the optional parameters
        ' return the object
    End Function

End Class

Now you'd create classes for each one of your tables where the constructor accepts a database object and in it, you'd have the various operations you'd want to execute, e.g.

Public Class MyTableService
    Private ReadOnly _database As Database
    Private Const TABLE_NAME As String = "MyTable"

    Public Sub New(db As Database)
        _database = db
    End Sub

    Public Function Create(model As MyTableModel) As MyTableModel
        Dim command = $"INSERT INTO {TABLE_NAME} (Field1, etc) VALUES (@0, etc...);"
        Dim createdModel = _database.ExecuteRawSql(command, model.Field1, etc...)
        Return createdModel
    End Function

    Public Function Update(model As MyTableModel) As MyTableModel
        ' ...
    End Function

    Public Sub Delete(model As MyTableModel)
        ' ...
    End Function

End Class

Public Class MyTableModel
    Public Property Id As Integer
    Public Property Field1 As String
    ' etc...
End Class

You could then add this project as a project reference to all your projects and manage a single code base for the database operations.

[aashish_9601]

I think i should i have give more information in my question.
I wish to maintain a log like below

Attachment 193947

"Area" represents the 'Form' not the DB Table

Regards

[TysonLPrice]

I think i should i have give more information in my question.
I wish to maintain a log like below

Attachment 193947

"Area" represents the 'Form' not the DB Table

Regards

I clicked that link and it says "invalid".

[.paul.]

I think i should i have give more information in my question.
I wish to maintain a log like below

Attachment 193947

"Area" represents the 'Form' not the DB Table

Regards

Invalid Attachment specified. There's a known issue with posting images in the forum. The workaround is to click the Go Advanced button (below the post editor, to the right) to get to the advanced editor where you can upload images without the error...

[aashish_9601]

Reposting...

I think i should i have give more information in my question.
I wish to maintain a log like below



"Area" represents the 'Form' not the DB Table

Regards

[sapator]

SO, what is the problem?
from what I read and correct me if I am wrong, you just need to pass a line to the log for every interaction.
Create a DB log table with the columns you specified above and do an insert whenever the user take action.
Caution that the log can get very long if you do that on every interaction so you might do a delete on an SQL job for a few days or months ago.