Enabling ADO.net in ASP.NET Core MVC, Read Custom Property from appsettings.json and display the record in EmberJS

Rajnilari2015
Posted by in ASP.NET Core category on for Beginner level | Points: 250 | Views : 652 red flag
Rating: 4 out of 5  
 1 vote(s)

In this article, we will look into how to enable ADO.NET in ASP.NET Core MVC and present the record using Ember.js. In the process, we will learn how to create and read property values from "appsettings.json" file because ConfigurationManager has been removed from ASP.NET Core applications. WebAPI will act as the middleware.


 Download source code for Enabling ADO.net in ASP.NET Core MVC, Read Custom Property from appsettings.json and display the record in EmberJS

Introduction

Ember.js is an open source JavaScript client-side framework for developing the web applications and uses the MVC(Model-View-Controller) architecture pattern. It uses handlebar template as view, route is used as model and controller manipulates the data in the model. ADO.net is a data accessing technique by which the .net application can interact with the database.In this article, we will look into how to enable ADO.NET in ASP.NET Core MVC and present the record using Ember.js. In the process, we will learn how to create and read property values from "appsettings.json" file because ConfigurationManager has been removed from ASP.NET Core applications. WebAPI will act as the middle ware.

Creating the WebAPI project

Fire up Visual Studio 2015 and then File -> New -> Project -> Web -> ASP.NET Core Web Applications(.NET Core).Choose the target Framework as .Net Framework 4.6.1.

The next screen that pops up display's a list of templates and we need to choose Web API template.

ASP.NET Core 1.0 includes only Web API and MVC but not SignalR or Web Pages yet. We choose the Web API and Click "OK" button and here is our "Welcome Screen"

From the Nuget Package Manager, issue the below command

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Create and Read Custom Property from appsettings.json

Let us first create a folder say "Configuration" and create a class "ConnectionStrings.cs". The "ConnectionStrings" class content is as under

namespace WebAPI_Asp.netCore.Configuration
{
    public class ConnectionStrings
    {
        public string DbConnection { get; set; }       
    }
}

We created a scalar property "DbConnection" to hold our Database Connection String settings. Now we will add the below code to our appsettings.json file

"ConnectionStrings": {
    "DbConnection": "data source=YOURSERVERNAME;database=YOURDATABASENAME;user id=USERNAME;password=DBPASSWORD$"
  }

The complete appsettings.json file looks as under

{
  "ConnectionStrings": {
    "DbConnection": "data source=YOURSERVERNAME;database=YOURDATABASENAME;user id=USERNAME;password=DBPASSWORD$"
  },

  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  }
}

Configuring the Services in Startup.cs

The Startup.cs is where we define all of our settings for our application for pipelining using middleware. In the ConfigureServices method,for our custom configuration to work properly, we need some additional services as shown under which we have implemented by using the Options Pattern

public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddMvc();
	
    // Setup options with DI
    services.AddOptions();            

    // Configure ConnectionStrings using config
    services.Configure<ConnectionStrings>(Configuration.GetSection("ConnectionStrings"));
    

    services.AddSingleton<IDBOperation, DBOperations>();           

    //Add the CORS services 
    services.AddCors();
}

The AddOptions() is the setup for allowing IOptions<T> to be injected into the code. By using the Configure method, we register the ConnectionStrings section in the appsettings.json file and it will hold the ConnectionStrings object data.

Create the Model

Now let us first create the Employee model as under inside the "Model" folder

namespace WebApplication1.Models
{
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string EmployeeName { get; set; }
        public string EmailAddress { get; set; }
        public string PhoneNumber { get; set; }
    }
}

Our Employee model is a simple Employee Class which has some scalar properties like EmployeeID,EmployeeName,EmailAddress,PhoneNumber.

Create the Repositories

Now let us create the IDBOperation interface inside the "Repository" folder that will define the operation.

using System.Collections.Generic;
using WebAPI_Asp.netCore.Model;

namespace WebAPI_Asp.netCore.Repository
{
    public interface IDBOperation
    {
        List<Employee> GetEmployees();

    }        
}

The concrete operation for the same is defined in the DBOperations class. It has a method "GetEmployees" that uses ADO.NET and Stored Procedures for data retrival. Also here we will look into how to read our Custom Property from appsettings.json.

using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using WebAPI_Asp.netCore.Configuration;
using WebAPI_Asp.netCore.Model;

namespace WebAPI_Asp.netCore.Repository
{
    public class DBOperations : IDBOperation
    {
        private readonly ConnectionStrings connectionStrings;


        public DBOperations(IOptions<ConnectionStrings> options)
        {
            connectionStrings = options.Value;
        }

        /// <summary>
        /// Function: GetEmployees
        /// Get Employee Records from Database
        /// </summary>
        /// <returns></returns>

        public List<Employee> GetEmployees()
        {
            List<Employee> lstEmployees = new List<Employee>();
            try
            {
                string ConnectionPath = connectionStrings.DbConnection;

                using (var sqlCon = new SqlConnection(ConnectionPath))
                {
                    using (SqlCommand cmd = new SqlCommand("usp_GetEmployees", sqlCon))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        sqlCon.Open();

                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {

                            while (dr.Read())
                            {
                                lstEmployees
                                    .Add
                                    (
                                        new Employee
                                        {
                                            EmployeeID = Convert.ToInt32(dr["EmployeeID"].ToString())
                                             ,
                                            EmployeeName = dr["EmployeeName"].ToString()
                                             ,
                                            EmailAddress = dr["EmailAddress"].ToString()
                                             ,
                                            PhoneNumber = dr["PhoneNumber"].ToString()
                                        }

                                    );
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return lstEmployees;
        }
    }
}

Our custom configuration is now available through dependency injection. We require the class constructor to take on the IOptions<ConnectionStrings> since we are injecting the dependency through the class constructor.

DB Script

USE [YOURDB]
GO
/****** Object:  Table [dbo].[tblEmployees]    Script Date: 9/6/2016 10:48:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblEmployees](
	[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeName] [varchar](50) NOT NULL,
	[EmailAddress] [varchar](50) NOT NULL,
	[PhoneNumber] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tblEmployees] ON 

GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (1, N'Name1', N'Email1@test.com', N'493075244')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (2, N'Name2', N'Email2@test.com', N'1321353291')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (3, N'Name3', N'Email3@test.com', N'1910367622')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (4, N'Name4', N'Email4@test.com', N'1576620524')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (5, N'Name5', N'Email5@test.com', N'411133143')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (6, N'Name6', N'Email6@test.com', N'122444906')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (7, N'Name7', N'Email7@test.com', N'1902521141')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (8, N'Name8', N'Email8@test.com', N'1670140462')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (9, N'Name9', N'Email9@test.com', N'1669995904')
GO
INSERT [dbo].[tblEmployees] ([EmployeeID], [EmployeeName], [EmailAddress], [PhoneNumber]) VALUES (10, N'Name10', N'Email10@test.com', N'1127644659')
GO
SET IDENTITY_INSERT [dbo].[tblEmployees] OFF
GO
/****** Object:  StoredProcedure [dbo].[usp_GetEmployees]    Script Date: 9/6/2016 10:48:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- exec [dbo].[usp_GetEmployees]
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetEmployees] 
	-- Add the parameters for the stored procedure here	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 
			EmployeeID
			,EmployeeName
			,EmailAddress
			,PhoneNumber
	FROM [dbo].[tblEmployees]
END
GO

Create the Controller

Now expand the Controllers folder and create EmployeeController.cs and add the following code

using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using WebAPI_Asp.netCore.Model;
using WebAPI_Asp.netCore.Repository;

namespace WebAPI_Asp.netCore.Controllers
{
    [Route("EmployeeRecords")]
    public class EmployeeController : Controller
    { 
        public EmployeeController(IDBOperation dbOps)
        {
            DBRecords = dbOps;
        }       
        public IDBOperation DBRecords { get; set; }

        [HttpGet]
        [Route("GetEmployees")]        
        public List<Employee> GetEmployees()
        {
            return DBRecords.GetEmployees();            
        }
    }
}

Run the application and test through postman

Viewing through Ember.JS

Let us craete a "index.html" and add the below

<!DOCTYPE html>
  <html>
     <head> 
        <script src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.min.js"></script>
        <script src="https://code.jquery.com/jquery-2.1.3.min.js"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/ember.js/1.10.0/ember.min.js"></script>
        <script src="http://builds.emberjs.com/tags/v1.10.0-beta.3/ember-template-compiler.js"></script>
        <script src="http://builds.emberjs.com/release/ember.debug.js"></script>
        <script src="http://builds.emberjs.com/beta/ember-data.js"></script>
     </head>
     <body>

        <script type="text/x-handlebars">
           {{outlet}}
        </script>

        <script type="text/x-handlebars" data-template-name="index"> 
        <table border="1"> 
              <tr>
              	  <th>EmployeeID</th>
                  <th>EmployeeName</th>
                  <th>EmailAddress</th>
                  <th>PhoneNumber</th>
              </tr>
              {{#each employee in App.employeeArray}}
              <tr>
                  <td>{{employee.employeeID}}</td>
                  <td>{{employee.employeeName}}</td>
      			   <td>{{employee.emailAddress}}</td>   
      			   <td>{{employee.phoneNumber}}</td>             
              </tr>
              {{/each}}
        </table>  

        </script>

        <script type="text/javascript">
          var App = Ember.Application.create()
		     	App.employeeArray = null;

			   var uRL = 'http://localhost:7625/EmployeeRecords/GetEmployees';
               
                $.ajax({
                type: "get",
                url: uRL,
                cache: false,
                data: {},
                async:   false,
                success: function (data) { 
                             
                    App.employeeArray = data;
                },
                error: function (err) {               
                    alert(err);
                }
            }); 
        </script>
     </body>
  </html>

We are using JQuery's "ajax" method for the WebAPI call and displaying them through the Handlebars expressions by using the curly braces: {{}}. Here {{outlet}} is the placeholder that the router fills the appropriate template. The result is as under

References

Configuration
EmberJS

Conclusion

In this article we have learnt

  1. Enabling ADO.NET in ASP.NET Core MVC
  2. Read Custom Property from appsettings.json
  3. Display the record in EmberJS

Hope this will be useful. Thanks for reading. Zipped file attached.

Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)