Save and retrieve data from custom data

[salik634]

Can anyone tell me how to create custom module that can interact with custom table in which i can create, update and retrieve records from database.
Thanks

[Piedone]

This is a bit complex question so I encourage you to first learn the basics of Orchard development. For this, check out the docs and the Training Demo module.

[salik634]

Actually i need a audit trail feature and i am unable to find it in a project repository if there any repository with feature kindly share.

[Piedone]

As I've replied on YouTube, you can check it out under this PR.

[netwavebe]

Does it need to be a custom sql table? Or do you want to store custom data in de documents table? Check out the documentation of YesSql by Sebastian Ros: https://github.com/sebastienros/yessql/wiki/Tutorial#persisting-document

OrchardCore uses YesSql to store data. I used this to store/retrieve order data.

[mariojsnunes]

Hello, I had this necessity as well. The Document table was not good enough for the query freedom I wanted.
This is my code:

Creating a table:

Need these services:
ServiceProvider serviceProvider;
YesSql.IStore store;

// Creating a table
if (!store.TableExists("tableName")))
{
    using (DbConnection connection = store.Configuration.ConnectionFactory.CreateConnection())
    {
        connection.Open();

        using (DbTransaction transaction = connection.BeginTransaction(store.Configuration.IsolationLevel))
        {
            SchemaBuilder builder = new SchemaBuilder(store.Configuration, transaction);

            builder.CreateTable(...);

            transaction.Commit();
        }
    }
}

IStore extension to check if table exists

// IStore extension
    public static bool TableExists(this IStore store, string tableName)
    {
        string tableWithPrefix = string.IsNullOrWhiteSpace(store.Configuration.TablePrefix) ? tableName : $"{store.Configuration.TablePrefix}_{tableName}";

        using (DbConnection connection = store.Configuration.ConnectionFactory.CreateConnection())
        {
            connection.Open();

            using (DbCommand command = connection.CreateCommand())
            {
                // To support tests execution in sqlite
                if (store.Configuration.ConnectionFactory.DbConnectionType == typeof(Microsoft.Data.Sqlite.SqliteConnection))
                {
                    command.CommandText = $"SELECT name FROM sqlite_master WHERE type='table' AND name = '{tableWithPrefix}';";
                }
                else
                {
                    command.CommandText = $"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '{tableWithPrefix}';";
                }

                using (DbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        return true;
                    }
                }
            }
        }

        return false;
    }

Query the table:

public static async Task<MyEntity> Get(IDbConnectionAccessor _dbAccessor, ShellSettings shellSettings)
{
    using (DbConnection connection = _dbAccessor.CreateConnection())
    {
        if (connection.State == ConnectionState.Closed)
        {
            await connection.OpenAsync();
        }

        string selectCommand = $@"SELECT * FROM {tableName}";

        MyEntity myEntity = await connection.QueryFirstOrDefaultAsync<MyEntity>(selectCommand);

        return myEntity;
    }
}

Hope it helps!