Documentation for CRUD in the Database

[jonkas2211]

Hello,

is there any documentation on how to interact with the database through OC?
I couldnt find any, I had to read the poor documented source code and that is not how it supposed to be.

I found out that the ContentManager, I think so, is dealing with creating ContentItems, but the system isnt really documented so its a bit tricky to find out , how to use it.

If there is any documentation then please let me know, because I really want to use OrchardCore for my following projects, but without documentation I cant.

Thanks :)

[zl2fxy]

https://github.com/sebastienros/yessql

[pgy866]

http://sqlitebrowser.org/
This tool should also help you, you can view the database

[jonkas2211]

Thanks for your Response @zl2fxy. So does that mean that i have to deal it myself?
Is there in Orchard a way to get the current Database Connection?

[zl2fxy]

you can't do it by yourself,the orchardcore has many good supplies,you can download it(yessql) and use it for orchardcore, Ctrl+F12,I think you can find something of you needs;

[jonkas2211]

Yes, I meaned YesSql. @zl2fxy Do you have an idea where i can get the database connection?
Is it just the ISession?

[matiasmolleja]

You should take a look at the source code. One good, simple enough, example could be the Deployment admin controller.
You can repro the same thing in your own module as a learning exercise.

The controller gets ISession through DI and has action methods for each CRUD operation of "DeploymentPlan".

https://github.com/OrchardCMS/OrchardCore/blob/dev/src/OrchardCore.Modules/OrchardCore.Deployment/Controllers/DeploymentPlanController.cs

Remember that in order for this to work you need to add an index provider, required by yesql:
https://github.com/OrchardCMS/OrchardCore/blob/dev/src/OrchardCore.Modules/OrchardCore.Deployment/Indexes/DeploymentPlanIndex.cs

And you will need a migration to create the table for your object:
https://github.com/OrchardCMS/OrchardCore/blob/dev/src/OrchardCore.Modules/OrchardCore.Deployment/Migrations.cs

Index and migration will need the corresponding services registered in startup.cs

services.AddSingleton<IIndexProvider, DeploymentPlanIndexProvider>(); services.AddTransient<IDataMigration, Migrations>();

With this setup your table should be created when you first enable your module using the admin.

[jonkas2211]

Thank you @matiasmolleja, thats are some great instructions I appreciate it 🥇

[sebastienros]

When you mention CRUD it's not clear if you mean with the Document model or raw tables. Both are doable, @matiasmolleja gave you some examples for the document model. You can also create flat tables using Dapper, which we do for instance in the Indexing module. You just get the connection from an ISession instance then do any SQL you want with it.

[jonkas2211]

Thank you for the respond @sebastienros , so I have the chance to use direct SQL and/or I would use your YesSql project. Is there any instance for YesSql that is shared among the different modules?
If not I think I just have to create a new with help of the ISession.

And btw, if you are planning to translate the documentation to German I would willing to help out there, because my native language is German.

[jonkas2211]

@matiasmolleja is there any documentation on Migrations? I couldnt find any in the YesSql Repository.

[Skrypt]

Migrations are in OC

[sebastienros]

@jonkas2211 The ISession instance is provided by the DI and is unique to all modules during a request.

[sebastienros]

What is doable is to inject the IDbConnection in the DI such that any component can resolve it.

[mdockal]

@sebastienros would it be possible to create an ExtendedSession object that inherits from Session and inject that Orchard Core instead of the Session Object? The ExtendedSession object could have a generic Save<T>(T entity), Delete<T>(T Entity), and etc. which would save/delete the entity into/from its own table instead of the document table. In order to use extend Session one would need to make the DbConnection protected so ExtendedSession can access it. I don't know if you would want to add the Save<T>(T entity) and other CRUD methods to YesSql since that may be beyond the scope what you are trying to do with it. However, if we make DBConnection protected and are able to replace the injected Session object I think it would be cool. I am running into a similar issue where I would like to add custom tables to orchard core separate from the Document table. This is the one thing that is kindof holding me back from using OrchardCore. You have some pretty cool stuff inside YesSql that could be utilized to create tables / saving data instead of using Dapper directly. Dapper doesn't save data to tables unless you use something like Dapper.Contrib. I would be happy to work with you on a PR to extend YesSql.

[mdockal]

What is doable is to inject the IDbConnection in the DI such that any component can resolve it.

@sebastienros You have created a better framework in YesSql for saving data than Dapper.Contrib or any of the other Dapper Project. The Dapper base project doesn't save data to the database unless you write the raw SQL yourself.

For instance, I could create a GenericCommmand<T> base class and use that to create Generic CRUD commands.

    public abstract class GenericCommand<TEntity> : IIndexCommand
    {
        public GenericCommand(TEntity entity)
        {
            Entity = entity;
        }

        protected static readonly PropertyInfo[] AllProperties = new PropertyInfo[]
        {
            typeof(TEntity).GetProperty("Type")
        };

        protected static readonly PropertyInfo[] AllKeys = new PropertyInfo[]
        {
            typeof(TEntity).GetProperty("Id")
        };

        public abstract int ExecutionOrder { get; }
        

        public TEntity Entity { get; }

        public abstract Task ExecuteAsync(IDbConnection connection, IDbTransaction transaction, ISqlDialect dialect);
    }

   public class CreateCommand<TEntity> : GenericCommand<TEntity>
   {
       private string _tablePrefix;

       public override int ExecutionOrder => 0;

       public CreateCommand(TEntity entity, string tablePrefix) : base(entity)
       {
           _tablePrefix = tablePrefix;
       }

       public override Task ExecuteAsync(IDbConnection connection, IDbTransaction transaction, ISqlDialect dialect)
       {            
           var prefixedName = CollectionHelper.Current.GetPrefixedName(typeof(TEntity).Name);
           var sqlText = string.Format("insert into {0} ({1}) values ({2});",
               dialect.QuoteForTableName(_tablePrefix + prefixedName),
               string.Join(", ", AllProperties.Select(c => dialect.QuoteForColumnName(c.Name))),
               string.Join(", ", AllKeys.Select(c => "@" + c.Name)));
           return connection.ExecuteScalarAsync<int>(sqlText, Entity, transaction);
       }       
   }