In this article, we will walk you through creating ajax call in javascript and fetching the employee data from MSSQL database.

We will do this demo in ASP.net core covering the following aspects

1. Managing the connection string in new setup provided by core
2. Using DBContext Entity Framework
3. jQuery Ajax call to fetch employee details
4. Calling the ajax method on page load

Let’s get started by creating a new ASP.net core application. Launch VS 2017, select File from the top Menu and click New-> Project

Select ASP.NET Core Web Application from the Project Template window (Refer below screenshot). Enter the Project name, select the directory and click OK button

Once you click on OK button, below dialog box will be displayed to select the framework and version along with the type of Project to be developed

Select .Net Core & ASP.net Core 2.1 & Above and Web Application (Model-View-Controller) project type. Make sure your “Configure for HTTPS” checkbox is un-checked and click OK

Setup the Package

By default Core will install the dependencies packages however we need System.Configuration.ConfigurationManager package to be installed that will enable us to read the user defined values from configuration file ( in our case appSetting.json)

Database setup

We have created table named as “Employees” with the following fields

1. EmployeeId
2. Employeename
3. Mobile

Entity class

Create entity class that will hold the data fetched from database. Right click on Models and select Add new class. Name it as Employees and add the below properties

Setup the DBContext and Connection String

Now we will setup the required class and configuration settings to access the database and records using the DBContext class.

Right click on Models folder and select new class and name it as “MyDBContext”. This class created will inherit the DBContext class and create method to class employee data from database.

public MyDBContext(DbContextOptions<MyDBContext> options) : base(options)
{
}

//Method to Pass the Entity Class and Get the data from Database 
public DbSet<Employees> Employees { get; set; }

Once our method is setup, we will then add the connection string in the appSetting.json file and make changes in the startup.cs class

Once the connection string is added in the appSettings, the same need to be called from configureservice method provided in startup.cs class.

public void ConfigureServices(IServiceCollection services)
{
      services.Configure<CookiePolicyOptions>(options =>
      {
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
      });

//DefaultConnection is the connection string name mentioned in appSetting.json 
       services.AddDbContext<MyDBContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
}

Setup the MVC component and fire jQuery Ajax call

Now lets add the controller method and create view the display the record using the jQuery Ajax call

Right click on controllers folder and add new controller class as “default”. Create a IActionResult method that will return View() and GetEmpData() that will return the data in jSON object. The GetEmpData() method will be called by jQuery Ajax method to fetch data and display in tabular format.

public IActionResult GetAllEmployees()
{
    return View();
}
public JsonResult GetEmpData()
{         
     return Json(_context.Employees.ToList());
}

The GetEmpData() method will invoke the Employee method created in MyDBContext class

So far we have setup the connection string, method to fetch the data and now its time we create the view that will make the jquery Ajax call and display the data in tabular format.

Right click on GetAllEmployees() IActionResult in default.cs controller class and select Add View.

View page will GetAllEmployees.cshtml will be added under the Views Folder. Open the HTML editor and insert the following code snippet

The script written within the <Scripts> tag actually performs the main function to make an ajax call by passing the controller name and method, type (POST), interpreting the response, received in json format and finally creating html tabular format to display the record in view page.

The final output of the above code implemented in our example is pasted below

We are also going to cover CRUD operations using ASP.net core platform in our next article so please stay tuned and do subscribe our newsletters for more insightful articles.

Hope you liked the article and please do subscribe to receive such articles posted on Digital TechJoint and click here to subscribe to our YouTube channel.

Thanks for visiting DigitalTechJoint.com