Skip to content
This repository has been archived by the owner on May 10, 2019. It is now read-only.
/ blog-database Public archive

A CIS 560 Databases project using ASP.net core with an SQL database

Notifications You must be signed in to change notification settings

Afulton11/blog-database

Repository files navigation

Archived

This repository has been archived as it was a group project as part of the CIS-560 Database Management class at Kansas State University. The project has been left in the same state as when it was turned in.

Blog Database

This repository is a simple skeleton for a blog with a database. Created as a part of a CIS-560 project at Kansas State University.

Commit Guideline

Title: [Project-Name] 'Short Description of what you did'

  • Also, reference the issue the commit relates to using "#{issue-number}" that way when we look at the issue, we can see all the commits related to that specific issue.

Coding Guidelines

todo: discuss: common coding practices/guidelines so that we all have consistent formatting throughout each commit

Dependency Graph

Dependency Graph

System Design

System Design

Database Design

todo: add design diagram and explain it

CQRS (Command Query Responsibility Segregation)

Useful Articles

Creating a new Command or Query in C# to interact with the Database

Command

For this example we will create a Command that will create a point for a given userId by calling a procedure in a sql database.

The SQL Procedure might look something like this:

CREATE OR ALTER PROCEDURE Blog.CreatePoint
(
     @UserId INT
)
BEGIN
     ...
END
GO
  • We can see that the procedure uses the SCHEMA Blog and is named CreatePoint
    • The full name of the query is Blog.CreatePoint
  • It also has 1 input parameter: @UserId
    • This parameter is of type int and is labeled UserId, so it probably wants the Id property on a User i.e. User.UserId

Command Model

  • A Command Model is a POCO (Plain Old Common Language Runtime Object

The Command Model might look something like this:

public class CreatePointCommand : ICommand
{
     [Required]
     public int UserId { get; set; }
}
  • We create a class whose properties are equivalent to the parameters of the SQL procedure.
  • the UserId uses the Required attribute because the SQL procedure will not be able to create a point without it.

Command Service

  • A Command Service is a simple class that performs an action on the Command Model

UPDATE TO QUERY SERVICES!!

  • see #27

The Command Service might look something like this:

public class CreatePointCommandService : ICommandService<CreatePointCommand>
{
     private readonly IDatabase database;
     
     public CreatePointCommandService(IDatabase database)
     {
          EnsureArg.IsNotNull(database, nameof(database));
          
          this.database = database;
     }
     
     public void Execute(CreatePointCommand command)
     {
          database.TryExecute((transaction) =>
          {
               var procedure = database.CreateStoredProcedure("Blog.CreatePoint", transaction);
               var parameter = database.CreateParameter("@UserId", command.UserId);
               
               procedure.Parameters.Add(parameter);
               
               database.Execute(procedure);
          });
     }
}
  • First we will create our constructor that takes in a IDatabase.
    • this parameter will be injected by our Dependency Injection library at runtime automatically.
  • Then, in our Execute method we will try to execute a transaction.
    • Using the transaction created, we will then create a call to our stored procedure Blog.CreatePoint passing in the command model's properties as the arguments to that procedure.
  • Finally, we make sure to execute that procedure call we just created on the database within the scope of the try execute transaction.

Using the base class DbCommandService we can simplify this class to be the following:

public class CreatePointCommandService : DbCommandService<CreatePointCommand>
{     
     public CreatePointCommandService(IDatabase database) : base(database)
     {
     }
	 
	 protected override IEnumerable<IDataParameter> GetParameters(CreatePointCommand command)
	 {
		yield return Database.CreateParameter("@UserId", command.UserId);
	 }
	 
	 protected override string ProcedureName => "Blog.CreatePoint"
}

Query

  • A Query is similar to command, but returns a result.

For this example we will create a Query that will return a list of articles for a given authorId by calling a procedure in a sql database.

The SQL Procedure might look something like this:

CREATE OR ALTER PROCEDURE Blog.GetAuthorArticles
(
     @AuthorId INT,
	 @PageIndex INT,
	 @PageSize INT,
)
BEGIN
     SELECT *
	 FROM Blog.Article Article
	 WHERE Article.AuthorId = @AuthorId
	 ... Paging ...
END
GO
  • We can see that the procedure uses the SCHEMA Blog and is named GetAuthorArticles
    • The full name of the query is Blog.GetAuthorArticles
  • It also has 1 input parameter: @AuthorId
    • This parameter is of type int and is labeled AuthorId, so it probably wants the Id property on a Author i.e. Author.AuthorUserId

Query Model

  • A Query Model is a POCO (Plain Old Common Language Runtime Object

The Query Model might look something like this:

public class FetchUserArticlesQuery : IQuery<Paged<Article>>
{
     [Required]
     public int AuthorId { get; set; }
	 public PageInfo Paging { get; set; } = new PageInfo();
}
  • We create a class whose properties are equivalent to the parameters of the SQL procedure.
  • It implements the IQuery<Paged<Article>> interface because this query model will be used to fetch a paginated list of Article
  • the AuthorId uses the Required attribute because the SQL procedure will not be able to get a list of articles without it.
  • the Paging property is added here because we don't always fetch every single article that user has created at once.
    • Paging allows us to fetch only a certain amount of articles at a time.

Query Service

  • A Query Service is a simple class that performs a function on the Query Model and returns a result.

UPDATE TO QUERY SERVICES!!

  • see #26

The Query Service might look something like this:

public class FetchAuthorArticlesQueryService : IQueryService<FetchUserArticlesQuery, Paged<Article>>
{
     private readonly IDatabase database;
	 private readonly IReader<Article> articleReader;
     
     public FetchAuthorArticlesQueryService(IDatabase database, IReader<Article> articleReader)
     {
          EnsureArg.IsNotNull(database, nameof(database));
          EnsureArg.IsNotNull(articleReader, nameof(articleReader));
          
          this.database = database;
          this.articleReader = articleReader;
     }
     
     public Paged<Article> Execute(FetchUserArticlesQuery query)
     {
          return database.TryExecute((transaction) =>
          {
               var procedure = database.CreateStoredProcedure("Blog.GetAuthorArticles", transaction);
               var authorIdParameter = database.CreateParameter("@AuthorId", query.AuthorId);
               var pageIndexParameter = database.CreateParameter("@PageIndex", query.Paging.PageIndex);
               var pageSizeParameter = database.CreateParameter("@PageSize", query.Paging.PageSize);
               
               procedure.Parameters.Add(authorIdParameter);
               procedure.Parameters.Add(pageIndexParameter);
               procedure.Parameters.Add(pageSizeParameter);
               
               return database.ExecuteReader(procedure, (dataReader) => new Paged<Article>
			   {
				   Paging = query.Paging,
				   Items = articleReader.Read(dataReader)
			   });
          });
     }
}
  • First we will create our constructor that takes in a IDatabase and a IReader<Article>.
    • these parameters will be injected by our Dependency Injection library at runtime automatically.
    • the IReader<Article> is used to read in Articles from the database into memory.
  • Then, in our Execute method we will try to execute a transaction.
    • Using the transaction created, we will then create a call to our stored procedure Blog.GetAuthorArticles passing in the query model's properties as the arguments to that procedure.
  • Finally, we make sure to execute that procedure call we just created on the database within the scope of the try execute transaction.
    • This will execute the procedure and read the results using our articleReader, then return the new Paged list of Articles.

Using the base class DbPagedQueryService we can simplify this class to be the following:

    public class FetchAuthorArticlesQueryService :
        DbPagedQueryService<FetchArticlesByCategoryQuery, Article>,
        IFetchArticlesByCategoryQueryService
    {
        private readonly IReader<Article> articleReader;
        public FetchAuthorArticlesQueryService(IDatabase database, IReader<Article> articleReader)
            : base(database)
        {
            EnsureArg.IsNotNull(articleReader, nameof(articleReader));

            this.articleReader = articleReader;
        }

        protected override IEnumerable<Article> ReadItems(IDataReader dataReader) =>
            articleReader.Read(dataReader);

        protected override IEnumerable<IDataParameter> GetQueryParameters(FetchArticlesByCategoryQuery query)
        {
            yield return Database.CreateParameter("@AuthorId", query.AuthorId);
        }

        protected override string ProcedureName => "Blog.GetArticlesByCategory";
    }

This base class will automatically handle the paginated parameters so they do not need to be added to the GetQueryParameters method.

For queries there is another base class DbQueryService which is very similar to DbPagedQueryService except pagination is not automatically added to the query parameters.

About

A CIS 560 Databases project using ASP.net core with an SQL database

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Contributors 4

  •  
  •  
  •  
  •