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.
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
- Enabling ADO.NET in ASP.NET Core MVC
- Read Custom Property from appsettings.json
- Display the record in EmberJS
Hope this will be useful. Thanks for reading. Zipped file attached.