DataTable.js Tutorial for .Net Core Razor Pages Application - Part 2 - Full CRUD
Tags: dotnet, javascript, tutorial, beginners
Published: 2019-12-12 14:47:18

This is the continuation of the Part 1 tutorial. The main goal here is to apply rendered elements in the datatable, which can contain various HTML tags, such as links/operations with other pages.
*Technologies used:*
1. Javascript, Datatables.js
2. Razor Pages, .Net Core
3. In-Memory Database in .Net Core
*Prerequisites:*
1. Asp.Net Core 2.2  Razor Pages, see suggested learning: https://mydev-journey.blogspot.com/2019/11/razor-pages-not-for-shaving.html
2. In-memory Database, presented in tutorial: https://exceptionnotfound.net/ef-core-inmemory-asp-net-core-store-database/
3. I was inspired by this tutorial: https://www.c-sharpcorner.com/article/using-datatables-grid-with-asp-net-mvc/
4. See Part 1, which is the more simple approach for DataTables: https://dev.to/zoltanhalasz/datatable-js-tutorial-for-net-core-razor-pages-application-part-1-3d76  
5. Link for C# code ( Part 2 Repository, zipped): https://drive.google.com/open?id=1PT9Tk77m2gfZVrFmLwefSt_lqXuYyvEr
6. setup the wwwroot folder, in a similar way as for the Part 1 tutorial
7. you can view the application online: http://datatables.azurewebsites.net/

Steps:
*Create Razor Web Project*
*Create Base Class:*
```csharp
public class InvoiceModel
    {
        [JsonProperty(PropertyName = "ID")]
        public int ID { get; set; }
        [JsonProperty(PropertyName = "InvoiceNumber")]
        public int InvoiceNumber { get; set; }
        [JsonProperty(PropertyName = "Amount")]
        public double Amount { get; set; }
        [JsonProperty(PropertyName = "CostCategory")]
        public string CostCategory { get; set; }
        [JsonProperty(PropertyName = "Period")]
        public string Period { get; set; }   
    }
```
*Create and Populate in-memory database and table*
Create Context:
```csharp
    public class InvoiceContext : DbContext
    {
        public InvoiceContext(DbContextOptions<InvoiceContext> options)
            : base(options)
        {
        }

        public DbSet<InvoiceModel> InvoiceTable { get; set; }
    }
```
Create Invoice Generator Service
```csharp
public class InvoiceGenerator
    {
      public static void Initialize(IServiceProvider serviceProvider)
        {
            using (var context = new InvoiceContext(serviceProvider.GetRequiredService<DbContextOptions<InvoiceContext>>()))
            {
                // Look for any board games.
                if (context.InvoiceTable.Any())
                {
                    return;   // Data was already seeded
                }

                context.InvoiceTable.AddRange(
                new InvoiceModel() { ID=1, InvoiceNumber = 1, Amount = 10, CostCategory = "Utilities", Period = "2019_11" },
                new InvoiceModel() { ID=2, InvoiceNumber = 2, Amount = 50, CostCategory = "Telephone", Period = "2019_12" },
                new InvoiceModel() { ID = 3, InvoiceNumber = 3, Amount = 30, CostCategory = "Services", Period = "2019_11" },
                new InvoiceModel() { ID = 4, InvoiceNumber = 4, Amount = 40, CostCategory = "Consultancy", Period = "2019_11" },
                new InvoiceModel() { ID = 5, InvoiceNumber = 5, Amount = 60, CostCategory = "Raw materials", Period = "2019_10" },
                new InvoiceModel() { ID = 6, InvoiceNumber = 6, Amount = 10, CostCategory = "Raw materials", Period = "2019_11" },
                new InvoiceModel() { ID = 7, InvoiceNumber = 7, Amount = 30, CostCategory = "Raw materials", Period = "2019_11" },
                new InvoiceModel() { ID = 8, InvoiceNumber = 8, Amount = 30, CostCategory = "Services", Period = "2019_11" },
                new InvoiceModel() { ID = 9, InvoiceNumber = 8, Amount = 20, CostCategory = "Services", Period = "2019_11" },
                new InvoiceModel() { ID = 10, InvoiceNumber = 9, Amount = 2, CostCategory = "Services", Period = "2019_11" },
                new InvoiceModel() { ID = 11, InvoiceNumber = 10, Amount = 24, CostCategory = "Services", Period = "2019_11" },
                new InvoiceModel() { ID = 12, InvoiceNumber = 11, Amount = 10, CostCategory = "Telephone", Period = "2019_11" },
                new InvoiceModel() { ID = 13, InvoiceNumber = 12, Amount = 40, CostCategory = "Consultancy", Period = "2019_12" },
                new InvoiceModel() { ID = 14, InvoiceNumber = 13, Amount = 50, CostCategory = "Services", Period = "2019_11" },
                new InvoiceModel() { ID = 15, InvoiceNumber = 14, Amount = 40, CostCategory = "Utilities", Period = "2019_11" },
                new InvoiceModel() { ID = 16, InvoiceNumber = 15, Amount = 10, CostCategory = "Services", Period = "2019_11" });
                context.SaveChanges();
            }
        }
```
*Register the database*
**within Startup cs**, above add MVC command:
```csharp
            services.AddDbContext<InvoiceContext>(options => options.UseInMemoryDatabase(databaseName: "InvoiceDB"));
```
**within Program Cs**, we need to make changes, see final version:
```csharp
 public class Program
    {
        public static void Main(string[] args)
        {


            var host = CreateWebHostBuilder(args).Build();

            //2. Find the service layer within our scope.
            using (var scope = host.Services.CreateScope())
            {
                //3. Get the instance of BoardGamesDBContext in our services layer
                var services = scope.ServiceProvider;
                var context = services.GetRequiredService<InvoiceContext>();

                //4. Call the DataGenerator to create sample data
                InvoiceGenerator.Initialize(services);
            }
            //Continue to run the application
            host.Run();
            //CreateWebHostBuilder(args).Build().Run();
        }

        public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>();
    }
```
*Using EF, the tables are populated in all Pages, see example for Index:*
**PageModel:**
```csharp
  public class IndexModel : PageModel
    {
        private InvoiceContext _context;

        public List<InvoiceModel> InvoiceList;
        public IndexModel(InvoiceContext context)
        {
            _context = context;
        }
        public void OnGet()
        {
            InvoiceList = _context.InvoiceTable.ToList();
        }
    }
```
**CSHTML file**
will be a simple listing of the InvoiceTable using foreach (actually you can scaffold this view)
*DataTableArrayRender page:*
Will contain the datatable js code, together with the rendered html elements:
```html
@page
@model DataTableArrayRenderModel
@{
    ViewData["Title"] = "Invoice List - With Datatable - from Javascript Array";
}

    <div class="text-center">
        <h1 class="display-4">Show DataTable - from Javascript Array -  Rendered Columns</h1>
        <p>
            <a asp-page="Index">Show original Table (Html from Razor)</a>
        </p>
        <p>
            <a asp-page="InvoiceAdd" class="btn btn-info">Add New Invoice</a>
        </p>
    </div>

<script type="text/javascript" language="javascript" src="~/lib/jquery/dist/jquery.min.js"></script>
<script src="~/js/jquery.dataTables.min.js"></script>

<script>
    /////////
    function convertToDataSet(responseJSON) {
        console.log(responseJSON);
        var returnList = [];
        var returnitem = [];
        for (var i = 0; i < responseJSON.length; i++) {
            console.log(responseJSON[i]);
            returnitem = [];
            returnitem.push(responseJSON[i].ID);
            returnitem.push(responseJSON[i].InvoiceNumber);
            returnitem.push(responseJSON[i].Amount);
            returnitem.push(responseJSON[i].CostCategory);
            returnitem.push(responseJSON[i].Period);
            returnList.push(returnitem);
        }
        return returnList;
    }

    function getTable() {
        return fetch('./DataTableArrayRender?handler=ArrayDataRender',
            {
                method: 'get',
                headers: {
                    'Content-Type': 'application/json;charset=UTF-8'
                }
            })
            .then(function (response) {
                if (response.ok) {
                    return response.text();
                } else {
                    throw Error('Response Not OK');
                }
            })
            .then(function (text) {
                try {
                    return JSON.parse(text);
                } catch (err) {
                    throw Error('Method Not Found');
                }
            })
            .then(function (responseJSON) {
                var dataSet = convertToDataSet(responseJSON);
                console.log(dataSet);
                $(document).ready(function () {
                    $('#example').DataTable({
                        data: dataSet,
                        "processing": true, // for show progress bar
                        "filter": true, // this is for disable filter (search box)
                        "orderMulti": false, // for disable multiple column at once
           
                        columns: [
                            { title: "ID" },
                            { title: "InvoiceNumber" },
                            { title: "Amount" },
                            { title: "CostCategory" },
                            { title: "Period" },
                            {
                                data: null, render: function (data, type, row) {                                 
                                    return '<a class="btn btn-danger" href="/InvoiceDelete?id=' + row[0] + '">Delete</a>';
                                }
                            },
                            {
                                "render": function (data, type, full, meta)
                                { return '<a class="btn btn-info" href="/InvoiceEdit?id=' + full[0] + '">Edit</a>'; }
                            },
                            {
                                "render": function (data, type, full, meta)
                                { return '<a class="btn btn-warning" href="/Index">Main Page</a>'; }
                            },
                        ]
                    });
                });
            })
    }
    getTable();
</script>
<table id="example" class="display" width="100%"></table>
```
Using the InvoiceModel, we can scaffold all pages like Delete, Create, Edit using EF model scaffolding of Razor Pages.
*The end result will be a nice navigation table that besides the invoice data, will contain the rendered buttons/links.*
End result:
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/peb9xhy6ps30fz31tlhw.jpg)
**I hope you enjoyed this tutorial.**
Further reading: https://www.c-sharpcorner.com/article/jquery-datatables-with-asp-net-core-server-side-dynamic-multiple-column-searchin/