Click here to Skip to main content
14,355,571 members

How to Build A CRUD Application using .NET Core, SQL, and HTML with JavaScript

Rate this:
2.67 (3 votes)
Please Sign up or sign in to vote.
2.67 (3 votes)
28 Sep 2019CPOL
Here, I am building a CRUD application using .NET Core, Entity Framework for SQLserver (back-end), and HTML (front-end) with JavaScript to consume that web service.

Introduction

In this article, I am going to illustrate an end-to-end solution (service > back-end > front-end) to create a new .NET Core web service and consume that service by the front-end. I will be demonstrating step-by-step how to develop a RESTful Web Service application in ASP.NET Core and connect that service to SQL Server using Entity Framework and consume by front-end UI developed in HTML using JavaScript.

Using the Code

I will be creating a Training Service as an example in my complete course to demonstrate Web Service, Entity Framework, and a few front-end concepts. The front-end will provide a UI to list all the training details in table form using REST API and another form with input fields as name, start date, end date, and confirm Submit button. On confirmation, the REST API should save the new training details into the database.

Let's start with Visual Studio to create a new web service (REST) application using .NET Core.

  1. Open VS, go to File > New and select Project > ASP.NET Core Web Application. Select the solution name (e.g., Training service) and the location of your local directory. Click OK.

    Image 1

  2. Select the API for creating REST API using default example valuecontroller (can be deleted later). You can uncheck the 'Configure for HTTPS' checkbox because that creates SSL-based project and you have to use HTTPS instead of HTTP in your URLs (this is an optional step).

    Image 2

  3. Click OK and you should see the below project structure:

    Image 3

  4. Let's setup the Entity framework Model for SQL Server to create one table, i.e., tblTraining having three properties, Name(char[256)), StartDate(datetime), EndDate(datetime).
    CREATE TABLE [dbo].[TblTraining] (
    [Name] CHAR (256) NOT NULL,    
    [StartDate] DATETIME NOT NULL,    
    [EndDate] DATETIME NULL,    
    PRIMARY KEY CLUSTERED ([Name] ASC)); 

    You can also use VS to setup entity framework and setup the Model, I am not adding more steps here for entity framework and it is not the scope of this article. Once your Model is ready, take the connection string.

    If you already have any existing framework, then just add this new table for test and execute the below command:

    scaffold-DBContext "your connection string which start with Data Source= ……………….." 
    Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -tables tblTraining
         public partial class tempdbContext : DbContext
         {
            public tempdbContext()
            {
            }
    
            public tempdbContext(DbContextOptions<tempdbContext> options)
                : base(options)
            {
            }
    
            public virtual DbSet<TblTraining> TblTraining { get; set; }
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                if (!optionsBuilder.IsConfigured)
                {
    #warning To protect potentially sensitive information in your connection string, 
    #you should move it out of source code. 
    #See http://go.microsoft.com/fwlink/?LinkId=723263 
    #for guidance on storing connection strings.
                    optionsBuilder.UseSqlServer
                          (@"your connection string starts with Data Source=...........");
                }
            }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.HasAnnotation("ProductVersion", "2.2.4-servicing-10062");
    
                modelBuilder.Entity<TblTraining>(entity =>
                {
                    entity.HasKey(e => e.Name);
    
                    entity.Property(e => e.Name)
                        .HasMaxLength(256)
                        .IsUnicode(false)
                        .ValueGeneratedNever();
    
                    entity.Property(e => e.EndDate).HasColumnType("date");
    
                    entity.Property(e => e.StartDate).HasColumnType("date");
                });
            }
        }
    
         public partial class TblTraining
        {
            public string Name { get; set; }
            public DateTime StartDate { get; set; }
            public DateTime? EndDate { get; set; }
        }
    
        public class TrainingViewModel
        {
            public string EndDate { get; set; }
    
            public string StartDate { get; set; }
    
            public string Name { get; set; }
        }
  5. Open the Startup.cs and setup the connection with the database for this webservice. In my case, my database name is tempdb which created tempdbContext. Add the below code in your ConfigureServices method.
    // This method gets called by the runtime. 
    // Use this method to add services to the container.
            public void ConfigureServices(IServiceCollection services)
            {
                services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
    
                var connection =@"your connection string starts with Data Source=...........";
                services.AddDbContext<tempdbContext>(o => o.UseSqlServer(connection));
    
                services.AddScoped<ITrainingServices, TrainingServices>();
            }
  6. Next, you can use the internal services/helper concept to read/validate the data when service requests for it. I have created ITrainingServices interface and corresponding service class TrainingServices which use database context to manage the data. Using this approach, you can also create one local service (LocalTrainingServices) which is independent of db context, and keeps values in memory for testing. If you have not established entity framework yet in that case, you can use this local service as Scope in startup.cs. Replace AddScoped<> line in startup.cs with the below code:
    services.AddScoped<ITrainingServices, LocalTrainingServices>(); 
     public interface ITrainingServices
        {
            Task<IList<TrainingViewModel>> GetTrainingData();
    
            TrainingViewModel GetTrainingData(string name);
    
            bool CreateTraining(TrainingViewModel viewModel);
    
            void Delete(string name);
        }
    
    public class TrainingServices : ITrainingServices
        {
            private readonly tempdbContext dbcontext;
    
            public TrainingServices(tempdbContext context)
            {
                this.dbcontext = context;
            }
    
            public async Task<IList<TrainingViewModel>> GetTrainingData()
            {
                return await dbcontext.TblTraining.Select(s => new TrainingViewModel() 
                  { Name = s.Name, StartDate = s.StartDate.ToString(), 
                    EndDate = s.EndDate.ToString() }).ToListAsync();
            }
    
            public TrainingViewModel GetTrainingData(string name)
            {
                var s = dbcontext.TblTraining.FirstOrDefault(d => d.Name == name);
                if (s == null)
                {
                    return null;
                }
    
                return new TrainingViewModel { Name = s.Name, 
                  StartDate = s.StartDate.ToString(), EndDate = s.EndDate.ToString() };
            }
    
            public async void Delete(string name)
            {
                var s = dbcontext.TblTraining.FirstOrDefault(d => d.Name == name);
                if (s == null)
                {
                    return;
                }
    
                dbcontext.TblTraining.Remove(s);
                await dbcontext.SaveChangesAsync();
            }
    
            public bool CreateTraining(TrainingViewModel viewModel)
            {
                try
                {
                    var start = DateTime.Parse(viewModel.StartDate);
                    var end = DateTime.Parse(viewModel.EndDate);
                    if (start < end)
                    {
                        return false;
                    }
    
                    dbcontext.TblTraining.Add(new TblTraining() 
                        { Name = viewModel.Name, StartDate = start, EndDate = end });
                    int count = dbcontext.SaveChanges();
                    if (count > 0)
                    {
                        return true;
                    }
    
                    return false;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }
    
     public class LocalTrainingServices : ITrainingServices
        {
            private static readonly List<TrainingViewModel> 
                      trainingViewModels = new List<TrainingViewModel>()
            {
                new TrainingViewModel() 
                    {Name = "c#", StartDate = "2012-01-01", EndDate = "2012-12-01"},
                new TrainingViewModel() 
                    {Name = "java", StartDate = "2013-01-01", EndDate = "2014-12-01"},
                new TrainingViewModel() 
                    {Name = "python", StartDate = "2018-01-01", EndDate = "2018-12-01"},
            };
    
            public Task<IList<TrainingViewModel>> GetTrainingData()
            {
                return new Task<IList<TrainingViewModel>>(() => trainingViewModels);
            }
    
            public TrainingViewModel GetTrainingData(string name) =>
                trainingViewModels.FirstOrDefault(d => d.Name == name);
    
            public void Delete(string name)
            {
                var s = trainingViewModels.FirstOrDefault(d => d.Name == name);
                if (s == null)
                {
                    return;
                }
    
                trainingViewModels.Remove(s);
            }
    
            public bool CreateTraining(TrainingViewModel viewModel)
            {
                if (GetTrainingData(viewModel.Name) == null)
                {
                    trainingViewModels.Add(viewModel);
                    return true;
                }
    
                return false;
            }
        }
  7. Now, let's start the real implementation of REST API/web service. Create a new controller by right clicking on Controller folder => Add => Controller. Select the one which matches our case; if you want to map your model entity with controller directly, then you can choose the last option; i.e., API controller with action using entity framework. But in my case, I am taking API controller with read/write actions. Select name TrainingController as this will be used in the final URL name to access the service.

    Image 4

    Main Controller Class.

    [Route("api/[controller]")]
        [ApiController]
        public class TrainingController : ControllerBase
        {
            private readonly ITrainingServices _trainingService;
    
            public TrainingController(ITrainingServices trainingService)
            {
                _trainingService = trainingService;
            }
    
            // GET: api/Training
            [HttpGet]
            public async Task<IEnumerable<TrainingViewModel>> Get()
            {
                return await _trainingService.GetTrainingData();
            }
    
            // GET: api/Training/5
            [HttpGet("{name}", Name = "Get")]
            public TrainingViewModel Get(string name)
            {
                return _trainingService.GetTrainingData(name);
            }
    
            // POST: api/Training
            [HttpPost]
            public void Post(TrainingViewModel value)
            {
                if (!_trainingService.CreateTraining(value))
                {
                    ModelState.AddModelError
                            (value.Name, "End date is greater than start date");
                }
            }
    
            // DELETE: api/ApiWithActions/5
            [HttpDelete("{name}")]
            public void Delete(string name)
            {
                _trainingService.Delete(name);
            }
        }
  8. Well, we are done with web service using REST (i.e., HttpGet, HttpPost, etc.) and you can deploy this service either in IIS or as locally from VS, select the service name and click on run green button. You must be finally getting the URL for this service something like https://localhost:yourportno[default:5001]/api/Training. Here, the item is training; i.e., controller name to access the service.

    Image 5

  9. Service is running now, let's try to consume this service. Before writing the UI part, let's check if the service is really working, you can use Chrome extension "ARC" or postman like tool to verify it. Just paste the URL and check the response.

    Image 6

  10. Let's write one small front end application to load this data in HTML and using JavaScript. Here is the code:
    <!DOCTYPE html>
    
    <html lang="en" xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta charset="utf-8" />
        <title>Training</title>
        <!--Styles from Bootstrap-->
        <link rel="stylesheet" 
    
        href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" 
    
        integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" 
    
        crossorigin="anonymous">
    </head>
    <body>
        <!--<header>Traning Details</header>-->
        <div id="header"></div>
        <main class="container">
            <table class="table">
            <thead>
                <tr>
                    <th>
                        Training Name
                    </th>
                    <th>
                        Start Date
                    </th>
                    <th>
                        End Date
                    </th>
                    <th></th>
                </tr>
            </thead>
            <tbody class="tbody">
            </tbody>
        </table>
    
        <div>
           <!--Get : Query string /url
            Post : Hidden fields-->
            <form method="get" id="form">
                <label>Training Name:</label>
                <input id="name" type="text" width="20"  
    
                placeholder="enter Traning name" required/> <br />
        
                <label>Start Date     :</label>
                <input id="startdate" type="datetime" 
    
                width="20"  data-provide="datepicker" /> <br />
        
                <label>End Date       :</label>
                <input id="enddate" type="datetime" 
    
                width="20" data-provide="datepicker"/><br />
    
                <input type="submit" value="Submit"/>
                
            </form>
        </div>
    
        <div id="footer"></div>
        <!--<footer>End of Tranings Details</footer>-->
        <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" 
    
        integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" 
    
        crossorigin="anonymous"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" 
    
        integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" 
    
        crossorigin="anonymous"></script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" 
    
        integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" 
    
        crossorigin="anonymous"></script>
    
        <!--JQuery-->
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js">
        </script>
    
        <script type="text/javascript" charset="utf8" 
    
        src="https://cdnjs.cloudflare.com/ajax/libs/jstree/3.3.3/jstree.min.js"></script>
        <script src="main.js"></script>
    
        <script>
            $('form').submit(function (e) {
                    e.preventDefault();
                    const data = {
                        Name: $('#name').val(),
                        Startdate: $('#startdate').val(),
                        EndDate: $('#enddate').val()
                    }
    
                    let tag = '<tr><td>' + data.Name + '</td>';
                    tag += '<td>' + data.Startdate + '</td>';
                    tag += '<td>' + data.Enddate + '</td></tr>';
                    $('table>tbody').append(tag);
    
                    const api = 'https://localhost:5001/api/training'
                    console.log(data);
                    $.post(api, { Name: data.Name, Startdate:data.Startdate, 
                                  EndDate:data.EndDate });
                });
        </script>
    
        </main>
    </body>
    </html>

    JavaScript:

    // JavaScript source code
    'use strict'
    $(document).ready(function ()
    {
        getpartials('header');
        getpartials('footer');
        getTraningData();
    });
    
    $('form').submit(function (e) {
        e.preventDefault();
        const data = {
            Name: $('#name').val(),
            Startdate: $('#startdate').val(),
            EndDate: $('#enddate').val()
        }
    
        let tag = '<tr><td>' + data.Name + '</td>';
        tag += '<td>' + data.Startdate + '</td>';
        tag += '<td>' + data.Enddate + '</td></tr>';
        $('table>tbody').append(tag);
    
        const api = 'https://localhost:5001/api/training'
        console.log(data);
        $.post(api, { Name: data.Name, Startdate:data.Startdate, EndDate:data.EndDate });
    });
    
    function getpartials(type)
    {
        $.get('partials/' + type + '.html')
            .then(function (data)
        {
            $(type).html(data);
        }).catch(function (err)
        {
            console.log(err);
        });
    }
    
    function getTraningData() {
        const api = 'https://localhost:5001/api/training'
        $.getJSON(api).then(function (data) {
            console.log(data);
            for (var i = 0, len = data.length; i < len; i++) {
    
                $('table>tbody').append(createTraining(data[i]));
                }
            }).catch(function (err) {
                console.log(err);
            });
    }
    
    function createTraining(training) {
        let tag = `
            <tr>
                <td>
                    ${training.name}
                </td>
                <td>
                    ${training.startDate}
                </td>
                <td>
                   ${training.endDate}
                </td>
            </tr>
        `;
    
        return tag;
    }
  11. Final output with SQL server data.

    Image 7

Points of Interest

Here, we learned end to end solution working with Micro services and front end application consuming it.

History

  • 28th September, 2019: 1.0

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ashutossh
Architect SE
India India
a hardcore programmer cum architect and a learner in area of full-stack programming

Comments and Discussions

 
PraiseBest article Pin
shabrin sheik21hrs 48mins ago
membershabrin sheik21hrs 48mins ago 
GeneralRe: Best article Pin
Ashutossh21hrs 47mins ago
professionalAshutossh21hrs 47mins ago 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 28 Sep 2019

Stats

2.5K views
113 downloads
9 bookmarked